|
1
by brian
clean slate |
1 |
DROP TABLE IF EXISTS t1;
|
2 |
SELECT IF(NULL AND 1, 1, 2), IF(1 AND NULL, 1, 2);
|
|
3 |
IF(NULL AND 1, 1, 2) IF(1 AND NULL, 1, 2)
|
|
4 |
2 2
|
|
5 |
SELECT NULL AND 1, 1 AND NULL, 0 AND NULL, NULL and 0;
|
|
6 |
NULL AND 1 1 AND NULL 0 AND NULL NULL and 0
|
|
7 |
NULL NULL 0 0
|
|
8 |
create table t1 (a int);
|
|
9 |
insert into t1 values (0),(1),(NULL);
|
|
10 |
SELECT * FROM t1 WHERE IF(a AND 1, 0, 1);
|
|
11 |
a
|
|
12 |
0
|
|
13 |
NULL
|
|
14 |
SELECT * FROM t1 WHERE IF(1 AND a, 0, 1);
|
|
15 |
a
|
|
16 |
0
|
|
17 |
NULL
|
|
18 |
SELECT * FROM t1 where NOT(a AND 1);
|
|
19 |
a
|
|
20 |
0
|
|
21 |
SELECT * FROM t1 where NOT(1 AND a);
|
|
22 |
a
|
|
23 |
0
|
|
24 |
SELECT * FROM t1 where (a AND 1)=0;
|
|
25 |
a
|
|
26 |
0
|
|
27 |
SELECT * FROM t1 where (1 AND a)=0;
|
|
28 |
a
|
|
29 |
0
|
|
30 |
SELECT * FROM t1 where (1 AND a)=1;
|
|
31 |
a
|
|
32 |
1
|
|
33 |
SELECT * FROM t1 where (1 AND a) IS NULL;
|
|
34 |
a
|
|
35 |
NULL
|
|
36 |
select a, a is false, a is true, a is unknown from t1;
|
|
|
685.2.3
by Monty Taylor
Fixed bool. No more sql_mode. |
37 |
a a is false a is true a is unknown
|
|
1
by brian
clean slate |
38 |
0 1 0 0
|
39 |
1 0 1 0
|
|
40 |
NULL 0 0 1
|
|
41 |
select a, a is not false, a is not true, a is not unknown from t1;
|
|
|
685.2.3
by Monty Taylor
Fixed bool. No more sql_mode. |
42 |
a a is not false a is not true a is not unknown
|
|
1
by brian
clean slate |
43 |
0 0 1 1
|
44 |
1 1 0 1
|
|
45 |
NULL 1 1 0
|
|
46 |
SET @a=0, @b=0;
|
|
47 |
SELECT * FROM t1 WHERE NULL AND (@a:=@a+1);
|
|
48 |
a
|
|
49 |
SELECT * FROM t1 WHERE NOT(a>=0 AND NULL AND (@b:=@b+1));
|
|
50 |
a
|
|
51 |
SELECT * FROM t1 WHERE a=2 OR (NULL AND (@a:=@a+1));
|
|
52 |
a
|
|
53 |
SELECT * FROM t1 WHERE NOT(a=2 OR (NULL AND (@b:=@b+1)));
|
|
54 |
a
|
|
55 |
DROP TABLE t1;
|
|
56 |
create table t1 (a int, b int);
|
|
57 |
insert into t1 values(null, null), (0, null), (1, null), (null, 0), (null, 1), (0, 0), (0, 1), (1, 0), (1, 1);
|
|
58 |
select 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;
|
|
59 |
A B nA nB AB n(AB) nAonB AoB n(AoB) nAnB
|
|
60 |
N N N N N N N N N N
|
|
61 |
0 N 1 N 0 1 1 N N N
|
|
62 |
1 N 0 N N N N 1 0 0
|
|
63 |
N 0 N 1 0 1 1 N N N
|
|
64 |
N 1 N 0 N N N 1 0 0
|
|
65 |
0 0 1 1 0 1 1 0 1 1
|
|
66 |
0 1 1 0 0 1 1 1 0 0
|
|
67 |
1 0 0 1 0 1 1 1 0 0
|
|
68 |
1 1 0 0 1 0 0 1 0 0
|
|
69 |
select 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;
|
|
70 |
A B nA nB AB n(AB) nAonB AoB n(AoB) nAnB
|
|
71 |
N N N N N N N N N N
|
|
72 |
0 N 1 N 0 1 1 N N N
|
|
73 |
1 N 0 N N N N 1 0 0
|
|
74 |
N 0 N 1 0 1 1 N N N
|
|
75 |
N 1 N 0 N N N 1 0 0
|
|
76 |
0 0 1 1 0 1 1 0 1 1
|
|
77 |
0 1 1 0 0 1 1 1 0 0
|
|
78 |
1 0 0 1 0 1 1 1 0 0
|
|
79 |
1 1 0 0 1 0 0 1 0 0
|
|
80 |
drop table t1;
|