[MDEV-5257] MIN/MAX Optimization (Select tables optimized away) does not work for DateTime - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 5.5.33a
Fix Version/s: 5.5.34
Component/s: None
Labels:
None
Environment:
All OS

Description

MIN/MAX Optimization (Select tables optimized away) does not work for DateTime.

This is similar to bug #3855:

https://mariadb.atlassian.net/browse/MDEV-3855

However, that bug is specifically about inet_aton.

This one is regarding datetime values, and they are not optimized away, thus the query can take much longer than expected.

MariaDB:

mysql> EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59';

+------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+

| id   | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |

+------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+

|    1 | SIMPLE      | t1    | range | idx_b         | idx_b | 9       | NULL |   11 | Using where; Using index |

+------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+

MySQL:

mysql> EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59';

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |

+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

The following illustrates this:

CREATE TABLE `t1` (

  `a` int(11) NOT NULL AUTO_INCREMENT,

  `b` datetime DEFAULT NULL,

  PRIMARY KEY (`a`),

  KEY `idx_b` (`b`)

) ENGINE=InnoDB;

INSERT INTO `t1` (b) VALUES ('2013-01-06 23:59:59');

INSERT INTO `t1` (b) VALUES ('2013-02-06 23:59:59');

INSERT INTO `t1` (b) VALUES ('2013-03-06 23:59:59');

INSERT INTO `t1` (b) VALUES ('2013-04-06 23:59:59');

INSERT INTO `t1` (b) VALUES ('2013-05-06 23:59:59');

INSERT INTO `t1` (b) VALUES ('2013-06-06 23:59:59');

INSERT INTO `t1` (b) VALUES ('2013-07-06 23:59:59');

INSERT INTO `t1` (b) VALUES ('2013-08-06 23:59:59');

INSERT INTO `t1` (b) VALUES ('2013-09-06 23:59:59');

INSERT INTO `t1` (b) VALUES ('2013-10-06 23:59:59');

INSERT INTO `t1` (b) VALUES ('2013-11-06 23:59:59');

INSERT INTO `t1` (b) VALUES ('2013-12-06 23:59:59');

EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59';

Attachments

Activity

Ascending order - Click to sort in descending order

Chris Calender (Inactive) created issue - 2013-11-07 18:33

Patryk Pomykalski added a comment - 2013-11-08 00:44

I couldn't reproduce it with 5.5.33a and latest bzr version.

Patryk Pomykalski added a comment - 2013-11-08 00:44 I couldn't reproduce it with 5.5.33a and latest bzr version.

Chris Calender (Inactive) added a comment - 2013-11-08 15:53

Well, I think you might need to test it again.

I just downloaded another 5.5.33a (Windows 64-bit), re-installed, and copy/pasted the above test case, and I see the issue immediately.

