github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/operator/between_and_operator.result (about) 1 SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1; 2 2 between 1 and 3 2 between 3 and 1 3 true false 4 SELECT 1 BETWEEN 2 AND 3; 5 1 between 2 and 3 6 false 7 SELECT 'b' BETWEEN 'a' AND 'c'; 8 b between a and c 9 true 10 SELECT 2 BETWEEN 2 AND '3'; 11 2 between 2 and 3 12 true 13 DROP TABLE IF EXISTS between_and_test; 14 CREATE TABLE between_and_test( 15 int_test_max INT, 16 int_test_min INT, 17 str1 VARCHAR(20), 18 str2 CHAR(20), 19 float_32 FLOAT, 20 float_64 DOUBLE 21 ); 22 INSERT INTO between_and_test() VALUES(2147483647, -2147483648, 'A', 'Z', 0.0000000000000000000000001, -1.1); 23 INSERT INTO between_and_test() VALUES(2147483647, -2147483648, 'a', 'z', 0.002, 0.00003); 24 INSERT INTO between_and_test(float_32, float_64) VALUES(2147483648, 16542147483647); 25 SELECT * FROM between_and_test WHERE 'A' BETWEEN str1 AND str2; 26 int_test_max int_test_min str1 str2 float_32 float_64 27 2147483647 -2147483648 A Z 1.0E-25 -1.1 28 SELECT * FROM between_and_test WHERE 'Z' BETWEEN str1 AND str2; 29 int_test_max int_test_min str1 str2 float_32 float_64 30 2147483647 -2147483648 A Z 1.0E-25 -1.1 31 SELECT * FROM between_and_test WHERE 'a' BETWEEN str1 AND str2; 32 int_test_max int_test_min str1 str2 float_32 float_64 33 2147483647 -2147483648 a z 0.002 3.0E-5 34 SELECT * FROM between_and_test WHERE 'z' BETWEEN str1 AND str2; 35 int_test_max int_test_min str1 str2 float_32 float_64 36 2147483647 -2147483648 a z 0.002 3.0E-5 37 SELECT * FROM between_and_test WHERE 'ABc' BETWEEN str1 AND str2; 38 int_test_max int_test_min str1 str2 float_32 float_64 39 2147483647 -2147483648 A Z 1.0E-25 -1.1 40 SELECT * FROM between_and_test WHERE 'B' BETWEEN str1 AND str2; 41 int_test_max int_test_min str1 str2 float_32 float_64 42 2147483647 -2147483648 A Z 1.0E-25 -1.1 43 SELECT * FROM between_and_test WHERE '' BETWEEN str1 AND str2; 44 int_test_max int_test_min str1 str2 float_32 float_64 45 SELECT * FROM between_and_test WHERE ' ' BETWEEN str1 AND str2; 46 int_test_max int_test_min str1 str2 float_32 float_64 47 SELECT * FROM between_and_test WHERE NULL BETWEEN str1 AND str2; 48 int_test_max int_test_min str1 str2 float_32 float_64 49 SELECT int_test_max, int_test_min FROM between_and_test WHERE 2147483647 BETWEEN int_test_min AND int_test_max; 50 int_test_max int_test_min 51 2147483647 -2147483648 52 2147483647 -2147483648 53 SELECT int_test_max, int_test_min FROM between_and_test WHERE -2147483649 BETWEEN int_test_min AND int_test_max; 54 int_test_max int_test_min 55 SELECT int_test_max, int_test_min FROM between_and_test WHERE 0 BETWEEN int_test_min AND int_test_max; 56 int_test_max int_test_min 57 2147483647 -2147483648 58 2147483647 -2147483648 59 SELECT float_32, float_64 FROM between_and_test WHERE float_64 BETWEEN -1.1 AND 0.00003; 60 float_32 float_64 61 1.0E-25 -1.1 62 0.002 3.0E-5 63 SELECT float_32, float_64 FROM between_and_test WHERE -0.0 BETWEEN -1.1 AND 0.00003; 64 float_32 float_64 65 1.0E-25 -1.1 66 0.002 3.0E-5 67 2.1474836E9 1.6542147483647E13 68 SELECT * FROM between_and_test WHERE float_32 BETWEEN int_test_min AND int_test_max; 69 int_test_max int_test_min str1 str2 float_32 float_64 70 2147483647 -2147483648 A Z 1.0E-25 -1.1 71 2147483647 -2147483648 a z 0.002 3.0E-5 72 SELECT * FROM between_and_test WHERE float_64 BETWEEN int_test_max AND int_test_min; 73 int_test_max int_test_min str1 str2 float_32 float_64 74 SELECT * FROM between_and_test WHERE NULL NOT BETWEEN 'A' AND 'z'; 75 int_test_max int_test_min str1 str2 float_32 float_64 76 SELECT * FROM between_and_test WHERE '#' NOT BETWEEN 'A' AND 'z'; 77 int_test_max int_test_min str1 str2 float_32 float_64 78 2147483647 -2147483648 A Z 1.0E-25 -1.1 79 2147483647 -2147483648 a z 0.002 3.0E-5 80 null null null null 2.1474836E9 1.6542147483647E13 81 SELECT * FROM between_and_test WHERE 'A' NOT BETWEEN str1 AND str2; 82 int_test_max int_test_min str1 str2 float_32 float_64 83 2147483647 -2147483648 a z 0.002 3.0E-5 84 SELECT * FROM between_and_test WHERE 'a' NOT BETWEEN str1 AND str2; 85 int_test_max int_test_min str1 str2 float_32 float_64 86 2147483647 -2147483648 A Z 1.0E-25 -1.1 87 SELECT * FROM between_and_test WHERE float_32 NOT BETWEEN -2147483648 AND 2147483647; 88 int_test_max int_test_min str1 str2 float_32 float_64 89 null null null null 2.1474836E9 1.6542147483647E13 90 SELECT * FROM between_and_test WHERE float_64 NOT BETWEEN -2147483648 AND 2147483647; 91 int_test_max int_test_min str1 str2 float_32 float_64 92 null null null null 2.1474836E9 1.6542147483647E13 93 DELETE FROM between_and_test; 94 DROP TABLE IF EXISTS between_and_test; 95 CREATE TABLE between_and_test( 96 d1 DATE, 97 d2 DATETIME, 98 d3 TIMESTAMP 99 ); 100 INSERT INTO between_and_test() VALUES('2022-06-06','2022-06-09 13:13:16', '2022-08-11 15:56:16.4561'); 101 INSERT INTO between_and_test() VALUES('2022-06-30','2022-07-31 00:00:00', '2022-08-11 15:56:16.4561'); 102 INSERT INTO between_and_test() VALUES('2021-06-30','2022-07-31 00:00:01', '2022-09-01 13:03:13.65456'); 103 SELECT * FROM between_and_test WHERE d1 BETWEEN '2022-06-01' AND '2022-06-30'; 104 d1 d2 d3 105 2022-06-06 2022-06-09 13:13:16 2022-08-11 15:56:16 106 2022-06-30 2022-07-31 00:00:00 2022-08-11 15:56:16 107 SELECT * FROM between_and_test WHERE d1 BETWEEN '2022-06-01' AND NULL; 108 d1 d2 d3 109 SELECT * FROM between_and_test WHERE d1 BETWEEN NULL AND '2022-06-30'; 110 d1 d2 d3 111 SELECT * FROM between_and_test WHERE d1 BETWEEN NULL AND NULL; 112 d1 d2 d3 113 SELECT * FROM between_and_test WHERE d1 BETWEEN CAST('2022-06-01' AS DATE) AND '2022-06-30'; 114 d1 d2 d3 115 2022-06-06 2022-06-09 13:13:16 2022-08-11 15:56:16 116 2022-06-30 2022-07-31 00:00:00 2022-08-11 15:56:16 117 SELECT * FROM between_and_test WHERE d2 BETWEEN CAST('2022-07-01' AS DATE) AND CAST('2022-07-31 00:00:00' AS DATETIME); 118 d1 d2 d3 119 2022-06-30 2022-07-31 00:00:00 2022-08-11 15:56:16 120 SELECT d2 FROM between_and_test WHERE d2 BETWEEN '2022-06-01' AND '2022-07-30 23:59:59'; 121 d2 122 2022-06-09 13:13:16 123 SELECT d2 FROM between_and_test WHERE 6 BETWEEN MONTH('2022-06-09 13:13:16') AND MONTH('2022-07-31 00:00:00')-1; 124 d2 125 2022-06-09 13:13:16 126 2022-07-31 00:00:00 127 2022-07-31 00:00:01 128 SELECT * FROM between_and_test WHERE d1 NOT BETWEEN '2022-06-01' AND '2022-06-15'; 129 d1 d2 d3 130 2022-06-30 2022-07-31 00:00:00 2022-08-11 15:56:16 131 2021-06-30 2022-07-31 00:00:01 2022-09-01 13:03:14 132 SELECT * FROM between_and_test WHERE d1 NOT BETWEEN '2022-06-15' AND '2022-06-01'; 133 d1 d2 d3 134 2022-06-06 2022-06-09 13:13:16 2022-08-11 15:56:16 135 2022-06-30 2022-07-31 00:00:00 2022-08-11 15:56:16 136 2021-06-30 2022-07-31 00:00:01 2022-09-01 13:03:14 137 SELECT d2 FROM between_and_test WHERE d2 NOT BETWEEN '2022-06-09 00:13:16' AND '2022-06-9 14:00:00'; 138 d2 139 2022-07-31 00:00:00 140 2022-07-31 00:00:01 141 SELECT d2 FROM between_and_test WHERE d2 BETWEEN '2022-07-31' AND '2022-07-31'; 142 d2 143 2022-07-31 00:00:00 144 SELECT d2 FROM between_and_test WHERE d2 NOT BETWEEN '2022-07-31' AND '2022-07-31'; 145 d2 146 2022-06-09 13:13:16 147 2022-07-31 00:00:01 148 DELETE FROM between_and_test; 149 DROP TABLE IF EXISTS between_and_test; 150 CREATE TABLE t1( 151 id INT, 152 str VARCHAR(20) 153 ); 154 CREATE TABLE t2( 155 id INT, 156 order_time DATE 157 ); 158 INSERT INTO t1 VALUES(0, 'honda'), (-1, 'toyota'), (1, ' '), (-2, 'NULL'); 159 INSERT INTO t2 VALUES(0, '2022-09-21'), (-1, '2022-09-11'), (1, '2021-09-21'), (-2, '2021-09-11'); 160 INSERT INTO t1 VALUES(2, '#$%@RETE'); 161 SELECT * FROM t1 WHERE id BETWEEN '0' AND '2'; 162 id str 163 0 honda 164 1 165 2 #$%@RETE 166 SELECT id,str FROM t1 WHERE id BETWEEN 0 AND 0 AND str BETWEEN 'A' AND 'Z'; 167 id str 168 SELECT * FROM (SELECT * FROM t1 WHERE str BETWEEN 'a' AND 'x') AS t WHERE id BETWEEN 0 AND 1; 169 id str 170 0 honda 171 SELECT * FROM (SELECT * FROM t1 WHERE str BETWEEN '' AND 'z') AS t WHERE id BETWEEN -1 AND 1; 172 id str 173 0 honda 174 -1 toyota 175 1 176 SELECT t1.id, str, order_time 177 FROM t1 INNER JOIN t2 ON t1.id = t2.id 178 WHERE t1.id BETWEEN -2 AND 2 AND t2.order_time BETWEEN '2022-09-11' AND '2022-09-21'; 179 id str order_time 180 0 honda 2022-09-21 181 -1 toyota 2022-09-11 182 SELECT t1.id, str, order_time 183 FROM t1 INNER JOIN t2 ON t1.id = t2.id 184 WHERE YEAR(order_time) BETWEEN '2021' AND '2022'; 185 id str order_time 186 0 honda 2022-09-21 187 -1 toyota 2022-09-11 188 1 2021-09-21 189 -2 NULL 2021-09-11 190 DROP TABLE IF EXISTS t1; 191 DROP TABLE IF EXISTS t2; 192 CREATE TABLE t1( 193 id VARCHAR(20), 194 name VARCHAR(20) 195 ); 196 CREATE TABLE t2( 197 id VARCHAR(20), 198 n1 INT, 199 n2 FLOAT 200 ); 201 INSERT INTO t1 VALUES('0001', 'Smi'), ('1111', 'Fri'), ('2222', '22TOM'); 202 INSERT INTO t2 VALUES('0001', 0001, 0.01), ('1111', 1111, -0.01), ('2222', 2222, NULL); 203 SELECT * FROM t1 WHERE id BETWEEN '0' AND '2'; 204 id name 205 0001 Smi 206 1111 Fri 207 SELECT * FROM t1 WHERE name BETWEEN '0' AND '2'; 208 id name 209 SELECT * FROM t1 WHERE id NOT BETWEEN '0' AND '2'; 210 id name 211 2222 22TOM 212 SELECT t1.id, name, n1, n2 213 FROM t1 LEFT JOIN t2 ON t1.id = t2.id 214 WHERE id NOT BETWEEN '0' AND '2' AND n2 BETWEEN NULL AND NULL; 215 [unknown result because it is related to issue#5150] 216 SELECT t1.id, name, n1, n2 217 FROM t1 LEFT JOIN t2 ON t1.id = t2.id 218 WHERE t1.id NOT BETWEEN '0' AND '2' AND n2 BETWEEN NULL AND NULL; 219 id name n1 n2 220 SELECT t1.id, name, n1, n2 221 FROM t1 RIGHT JOIN t2 ON t1.id = t2.id 222 WHERE t1.id NOT BETWEEN 'a' AND 'Z' AND n2 BETWEEN -0.01 AND 0.01; 223 id name n1 n2 224 0001 Smi 1 0.01 225 1111 Fri 1111 -0.01 226 SELECT t1.id, name, n1, n2 227 FROM t1 INNER JOIN t2 ON t1.id = t2.id 228 WHERE t1.id BETWEEN '0' AND '1' AND n1 BETWEEN '0' AND 1111; 229 id name n1 n2 230 0001 Smi 1 0.01 231 DROP TABLE IF EXISTS t1; 232 DROP TABLE IF EXISTS t2; 233 CREATE TABLE t1( 234 str1 VARCHAR(50), 235 b BOOL 236 ); 237 INSERT INTO t1(str1) VALUES('This product is merged some high tech.'); 238 INSERT INTO t1(str1) VALUES('&^%$#@ has some error'); 239 INSERT INTO t1() VALUES('many years age, joe smith always', TRUE); 240 INSERT INTO t1(b) VALUES(TRUE), (FALSE), (NULL); 241 SELECT * FROM t1 WHERE SUBSTRING(str1,LENGTH(str1)) BETWEEN 'A' AND 'z'; 242 str1 b 243 &^%$#@ has some error null 244 many years age, joe smith always true 245 SELECT * FROM t1 WHERE SUBSTRING(str1,LENGTH(str1) - (LENGTH(str1)-1)) BETWEEN 'A' AND 'z'; 246 str1 b 247 This product is merged some high tech. null 248 many years age, joe smith always true 249 SELECT * FROM t1 WHERE SUBSTRING(str1,LENGTH(str1) - (LENGTH(str1)-1)) NOT BETWEEN 'A' AND 'z'; 250 str1 b 251 &^%$#@ has some error null 252 SELECT * FROM t1 WHERE str1 NOT BETWEEN 'm' AND 'T'; 253 str1 b 254 This product is merged some high tech. null 255 &^%$#@ has some error null 256 many years age, joe smith always true 257 SELECT * FROM t1 WHERE b BETWEEN TRUE AND FALSE; 258 str1 b 259 SELECT * FROM t1 WHERE b NOT BETWEEN TRUE AND FALSE; 260 str1 b 261 many years age, joe smith always true 262 null true 263 null false 264 SELECT * FROM t1 WHERE LENGTH(str1) BETWEEN 25 AND 50; 265 str1 b 266 This product is merged some high tech. null 267 many years age, joe smith always true 268 SELECT * FROM t1 WHERE LENGTH(str1) BETWEEN '25'+5 AND 50; 269 str1 b 270 This product is merged some high tech. null 271 many years age, joe smith always true 272 SELECT * FROM t1 WHERE LENGTH(str1) NOT BETWEEN 25 AND 50; 273 str1 b 274 &^%$#@ has some error null 275 DROP TABLE IF EXISTS t1; 276 CREATE TABLE t1( 277 d1 DATE, 278 d2 DATETIME 279 ); 280 INSERT INTO t1 VALUES('2022-09-11', '2029-12-16 01:21:34'); 281 INSERT INTO t1 VALUES('2021-11-11', '2028-11-06 00:21:34'); 282 INSERT INTO t1 VALUES('2222-12-11', '1999-05-16 21:21:34'); 283 SELECT * FROM t1 WHERE DATE_ADD(d1, INTERVAL 2 MONTH) BETWEEN '2022-01-01' AND '2022-12-31'; 284 d1 d2 285 2022-09-11 2029-12-16 01:21:34 286 2021-11-11 2028-11-06 00:21:34 287 SELECT * FROM t1 WHERE DATE_SUB(d1, INTERVAL 10 DAY) BETWEEN '2021-11-11' AND '2022-12-31'; 288 d1 d2 289 2022-09-11 2029-12-16 01:21:34 290 SELECT * FROM t1 WHERE d1 BETWEEN DATE_SUB('2022-09-11', INTERVAL 10 DAY) AND DATE_SUB('2222-12-11', INTERVAL -200 YEAR); 291 d1 d2 292 2022-09-11 2029-12-16 01:21:34 293 2222-12-11 1999-05-16 21:21:34 294 SELECT * FROM t1 WHERE d1 NOT BETWEEN DATE_SUB('2022-09-11', INTERVAL 10 DAY) AND DATE_SUB('2222-12-11', INTERVAL -200 YEAR); 295 d1 d2 296 2021-11-11 2028-11-06 00:21:34 297 SELECT * FROM t1 WHERE MONTH(d1) - 2 BETWEEN 9 AND 12; 298 d1 d2 299 2021-11-11 2028-11-06 00:21:34 300 2222-12-11 1999-05-16 21:21:34 301 SELECT * FROM t1 WHERE YEAR(d2) BETWEEN 0 AND '2020'; 302 d1 d2 303 2222-12-11 1999-05-16 21:21:34 304 SELECT * FROM t1 WHERE YEAR(d2) NOT BETWEEN 0 AND '2020'; 305 d1 d2 306 2022-09-11 2029-12-16 01:21:34 307 2021-11-11 2028-11-06 00:21:34 308 SELECT * FROM t1 WHERE DATE_ADD(d2, INTERVAL 2 HOUR) BETWEEN '2028-11-06' AND '2029-12-16'; 309 d1 d2 310 2021-11-11 2028-11-06 00:21:34 311 SELECT * FROM t1 WHERE DATE_ADD(d2, INTERVAL 2 HOUR) NOT BETWEEN '2028-11-06' AND '2029-12-16'; 312 d1 d2 313 2022-09-11 2029-12-16 01:21:34 314 2222-12-11 1999-05-16 21:21:34