Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
DATASET
set @@use_stat_tables=PREFERABLY; |
CREATE TABLE t1 (i int, a VARCHAR(1000) DEFAULT "AAA") |
PARTITION BY RANGE COLUMNS (a)( |
PARTITION p0 VALUES LESS THAN ('g') COMMENT "p0", |
PARTITION p1 VALUES LESS THAN ('m'), |
PARTITION p2 VALUES LESS THAN ('t'), |
PARTITION p3 VALUES LESS THAN ('w')); |
|
ALTER TABLE t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN (MAXVALUE)); |
|
INSERT INTO t1 VALUES (1,REPEAT('a',100)),(2,REPEAT('v',200)),(3,REPEAT('r',300)),(4,NULL); |
INSERT INTO t1 VALUES (5,REPEAT('k',500)),(6,'April'),(7,7),(8,""),(9,"M"),(10,DEFAULT); |
|
DELETE FROM t1 where a=""; |
DELETE FROM t1 where a=(REPEAT('a',100)); |
DELETE FROM t1 where a like "%v"; |
ALTER TABLE t1 ANALYZE PARTITION p1; |
Then I run:
Case 1
MariaDB [test]> set @@use_stat_tables=PREFERABLY;
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> EXPLAIN PARTITIONS SELECT i,SUBSTRING(a,1,10) FROM t1 where (a>'m');
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | p2,p3,p4 | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|
Case 2
|
MariaDB [test]> set @@use_stat_tables=NEVER;
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> EXPLAIN PARTITIONS SELECT i,SUBSTRING(a,1,10) FROM t1 where (a>'m');
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | p2,p3,p4 | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
|
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.001 sec)
|
The rows in case 1 return all records of the table, that is 7 in this case
while rows in case 2 return records that belong to the partitions p2,,p3 and p4.
Attachments
Issue Links
- is part of
-
MDEV-15253 Default optimizer setting changes for MariaDB 10.4
- Closed