mysql> CREATE TABLE `t1` (
-> `a` int(11) NOT NULL AUTO_INCREMENT,
-> `b` datetime DEFAULT NULL,
-> PRIMARY KEY (`a`),
-> KEY `idx_b` (`b`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO `t1` (b) VALUES ('2013-01-06 23:59:59');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `t1` (b) VALUES ('2013-02-06 23:59:59');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `t1` (b) VALUES ('2013-03-06 23:59:59');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `t1` (b) VALUES ('2013-04-06 23:59:59');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `t1` (b) VALUES ('2013-05-06 23:59:59');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `t1` (b) VALUES ('2013-06-06 23:59:59');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `t1` (b) VALUES ('2013-07-06 23:59:59');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `t1` (b) VALUES ('2013-08-06 23:59:59');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `t1` (b) VALUES ('2013-09-06 23:59:59');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `t1` (b) VALUES ('2013-10-06 23:59:59');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `t1` (b) VALUES ('2013-11-06 23:59:59');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `t1` (b) VALUES ('2013-12-06 23:59:59');
Query OK, 1 row affected (0.00 sec)

mysql> EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59';
------------------------------------------------------------------------------------+

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

------------------------------------------------------------------------------------+

SIMPLE

range

idx_b

NULL

Using where; Using index

------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
-----------------

version()

-----------------

5.5.33a-MariaDB

-----------------
1 row in set (0.00 sec)

mysql>

I can even see the issue with NO data:

mysql> CREATE TABLE `t1` (
-> `a` int(11) NOT NULL AUTO_INCREMENT,
-> `b` datetime DEFAULT NULL,
-> PRIMARY KEY (`a`),
-> KEY `idx_b` (`b`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59';
------------------------------------------------------------------------------------+

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

------------------------------------------------------------------------------------+

SIMPLE

index

idx_b

NULL

Using where; Using index

------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql> select version();
-----------------

version()

-----------------

5.5.33a-MariaDB

-----------------
1 row in set (0.00 sec)

Chris Calender (Inactive) added a comment - 2013-11-08 15:53 Well, I think you might need to test it again. I just downloaded another 5.5.33a (Windows 64-bit), re-installed, and copy/pasted the above test case, and I see the issue immediately. mysql> CREATE TABLE `t1` ( -> `a` int(11) NOT NULL AUTO_INCREMENT, -> `b` datetime DEFAULT NULL, -> PRIMARY KEY (`a`), -> KEY `idx_b` (`b`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO `t1` (b) VALUES ('2013-01-06 23:59:59'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `t1` (b) VALUES ('2013-02-06 23:59:59'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `t1` (b) VALUES ('2013-03-06 23:59:59'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `t1` (b) VALUES ('2013-04-06 23:59:59'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `t1` (b) VALUES ('2013-05-06 23:59:59'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO `t1` (b) VALUES ('2013-06-06 23:59:59'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `t1` (b) VALUES ('2013-07-06 23:59:59'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `t1` (b) VALUES ('2013-08-06 23:59:59'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `t1` (b) VALUES ('2013-09-06 23:59:59'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `t1` (b) VALUES ('2013-10-06 23:59:59'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `t1` (b) VALUES ('2013-11-06 23:59:59'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `t1` (b) VALUES ('2013-12-06 23:59:59'); Query OK, 1 row affected (0.00 sec) mysql> EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59'; ----- ----------- ----- ----- ------------- ----- ------- ---- ---- -------------------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ----------- ----- ----- ------------- ----- ------- ---- ---- -------------------------+ 1 SIMPLE t1 range idx_b idx_b 9 NULL 11 Using where; Using index ----- ----------- ----- ----- ------------- ----- ------- ---- ---- -------------------------+ 1 row in set (0.00 sec) mysql> select version(); ----------------- version() ----------------- 5.5.33a-MariaDB ----------------- 1 row in set (0.00 sec) mysql> I can even see the issue with NO data: mysql> CREATE TABLE `t1` ( -> `a` int(11) NOT NULL AUTO_INCREMENT, -> `b` datetime DEFAULT NULL, -> PRIMARY KEY (`a`), -> KEY `idx_b` (`b`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.06 sec) mysql> EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59'; ----- ----------- ----- ----- ------------- ----- ------- ---- ---- -------------------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ----------- ----- ----- ------------- ----- ------- ---- ---- -------------------------+ 1 SIMPLE t1 index idx_b idx_b 9 NULL 1 Using where; Using index ----- ----------- ----- ----- ------------- ----- ------- ---- ---- -------------------------+ 1 row in set (0.00 sec) mysql> mysql> select version(); ----------------- version() ----------------- 5.5.33a-MariaDB ----------------- 1 row in set (0.00 sec)

Patryk Pomykalski added a comment - 2013-11-08 16:50

Ok, some more info When I use mariadb compiled from source I don't see this bug (in mtr). When I used package from mariadb.org I can see it.

Patryk Pomykalski added a comment - 2013-11-08 16:50 Ok, some more info When I use mariadb compiled from source I don't see this bug (in mtr). When I used package from mariadb.org I can see it.

Elena Stepanova made changes - 2013-11-08 18:39

Field	Original Value	New Value
Description	MIN/MAX Optimization (Select tables optimized away) does not work for DateTime. This is similar to bug #3855: https://mariadb.atlassian.net/browse/MDEV-3855 However, that bug is specifically about inet_aton. This one is regarding datetime values, and they are not optimized away, thus the query can take much longer than expected. MariaDB: mysql> EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59'; +------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+ \| 1 \| SIMPLE \| t1 \| range \| idx_b \| idx_b \| 9 \| NULL \| 11 \| Using where; Using index \| +------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+ MySQL: mysql> EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59'; +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ \| 1 \| SIMPLE \| NULL \| NULL \| NULL \| NULL \| NULL \| NULL \| NULL \| Select tables optimized away \| +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ The following illustrates this: CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` datetime DEFAULT NULL, PRIMARY KEY (`a`), KEY `idx_b` (`b`) ) ENGINE=InnoDB; INSERT INTO `t1` (b) VALUES ('2013-01-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-02-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-03-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-04-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-05-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-06-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-07-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-08-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-09-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-10-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-11-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-12-06 23:59:59'); EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59';	MIN/MAX Optimization (Select tables optimized away) does not work for DateTime. This is similar to bug #3855: https://mariadb.atlassian.net/browse/MDEV-3855 However, that bug is specifically about inet_aton. This one is regarding datetime values, and they are not optimized away, thus the query can take much longer than expected. MariaDB: {code:sql} mysql> EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59'; +------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+ \| 1 \| SIMPLE \| t1 \| range \| idx_b \| idx_b \| 9 \| NULL \| 11 \| Using where; Using index \| +------+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+ {code} MySQL: {code:sql} mysql> EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59'; +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ \| 1 \| SIMPLE \| NULL \| NULL \| NULL \| NULL \| NULL \| NULL \| NULL \| Select tables optimized away \| +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ {code} The following illustrates this: {code:sql} CREATE TABLE `t1` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` datetime DEFAULT NULL, PRIMARY KEY (`a`), KEY `idx_b` (`b`) ) ENGINE=InnoDB; INSERT INTO `t1` (b) VALUES ('2013-01-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-02-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-03-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-04-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-05-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-06-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-07-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-08-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-09-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-10-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-11-06 23:59:59'); INSERT INTO `t1` (b) VALUES ('2013-12-06 23:59:59'); EXPLAIN SELECT MIN(b) FROM t1 WHERE b <= '2013-11-06 23:59:59'; {code}

Elena Stepanova added a comment - 2013-11-08 18:47 - edited

For me, it's reproducible all right on a source build as well, just not via MTR. If I run the provided test from MySQL client on a server with all defaults, it's reproducible. Same test via MTR gives "optimized away". MTR sets lots of non-default parameters, apparently they change the picture.

Elena Stepanova added a comment - 2013-11-08 18:47 - edited For me, it's reproducible all right on a source build as well, just not via MTR. If I run the provided test from MySQL client on a server with all defaults, it's reproducible. Same test via MTR gives "optimized away". MTR sets lots of non-default parameters, apparently they change the picture.

Elena Stepanova added a comment - 2013-11-08 19:06

To reproduce with MTR, SET NAMES utf8 should be added to the test.

Elena Stepanova added a comment - 2013-11-08 19:06 To reproduce with MTR, SET NAMES utf8 should be added to the test.

Patryk Pomykalski added a comment - 2013-11-08 20:08

Caused by this fix:
http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/2502.528.13

Patryk Pomykalski added a comment - 2013-11-08 20:08 Caused by this fix: http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/2502.528.13

Sergei Golubchik made changes - 2013-11-12 10:31

Assignee

Sergei Petrunia [ psergey ]

Sergei Golubchik made changes - 2013-11-12 10:32

Fix Version/s

5.5.34 [ 13700 ]

Sergei Golubchik made changes - 2013-11-12 10:32

Priority

Major [ 3 ]

Critical [ 2 ]

Sergei Petrunia added a comment - 2013-11-12 12:23

I was looking at the code of this optimization in opt_sum_query() and co., and it looks very odd. Indeed, a short experimentation shows that opt-sum optimization doesn't correctly take datatypes into account. I've created an example and filed it as MDEV-5283. MySQL is also affected.

It will require substantial amount of effort to fix the problem completely. I think, we should fix this MDEV by providing adequate handling for unambigous cases like the one in this MDEV.

Sergei Petrunia added a comment - 2013-11-12 12:23 I was looking at the code of this optimization in opt_sum_query() and co., and it looks very odd. Indeed, a short experimentation shows that opt-sum optimization doesn't correctly take datatypes into account. I've created an example and filed it as MDEV-5283 . MySQL is also affected. It will require substantial amount of effort to fix the problem completely. I think, we should fix this MDEV by providing adequate handling for unambigous cases like the one in this MDEV.

Patryk Pomykalski added a comment - 2013-11-12 13:02

I think max_length check should be limited to string columns.

Patryk Pomykalski added a comment - 2013-11-12 13:02 I think max_length check should be limited to string columns.

Sergei Petrunia added a comment - 2013-11-12 15:41

Agree. I've also changed max_length to compare characters, not bytes.

Sergei Petrunia added a comment - 2013-11-12 15:41 Agree. I've also changed max_length to compare characters, not bytes.

Sergei Petrunia made changes - 2013-11-13 10:40

Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2014-06-13 15:06

Workflow

defaullt [ 29630 ]

MariaDB v2 [ 44238 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 44238 ]

MariaDB v3 [ 63533 ]

Sergei Golubchik made changes - 2021-12-06 21:39

Workflow

MariaDB v3 [ 63533 ]

MariaDB v4 [ 147207 ]

People

Assignee:: Sergei Petrunia

Reporter:: Chris Calender (Inactive)

Votes:: 1 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2013-11-07 18:33

Updated:: 2013-11-13 10:40

Resolved:: 2013-11-13 10:40

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration