ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- -- this test is for logical operators (AND, OR, etc.) -- -- create a table. Logical operators work on the results of comparisons, -- which are tested in a separate test, so the types of the columns being -- compared are irrelevant here. create table t (x int, y int); 0 rows inserted/updated/deleted ij> -- insert some values, including nulls insert into t values (1, 1); 1 row inserted/updated/deleted ij> insert into t values (1, 2); 1 row inserted/updated/deleted ij> insert into t values (2, 1); 1 row inserted/updated/deleted ij> insert into t values (2, 2); 1 row inserted/updated/deleted ij> insert into t values (null, 2); 1 row inserted/updated/deleted ij> insert into t values (1, null); 1 row inserted/updated/deleted ij> insert into t values (null, null); 1 row inserted/updated/deleted ij> -- basic AND test select x, y from t where x = 1 and y = 2; X |Y ----------------------- 1 |2 ij> select x, y from t where y = 2 and x = 1; X |Y ----------------------- 1 |2 ij> select x, y from t where x = 1 and y = 3; X |Y ----------------------- ij> select x, y from t where y = 3 and x = 1; X |Y ----------------------- ij> create table s (x int); 0 rows inserted/updated/deleted ij> insert into s values (1); 1 row inserted/updated/deleted ij> -- there is no short-circuiting with AND: ie when the first operand is FALSE, -- the second operant still got evaluated for AND. Same behavior in DB2 as well. select x from s where x = 5 and 2147483647 + 10 = 2; ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> -- Does not matter it in what order the 2 operands are. Both of them always gets evaluated. select x from s where 2147483647 + 10 = 2 and x = 5; ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> -- Now try a chain of ANDs select x, y from t where x = 1 and x + 0 = 1 and y = 2 and y + 0 = 2; X |Y ----------------------- 1 |2 ij> -- basic OR test select x, y from t where x = 1 or y = 2; X |Y ----------------------- 1 |1 1 |2 2 |2 NULL |2 1 |NULL ij> select x, y from t where y = 2 or x = 1; X |Y ----------------------- 1 |1 1 |2 2 |2 NULL |2 1 |NULL ij> select x, y from t where x = 4 or y = 5; X |Y ----------------------- ij> select x, y from t where y = 5 or x = 4; X |Y ----------------------- ij> -- test short-circuiting: for OR, when the first operand is TRUE, the second -- operand should not be evaluated. We test this by deliberately causing an -- error in one of the operands. select x from s where x = 1 or 2147483647 + 10 = 2; X ----------- 1 ij> -- Now try it with the error on the left, just to be sure the error really -- happens. select x from s where 2147483647 + 10 = 2 or x = 1; X ----------- ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> -- Now try a chain of ORs select x, y from t where x = 1 or x + 0 = 1 or y = 2 or y + 0 = 2; X |Y ----------------------- 1 |1 1 |2 2 |2 NULL |2 1 |NULL ij> -- Test the precedence of AND versus OR. AND is supposed to have a higher -- precedence that OR, i.e. "a OR b AND c" is equivalent to "a OR (b AND c)" -- First test TRUE OR TRUE AND FALSE. This should evaluate to TRUE. If -- the precedence is wrong, it will evaluate to FALSE. select x from s where (1 = 1) or (2 = 2) and (3 = 4); X ----------- 1 ij> -- Now test FALSE AND TRUE OR TRUE. This should evaluate to to TRUE. If -- the precedence is wrong, it will evaluate to FALSE. select x from s where (1 = 2) and (3 = 3) or (4 = 4); X ----------- 1 ij> -- Now test whether parenthesized expressions work. Use the parentheses to -- force the OR to be evaluated before the AND. select x from s where ( (1 = 1) or (2 = 2) ) and (3 = 4); X ----------- ij> select x from s where (1 = 2) and ( (3 = 3) or (4 = 4) ); X ----------- ij> -- More involved testing of expression normalization -- Ands under ands under ands ... select * from s where ( ( ( ((1=1) and (1=1)) and ((1=1) and (1=1)) ) and ( ((1=1) and (1=1)) and ((1=1) and (1=1)) ) ) and ( ( ((1=1) and (1=1)) and ((1=1) and (1=1)) ) and ( ((1=1) and (1=1)) and ((1=1) and (1=1)) ) ) ); X ----------- 1 ij> -- Ors under ors under ors ... select * from s where ( ( ( ((1=1) or (1=1)) or ((1=1) or (1=1)) ) or ( ((1=1) or (1=1)) or ((1=1) or (1=1)) ) ) or ( ( ((1=1) or (1=1)) or ((1=1) or (1=1)) ) or ( ((1=1) or (1=1)) or ((1=1) or (1=1)) ) ) ); X ----------- 1 ij> -- Ands under ors under ors ... select * from s where ( ( ( ((1=1) and (1=1)) or ((1=1) and (1=1)) ) or ( ((1=1) and (1=1)) or ((1=1) and (1=1)) ) ) or ( ( ((1=1) and (1=1)) or ((1=1) and (1=1)) ) or ( ((1=1) and (1=1)) or ((1=1) and (1=1)) ) ) ); X ----------- 1 ij> -- Ors under ands under ands select * from s where ( ( ( ((1=1) or (1=1)) and ((1=1) or (1=1)) ) and ( ((1=1) or (1=1)) and ((1=1) or (1=1)) ) ) and ( ( ((1=1) or (1=1)) and ((1=1) or (1=1)) ) and ( ((1=1) or (1=1)) and ((1=1) or (1=1)) ) ) ); X ----------- 1 ij> -- left deep with ands select * from s where ( ( ( ( ( ((1=1) and (1=1)) and (1=1)) and (1=1)) and (1=1)) and (1=1)) and (1=1)); X ----------- 1 ij> -- left deep with ors select * from s where ( ( ( ( ( ((1=1) or (1=1)) or (1=1)) or (1=1)) or (1=1)) or (1=1)) or (1=1)); X ----------- 1 ij> select * from s where ( ( ( ( ( ((1=1) or (1=2)) or (1=2)) or (1=2)) or (1=2)) or (1=2)) or (1=2)); X ----------- 1 ij> select * from s where ( ( ( ( ( ((1=2) or (1=2)) or (1=1)) or (1=2)) or (1=2)) or (1=2)) or (1=2)); X ----------- 1 ij> select * from s where ( ( ( ( ( ((1=2) or (1=2)) or (1=2)) or (1=1)) or (1=2)) or (1=2)) or (1=2)); X ----------- 1 ij> select * from s where ( ( ( ( ( ((1=2) or (1=2)) or (1=2)) or (1=2)) or (1=1)) or (1=2)) or (1=2)); X ----------- 1 ij> select * from s where ( ( ( ( ( ((1=2) or (1=2)) or (1=2)) or (1=2)) or (1=2)) or (1=1)) or (1=2)); X ----------- 1 ij> select * from s where ( ( ( ( ( ((1=2) or (1=2)) or (1=2)) or (1=2)) or (1=2)) or (1=2)) or (1=1)); X ----------- 1 ij> -- right deep with ors select * from s where ( ( ( ( ( (1=1) or ((1=2) or (1=2)) ) or (1=2)) or (1=2)) or (1=2)) or (1=2)); X ----------- 1 ij> select * from s where ( ( ( ( ( (1=2) or ((1=1) or (1=2)) ) or (1=2)) or (1=2)) or (1=2)) or (1=2)); X ----------- 1 ij> select * from s where ( ( ( ( ( (1=2) or ((1=2) or (1=1)) ) or (1=2)) or (1=2)) or (1=2)) or (1=2)); X ----------- 1 ij> select * from s where ( ( ( ( ( (1=2) or ((1=2) or (1=2)) ) or (1=1)) or (1=2)) or (1=2)) or (1=2)); X ----------- 1 ij> select * from s where ( ( ( ( ( (1=2) or ((1=2) or (1=2)) ) or (1=2)) or (1=1)) or (1=2)) or (1=2)); X ----------- 1 ij> select * from s where ( ( ( ( ( (1=2) or ((1=2) or (1=2)) ) or (1=2)) or (1=2)) or (1=1)) or (1=2)); X ----------- 1 ij> select * from s where ( ( ( ( ( (1=2) or ((1=2) or (1=2)) ) or (1=2)) or (1=2)) or (1=2)) or (1=1)); X ----------- 1 ij> -- ... and false and ... should get resolved to false select x from s where 2147483647 + 10 = 2 and (1=2); ERROR 22003: The resulting value is outside the range for the data type INTEGER. ij> select x from s where (1=2) and 2147483647 + 10 = 2; X ----------- ij> -- nots select x from s where not ( (1 = 1) or (2 = 2) ) and (3 = 4); X ----------- ij> select x from s where not ( ( (1 = 1) or (2 = 2) ) and (3 = 4) ); X ----------- 1 ij> select x from s where (1 = 2) and not ( (3 = 3) or (4 = 4) ); X ----------- ij> select x from s where not ( (1 = 2) and ( (3 = 3) or (4 = 4) ) ); X ----------- 1 ij> -- following NOTs in select clause won't work because it results in a transient boolean datatype select ( not ( (1 = 1) or (2 = 2) ) and (3 = 4) ) from s; ERROR 42X01: Syntax error: Encountered "not" at line 2, column 10. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- select ( not ( ( (1 = 1) or (2 = 2) ) and (3 = 4) ) ) from s; ERROR 42X01: Syntax error: Encountered "not" at line 2, column 10. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- select ( (1 = 2) and not ( (3 = 3) or (4 = 4) ) ) from s; ERROR 42X01: Syntax error: Encountered "=" at line 2, column 13. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- select ( not ( (1 = 2) and ( (3 = 3) or (4 = 4) ) ) ) from s; ERROR 42X01: Syntax error: Encountered "not" at line 2, column 10. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- Ands under ands under ands ... select * from s where not ( ( ( ((1=1) and (1=1)) and ((1=1) and (1=1)) ) and ( ((1=1) and (1=1)) and ((1=1) and (1=1)) ) ) and ( ( ((1=1) and (1=1)) and ((1=1) and (1=1)) ) and ( ((1=1) and (1=1)) and ((1=1) and (1=2)) ) ) ); X ----------- 1 ij> -- Ors under ors under ors ... select * from s where not ( ( ( ((1=1) or (1=1)) or ((1=1) or (1=1)) ) or ( ((1=1) or (1=1)) or ((1=1) or (1=1)) ) ) or ( ( ((1=1) or (1=1)) or ((1=1) or (1=1)) ) or ( ((1=1) or (1=1)) or ((1=1) or (1=2)) ) ) ); X ----------- ij> -- Ands under ors under ors ... select * from s where not ( ( ( ((1=1) and (1=1)) or ((1=1) and (1=1)) ) or ( ((1=1) and (1=1)) or ((1=1) and (1=1)) ) ) or ( ( ((1=1) and (1=1)) or ((1=1) and (1=1)) ) or ( ((1=1) and (1=1)) or ((1=1) and (1=2)) ) ) ); X ----------- ij> -- Ors under ands under ands select * from s where not ( ( ( ((1=1) or (1=1)) and ((1=1) or (1=1)) ) and ( ((1=1) or (1=1)) and ((1=1) or (1=1)) ) ) and ( ( ((1=1) or (1=1)) and ((1=1) or (1=1)) ) and ( ((1=1) or (1=1)) and ((1=1) or (1=2)) ) ) ); X ----------- ij> -- left deep with ands select * from s where not ( ( ( ( ( ((1=1) and (1=2)) and (1=1)) and (1=1)) and (1=1)) and (1=1)) and (1=1)); X ----------- 1 ij> -- left deep with ors select * from s where not ( ( ( ( ( ((1=2) or (1=1)) or (1=1)) or (1=1)) or (1=1)) or (1=1)) or (1=1)); X ----------- ij> select * from s where not ( ( ( ( ( ((1=2) or (1=2)) or (1=2)) or (1=2)) or (1=2)) or (1=2)) or (1=2)); X ----------- 1 ij> select * from s where not ( ( ( ( ( ((1=2) or (1=2)) or (1=1)) or (1=2)) or (1=2)) or (1=2)) or (1=2)); X ----------- ij> select * from s where not ( ( ( ( ( ((1=2) or (1=2)) or (1=2)) or (1=1)) or (1=2)) or (1=2)) or (1=2)); X ----------- ij> select * from s where not ( ( ( ( ( ((1=2) or (1=2)) or (1=2)) or (1=2)) or (1=1)) or (1=2)) or (1=2)); X ----------- ij> select * from s where not ( ( ( ( ( ((1=2) or (1=2)) or (1=2)) or (1=2)) or (1=2)) or (1=1)) or (1=2)); X ----------- ij> select * from s where not ( ( ( ( ( ((1=2) or (1=2)) or (1=2)) or (1=2)) or (1=2)) or (1=2)) or (1=1)); X ----------- ij> -- right deep with ors select * from s where not ( ( ( ( ( (1=1) or ((1=2) or (1=2)) ) or (1=2)) or (1=2)) or (1=2)) or (1=2)); X ----------- ij> select * from s where not ( ( ( ( ( (1=2) or ((1=2) or (1=2)) ) or (1=2)) or (1=2)) or (1=2)) or (1=2)); X ----------- 1 ij> select * from s where not ( ( ( ( ( (1=2) or ((1=2) or (1=1)) ) or (1=2)) or (1=2)) or (1=2)) or (1=2)); X ----------- ij> select * from s where not ( ( ( ( ( (1=2) or ((1=2) or (1=2)) ) or (1=1)) or (1=2)) or (1=2)) or (1=2)); X ----------- ij> select * from s where not ( ( ( ( ( (1=2) or ((1=2) or (1=2)) ) or (1=2)) or (1=1)) or (1=2)) or (1=2)); X ----------- ij> select * from s where not ( ( ( ( ( (1=2) or ((1=2) or (1=2)) ) or (1=2)) or (1=2)) or (1=1)) or (1=2)); X ----------- ij> select * from s where not ( ( ( ( ( (1=2) or ((1=2) or (1=2)) ) or (1=2)) or (1=2)) or (1=2)) or (1=1)); X ----------- ij> -- nots on nots select * from s where not ( not ( not ((1=2) or (1=1))) or (not ((1=2) or (1=2)) ) ); X ----------- ij> -- following nots on nots won't work because they result in transient boolean datatype in the select clause select not ( not ( not ((1=2) or (1=1))) or (not ((1=2) or (1=2)) ) ) from s; ERROR 42X01: Syntax error: Encountered "not" at line 2, column 8. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> -- negative testing -- non boolean where clauses select * from s where 1; ERROR 42X19: The WHERE or HAVING clause or CHECK CONSTRAINT definition is a 'INTEGER' expression. It must be a BOOLEAN expression. ij> select * from s where 1 and (1=1); ERROR 42Y94: An AND or OR has a non-boolean operand. The operands of AND and OR must evaluate to TRUE, FALSE, or UNKNOWN. ij> select * from s where (1=1) and 1; ERROR 42Y94: An AND or OR has a non-boolean operand. The operands of AND and OR must evaluate to TRUE, FALSE, or UNKNOWN. ij> select * from s where 1 or (1=1); ERROR 42Y94: An AND or OR has a non-boolean operand. The operands of AND and OR must evaluate to TRUE, FALSE, or UNKNOWN. ij> select * from s where (1=1) or 1; ERROR 42Y94: An AND or OR has a non-boolean operand. The operands of AND and OR must evaluate to TRUE, FALSE, or UNKNOWN. ij> select * from s where not 1; ERROR 42X40: A NOT statement has an operand that is not boolean . The operand of NOT must evaluate to TRUE, FALSE, or UNKNOWN. ij> -- Clean up drop table t; 0 rows inserted/updated/deleted ij> drop table s; 0 rows inserted/updated/deleted ij>