1#
2# Test of boolean operations with NULL
3#
4
5--disable_warnings
6DROP TABLE IF EXISTS t1;
7--enable_warnings
8
9SELECT IF(NULL AND 1, 1, 2), IF(1 AND NULL, 1, 2);
10SELECT NULL AND 1, 1 AND NULL, 0 AND NULL, NULL and 0;
11
12create table t1 (a int);
13insert into t1 values (0),(1),(NULL);
14SELECT * FROM t1 WHERE IF(a AND 1, 0, 1);
15SELECT * FROM t1 WHERE IF(1 AND a, 0, 1);
16SELECT * FROM t1 where NOT(a AND 1);
17SELECT * FROM t1 where NOT(1 AND a);
18SELECT * FROM t1 where (a AND 1)=0;
19SELECT * FROM t1 where (1 AND a)=0;
20SELECT * FROM t1 where (1 AND a)=1;
21SELECT * FROM t1 where (1 AND a) IS NULL;
22
23# WL#638 - Behaviour of NOT does not follow SQL specification
24set sql_mode='high_not_precedence';
25select * from t1 where not a between 2 and 3;
26set sql_mode=default;
27select * from t1 where not a between 2 and 3;
28
29# SQL boolean tests
30select a, a is false, a is true, a is unknown from t1;
31select a, a is not false, a is not true, a is not unknown from t1;
32
33# Verify that NULL optimisation works in AND clause:
34SET @a=0, @b=0;
35SELECT * FROM t1 WHERE NULL AND (@a:=@a+1);
36SELECT * FROM t1 WHERE NOT(a>=0 AND NULL AND (@b:=@b+1));
37SELECT * FROM t1 WHERE a=2 OR (NULL AND (@a:=@a+1));
38SELECT * FROM t1 WHERE NOT(a=2 OR (NULL AND (@b:=@b+1)));
39DROP TABLE t1;
40
41
42# Test boolean operators in select part
43# NULLs are represented as N for readability
44# Read nA as !A, AB as A && B, AoB as A || B
45# Result table makes ANSI happy
46
47create table t1 (a int, b int);
48insert into t1 values(null, null), (0, null), (1, null), (null, 0), (null, 1), (0, 0), (0, 1), (1, 0), (1, 1);
49
50# Below test is valid untill we have True/False implemented as 1/0
51# To comply to all rules it must show that:  n(AB) = nAonB,  n(AoB) = nAnB
52
53select ifnull(A, 'N') as A, ifnull(B, 'N') as B, ifnull(not A, 'N') as nA, ifnull(not B, 'N') as nB, ifnull(A and B, 'N') as AB, ifnull(not (A and B), 'N') as `n(AB)`, ifnull((not A or not B), 'N') as nAonB, ifnull(A or B, 'N') as AoB, ifnull(not(A or B), 'N') as `n(AoB)`, ifnull(not A and not B, 'N') as nAnB from t1;
54
55# This should work with any internal representation of True/False
56# Result must be same as above
57
58select ifnull(A=1, 'N') as A, ifnull(B=1, 'N') as B, ifnull(not (A=1), 'N') as nA, ifnull(not (B=1), 'N') as nB, ifnull((A=1) and (B=1), 'N') as AB, ifnull(not ((A=1) and (B=1)), 'N') as `n(AB)`, ifnull((not (A=1) or not (B=1)), 'N') as nAonB, ifnull((A=1) or (B=1), 'N') as AoB, ifnull(not((A=1) or (B=1)), 'N') as `n(AoB)`, ifnull(not (A=1) and not (B=1), 'N') as nAnB from t1;
59
60drop table t1;
61
62# End of 4.1 tests
63