Geek Answers Handbook

Compiling SQL plans and truth tables

If I have NOT ( 1 <> 1 AND NULL <> 1 )

I see how SQL turns this into an XML execution plan: ( 1 = 1 OR NULL = 1)

If you literally evaluated the former expression, True AND Null would be Null and eliminate the string. However, a compiled expression may return a string due to OR.

Can I assume that this type of compilation is guaranteed to always execute? Will SQL Server never try to translate tangled logic into a compiled plan? Is there any documentation on this?

This article was very helpful, but I just skipped a piece of the puzzle: https://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/

Here is an example of SQL

 SELECT 1 FROM TT LEFT JOIN T2 T2 --t2 has zero rows ON T.id = t2.t_id WHERE NOT ( T.id <> 99 AND T2.id <> 99 )

From my experience with SQL, I know that under normal circumstances (without evaluating a short circuit) T2.id <> 99 effectively turns the left join into an internal join. This was the behavior that I originally expected. I was surprised when this filter really worked.

+7
null sql sql-server left-join
source share
2 answers

TL; DR "Compiled Result" is not a useful concept. It is important that the “specified result” is determined by the definition of the language. The DBMS should force the operator to act as you wrote it.

The truth [sic] table for AND in your link is incorrect. And with False, always False and OR with True is always True in SQL.

Comparison in SQL returns True, False, or Unknown. An unknown may arise from a comparison with NULL or 3VL logic logic (AND / OR / NOT, etc.) on Unknown. "NULL" is not literal. True, False and Unknown are values ​​with (assorted) literals in the SQL standard, but not in most DBMSs. (And Unknown can be returned as NULL.) IS is not a match; IS NULL and NOT NULL are 3Vl unary logical relationships, as well as similar names with TRUE, FALSE, and UNKNOWN. They always return True or False.

True AND Null will be True AND Null and will delete the line. However, a compiled expression may return a string due to OR.

Not. The truth [sic] table for AND in your link is incorrect. And with False, always False and OR with True is always True in SQL. So your AND is always False from NOT False from AND of False from 1 <> 1, and your OR is always True from 1 = 1. No matter what other comparisons return (True, False or Unknown). If you work through these two expressions using (correct) SQL truth tables) , they both always give the same result, True.

You have to be very careful about rewriting conditions in SQL. You can exchange NOT (E1 *comparison* E2) for E1 *NOT-comparison* E2 or NOT (E IS ?) And E IS NOT ? . You can safely rewrite the expression using standard logical identifiers / rules if no value is NULL. You can also safely apply rewrite rules to

  (E1 *comparison* E2) AND E1 IS NOT NULL AND E2 IS NOT NULL

Also be careful that you must correctly use the Unknown End Result, which includes not matching for WHERE, but not failing to limit.

 SELECT 1 FROM TT LEFT JOIN T2 T2 --t2 has zero rows ON T.id = t2.t_id WHERE NOT ( T.id <> 99 AND T2.id <> 99 )

LEFT JOIN returns INNER JOIN rows plus unsurpassed T rows extended by T2 NULL columns. (With T2 empty, INNER JOIN is empty, and all T lines are unmatched.) All extended lines have T2.id <> 99 Unknown, since T2.id is NULL. For T.id = 99, And is False, and NOT is true; WHERE returns all rows. For T1.id, any other integer or NULL, AND will be Unknown, NOT will be Unknown; WHERE does not return rows.

(There is no “short loop” evaluation in SQL. Each bundle argument must be defined.)

+4
source share

If you literally evaluated the former expression, True AND Null would be Null and eliminate the string.

Not. You evaluate the expression. NOT ( 1 <> 1 AND NULL <> 1 ) is NOT (FALSE AND UNKNOWN) is NOT FALSE is TRUE .

( 1 = 1 OR NULL = 1) - TRUE OR UNKNOWN - TRUE . They are both equivalent.

NOT ( 1 <> 1 AND NULL <> 1 ) can be rewritten as NOT ((NOT (1=1)) AND (NOT (NULL = 1))) . In the usual logic of values, there are two , De Morgan Laws , which can be rewritten as NOT (NOT ((1 = 1) OR (NULL = 1))) , and then (1=1) OR (NULL = 1) . As it turned out, De Morgan Laws also contains three-valued SQL logic. This can be demonstrated by creating comprehensive truth tables for the two laws.

A truth table showing that one of De Morgan's laws, (NOT A) OR (NOT B) equivalent to NOT (A AND B) , is contained in three-valued SQL logic:

 AB | (NOT A) OR (NOT B) | equiv? | NOT (A AND B) ======================================================== TT | FTFFT | T | FTTT TF | FTTTF | T | TTFF TU | FTUUU | T | UTUU ------------------------------------------------------- FT | TFTFT | T | TFFT FF | TFTTF | T | TFFF FU | TFTUU | T | TFFU ------------------------------------------------------- UT | UUUFT | T | UUUT UF | UUTTF | T | TUFF UU | UUUUU | T | UUUU

Similarly, another law (NOT A) AND (NOT B) equivalent to NOT (A OR B) .

Can I assume that this type of compilation will always be guaranteed?

No, specific compilations are never (almost never) guaranteed. Error checking in SQL Server, selected query plans, applied transformations return the results specified by the query.

Edited to add: Let T.id be 99 and T2.id be NULL . Then:

  • WHERE NOT ( T.id <> 99 AND T2.id <> 99 )
  • WHERE NOT (99 <> 99 AND NULL <> 99)
  • WHERE NOT (FALSE AND UNKNOWN)
  • WHERE NOT (FALSE)
  • WHERE TRUE
+3
source share

More articles:


All Articles