{"id":892,"date":"2015-12-11T13:29:20","date_gmt":"2015-12-11T12:29:20","guid":{"rendered":"http:\/\/www.pitss.org\/blog\/?p=892"},"modified":"2017-09-11T15:10:49","modified_gmt":"2017-09-11T13:10:49","slug":"why-we-should-think-according-to-three-valued-logic-and-why-we-cant","status":"publish","type":"post","link":"https:\/\/pitss.org\/de\/why-we-should-think-according-to-three-valued-logic-and-why-we-cant\/","title":{"rendered":"Why we should think according to three-valued logic, and why we can’t…"},"content":{"rendered":"

Consider the following supplier and parts scenario (adapted from C. Date, 2005, Database in Depth: Relational Theory for Practitioners<\/em>): \u00a0A company has two suppliers S1 and S2. S1 is located in New York and supplies part P1, which is produced in Bangalore. Supplier S2, located in Berlin, supplies part P2 which is also produced in Bangalore. Assume further that the company introduces a new supplier policy which states that if the supplier is located in Paris, then its parts must be produced in a city different from Paris<\/em>. So the DB admin makes a mental note that whenever he adds a supplier from Paris he must check that the parts are not produced in Paris. Soon after the introduction of this policy the company closes a contract with a new supplier S3 based in Berlin, which is going to supply part P3. But incidentally, the supplier forgot to mention where the production site of P3 is. Since the supplier is not from Paris, the supplier policy will not be violated irrespective of where the part P3 will be produced. So the DB admin specifies the location of S3 as Berlin and leaves the location of P3 as NULL. His supplier and parts tables are now as following:
\n
\nSUPPLIER:
\nS_ID LOCATION
\nS1 New York
\nS2 London
\nS3 Berlin<\/code><\/p>\n

PARTS:
\nP_ID LOCATION\u00a0\u00a0\u00a0 S_ID
\nP1\u00a0\u00a0\u00a0\u00a0 Bangalore\u00a0\u00a0\u00a0S1
\nP2\u00a0\u00a0\u00a0\u00a0 Bangalore\u00a0\u00a0\u00a0S2
\nP3\u00a0\u00a0\u00a0\u00a0 NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0S3<\/p>\n

A few days later the DB admin is asked to produce a list of all suppliers whose \u00a0parts location is not Paris or the parts location differs from the supplier location. He writes the following SELECT statement
\n
\nSELECT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 s_id
\nFROM \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0supplier, parts
\nWHERE \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 supplier.s_id = parts.s_id AND
\n(parts.location <> \u2018Paris\u2019 OR parts.location <> supplier.location)
\n<\/code>
\nand reasons as follows: S1 supplies P1 located in Bangalore, which differs from Paris, so the first disjunct part.location <> \u2018Paris\u2019 evaluates to true, so the WHERE clause is true of S1 and P1, and consequently S1 gets listed. S2 supplies P2 which is also located in Bangalore, so again the first disjunct part.location <> \u2018Paris\u2019 is true, so the WHERE clause is true of S2 and P2, and consequently S2 also gets listed. Finally, the DB admin reasons, the location of part P3 either is or isn\u2019t Paris. If it isn\u2019t Paris, then again the first disjunct is true, so the WHERE clause would be true of S3 and P3. And if the location of P3 is Paris, then it differs from the supplier S3 location, which is Berlin, so the second disjunct is true. Therefore the WHERE clause would be true whatever the actual location of part P3. So he runs the query, expecting to see all three suppliers S1, S2 and S3. But to his surprise S3 is not listed.<\/p>\n

After thinking about this for a moment, the DB admin smacks his forehead, and breathes a sigh of relief. \u2018Of course\u2019, he says, \u2018S3 should not be listed\u2019. Since the location of P3 is NULL, the disjunct parts.location <> \u2018Paris\u2019 will evaluate to UNKNOWN (or whatever you choose to call the third truth value), and the same goes for parts.location <> supplier.location. Consequently the WHERE clause evaluates to UNKNOWN for the tuple <S3, Berlin, P3, NULL, S3>, and therefore S3 won\u2019t be listed in the result set. But it\u2019s much harder to avoid such mistakes.<\/p>\n

The reason for this is that the way we think is not the way that SQL \u2018thinks\u2019. For us, either there is water on Mars, or there isn\u2019t (there is no third possibility), either the production site of part P3 is \u00a0Paris or it isn\u2019t. For SQL, however, there is a third way! Using three-valued logic means among other things that we \u00a0allow for a statement to be neither true nor false. \u00a0And therefore, we are not (!) allowed to assume that the production site of P3 either is or isn\u2019t Paris. To avoid his mistake, the DB admin should have tested also for the third case, where the truth value of \u2018The production site of P3 is Paris\u2019 is neither TRUE nor FALSE. And then he would have realized that in this case the WHERE clause is not true of S3 and P3.<\/p>\n

The moral of the story: \u00a0As long as SQL is based on three-valued logic, we also need to think according to the laws of three-valued logic. Questions which presuppose two-valued logic should not be formulated as queries which presuppose three-valued logic. If we do so, we shouldn’t be surprised if the results are counter-intuitive.<\/p>\n","protected":false},"excerpt":{"rendered":"

Consider the following supplier and parts scenario (adapted from C. Date, 2005, Database in Depth: Relational Theory for Practitioners): \u00a0A company has two suppliers S1 and S2. S1 is located in New York and supplies part P1, which is produced in Bangalore. Supplier S2, located in Berlin, supplies part P2 which is also produced in […]<\/p>\n","protected":false},"author":53,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[5,137,81],"tags":[],"class_list":["post-892","post","type-post","status-publish","format-standard","hentry","category-allgemein","category-developer","category-tech-blog"],"_links":{"self":[{"href":"https:\/\/pitss.org\/de\/wp-json\/wp\/v2\/posts\/892","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pitss.org\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pitss.org\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pitss.org\/de\/wp-json\/wp\/v2\/users\/53"}],"replies":[{"embeddable":true,"href":"https:\/\/pitss.org\/de\/wp-json\/wp\/v2\/comments?post=892"}],"version-history":[{"count":1,"href":"https:\/\/pitss.org\/de\/wp-json\/wp\/v2\/posts\/892\/revisions"}],"predecessor-version":[{"id":16764,"href":"https:\/\/pitss.org\/de\/wp-json\/wp\/v2\/posts\/892\/revisions\/16764"}],"wp:attachment":[{"href":"https:\/\/pitss.org\/de\/wp-json\/wp\/v2\/media?parent=892"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pitss.org\/de\/wp-json\/wp\/v2\/categories?post=892"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pitss.org\/de\/wp-json\/wp\/v2\/tags?post=892"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}