You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
96 lines
3.4 KiB
96 lines
3.4 KiB
DROP TABLE IF EXISTS t1;
|
|
SELECT IF(NULL AND 1, 1, 2), IF(1 AND NULL, 1, 2);
|
|
IF(NULL AND 1, 1, 2) IF(1 AND NULL, 1, 2)
|
|
2 2
|
|
SELECT NULL AND 1, 1 AND NULL, 0 AND NULL, NULL and 0;
|
|
NULL AND 1 1 AND NULL 0 AND NULL NULL and 0
|
|
NULL NULL 0 0
|
|
create table t1 (a int);
|
|
insert into t1 values (0),(1),(NULL);
|
|
SELECT * FROM t1 WHERE IF(a AND 1, 0, 1);
|
|
a
|
|
0
|
|
NULL
|
|
SELECT * FROM t1 WHERE IF(1 AND a, 0, 1);
|
|
a
|
|
0
|
|
NULL
|
|
SELECT * FROM t1 where NOT(a AND 1);
|
|
a
|
|
0
|
|
SELECT * FROM t1 where NOT(1 AND a);
|
|
a
|
|
0
|
|
SELECT * FROM t1 where (a AND 1)=0;
|
|
a
|
|
0
|
|
SELECT * FROM t1 where (1 AND a)=0;
|
|
a
|
|
0
|
|
SELECT * FROM t1 where (1 AND a)=1;
|
|
a
|
|
1
|
|
SELECT * FROM t1 where (1 AND a) IS NULL;
|
|
a
|
|
NULL
|
|
set sql_mode='high_not_precedence';
|
|
select * from t1 where not a between 2 and 3;
|
|
a
|
|
set sql_mode=default;
|
|
select * from t1 where not a between 2 and 3;
|
|
a
|
|
0
|
|
1
|
|
select a, a is false, a is true, a is unknown from t1;
|
|
a a is false a is true a is unknown
|
|
0 1 0 0
|
|
1 0 1 0
|
|
NULL 0 0 1
|
|
select a, a is not false, a is not true, a is not unknown from t1;
|
|
a a is not false a is not true a is not unknown
|
|
0 0 1 1
|
|
1 1 0 1
|
|
NULL 1 1 0
|
|
SET @a=0, @b=0;
|
|
SELECT * FROM t1 WHERE NULL AND (@a:=@a+1);
|
|
a
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
SELECT * FROM t1 WHERE NOT(a>=0 AND NULL AND (@b:=@b+1));
|
|
a
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
SELECT * FROM t1 WHERE a=2 OR (NULL AND (@a:=@a+1));
|
|
a
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
SELECT * FROM t1 WHERE NOT(a=2 OR (NULL AND (@b:=@b+1)));
|
|
a
|
|
Warnings:
|
|
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
|
|
DROP TABLE t1;
|
|
create table t1 (a int, b int);
|
|
insert into t1 values(null, null), (0, null), (1, null), (null, 0), (null, 1), (0, 0), (0, 1), (1, 0), (1, 1);
|
|
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;
|
|
A B nA nB AB n(AB) nAonB AoB n(AoB) nAnB
|
|
N N N N N N N N N N
|
|
0 N 1 N 0 1 1 N N N
|
|
1 N 0 N N N N 1 0 0
|
|
N 0 N 1 0 1 1 N N N
|
|
N 1 N 0 N N N 1 0 0
|
|
0 0 1 1 0 1 1 0 1 1
|
|
0 1 1 0 0 1 1 1 0 0
|
|
1 0 0 1 0 1 1 1 0 0
|
|
1 1 0 0 1 0 0 1 0 0
|
|
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;
|
|
A B nA nB AB n(AB) nAonB AoB n(AoB) nAnB
|
|
N N N N N N N N N N
|
|
0 N 1 N 0 1 1 N N N
|
|
1 N 0 N N N N 1 0 0
|
|
N 0 N 1 0 1 1 N N N
|
|
N 1 N 0 N N N 1 0 0
|
|
0 0 1 1 0 1 1 0 1 1
|
|
0 1 1 0 0 1 1 1 0 0
|
|
1 0 0 1 0 1 1 1 0 0
|
|
1 1 0 0 1 0 0 1 0 0
|
|
drop table t1;
|
|
|