| -- |
| -- 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); |
| |
| -- insert some values, including nulls |
| insert into t values (1, 1); |
| insert into t values (1, 2); |
| insert into t values (2, 1); |
| insert into t values (2, 2); |
| insert into t values (null, 2); |
| insert into t values (1, null); |
| insert into t values (null, null); |
| |
| -- basic AND test |
| select x, y from t where x = 1 and y = 2; |
| select x, y from t where y = 2 and x = 1; |
| |
| select x, y from t where x = 1 and y = 3; |
| select x, y from t where y = 3 and x = 1; |
| |
| create table s (x int); |
| |
| insert into s values (1); |
| |
| -- 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; |
| |
| -- 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; |
| |
| -- 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; |
| |
| -- basic OR test |
| select x, y from t where x = 1 or y = 2; |
| select x, y from t where y = 2 or x = 1; |
| |
| select x, y from t where x = 4 or y = 5; |
| select x, y from t where y = 5 or x = 4; |
| |
| -- 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; |
| |
| -- 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; |
| |
| -- 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; |
| -- 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); |
| |
| -- 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); |
| |
| -- 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); |
| |
| select x from s where (1 = 2) and ( (3 = 3) or (4 = 4) ); |
| |
| -- 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)) ) ) ); |
| |
| -- 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)) ) ) ); |
| |
| -- 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)) ) ) ); |
| |
| -- 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)) ) ) ); |
| |
| -- 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)); |
| |
| -- 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)); |
| |
| select * from s where ( ( ( ( ( ((1=1) or (1=2)) or (1=2)) or (1=2)) or (1=2)) |
| or (1=2)) or (1=2)); |
| |
| select * from s where ( ( ( ( ( ((1=2) or (1=2)) or (1=1)) or (1=2)) or (1=2)) |
| or (1=2)) or (1=2)); |
| |
| select * from s where ( ( ( ( ( ((1=2) or (1=2)) or (1=2)) or (1=1)) or (1=2)) |
| or (1=2)) or (1=2)); |
| |
| select * from s where ( ( ( ( ( ((1=2) or (1=2)) or (1=2)) or (1=2)) or (1=1)) |
| or (1=2)) or (1=2)); |
| |
| select * from s where ( ( ( ( ( ((1=2) or (1=2)) or (1=2)) or (1=2)) or (1=2)) |
| or (1=1)) or (1=2)); |
| |
| select * from s where ( ( ( ( ( ((1=2) or (1=2)) or (1=2)) or (1=2)) or (1=2)) |
| or (1=2)) or (1=1)); |
| |
| -- 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)); |
| |
| select * from s where ( ( ( ( ( (1=2) or ((1=1) or (1=2)) ) or (1=2)) or (1=2)) |
| or (1=2)) or (1=2)); |
| |
| select * from s where ( ( ( ( ( (1=2) or ((1=2) or (1=1)) ) or (1=2)) or (1=2)) |
| or (1=2)) or (1=2)); |
| |
| select * from s where ( ( ( ( ( (1=2) or ((1=2) or (1=2)) ) or (1=1)) or (1=2)) |
| or (1=2)) or (1=2)); |
| |
| select * from s where ( ( ( ( ( (1=2) or ((1=2) or (1=2)) ) or (1=2)) or (1=1)) |
| or (1=2)) or (1=2)); |
| |
| select * from s where ( ( ( ( ( (1=2) or ((1=2) or (1=2)) ) or (1=2)) or (1=2)) |
| or (1=1)) or (1=2)); |
| |
| select * from s where ( ( ( ( ( (1=2) or ((1=2) or (1=2)) ) or (1=2)) or (1=2)) |
| or (1=2)) or (1=1)); |
| |
| -- ... and false and ... should get resolved to false |
| select x from s where 2147483647 + 10 = 2 and (1=2); |
| select x from s where (1=2) and 2147483647 + 10 = 2; |
| |
| -- nots |
| select x from s where not ( (1 = 1) or (2 = 2) ) and (3 = 4); |
| select x from s where not ( ( (1 = 1) or (2 = 2) ) and (3 = 4) ); |
| select x from s where (1 = 2) and not ( (3 = 3) or (4 = 4) ); |
| select x from s where not ( (1 = 2) and ( (3 = 3) or (4 = 4) ) ); |
| select ( not ( (1 = 1) or (2 = 2) ) and (3 = 4) ) from s; |
| -- |
| select ( not ( ( (1 = 1) or (2 = 2) ) and (3 = 4) ) ) from s; |
| -- |
| select ( (1 = 2) and not ( (3 = 3) or (4 = 4) ) ) from s; |
| -- |
| select ( not ( (1 = 2) and ( (3 = 3) or (4 = 4) ) ) ) from s; |
| |
| -- 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)) ) ) ); |
| |
| -- 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)) ) ) ); |
| |
| -- 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)) ) ) ); |
| |
| -- 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)) ) ) ); |
| |
| -- 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)); |
| |
| -- 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)); |
| |
| 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)); |
| |
| 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)); |
| |
| 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)); |
| |
| 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)); |
| |
| 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)); |
| |
| 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)); |
| |
| -- 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)); |
| |
| 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)); |
| |
| 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)); |
| |
| 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)); |
| |
| 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)); |
| |
| 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)); |
| |
| 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)); |
| |
| -- nots on nots |
| select * from s where not ( not ( not ((1=2) or (1=1))) or (not ((1=2) or (1=2)) ) ); |
| select not ( not ( not ((1=2) or (1=1))) or (not ((1=2) or (1=2)) ) ) from s; |
| |
| -- negative testing |
| -- non boolean where clauses |
| select * from s where 1; |
| select * from s where 1 and (1=1); |
| select * from s where (1=1) and 1; |
| select * from s where 1 or (1=1); |
| select * from s where (1=1) or 1; |
| select * from s where not 1; |
| |
| -- Clean up |
| drop table t; |
| drop table s; |
| |
