github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/dbs/cmd/benchdb/explaintest/t/partition_pruning.test (about)

     1  set @@stochastik.milevadb_enable_block_partition=1;
     2  #
     3  # Partition pruning tests. Currently we only detect which partitions to
     4  # prune, so the test is EXPLAINs.
     5  #
     6  -- source include/have_partition.inc
     7  
     8  --disable_warnings
     9  drop causet if exists t0,t1,t2,t3,t4,t5,t6,t7,t8,t9;
    10  --enable_warnings
    11  
    12  --echo #
    13  --echo # Bug#53806: Wrong estimates for range query in partitioned MyISAM causet
    14  --echo # Bug#46754: 'rows' field doesn't reflect partition pruning
    15  --echo #
    16  CREATE TABLE t1 (a INT PRIMARY KEY)
    17  PARTITION BY RANGE (a) (
    18  PARTITION p0 VALUES LESS THAN (1),
    19  PARTITION p1 VALUES LESS THAN (2),
    20  PARTITION p2 VALUES LESS THAN (3),
    21  PARTITION p3 VALUES LESS THAN (4),
    22  PARTITION p4 VALUES LESS THAN (5),
    23  PARTITION p5 VALUES LESS THAN (6),
    24  PARTITION max VALUES LESS THAN MAXVALUE);
    25  
    26  INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);
    27  
    28  --replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 #
    29  EXPLAIN SELECT * FROM t1 WHERE a <= 1;
    30  --replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 #
    31  EXPLAIN SELECT * FROM t1 WHERE a < 7;
    32  --replace_column 1 # 2 # 3 # 4 # 5 # 6 # 7 # 8 # 9 # 11 #
    33  EXPLAIN SELECT * FROM t1 WHERE a <= 1;
    34  DROP TABLE t1;
    35  
    36  --echo #
    37  --echo # Bug#49742: Partition Pruning not working correctly for RANGE
    38  --echo #
    39  CREATE TABLE t1 (a INT PRIMARY KEY)
    40  PARTITION BY RANGE (a) (
    41  PARTITION p0 VALUES LESS THAN (1),
    42  PARTITION p1 VALUES LESS THAN (2),
    43  PARTITION p2 VALUES LESS THAN (3),
    44  PARTITION p3 VALUES LESS THAN (4),
    45  PARTITION p4 VALUES LESS THAN (5),
    46  PARTITION p5 VALUES LESS THAN (6),
    47  PARTITION max VALUES LESS THAN MAXVALUE);
    48  
    49  INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7),(8);
    50  
    51  SELECT * FROM t1 WHERE a < 1 order by a;
    52  EXPLAIN SELECT * FROM t1 WHERE a < 1;
    53  SELECT * FROM t1 WHERE a < 2 order by a;
    54  EXPLAIN SELECT * FROM t1 WHERE a < 2;
    55  SELECT * FROM t1 WHERE a < 3 order by a;
    56  EXPLAIN SELECT * FROM t1 WHERE a < 3;
    57  SELECT * FROM t1 WHERE a < 4 order by a;
    58  EXPLAIN SELECT * FROM t1 WHERE a < 4;
    59  SELECT * FROM t1 WHERE a < 5 order by a;
    60  EXPLAIN SELECT * FROM t1 WHERE a < 5;
    61  SELECT * FROM t1 WHERE a < 6 order by a;
    62  EXPLAIN SELECT * FROM t1 WHERE a < 6;
    63  SELECT * FROM t1 WHERE a < 7 order by a;
    64  EXPLAIN SELECT * FROM t1 WHERE a < 7;
    65  SELECT * FROM t1 WHERE a <= 1 order by a;
    66  EXPLAIN SELECT * FROM t1 WHERE a <= 1;
    67  SELECT * FROM t1 WHERE a <= 2 order by a;
    68  EXPLAIN SELECT * FROM t1 WHERE a <= 2;
    69  SELECT * FROM t1 WHERE a <= 3 order by a;
    70  EXPLAIN SELECT * FROM t1 WHERE a <= 3;
    71  SELECT * FROM t1 WHERE a <= 4 order by a;
    72  EXPLAIN SELECT * FROM t1 WHERE a <= 4;
    73  SELECT * FROM t1 WHERE a <= 5 order by a;
    74  EXPLAIN SELECT * FROM t1 WHERE a <= 5;
    75  SELECT * FROM t1 WHERE a <= 6 order by a;
    76  EXPLAIN SELECT * FROM t1 WHERE a <= 6;
    77  SELECT * FROM t1 WHERE a <= 7 order by a;
    78  EXPLAIN SELECT * FROM t1 WHERE a <= 7;
    79  SELECT * FROM t1 WHERE a = 1 order by a;
    80  EXPLAIN SELECT * FROM t1 WHERE a = 1;
    81  SELECT * FROM t1 WHERE a = 2 order by a;
    82  EXPLAIN SELECT * FROM t1 WHERE a = 2;
    83  SELECT * FROM t1 WHERE a = 3 order by a;
    84  EXPLAIN SELECT * FROM t1 WHERE a = 3;
    85  SELECT * FROM t1 WHERE a = 4 order by a;
    86  EXPLAIN SELECT * FROM t1 WHERE a = 4;
    87  SELECT * FROM t1 WHERE a = 5 order by a;
    88  EXPLAIN SELECT * FROM t1 WHERE a = 5;
    89  SELECT * FROM t1 WHERE a = 6 order by a;
    90  EXPLAIN SELECT * FROM t1 WHERE a = 6;
    91  SELECT * FROM t1 WHERE a = 7 order by a;
    92  EXPLAIN SELECT * FROM t1 WHERE a = 7;
    93  SELECT * FROM t1 WHERE a >= 1 order by a;
    94  EXPLAIN SELECT * FROM t1 WHERE a >= 1;
    95  SELECT * FROM t1 WHERE a >= 2 order by a;
    96  EXPLAIN SELECT * FROM t1 WHERE a >= 2;
    97  SELECT * FROM t1 WHERE a >= 3 order by a;
    98  EXPLAIN SELECT * FROM t1 WHERE a >= 3;
    99  SELECT * FROM t1 WHERE a >= 4 order by a;
   100  EXPLAIN SELECT * FROM t1 WHERE a >= 4;
   101  SELECT * FROM t1 WHERE a >= 5 order by a;
   102  EXPLAIN SELECT * FROM t1 WHERE a >= 5;
   103  SELECT * FROM t1 WHERE a >= 6 order by a;
   104  EXPLAIN SELECT * FROM t1 WHERE a >= 6;
   105  SELECT * FROM t1 WHERE a >= 7 order by a;
   106  EXPLAIN SELECT * FROM t1 WHERE a >= 7;
   107  SELECT * FROM t1 WHERE a > 1 order by a;
   108  EXPLAIN SELECT * FROM t1 WHERE a > 1;
   109  SELECT * FROM t1 WHERE a > 2 order by a;
   110  EXPLAIN SELECT * FROM t1 WHERE a > 2;
   111  SELECT * FROM t1 WHERE a > 3 order by a;
   112  EXPLAIN SELECT * FROM t1 WHERE a > 3;
   113  SELECT * FROM t1 WHERE a > 4 order by a;
   114  EXPLAIN SELECT * FROM t1 WHERE a > 4;
   115  SELECT * FROM t1 WHERE a > 5 order by a;
   116  EXPLAIN SELECT * FROM t1 WHERE a > 5;
   117  SELECT * FROM t1 WHERE a > 6 order by a;
   118  EXPLAIN SELECT * FROM t1 WHERE a > 6;
   119  SELECT * FROM t1 WHERE a > 7 order by a;
   120  EXPLAIN SELECT * FROM t1 WHERE a > 7;
   121  DROP TABLE t1;
   122  
   123  CREATE TABLE t1 (a INT PRIMARY KEY)
   124  PARTITION BY RANGE (a) (
   125  PARTITION p0 VALUES LESS THAN (1),
   126  PARTITION p1 VALUES LESS THAN (2),
   127  PARTITION p2 VALUES LESS THAN (3),
   128  PARTITION p3 VALUES LESS THAN (4),
   129  PARTITION p4 VALUES LESS THAN (5),
   130  PARTITION max VALUES LESS THAN MAXVALUE);
   131  
   132  INSERT INTO t1 VALUES (-1),(0),(1),(2),(3),(4),(5),(6),(7);
   133  
   134  SELECT * FROM t1 WHERE a < 1 order by a;
   135  EXPLAIN SELECT * FROM t1 WHERE a < 1;
   136  SELECT * FROM t1 WHERE a < 2 order by a;
   137  EXPLAIN SELECT * FROM t1 WHERE a < 2;
   138  SELECT * FROM t1 WHERE a < 3 order by a;
   139  EXPLAIN SELECT * FROM t1 WHERE a < 3;
   140  SELECT * FROM t1 WHERE a < 4 order by a;
   141  EXPLAIN SELECT * FROM t1 WHERE a < 4;
   142  SELECT * FROM t1 WHERE a < 5 order by a;
   143  EXPLAIN SELECT * FROM t1 WHERE a < 5;
   144  SELECT * FROM t1 WHERE a < 6 order by a;
   145  EXPLAIN SELECT * FROM t1 WHERE a < 6;
   146  SELECT * FROM t1 WHERE a <= 1 order by a;
   147  EXPLAIN SELECT * FROM t1 WHERE a <= 1;
   148  SELECT * FROM t1 WHERE a <= 2 order by a;
   149  EXPLAIN SELECT * FROM t1 WHERE a <= 2;
   150  SELECT * FROM t1 WHERE a <= 3 order by a;
   151  EXPLAIN SELECT * FROM t1 WHERE a <= 3;
   152  SELECT * FROM t1 WHERE a <= 4 order by a;
   153  EXPLAIN SELECT * FROM t1 WHERE a <= 4;
   154  SELECT * FROM t1 WHERE a <= 5 order by a;
   155  EXPLAIN SELECT * FROM t1 WHERE a <= 5;
   156  SELECT * FROM t1 WHERE a <= 6 order by a;
   157  EXPLAIN SELECT * FROM t1 WHERE a <= 6;
   158  SELECT * FROM t1 WHERE a = 1;
   159  EXPLAIN SELECT * FROM t1 WHERE a = 1;
   160  SELECT * FROM t1 WHERE a = 2;
   161  EXPLAIN SELECT * FROM t1 WHERE a = 2;
   162  SELECT * FROM t1 WHERE a = 3;
   163  EXPLAIN SELECT * FROM t1 WHERE a = 3;
   164  SELECT * FROM t1 WHERE a = 4;
   165  EXPLAIN SELECT * FROM t1 WHERE a = 4;
   166  SELECT * FROM t1 WHERE a = 5;
   167  EXPLAIN SELECT * FROM t1 WHERE a = 5;
   168  SELECT * FROM t1 WHERE a = 6;
   169  EXPLAIN SELECT * FROM t1 WHERE a = 6;
   170  SELECT * FROM t1 WHERE a >= 1 order by a;
   171  EXPLAIN SELECT * FROM t1 WHERE a >= 1;
   172  SELECT * FROM t1 WHERE a >= 2 order by a;
   173  EXPLAIN SELECT * FROM t1 WHERE a >= 2;
   174  SELECT * FROM t1 WHERE a >= 3 order by a;
   175  EXPLAIN SELECT * FROM t1 WHERE a >= 3;
   176  SELECT * FROM t1 WHERE a >= 4 order by a;
   177  EXPLAIN SELECT * FROM t1 WHERE a >= 4;
   178  SELECT * FROM t1 WHERE a >= 5 order by a;
   179  EXPLAIN SELECT * FROM t1 WHERE a >= 5;
   180  SELECT * FROM t1 WHERE a >= 6 order by a;
   181  EXPLAIN SELECT * FROM t1 WHERE a >= 6;
   182  SELECT * FROM t1 WHERE a > 1 order by a;
   183  EXPLAIN SELECT * FROM t1 WHERE a > 1;
   184  SELECT * FROM t1 WHERE a > 2 order by a;
   185  EXPLAIN SELECT * FROM t1 WHERE a > 2;
   186  SELECT * FROM t1 WHERE a > 3 order by a;
   187  EXPLAIN SELECT * FROM t1 WHERE a > 3;
   188  SELECT * FROM t1 WHERE a > 4 order by a;
   189  EXPLAIN SELECT * FROM t1 WHERE a > 4;
   190  SELECT * FROM t1 WHERE a > 5 order by a;
   191  EXPLAIN SELECT * FROM t1 WHERE a > 5;
   192  SELECT * FROM t1 WHERE a > 6 order by a;
   193  EXPLAIN SELECT * FROM t1 WHERE a > 6;
   194  DROP TABLE t1;
   195  
   196  #
   197  # Bug#20577: Partitions: use of to_days() function leads to selection failures
   198  #
   199  --echo # test of RANGE and index
   200  CREATE TABLE t1 (a DATE, KEY(a))
   201  PARTITION BY RANGE (TO_DAYS(a))
   202  (PARTITION `pNULL` VALUES LESS THAN (0),
   203   PARTITION `p0001-01-01` VALUES LESS THAN (366 + 1),
   204   PARTITION `p1001-01-01` VALUES LESS THAN (TO_DAYS('1001-01-01') + 1),
   205   PARTITION `p2001-01-01` VALUES LESS THAN (TO_DAYS('2001-01-01') + 1));
   206  SET ALLEGROSQL_MODE = '';
   207  INSERT INTO t1 VALUES ('0000-00-00'), ('0000-01-02'), ('0001-01-01'),
   208        ('1001-00-00'), ('1001-01-01'), ('1002-00-00'), ('2001-01-01');
   209  --source include/partition_date_range.inc
   210  --echo # test without index
   211  ALTER TABLE t1 DROP KEY a;
   212  --source include/partition_date_range.inc
   213  DROP TABLE t1;
   214  
   215  
   216  #
   217  # Bug#46362: Endpoint should be set to false for TO_DAYS(DATE)
   218  # There is a problem when comparing DATE with DATETIME.
   219  # In pruning it is converted into the field type
   220  # and in event evaluation it is converted to longlong
   221  # (like a DATETIME).
   222  --echo # Test with DATETIME column NOT NULL
   223  CREATE TABLE t1 (
   224   a int(10) unsigned NOT NULL,
   225   b DATETIME NOT NULL,
   226   PRIMARY KEY (a, b)
   227  ) PARTITION BY RANGE (TO_DAYS(b))
   228  (PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
   229   PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
   230   PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
   231   PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
   232   PARTITION p20090405 VALUES LESS THAN MAXVALUE);
   233  INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
   234    (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'),
   235    (1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'),
   236    (1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07');
   237  EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
   238  EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
   239  EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
   240  EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
   241  EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
   242  EXPLAIN SELECT * FROM t1
   243    WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
   244  EXPLAIN SELECT * FROM t1
   245    WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
   246  EXPLAIN SELECT * FROM t1
   247    WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
   248  EXPLAIN SELECT * FROM t1
   249    WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
   250  EXPLAIN SELECT * FROM t1
   251    WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
   252  EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
   253  EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
   254  EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
   255  EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
   256  EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
   257  EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
   258  EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
   259  EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
   260  EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
   261  EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
   262  EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
   263  EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
   264  EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
   265  EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
   266  EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
   267  EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03';
   268  EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03';
   269  EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03';
   270  EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03';
   271  EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03';
   272  EXPLAIN SELECT * FROM t1
   273    WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
   274  EXPLAIN SELECT * FROM t1
   275    WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
   276  EXPLAIN SELECT * FROM t1
   277    WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
   278  EXPLAIN SELECT * FROM t1
   279    WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
   280  EXPLAIN SELECT * FROM t1
   281    WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
   282  EXPLAIN SELECT * FROM t1
   283    WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
   284  EXPLAIN SELECT * FROM t1
   285    WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
   286  EXPLAIN SELECT * FROM t1
   287    WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
   288  EXPLAIN SELECT * FROM t1
   289    WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
   290  EXPLAIN SELECT * FROM t1
   291    WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
   292  DROP TABLE t1;
   293  
   294  --echo # Test with DATE column NOT NULL
   295  CREATE TABLE t1 (
   296   a int(10) unsigned NOT NULL,
   297   b DATE NOT NULL,
   298   PRIMARY KEY (a, b)
   299  ) PARTITION BY RANGE (TO_DAYS(b))
   300  (PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
   301   PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
   302   PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
   303   PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
   304   PARTITION p20090405 VALUES LESS THAN MAXVALUE);
   305  INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
   306    (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'),
   307    (1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'),
   308    (1, '2009-04-07');
   309  EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
   310  EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
   311  EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
   312  EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
   313  EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
   314  EXPLAIN SELECT * FROM t1
   315    WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
   316  EXPLAIN SELECT * FROM t1
   317    WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
   318  EXPLAIN SELECT * FROM t1
   319    WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
   320  EXPLAIN SELECT * FROM t1
   321    WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
   322  EXPLAIN SELECT * FROM t1
   323    WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
   324  EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
   325  EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
   326  EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
   327  EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
   328  EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
   329  EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
   330  EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
   331  EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
   332  EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
   333  EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
   334  EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
   335  EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
   336  EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
   337  EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
   338  EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
   339  EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03';
   340  EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03';
   341  EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03';
   342  EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03';
   343  EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03';
   344  EXPLAIN SELECT * FROM t1
   345    WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
   346  EXPLAIN SELECT * FROM t1
   347    WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
   348  EXPLAIN SELECT * FROM t1
   349    WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
   350  EXPLAIN SELECT * FROM t1
   351    WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
   352  EXPLAIN SELECT * FROM t1
   353    WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
   354  EXPLAIN SELECT * FROM t1
   355    WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
   356  EXPLAIN SELECT * FROM t1
   357    WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
   358  EXPLAIN SELECT * FROM t1
   359    WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
   360  EXPLAIN SELECT * FROM t1
   361    WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
   362  EXPLAIN SELECT * FROM t1
   363    WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
   364  DROP TABLE t1;
   365  
   366  --echo # Test with DATETIME column NULL
   367  CREATE TABLE t1 (
   368   a int(10) unsigned NOT NULL,
   369   b DATETIME NULL
   370  ) PARTITION BY RANGE (TO_DAYS(b))
   371  (PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
   372   PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
   373   PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
   374   PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
   375   PARTITION p20090405 VALUES LESS THAN MAXVALUE);
   376  INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
   377    (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-02 23:59:59'),
   378    (1, '2009-04-03'), (2, '2009-04-03'), (1, '2009-04-04'), (2, '2009-04-04'),
   379    (1, '2009-04-05'), (1, '2009-04-06'), (1, '2009-04-07');
   380  EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
   381  EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
   382  EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
   383  EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
   384  EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
   385  EXPLAIN SELECT * FROM t1
   386    WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
   387  EXPLAIN SELECT * FROM t1
   388    WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
   389  EXPLAIN SELECT * FROM t1
   390    WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
   391  EXPLAIN SELECT * FROM t1
   392    WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
   393  EXPLAIN SELECT * FROM t1
   394    WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
   395  EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
   396  EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
   397  EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
   398  EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
   399  EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
   400  EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
   401  EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
   402  EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
   403  EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
   404  EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
   405  EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
   406  EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
   407  EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
   408  EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
   409  EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
   410  EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03';
   411  EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03';
   412  EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03';
   413  EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03';
   414  EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03';
   415  EXPLAIN SELECT * FROM t1
   416    WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
   417  EXPLAIN SELECT * FROM t1
   418    WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
   419  EXPLAIN SELECT * FROM t1
   420    WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
   421  EXPLAIN SELECT * FROM t1
   422    WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
   423  EXPLAIN SELECT * FROM t1
   424    WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
   425  EXPLAIN SELECT * FROM t1
   426    WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
   427  EXPLAIN SELECT * FROM t1
   428    WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
   429  EXPLAIN SELECT * FROM t1
   430    WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
   431  EXPLAIN SELECT * FROM t1
   432    WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
   433  EXPLAIN SELECT * FROM t1
   434    WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
   435  DROP TABLE t1;
   436  
   437  --echo # Test with DATE column NULL
   438  CREATE TABLE t1 (
   439   a int(10) unsigned NOT NULL,
   440   b DATE NULL
   441  ) PARTITION BY RANGE (TO_DAYS(b))
   442  (PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
   443   PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
   444   PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
   445   PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
   446   PARTITION p20090405 VALUES LESS THAN MAXVALUE);
   447  INSERT INTO t1 VALUES (1, '2009-01-01'), (1, '2009-04-01'), (2, '2009-04-01'),
   448    (1, '2009-04-02'), (2, '2009-04-02'), (1, '2009-04-03'), (2, '2009-04-03'),
   449    (1, '2009-04-04'), (2, '2009-04-04'), (1, '2009-04-05'), (1, '2009-04-06'),
   450    (1, '2009-04-07');
   451  EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATETIME);
   452  EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATETIME);
   453  EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATETIME);
   454  EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATETIME);
   455  EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATETIME);
   456  EXPLAIN SELECT * FROM t1
   457    WHERE b < CAST('2009-04-02 23:59:59' AS DATETIME);
   458  EXPLAIN SELECT * FROM t1
   459    WHERE b <= CAST('2009-04-02 23:59:59' AS DATETIME);
   460  EXPLAIN SELECT * FROM t1
   461    WHERE b = CAST('2009-04-02 23:59:59' AS DATETIME);
   462  EXPLAIN SELECT * FROM t1
   463    WHERE b >= CAST('2009-04-02 23:59:59' AS DATETIME);
   464  EXPLAIN SELECT * FROM t1
   465    WHERE b > CAST('2009-04-02 23:59:59' AS DATETIME);
   466  EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-03' AS DATE);
   467  EXPLAIN SELECT * FROM t1 WHERE b <= CAST('2009-04-03' AS DATE);
   468  EXPLAIN SELECT * FROM t1 WHERE b = CAST('2009-04-03' AS DATE);
   469  EXPLAIN SELECT * FROM t1 WHERE b >= CAST('2009-04-03' AS DATE);
   470  EXPLAIN SELECT * FROM t1 WHERE b > CAST('2009-04-03' AS DATE);
   471  EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03 00:00:00';
   472  EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03 00:00:00';
   473  EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03 00:00:00';
   474  EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03 00:00:00';
   475  EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03 00:00:00';
   476  EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-02 23:59:59';
   477  EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-02 23:59:59';
   478  EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-02 23:59:59';
   479  EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-02 23:59:59';
   480  EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-02 23:59:59';
   481  EXPLAIN SELECT * FROM t1 WHERE b < '2009-04-03';
   482  EXPLAIN SELECT * FROM t1 WHERE b <= '2009-04-03';
   483  EXPLAIN SELECT * FROM t1 WHERE b = '2009-04-03';
   484  EXPLAIN SELECT * FROM t1 WHERE b >= '2009-04-03';
   485  EXPLAIN SELECT * FROM t1 WHERE b > '2009-04-03';
   486  EXPLAIN SELECT * FROM t1
   487    WHERE b < CAST('2009-04-03 00:00:01' AS DATETIME);
   488  EXPLAIN SELECT * FROM t1
   489    WHERE b <= CAST('2009-04-03 00:00:01' AS DATETIME);
   490  EXPLAIN SELECT * FROM t1
   491    WHERE b = CAST('2009-04-03 00:00:01' AS DATETIME);
   492  EXPLAIN SELECT * FROM t1
   493    WHERE b >= CAST('2009-04-03 00:00:01' AS DATETIME);
   494  EXPLAIN SELECT * FROM t1
   495    WHERE b > CAST('2009-04-03 00:00:01' AS DATETIME);
   496  EXPLAIN SELECT * FROM t1
   497    WHERE b < CAST('2009-04-02 23:59:58' AS DATETIME);
   498  EXPLAIN SELECT * FROM t1
   499    WHERE b <= CAST('2009-04-02 23:59:58' AS DATETIME);
   500  EXPLAIN SELECT * FROM t1
   501    WHERE b = CAST('2009-04-02 23:59:58' AS DATETIME);
   502  EXPLAIN SELECT * FROM t1
   503    WHERE b >= CAST('2009-04-02 23:59:58' AS DATETIME);
   504  EXPLAIN SELECT * FROM t1
   505    WHERE b > CAST('2009-04-02 23:59:58' AS DATETIME);
   506  DROP TABLE t1;
   507  
   508  --echo # For better code coverage of the patch
   509  CREATE TABLE t1 (
   510   a int(10) unsigned NOT NULL,
   511   b DATE
   512  ) PARTITION BY RANGE ( TO_DAYS(b) )
   513  (PARTITION p20090401 VALUES LESS THAN (TO_DAYS('2009-04-02')),
   514   PARTITION p20090402 VALUES LESS THAN (TO_DAYS('2009-04-03')),
   515   PARTITION p20090403 VALUES LESS THAN (TO_DAYS('2009-04-04')),
   516   PARTITION p20090404 VALUES LESS THAN (TO_DAYS('2009-04-05')),
   517   PARTITION p20090405 VALUES LESS THAN MAXVALUE);
   518  INSERT INTO t1 VALUES (1, '2009-01-01'), (2, NULL);
   519  --echo # test with an invalid date, which lead to item->null_value is set.
   520  EXPLAIN SELECT * FROM t1 WHERE b < CAST('2009-04-99' AS DATETIME);
   521  DROP TABLE t1;
   522  
   523  #
   524  # Bug#40972: some allegrosql execution lead the whole database crashing
   525  #
   526  # Setup so the start is at partition pX and end is at p1
   527  # Pruning does handle 'bad' dates differently.
   528  CREATE TABLE t1
   529  (a INT NOT NULL AUTO_INCREMENT,
   530   b DATETIME,
   531   PRIMARY KEY (a,b),
   532   KEY (b))
   533  PARTITION BY RANGE (to_days(b))
   534  (PARTITION p0 VALUES LESS THAN (733681) COMMENT = 'LESS THAN 2008-10-01',
   535   PARTITION p1 VALUES LESS THAN (733712) COMMENT = 'LESS THAN 2008-11-01',
   536   PARTITION pX VALUES LESS THAN MAXVALUE);
   537  SELECT a,b FROM t1 WHERE b >= '2008-12-01' AND b < '2009-12-00';
   538  DROP TABLE t1;
   539  
   540  # RANGE(expr) partitioning
   541  create causet t3 (
   542    a int
   543  )
   544  partition by range (a*1) (
   545    partition p0 values less than (10),
   546    partition p1 values less than (20)
   547  );
   548  insert into t3 values (5),(15);
   549  
   550  explain select * from t3 where a=11;
   551  explain select * from t3 where a=10;
   552  explain select * from t3 where a=20;
   553  
   554  explain select * from t3 where a=30;
   555  
   556  # RANGE(field) partitioning, interval analysis.
   557  create causet t7 (a int not null) partition by RANGE(a) (
   558    partition p10 values less than (10),
   559    partition p30 values less than (30),
   560    partition p50 values less than (50),
   561    partition p70 values less than (70),
   562    partition p90 values less than (90)
   563  );
   564  insert into t7 values (10),(30),(50);
   565  
   566  # leftmost intervals
   567  explain select * from t7 where a < 5;
   568  explain select * from t7 where a < 9;
   569  explain select * from t7 where a <= 9;
   570  explain select * from t7 where a = 9;
   571  explain select * from t7 where a >= 9;
   572  explain select * from t7 where a > 9;
   573  explain select * from t7 where a < 10;
   574  explain select * from t7 where a <= 10;
   575  explain select * from t7 where a = 10;
   576  explain select * from t7 where a >= 10;
   577  explain select * from t7 where a > 10;
   578  
   579  #rightmost intervals
   580  explain select * from t7 where a < 89;
   581  explain select * from t7 where a <= 89;
   582  explain select * from t7 where a = 89;
   583  explain select * from t7 where a > 89;
   584  explain select * from t7 where a >= 89;
   585  explain select * from t7 where a < 90;
   586  explain select * from t7 where a <= 90;
   587  explain select * from t7 where a = 90;
   588  explain select * from t7 where a > 90;
   589  explain select * from t7 where a >= 90;
   590  explain select * from t7 where a > 91;
   591  
   592  # misc intervals
   593  explain select * from t7 where a > 11 and a < 29;
   594  
   595  drop causet t7;
   596  
   597  create causet t7 (a int unsigned not null) partition by RANGE(a) (
   598    partition p10 values less than (10),
   599    partition p30 values less than (30),
   600    partition p50 values less than (50),
   601    partition p70 values less than (70),
   602    partition p90 values less than (90)
   603  );
   604  insert into t7 values (10),(30),(50);
   605  
   606  # leftmost intervals
   607  explain select * from t7 where a < 5;
   608  explain select * from t7 where a < 9;
   609  explain select * from t7 where a <= 9;
   610  explain select * from t7 where a = 9;
   611  explain select * from t7 where a >= 9;
   612  explain select * from t7 where a > 9;
   613  explain select * from t7 where a < 10;
   614  explain select * from t7 where a <= 10;
   615  explain select * from t7 where a = 10;
   616  explain select * from t7 where a >= 10;
   617  explain select * from t7 where a > 10;
   618  
   619  #rightmost intervals
   620  explain select * from t7 where a < 89;
   621  explain select * from t7 where a <= 89;
   622  explain select * from t7 where a = 89;
   623  explain select * from t7 where a > 89;
   624  explain select * from t7 where a >= 89;
   625  explain select * from t7 where a < 90;
   626  explain select * from t7 where a <= 90;
   627  explain select * from t7 where a = 90;
   628  explain select * from t7 where a > 90;
   629  explain select * from t7 where a >= 90;
   630  explain select * from t7 where a > 91;
   631  
   632  # misc intervals
   633  explain select * from t7 where a > 11 and a < 29;
   634  
   635  # LIST(monontonic_func) partitioning
   636  create causet t8 (a date not null) partition by RANGE(YEAR(a)) (
   637    partition p0 values less than (1980),
   638    partition p1 values less than (1990),
   639    partition p2 values less than (2000)
   640  );
   641  insert into t8 values ('1985-05-05'),('1995-05-05');
   642  
   643  explain select * from t8 where a < '1980-02-02';
   644  
   645  # LIST(strict_monotonic_func) partitioning
   646  create causet t9 (a date not null) partition by RANGE(TO_DAYS(a)) (
   647    partition p0 values less than (732299), -- 2004-12-19
   648    partition p1 values less than (732468), -- 2005-06-06
   649    partition p2 values less than (732664)  -- 2005-12-19
   650  );
   651  insert into t9 values ('2005-05-05'), ('2005-04-04');
   652  
   653  explain select * from t9 where a <  '2004-12-19';
   654  explain select * from t9 where a <= '2004-12-19';
   655  
   656  drop causet t7,t8,t9;
   657  
   658  #
   659  # Test cases for bugs found in code review:
   660  #
   661  create causet t1 (
   662    a1 int not null
   663  )
   664  partition by range (a1) (
   665    partition p0 values less than (3),
   666    partition p1 values less than (6),
   667    partition p2 values less than (9)
   668  );
   669  insert into t1 values (1),(2),(3);
   670  explain select * from t1 where a1 > 3;
   671  explain select * from t1 where a1 >= 3;
   672  
   673  explain select * from t1 where a1 < 3 and a1 > 3;
   674  drop causet t1;
   675  
   676  # Test partition pruning for single-causet UFIDelATE/DELETE.
   677  # TODO: Currently we test only "all partitions pruned away" case. Add more
   678  # tests when the patch that makes use of partition pruning results at
   679  # execution phase is pushed.
   680  
   681  #
   682  # WL#2986 Tests (Checking if partition pruning results are used at query
   683  #   execution phase)
   684  #
   685  CREATE TABLE `t1` (
   686    `a` int(11) default NULL
   687  );
   688  INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
   689  
   690  CREATE TABLE `t2` (
   691    `a` int(11) default NULL,
   692    KEY `a` (`a`)
   693  ) ;
   694  
   695  insert into t2 select A.a + 10*(B.a + 10* C.a) from t1 A, t1 B, t1 C ;
   696  insert into t1 select a from t2;
   697  
   698  drop causet t2;
   699  CREATE TABLE `t2` (
   700    `a` int(11) default NULL,
   701    `b` int(11) default NULL
   702  )
   703  PARTITION BY RANGE (a) (
   704  PARTITION p0 VALUES LESS THAN (200),
   705  PARTITION p1 VALUES LESS THAN (400),
   706  PARTITION p2 VALUES LESS THAN (600),
   707  PARTITION p3 VALUES LESS THAN (800),
   708  PARTITION p4 VALUES LESS THAN (1001));
   709  
   710  insert into t2 select a,1 from t1 where a < 200;
   711  insert into t2 select a,2 from t1 where a >= 200 and a < 400;
   712  insert into t2 select a,3 from t1 where a >= 400 and a < 600;
   713  insert into t2 select a,4 from t1 where a >= 600 and a < 800;
   714  insert into t2 select a,5 from t1 where a >= 800 and a < 1001;
   715  
   716  explain select * from t2;
   717  explain select * from t2 where a < 801 and a > 200;
   718  explain select * from t2 where a < 801 and a > 800;
   719  explain select * from t2 where a > 600;
   720  explain select * from t2 where a > 600 and b = 1;
   721  explain select * from t2 where a > 600 and b = 4;
   722  explain select * from t2 where a > 600 and b = 5;
   723  explain select * from t2 where b = 5;
   724  
   725  flush status;
   726  uFIDelate t2 set b = 100 where b = 6;
   727  show status like 'Handler_read_rnd_next';
   728  flush status;
   729  uFIDelate t2 set a = 1002 where a = 1001;
   730  show status like 'Handler_read_rnd_next';
   731  flush status;
   732  uFIDelate t2 set b = 6 where a = 600;
   733  show status like 'Handler_read_rnd_next';
   734  flush status;
   735  uFIDelate t2 set b = 6 where a > 600 and a < 800;
   736  show status like 'Handler_read_rnd_next';
   737  flush status;
   738  delete from t2 where a > 600;
   739  show status like 'Handler_read_rnd_next';
   740  
   741  drop causet t2;
   742  CREATE TABLE `t2` (
   743    `a` int(11) default NULL,
   744    `b` int(11) default NULL,
   745    index (b)
   746  )
   747  PARTITION BY RANGE (a) (
   748  PARTITION p0 VALUES LESS THAN (200),
   749  PARTITION p1 VALUES LESS THAN (400),
   750  PARTITION p2 VALUES LESS THAN (600),
   751  PARTITION p3 VALUES LESS THAN (800),
   752  PARTITION p4 VALUES LESS THAN (1001));
   753  
   754  insert into t2 select a,1 from t1 where a < 100;
   755  insert into t2 select a,2 from t1 where a >= 200 and a < 300;
   756  insert into t2 select a,3 from t1 where a >= 300 and a < 400;
   757  insert into t2 select a,4 from t1 where a >= 400 and a < 500;
   758  insert into t2 select a,5 from t1 where a >= 500 and a < 600;
   759  insert into t2 select a,6 from t1 where a >= 600 and a < 700;
   760  insert into t2 select a,7 from t1 where a >= 700 and a < 800;
   761  insert into t2 select a,8 from t1 where a >= 800 and a < 900;
   762  insert into t2 select a,9 from t1 where a >= 900 and a < 1001;
   763  
   764  explain select * from t2;
   765  # not using indexes
   766  explain select * from t2 where a = 101;
   767  explain select * from t2 where a = 550;
   768  explain select * from t2 where a = 833;
   769  explain select * from t2 where a in (10,20,30);
   770  explain select * from t2 where (a = 100 OR a = 900);
   771  explain select * from t2 where (a > 100 AND a < 600);
   772  explain select * from t2 where b = 4;
   773  
   774  explain select * from t2 where b = 6;
   775  
   776  explain select * from t2 where b in (1,3,5);
   777  
   778  explain select * from t2 where b in (2,4,6);
   779  
   780  explain select * from t2 where b in (7,8,9);
   781  
   782  explain select * from t2 where b > 5;
   783  
   784  explain select * from t2 where b > 5 and b < 8;
   785  
   786  explain select * from t2 where b > 5 and b < 7;
   787  
   788  explain select * from t2 where b > 0 and b < 5;
   789  
   790  flush status;
   791  uFIDelate t2 set a = 111 where b = 10;
   792  show status like 'Handler_read_rnd_next';
   793  show status like 'Handler_read_key';
   794  flush status;
   795  uFIDelate t2 set a = 111 where b in (5,6);
   796  show status like 'Handler_read_rnd_next';
   797  show status like 'Handler_read_key';
   798  flush status;
   799  uFIDelate t2 set a = 222 where b = 7;
   800  show status like 'Handler_read_rnd_next';
   801  show status like 'Handler_read_key';
   802  flush status;
   803  delete from t2 where b = 7;
   804  show status like 'Handler_read_rnd_next';
   805  show status like 'Handler_read_key';
   806  flush status;
   807  delete from t2 where b > 5;
   808  show status like 'Handler_read_rnd_next';
   809  show status like 'Handler_read_key';
   810  show status like 'Handler_read_prev';
   811  show status like 'Handler_read_next';
   812  flush status;
   813  delete from t2 where b < 5 or b > 3;
   814  show status like 'Handler_read_rnd_next';
   815  show status like 'Handler_read_key';
   816  show status like 'Handler_read_prev';
   817  show status like 'Handler_read_next';
   818  
   819  drop causet t1, t2;
   820  
   821  # BUG#20484 "Partitions: crash with explain and union"
   822  create causet t1 (s1 int);
   823  explain select 1 from t1 union all select 2;
   824  drop causet t1;
   825  
   826  #
   827  # Test all variants of usage for interval_via_mapping
   828  # and interval_via_walking
   829  #
   830  # t1 will use interval_via_mapping since it uses a
   831  # monotonic function, whereas t2 will use
   832  # interval_via_walking since the intervals are short
   833  # and the function isn't monotonic (it is, but it isn't
   834  # discovered in this version).
   835  #
   836    create causet t1 (a int)
   837    partition by range(a) (
   838    partition p0 values less than (64),
   839    partition p1 values less than (128),
   840    partition p2 values less than (255)
   841  );
   842  
   843  create causet t2 (a int)
   844    partition by range(a+0) (
   845    partition p0 values less than (64),
   846    partition p1 values less than (128),
   847    partition p2 values less than (255)
   848  );
   849  
   850  insert into t1 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE);
   851  insert into t2 values (0x20), (0x20), (0x41), (0x41), (0xFE), (0xFE);
   852  explain select * from t1 where a=0;
   853  explain select * from t2 where a=0;
   854  explain select * from t1 where a=0xFE;
   855  explain select * from t2 where a=0xFE;
   856  explain select * from t1 where a > 0xFE AND a <= 0xFF;
   857  explain select * from t2 where a > 0xFE AND a <= 0xFF;
   858  explain select * from t1 where a >= 0xFE AND a <= 0xFF;
   859  explain select * from t2 where a >= 0xFE AND a <= 0xFF;
   860  explain select * from t1 where a < 64 AND a >= 63;
   861  explain select * from t2 where a < 64 AND a >= 63;
   862  explain select * from t1 where a <= 64 AND a >= 63;
   863  explain select * from t2 where a <= 64 AND a >= 63;
   864  drop causet t1;
   865  drop causet t2;
   866  
   867  create causet t1(a bigint unsigned not null) partition by range(a+0) (
   868    partition p1 values less than (10),
   869    partition p2 values less than (20),
   870    partition p3 values less than (2305561538531885056),
   871    partition p4 values less than (2305561538531950591)
   872  );
   873  
   874  insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1);
   875  insert into t1 values (9),(19),(0xFFFF0000FFFF000-1), (0xFFFF0000FFFFFFF-1);
   876  
   877  explain select * from t1 where
   878    a >= 2305561538531885056-10 and a <= 2305561538531885056-8;
   879  
   880  explain select * from t1 where
   881    a > 0xFFFFFFFFFFFFFFEC and a < 0xFFFFFFFFFFFFFFEE;
   882  
   883  explain select * from t1 where a>=0 and a <= 0xFFFFFFFFFFFFFFFF;
   884  drop causet t1;
   885  
   886  create causet t1 (a bigint) partition by range(a+0) (
   887    partition p1 values less than (-1000),
   888    partition p2 values less than (-10),
   889    partition p3 values less than (10),
   890    partition p4 values less than (1000)
   891  );
   892  insert into t1 values (-15),(-5),(5),(15),(-15),(-5),(5),(15);
   893  explain select * from t1 where a>-2 and a <=0;
   894  drop causet t1;
   895  
   896  
   897  #
   898  # BUG#27927 Partition pruning not optimal with TO_DAYS function
   899  #
   900  
   901  CREATE TABLE t1 ( recdate  DATETIME NOT NULL )
   902  PARTITION BY RANGE( TO_DAYS(recdate) ) (
   903    PARTITION p0 VALUES LESS THAN ( TO_DAYS('2007-03-08') ),
   904    PARTITION p1 VALUES LESS THAN ( TO_DAYS('2007-04-01') )
   905  );
   906  INSERT INTO t1 VALUES ('2007-03-01 12:00:00');
   907  INSERT INTO t1 VALUES ('2007-03-07 12:00:00');
   908  INSERT INTO t1 VALUES ('2007-03-08 12:00:00');
   909  INSERT INTO t1 VALUES ('2007-03-15 12:00:00');
   910  -- echo must use p0 only:
   911  explain select * from t1 where recdate < '2007-03-08 00:00:00';
   912  
   913  drop causet t1;
   914  CREATE TABLE t1 ( recdate  DATETIME NOT NULL )
   915  PARTITION BY RANGE( YEAR(recdate) ) (
   916    PARTITION p0 VALUES LESS THAN (2006),
   917    PARTITION p1 VALUES LESS THAN (2007)
   918  );
   919  INSERT INTO t1 VALUES ('2005-03-01 12:00:00');
   920  INSERT INTO t1 VALUES ('2005-03-01 12:00:00');
   921  INSERT INTO t1 VALUES ('2006-03-01 12:00:00');
   922  INSERT INTO t1 VALUES ('2006-03-01 12:00:00');
   923  
   924  -- echo must use p0 only:
   925  explain select * from t1 where recdate < '2006-01-01 00:00:00';
   926  drop causet t1;
   927  
   928  -- echo #
   929  -- echo # BUG#33730 Full causet scan instead selected partitions for query more than 10 partitions
   930  -- echo #
   931  create causet t0 (a int);
   932  insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
   933  create causet t1 (a int)
   934    partition by range(a+0) (
   935    partition p0 values less than (64),
   936    partition p1 values less than (128),
   937    partition p2 values less than (255)
   938  );
   939  insert into t1 select A.a + 10*B.a from t0 A, t0 B;
   940  
   941  # this will use interval_via_walking
   942  explain select * from t1 where a between 10 and 13;
   943  explain select * from t1 where a between 10 and 10+33;
   944  
   945  drop causet t0, t1;
   946  
   947  drop causet if exists t;
   948  create causet t(a timestamp) partition by range(unix_timestamp(a)) (partition p0 values less than(unix_timestamp('2020-02-16 14:20:00')), partition p1 values less than (maxvalue));
   949  explain select * from t where a between timestamp'2020-02-16 14:19:00' and timestamp'2020-02-16 14:21:00';
   950  
   951  drop causet if exists t;
   952  create causet t(a int) partition by range(a) (partition p0 values less than (100), partition p1 values less than (200), partition p2 values less than (300));
   953  begin;
   954  explain select * from t;
   955  insert into t values(1);
   956  explain select * from t;
   957  rollback;
   958  begin;
   959  insert into t values(101);
   960  explain select * from t;
   961  rollback;
   962  begin;
   963  insert into t values(201);
   964  explain select * from t;
   965  rollback;
   966  explain select * from t;
   967  
   968  drop causet if exists t;
   969  CREATE TABLE `t` (
   970    `a` int(11) DEFAULT NULL,
   971    `b` int(11) DEFAULT NULL
   972  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
   973  PARTITION BY RANGE COLUMNS(a) (
   974    PARTITION p0 VALUES LESS THAN (1),
   975    PARTITION p1 VALUES LESS THAN (10),
   976    PARTITION p2 VALUES LESS THAN (100)
   977  );
   978  
   979  desc select * from t where a = 11 and b = 1 or a = 12 and b = 1;
   980  
   981  #
   982  # MyALLEGROSQL doesn't support partition pruning for 'floor(unix_timestamp(ts))' but it works on MilevaDB
   983  # https://github.com/whtcorpsinc/milevadb/issues/16354
   984  #
   985  drop causet if exists t;
   986  create causet t (ts timestamp(3) not null default current_timestamp(3))
   987  partition by range (floor(unix_timestamp(ts))) (
   988    partition p0 values less than (unix_timestamp('2020-04-05 00:00:00')),
   989    partition p1 values less than (unix_timestamp('2020-04-15 00:00:00')),
   990    partition p2 values less than (unix_timestamp('2020-04-25 00:00:00'))
   991  );
   992  
   993  explain select * from t where ts = '2020-04-06 00:00:00' -- p1;
   994  explain select * from t where ts = '2020-04-05 00:00:00.001' -- p1;
   995  explain select * from t where ts > '2020-04-15 00:00:00' -- p2;
   996  explain select * from t where ts > '2020-04-14 23:59:59.999' -- p1,p2;
   997  explain select * from t where ts > '2020-04-15 00:00:00.001' -- p2;
   998  explain select * from t where ts > '2020-04-26 00:00:00.001' -- dual;
   999  explain select * from t where ts >= '2020-04-04 12:22:32' --  p0,p1,p2;
  1000  explain select * from t where ts >= '2020-04-05 00:00:00' -- p1,p2;
  1001  explain select * from t where ts >= '2020-04-25 00:00:00' -- dual;
  1002  explain select * from t where ts < '2020-04-25 00:00:00' -- p0,p1,p2;
  1003  explain select * from t where ts < '2020-04-15 00:00:00.001' -- p0,p1,p2;
  1004  explain select * from t where ts < '2020-04-15 00:00:00' -- expect perfect : p0,p1,  obtain: p0,p1,p2;
  1005  explain select * from t where ts < '2020-04-14 23:59:59.999' -- p0,p1;
  1006  explain select * from t where ts < '2020-04-03 00:00:00' -- p0;
  1007  explain select * from t where ts < '2021-05-03 00:00:00' -- p0,p1,p2;
  1008  explain select * from t where ts <= '2020-04-05 00:00:00' -- p0,p1;
  1009  explain select * from t where ts <= '2020-04-03 00:00:00' -- p0;
  1010  explain select * from t where ts <= '2020-04-14 23:59:59.123' -- p0,p1;
  1011  explain select * from t where ts <= '2020-04-25 00:00:00' -- p0,p1,p2;
  1012  explain select * from t where ts > '2020-04-25 00:00:00' or ts < '2020-01-02 00:00:00' -- p0;
  1013  explain select * from t where ts > '2020-04-02 00:00:00' and ts < '2020-04-07 00:00:00' -- p0,p1;
  1014  
  1015  drop causet if exists t;
  1016  create causet t (id int, name varchar(20)) partition by hash(id) partitions 128;
  1017  explain SELECT * FROM t partition (p1) where name = '1';