github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/operator/between_and_operator.sql (about)

     1  -- @suit
     2  
     3  -- @case
     4  -- @desc:test for BETWEEN...AND and NOT BETWEEN...AND operator
     5  -- @label:bvt
     6  
     7  SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;
     8  SELECT 1 BETWEEN 2 AND 3;
     9  SELECT 'b' BETWEEN 'a' AND 'c';
    10  SELECT 2 BETWEEN 2 AND '3';
    11  
    12  DROP TABLE IF EXISTS between_and_test;
    13  CREATE TABLE between_and_test(
    14      int_test_max INT,
    15      int_test_min INT,
    16      str1 VARCHAR(20),
    17      str2 CHAR(20),
    18      float_32 FLOAT,
    19      float_64 DOUBLE
    20  );
    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  
    26  -- MO is strictly case sensitive, Ex : 'A' is not seem to 'a';
    27  SELECT * FROM between_and_test WHERE 'A' BETWEEN str1 AND str2;
    28  SELECT * FROM between_and_test WHERE 'Z' BETWEEN str1 AND str2;
    29  SELECT * FROM between_and_test WHERE 'a' BETWEEN str1 AND str2;
    30  SELECT * FROM between_and_test WHERE 'z' BETWEEN str1 AND str2;
    31  SELECT * FROM between_and_test WHERE 'ABc' BETWEEN str1 AND str2;
    32  SELECT * FROM between_and_test WHERE 'B' BETWEEN str1 AND str2;
    33  SELECT * FROM between_and_test WHERE '' BETWEEN str1 AND str2;
    34  SELECT * FROM between_and_test WHERE ' ' BETWEEN str1 AND str2;
    35  SELECT * FROM between_and_test WHERE NULL BETWEEN str1 AND str2;
    36  SELECT int_test_max, int_test_min FROM between_and_test WHERE 2147483647 BETWEEN int_test_min AND int_test_max;
    37  SELECT int_test_max, int_test_min FROM between_and_test WHERE -2147483649 BETWEEN int_test_min AND int_test_max;
    38  SELECT int_test_max, int_test_min FROM between_and_test WHERE 0 BETWEEN int_test_min AND int_test_max;
    39  SELECT float_32, float_64 FROM between_and_test WHERE float_64 BETWEEN -1.1 AND 0.00003;
    40  SELECT float_32, float_64 FROM between_and_test WHERE -0.0 BETWEEN -1.1 AND 0.00003;
    41  SELECT * FROM between_and_test WHERE float_32 BETWEEN int_test_min AND int_test_max;
    42  SELECT * FROM between_and_test WHERE float_64 BETWEEN int_test_max AND int_test_min;
    43  
    44  SELECT * FROM between_and_test WHERE NULL NOT BETWEEN 'A' AND 'z';
    45  SELECT * FROM between_and_test WHERE '#' NOT BETWEEN 'A' AND 'z';
    46  SELECT * FROM between_and_test WHERE 'A' NOT BETWEEN str1 AND str2;
    47  SELECT * FROM between_and_test WHERE 'a' NOT BETWEEN str1 AND str2;
    48  SELECT * FROM between_and_test WHERE float_32 NOT BETWEEN -2147483648 AND 2147483647;
    49  SELECT * FROM between_and_test WHERE float_64 NOT BETWEEN -2147483648 AND 2147483647;
    50  
    51  DELETE FROM between_and_test;
    52  DROP TABLE IF EXISTS between_and_test;
    53  
    54  CREATE TABLE between_and_test(
    55      d1 DATE,
    56      d2 DATETIME,
    57      d3 TIMESTAMP
    58  );
    59  
    60  INSERT INTO between_and_test() VALUES('2022-06-06','2022-06-09 13:13:16', '2022-08-11 15:56:16.4561');
    61  INSERT INTO between_and_test() VALUES('2022-06-30','2022-07-31 00:00:00', '2022-08-11 15:56:16.4561');
    62  INSERT INTO between_and_test() VALUES('2021-06-30','2022-07-31 00:00:01', '2022-09-01 13:03:13.65456');
    63  SELECT * FROM between_and_test WHERE d1 BETWEEN '2022-06-01' AND '2022-06-30';
    64  SELECT * FROM between_and_test WHERE d1 BETWEEN '2022-06-01' AND NULL;
    65  SELECT * FROM between_and_test WHERE d1 BETWEEN NULL AND '2022-06-30';
    66  SELECT * FROM between_and_test WHERE d1 BETWEEN NULL AND NULL;
    67  SELECT * FROM between_and_test WHERE d1 BETWEEN CAST('2022-06-01' AS DATE) AND '2022-06-30';
    68  SELECT * FROM between_and_test WHERE d2 BETWEEN CAST('2022-07-01' AS DATE) AND CAST('2022-07-31 00:00:00' AS DATETIME);
    69  SELECT d2 FROM between_and_test WHERE d2 BETWEEN '2022-06-01' AND '2022-07-30 23:59:59';
    70  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;
    71  
    72  SELECT * FROM between_and_test WHERE d1 NOT BETWEEN '2022-06-01' AND '2022-06-15';
    73  SELECT * FROM between_and_test WHERE d1 NOT BETWEEN '2022-06-15' AND '2022-06-01';
    74  SELECT d2 FROM between_and_test WHERE d2 NOT BETWEEN '2022-06-09 00:13:16' AND '2022-06-9 14:00:00';
    75  SELECT d2 FROM between_and_test WHERE d2 BETWEEN '2022-07-31' AND '2022-07-31';
    76  SELECT d2 FROM between_and_test WHERE d2 NOT BETWEEN '2022-07-31' AND '2022-07-31';
    77  
    78  DELETE FROM between_and_test;
    79  DROP TABLE IF EXISTS between_and_test;
    80  
    81  CREATE TABLE t1(
    82      id INT,
    83      str VARCHAR(20)
    84  );
    85  
    86  CREATE TABLE t2(
    87      id INT,
    88      order_time DATE
    89  );
    90  
    91  INSERT INTO t1 VALUES(0, 'honda'), (-1, 'toyota'), (1, ' '), (-2, 'NULL');
    92  INSERT INTO t2 VALUES(0, '2022-09-21'), (-1, '2022-09-11'), (1, '2021-09-21'), (-2, '2021-09-11');
    93  INSERT INTO t1 VALUES(2, '#$%@RETE');
    94  
    95  SELECT * FROM t1 WHERE id BETWEEN '0' AND '2';
    96  SELECT id,str FROM t1 WHERE id BETWEEN 0 AND 0 AND str BETWEEN 'A' AND 'Z';
    97  SELECT * FROM (SELECT * FROM t1 WHERE str BETWEEN 'a' AND 'x') AS t WHERE id BETWEEN 0 AND 1;
    98  SELECT * FROM (SELECT * FROM t1 WHERE str BETWEEN '' AND 'z') AS t WHERE id BETWEEN -1 AND 1;
    99  
   100  SELECT t1.id, str, order_time
   101  FROM t1 INNER JOIN t2 ON t1.id = t2.id
   102  WHERE t1.id BETWEEN -2 AND 2 AND t2.order_time BETWEEN '2022-09-11' AND '2022-09-21';
   103  
   104  SELECT t1.id, str, order_time
   105  FROM t1 INNER JOIN t2 ON t1.id = t2.id
   106  WHERE YEAR(order_time) BETWEEN '2021' AND '2022';
   107  
   108  DROP TABLE IF EXISTS t1;
   109  DROP TABLE IF EXISTS t2;
   110  
   111  CREATE TABLE t1(
   112      id VARCHAR(20),
   113      name VARCHAR(20)
   114  );
   115  
   116  CREATE TABLE t2(
   117      id VARCHAR(20),
   118      n1 INT,
   119      n2 FLOAT
   120  );
   121  
   122  INSERT INTO t1 VALUES('0001', 'Smi'), ('1111', 'Fri'), ('2222', '22TOM');
   123  INSERT INTO t2 VALUES('0001', 0001, 0.01), ('1111', 1111, -0.01), ('2222', 2222, NULL);
   124  
   125  SELECT * FROM t1 WHERE id BETWEEN '0' AND '2';
   126  SELECT * FROM t1 WHERE name BETWEEN '0' AND '2';
   127  SELECT * FROM t1 WHERE id NOT BETWEEN '0' AND '2';
   128  
   129  -- @bvt:issue#5150
   130  SELECT t1.id, name, n1, n2
   131  FROM t1 LEFT JOIN t2 ON t1.id = t2.id
   132  WHERE id NOT BETWEEN '0' AND '2' AND n2 BETWEEN NULL AND NULL;
   133  -- @bvt:issue
   134  
   135  SELECT t1.id, name, n1, n2
   136  FROM t1 LEFT JOIN t2 ON t1.id = t2.id
   137  WHERE t1.id NOT BETWEEN '0' AND '2' AND n2 BETWEEN NULL AND NULL;
   138  
   139  SELECT t1.id, name, n1, n2
   140  FROM t1 RIGHT JOIN t2 ON t1.id = t2.id
   141  WHERE t1.id NOT BETWEEN 'a' AND 'Z' AND n2 BETWEEN -0.01 AND 0.01;
   142  
   143  SELECT t1.id, name, n1, n2
   144  FROM t1 INNER JOIN t2 ON t1.id = t2.id
   145  WHERE t1.id BETWEEN '0' AND '1' AND n1 BETWEEN '0' AND 1111;
   146  
   147  DROP TABLE IF EXISTS t1;
   148  DROP TABLE IF EXISTS t2;
   149  
   150  -- @case
   151  -- @desc:test for BETWEEH...AND operator in function
   152  -- @label:bvt
   153  CREATE TABLE t1(
   154      str1 VARCHAR(50),
   155      b BOOL
   156  );
   157  
   158  INSERT INTO t1(str1) VALUES('This product is merged some high tech.');
   159  INSERT INTO t1(str1) VALUES('&^%$#@ has some error');
   160  INSERT INTO t1() VALUES('many years age, joe smith always', TRUE);
   161  INSERT INTO t1(b) VALUES(TRUE), (FALSE), (NULL);
   162  
   163  SELECT * FROM t1 WHERE SUBSTRING(str1,LENGTH(str1)) BETWEEN 'A' AND 'z';
   164  SELECT * FROM t1 WHERE SUBSTRING(str1,LENGTH(str1) - (LENGTH(str1)-1)) BETWEEN 'A' AND 'z';
   165  SELECT * FROM t1 WHERE SUBSTRING(str1,LENGTH(str1) - (LENGTH(str1)-1)) NOT BETWEEN 'A' AND 'z';
   166  SELECT * FROM t1 WHERE str1 NOT BETWEEN 'm' AND 'T';
   167  SELECT * FROM t1 WHERE b BETWEEN TRUE AND FALSE;
   168  SELECT * FROM t1 WHERE b NOT BETWEEN TRUE AND FALSE;
   169  SELECT * FROM t1 WHERE LENGTH(str1) BETWEEN 25 AND 50;
   170  SELECT * FROM t1 WHERE LENGTH(str1) BETWEEN '25'+5 AND 50;
   171  SELECT * FROM t1 WHERE LENGTH(str1) NOT BETWEEN 25 AND 50;
   172  
   173  DROP TABLE IF EXISTS t1;
   174  
   175  CREATE TABLE t1(
   176      d1 DATE,
   177      d2 DATETIME
   178  );
   179  
   180  INSERT INTO t1 VALUES('2022-09-11', '2029-12-16 01:21:34');
   181  INSERT INTO t1 VALUES('2021-11-11', '2028-11-06 00:21:34');
   182  INSERT INTO t1 VALUES('2222-12-11', '1999-05-16 21:21:34');
   183  
   184  SELECT * FROM t1 WHERE DATE_ADD(d1, INTERVAL 2 MONTH) BETWEEN '2022-01-01' AND '2022-12-31';
   185  SELECT * FROM t1 WHERE DATE_SUB(d1, INTERVAL 10 DAY) BETWEEN '2021-11-11' AND '2022-12-31';
   186  SELECT * FROM t1 WHERE d1 BETWEEN DATE_SUB('2022-09-11', INTERVAL 10 DAY) AND DATE_SUB('2222-12-11', INTERVAL -200 YEAR);
   187  SELECT * FROM t1 WHERE d1 NOT BETWEEN DATE_SUB('2022-09-11', INTERVAL 10 DAY) AND DATE_SUB('2222-12-11', INTERVAL -200 YEAR);
   188  SELECT * FROM t1 WHERE MONTH(d1) - 2 BETWEEN 9 AND 12;
   189  SELECT * FROM t1 WHERE YEAR(d2) BETWEEN 0 AND '2020';
   190  SELECT * FROM t1 WHERE YEAR(d2) NOT BETWEEN 0 AND '2020';
   191  SELECT * FROM t1 WHERE DATE_ADD(d2, INTERVAL 2 HOUR) BETWEEN '2028-11-06' AND '2029-12-16';
   192  SELECT * FROM t1 WHERE DATE_ADD(d2, INTERVAL 2 HOUR) NOT BETWEEN '2028-11-06' AND '2029-12-16';