github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/operator/is_not_operator.result (about) 1 SELECT 1 IS TRUE, 0 IS FALSE; 2 1 is true 0 is false 3 true true 4 SELECT NULL IS TRUE; 5 null is true 6 false 7 SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL; 8 1 is null 0 is null null is null 9 false false true 10 SELECT '' IS NULL, ' ' IS NULL; 11 is null is null 12 false false 13 DROP TABLE IF EXISTS is_test; 14 CREATE TABLE is_test( 15 str1 CHAR(20), 16 str2 VARCHAR(20), 17 d1 DATE, 18 d2 DATETIME, 19 d3 TIMESTAMP 20 ); 21 INSERT INTO is_test VALUES('', ' ', NULL, NULL, NULL); 22 INSERT INTO is_test VALUES('NULL', NULL, '0001-01-01', '2022-02-01 13:46:02', '1994-03-02'); 23 INSERT INTO is_test(str1, str2) VALUES('0', 1); 24 INSERT INTO is_test(str1, str2) VALUES('1', 0); 25 INSERT INTO is_test(str1, str2) VALUES(1, '1'); 26 INSERT INTO is_test(str1, str2) VALUES(0, '0'); 27 SELECT * FROM is_test WHERE str1 IS FALSE; 28 invalid input: '' is not a valid bool expression 29 SELECT d1, d2 FROM is_test WHERE str2 IS NOT FALSE; 30 invalid input: ' ' is not a valid bool expression 31 SELECT * FROM is_test WHERE str2 IS TRUE; 32 invalid input: ' ' is not a valid bool expression 33 SELECT * FROM is_test WHERE str1 IS FALSE; 34 invalid input: '' is not a valid bool expression 35 SELECT * FROM is_test WHERE str2 IS TRUE AND str1 IS TRUE; 36 invalid input: '' is not a valid bool expression 37 SELECT * FROM is_test WHERE LENGTH(str2) IS TRUE; 38 str1 str2 d1 d2 d3 39 null null null 40 0 1 null null null 41 1 0 null null null 42 1 1 null null null 43 0 0 null null null 44 SELECT * FROM is_test WHERE str1 IS TRUE OR str2 IS TRUE; 45 invalid input: '' is not a valid bool expression 46 DELETE FROM is_test; 47 DROP TABLE IF EXISTS is_test; 48 CREATE TABLE is_test( 49 tiny TINYINT, 50 small SMALLINT, 51 int_test INTEGER, 52 big BIGINT, 53 tiny_un TINYINT UNSIGNED, 54 small_un SMALLINT UNSIGNED, 55 int_test_un INTEGER UNSIGNED, 56 big_un BIGINT UNSIGNED, 57 float_32 FLOAT, 58 float_64 DOUBLE 59 ); 60 INSERT INTO is_test(tiny, small, int_test, big) VALUES(0, 1, 0, -0); 61 INSERT INTO is_test(tiny_un, small_un) VALUES(0, 1); 62 INSERT INTO is_test(tiny_un, small_un) VALUES(0, 2); 63 INSERT INTO is_test(float_32, float_64) VALUES(0.0, 0.00000000000000000000001); 64 INSERT INTO is_test(float_32, float_64) VALUES(1.0, 0.4999999999999); 65 INSERT INTO is_test(float_32, float_64) VALUES(0.51, -0.00000000000000000000001); 66 SELECT -1 IS TRUE; 67 -1 is true 68 true 69 SELECT 0 IS TRUE; 70 0 is true 71 false 72 SELECT -1 IS TRUE; 73 -1 is true 74 true 75 SELECT 1 IS TRUE; 76 1 is true 77 true 78 SELECT 2 IS TRUE; 79 2 is true 80 true 81 SELECT -2 IS TRUE; 82 -2 is true 83 true 84 SELECT * FROM is_test WHERE big IS TRUE; 85 tiny small int_test big tiny_un small_un int_test_un big_un float_32 float_64 86 SELECT small_un FROM is_test WHERE small_un IS TRUE; 87 small_un 88 1 89 2 90 SELECT float_32, float_64 FROM is_test WHERE float_32 > 0 IS FALSE; 91 float_32 float_64 92 0.0 1.0E-23 93 SELECT float_32, float_64 FROM is_test WHERE float_64 > 0 IS FALSE; 94 float_32 float_64 95 0.51 -1.0E-23 96 SELECT float_32, float_64 FROM is_test WHERE float_64 = 0 IS TRUE; 97 float_32 float_64 98 DELETE FROM is_test; 99 DROP TABLE IF EXISTS is_test; 100 CREATE TABLE is_test( 101 b1 BOOL, 102 b2 BOOL 103 ); 104 INSERT INTO is_test VALUES(TRUE, FALSE); 105 INSERT INTO is_test VALUES(1, FALSE); 106 INSERT INTO is_test VALUES(0, TRUE); 107 INSERT INTO is_test VALUES(FALSE, 1); 108 INSERT INTO is_test VALUES(1, NULL); 109 INSERT INTO is_test VALUES(NULL, 0); 110 SELECT * FROM is_test WHERE b1 IS TRUE; 111 b1 b2 112 true false 113 true false 114 true null 115 SELECT * FROM is_test WHERE b1 IS NOT FALSE; 116 b1 b2 117 true false 118 true false 119 true null 120 null false 121 SELECT * FROM is_test WHERE b2 IS FALSE; 122 b1 b2 123 true false 124 true false 125 null false 126 SELECT * FROM is_test WHERE b2 IS NOT FALSE; 127 b1 b2 128 false true 129 false true 130 true null 131 DELETE FROM is_test; 132 DROP TABLE IF EXISTS is_test; 133 DROP TABLE IF EXISTS t1; 134 DROP TABLE IF EXISTS t2; 135 CREATE TABLE t1( 136 id INT PRIMARY KEY, 137 name VARCHAR(20), 138 class VARCHAR(4) 139 ); 140 CREATE TABLE t2( 141 id INT, 142 nation VARCHAR(20), 143 major VARCHAR(20) 144 ); 145 INSERT INTO t1 VALUES('1001', 'JACK', ''); 146 INSERT INTO t1 VALUES('1002', 'TOM', '0'); 147 INSERT INTO t1 VALUES('1003', '1', '5'); 148 INSERT INTO t1 VALUES('1004', ' ', '9'); 149 INSERT INTO t2 VALUES('1001', 'BRAZIL', 'AI'); 150 INSERT INTO t2 VALUES('1002', 'IRELAND', '1'); 151 INSERT INTO t2 VALUES('1003', 'TRUE', 'AI'); 152 INSERT INTO t2 VALUES('1004', 'IRELAND', '0'); 153 SELECT id FROM t1 WHERE name IS TRUE; 154 invalid input: 'jack' is not a valid bool expression 155 SELECT name, class FROM t1 WHERE name IS NOT TRUE; 156 invalid input: 'jack' is not a valid bool expression 157 SELECT id, name, class FROM t1 WHERE CAST(SUBSTRING(id, 4) AS TINYINT) IS TRUE; 158 id name class 159 1001 JACK 160 1002 TOM 0 161 1003 1 5 162 1004 9 163 SELECT t1.id, t1.name, t2.nation FROM t1 INNER JOIN t2 ON t1.id = t2.id WHERE t1.class IS NOT FALSE; 164 invalid input: '' is not a valid bool expression 165 SELECT t1.id, t2.major FROM t1 INNER JOIN t2 ON t1.id = t2.id WHERE t1.name IS TRUE; 166 invalid input: 'jack' is not a valid bool expression 167 SELECT t1.id, t2.major FROM t1 INNER JOIN t2 ON t1.id = t2.id WHERE t2.nation IS TRUE; 168 invalid input: 'brazil' is not a valid bool expression 169 SELECT * FROM t1 WHERE id = 1004 AND LENGTH(name) IS TRUE; 170 id name class 171 1004 9 172 SELECT * FROM t1 WHERE LENGTH(name) IS FALSE; 173 id name class 174 SELECT id FROM t1 WHERE LENGTH(name) IS TRUE; 175 id 176 1001 177 1002 178 1003 179 1004 180 SELECT * FROM t2 WHERE STARTSWITH(id, '1') IS TRUE; 181 id nation major 182 1001 BRAZIL AI 183 1002 IRELAND 1 184 1003 TRUE AI 185 1004 IRELAND 0 186 SELECT * FROM t2 WHERE ENDSWITH(id, '1') IS FALSE; 187 id nation major 188 1002 IRELAND 1 189 1003 TRUE AI 190 1004 IRELAND 0 191 SELECT * FROM t1 WHERE FIND_IN_SET('J', name) IS TRUE; 192 id name class 193 DROP TABLE IF EXISTS t1; 194 DROP TABLE IF EXISTS t2; 195 CREATE TABLE t1( 196 user_id VARCHAR(50) PRIMARY KEY, 197 user_name VARCHAR(20), 198 order_time DATETIME, 199 pay_status BOOL 200 ); 201 CREATE TABLE t2( 202 order_id VARCHAR(50) PRIMARY KEY, 203 user_id VARCHAR(50), 204 product_id VARCHAR(50), 205 number INT, 206 price FLOAT 207 ); 208 INSERT INTO t1 VALUES('7321', '小明', '2022-09-18 13:33:56', TRUE); 209 INSERT INTO t1 VALUES('7322', '小红', '2021-07-11 22:39:18', NULL); 210 INSERT INTO t1 VALUES('7323', '小刘', '2022-11-11 23:49:02', 1); 211 INSERT INTO t1 VALUES('7324', ' ', '2022-12-06 09:22:19', 0); 212 INSERT INTO t2 VALUES('9991', '7321', '', 1, 0.01); 213 INSERT INTO t2 VALUES('9992', '7322', '1023', 0, 9.99); 214 INSERT INTO t2 VALUES('9993', '7323', '1024', 18, 0.0); 215 SELECT user_id, user_name FROM t1 WHERE pay_status IS NOT TRUE; 216 user_id user_name 217 7322 小红 218 7324 219 SELECT user_id, user_name FROM t1 WHERE YEAR(order_time) > 1 IS TRUE; 220 user_id user_name 221 7321 小明 222 7322 小红 223 7323 小刘 224 7324 225 SELECT user_id, user_name FROM t1 WHERE LENGTH(MONTH(order_time))-1 IS TRUE; 226 user_id user_name 227 7323 小刘 228 7324 229 SELECT * FROM (SELECT * FROM t1 WHERE pay_status IS FALSE) AS t WHERE LENGTH(user_name) IS TRUE; 230 user_id user_name order_time pay_status 231 7324 2022-12-06 09:22:19 false 232 SELECT 233 t1.user_id, user_name, pay_status 234 FROM t1 235 INNER JOIN t2 ON t1.user_id = t2.user_id 236 WHERE pay_status IS TRUE AND number IS NOT FALSE; 237 user_id user_name pay_status 238 7321 小明 true 239 7323 小刘 true 240 SELECT 241 t1.user_name, order_time, product_id 242 FROM 243 t1 244 LEFT JOIN t2 ON t1.user_id = t2.user_id 245 WHERE STARTSWITH(order_id, '9') IS TRUE AND number IS TRUE; 246 user_name order_time product_id 247 小明 2022-09-18 13:33:56 248 小刘 2022-11-11 23:49:02 1024 249 SELECT 250 t1.user_name, order_time, number, price 251 FROM 252 t1 253 RIGHT JOIN t2 ON t1.user_id = t2.user_id 254 WHERE number IS TRUE AND pay_status IS NOT FALSE; 255 user_name order_time number price 256 小明 2022-09-18 13:33:56 1 0.01 257 小刘 2022-11-11 23:49:02 18 0.0 258 DROP TABLE IF EXISTS t1; 259 DROP TABLE IF EXISTS t2; 260 CREATE TABLE temp( 261 tiny TINYINT, 262 str CHAR(20), 263 b BOOL, 264 int_test INT 265 ); 266 INSERT INTO temp VALUES(NULL, '1', FALSE, 1); 267 INSERT INTO temp VALUES(-1, '0', TRUE, 0); 268 INSERT INTO temp VALUES(-1, '1', 0, 0); 269 SELECT * FROM temp WHERE int_test IS FALSE; 270 tiny str b int_test 271 -1 0 true 0 272 -1 1 false 0 273 SELECT * FROM temp WHERE str IS FALSE AND int_test IS FALSE; 274 tiny str b int_test 275 -1 0 true 0 276 SELECT * FROM (SELECT * FROM temp WHERE str IS TRUE) AS t WHERE int_test IS TRUE; 277 tiny str b int_test 278 null 1 false 1 279 SELECT * FROM (SELECT * FROM temp WHERE tiny IS FALSE) AS t WHERE b IS NOT TRUE; 280 tiny str b int_test 281 SELECT * FROM (SELECT * FROM temp WHERE tiny IS TRUE) AS t WHERE str IS TRUE; 282 tiny str b int_test 283 -1 1 false 0