[MDEV-8229] GROUP_MIN_MAX is erroneously applied for BETWEEN in some cases - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL)
Fix Version/s: 10.1.6
Component/s: Optimizer
Labels:
None

Sprint:
10.1.6-1

Description

This problem is similar to ~~MDEV-6991~~, but now for BETWEEN.

This script:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;

INSERT INTO t1 VALUES (1,'2001-01-01');

INSERT INTO t1 VALUES (1,'2001-01-02');

INSERT INTO t1 VALUES (1,'2001-01-03');

INSERT INTO t1 VALUES (1,' 2001-01-04');

INSERT INTO t1 VALUES (2,'2001-01-01');

INSERT INTO t1 VALUES (2,'2001-01-02');

INSERT INTO t1 VALUES (2,'2001-01-03');

INSERT INTO t1 VALUES (2,' 2001-01-04');

INSERT INTO t1 VALUES (3,'2001-01-01');

INSERT INTO t1 VALUES (3,'2001-01-02');

INSERT INTO t1 VALUES (3,'2001-01-03');

INSERT INTO t1 VALUES (3,' 2001-01-04');

INSERT INTO t1 VALUES (4,'2001-01-01');

INSERT INTO t1 VALUES (4,'2001-01-02');

INSERT INTO t1 VALUES (4,'2001-01-03');

INSERT INTO t1 VALUES (4,' 2001-01-04');

SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;

correctly returns:

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

| id | MIN(a)      | MAX(a)      |

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

|  1 |  2001-01-04 |  2001-01-04 |

|  2 |  2001-01-04 |  2001-01-04 |

|  3 |  2001-01-04 |  2001-01-04 |

|  4 |  2001-01-04 |  2001-01-04 |

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

Notice, there are leading spaces in the records with '2001-01-04', .i.e. '<space>2001-01-04', and no leading spaces in the other records.

Now if I add an index and run the query again:

ALTER TABLE t1 ADD KEY(id,a);

SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;

it returns empty set.

This EXPLAIN:

EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;

tells that group-min-max optimization is used for the query

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

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

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

|    1 | SIMPLE      | t1    | range | NULL          | id   | 27      | NULL |    9 | Using where; Using index for group-by |

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

This is wrong, because DATE comparison cannot correctly use a VARCHAR index.
GROUP_MIN_MAX optimization should not be used for this query.

Attachments

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2015-05-26 12:35

Alexander Barkov made changes - 2015-05-26 12:36

Field	Original Value	New Value
Description	This problems is similar to ~~MDEV-6991~~, but now for BETWEEN. This script: {code} DROP TABLE IF EXISTS t1; CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,'2001-01-01'); INSERT INTO t1 VALUES (1,'2001-01-02'); INSERT INTO t1 VALUES (1,'2001-01-03'); INSERT INTO t1 VALUES (1,' 2001-01-04'); INSERT INTO t1 VALUES (2,'2001-01-01'); INSERT INTO t1 VALUES (2,'2001-01-02'); INSERT INTO t1 VALUES (2,'2001-01-03'); INSERT INTO t1 VALUES (2,' 2001-01-04'); INSERT INTO t1 VALUES (3,'2001-01-01'); INSERT INTO t1 VALUES (3,'2001-01-02'); INSERT INTO t1 VALUES (3,'2001-01-03'); INSERT INTO t1 VALUES (3,' 2001-01-04'); INSERT INTO t1 VALUES (4,'2001-01-01'); INSERT INTO t1 VALUES (4,'2001-01-02'); INSERT INTO t1 VALUES (4,'2001-01-03'); INSERT INTO t1 VALUES (4,' 2001-01-04'); SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id; {code} correctly returns: {noformat} +----+-------------+-------------+ \| id \| MIN(a) \| MAX(a) \| +----+-------------+-------------+ \| 1 \| 2001-01-04 \| 2001-01-04 \| \| 2 \| 2001-01-04 \| 2001-01-04 \| \| 3 \| 2001-01-04 \| 2001-01-04 \| \| 4 \| 2001-01-04 \| 2001-01-04 \| +----+-------------+-------------+ {noformat} Notice, there are leading spaces in the records with '2001-01-04', .i.e. '<space>2001-01-04', and no leading spaces in the other records. Now if I add an index and run the query again: {code} ALTER TABLE t1 ADD KEY(id,a); SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id; {code} it returns empty set. This EXPLAIN: {code} EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id; {code} tells that group-min-max optimization used for the query {noformat} +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ \| 1 \| SIMPLE \| t1 \| range \| NULL \| id \| 27 \| NULL \| 9 \| Using where; Using index for group-by \| +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ {noformat} This is wrong, because DATE comparison cannot correctly use a VARCHAR index. GROUP_MIN_MAX optimization should not be used for this query.	This problems is similar to ~~MDEV-6991~~, but now for BETWEEN. This script: {code} DROP TABLE IF EXISTS t1; CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,'2001-01-01'); INSERT INTO t1 VALUES (1,'2001-01-02'); INSERT INTO t1 VALUES (1,'2001-01-03'); INSERT INTO t1 VALUES (1,' 2001-01-04'); INSERT INTO t1 VALUES (2,'2001-01-01'); INSERT INTO t1 VALUES (2,'2001-01-02'); INSERT INTO t1 VALUES (2,'2001-01-03'); INSERT INTO t1 VALUES (2,' 2001-01-04'); INSERT INTO t1 VALUES (3,'2001-01-01'); INSERT INTO t1 VALUES (3,'2001-01-02'); INSERT INTO t1 VALUES (3,'2001-01-03'); INSERT INTO t1 VALUES (3,' 2001-01-04'); INSERT INTO t1 VALUES (4,'2001-01-01'); INSERT INTO t1 VALUES (4,'2001-01-02'); INSERT INTO t1 VALUES (4,'2001-01-03'); INSERT INTO t1 VALUES (4,' 2001-01-04'); SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id; {code} correctly returns: {noformat} +----+-------------+-------------+ \| id \| MIN(a) \| MAX(a) \| +----+-------------+-------------+ \| 1 \| 2001-01-04 \| 2001-01-04 \| \| 2 \| 2001-01-04 \| 2001-01-04 \| \| 3 \| 2001-01-04 \| 2001-01-04 \| \| 4 \| 2001-01-04 \| 2001-01-04 \| +----+-------------+-------------+ {noformat} Notice, there are leading spaces in the records with '2001-01-04', .i.e. '<space>2001-01-04', and no leading spaces in the other records. Now if I add an index and run the query again: {code} ALTER TABLE t1 ADD KEY(id,a); SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id; {code} it returns empty set. This EXPLAIN: {code} EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id; {code} tells that group-min-max optimization is used for the query {noformat} +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ \| 1 \| SIMPLE \| t1 \| range \| NULL \| id \| 27 \| NULL \| 9 \| Using where; Using index for group-by \| +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ {noformat} This is wrong, because DATE comparison cannot correctly use a VARCHAR index. GROUP_MIN_MAX optimization should not be used for this query.

Alexander Barkov made changes - 2015-05-26 12:38

Description

This problems is similar to ~~MDEV-6991~~, but now for BETWEEN.

This script:
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,'2001-01-01');
INSERT INTO t1 VALUES (1,'2001-01-02');
INSERT INTO t1 VALUES (1,'2001-01-03');
INSERT INTO t1 VALUES (1,' 2001-01-04');
INSERT INTO t1 VALUES (2,'2001-01-01');
INSERT INTO t1 VALUES (2,'2001-01-02');
INSERT INTO t1 VALUES (2,'2001-01-03');
INSERT INTO t1 VALUES (2,' 2001-01-04');
INSERT INTO t1 VALUES (3,'2001-01-01');
INSERT INTO t1 VALUES (3,'2001-01-02');
INSERT INTO t1 VALUES (3,'2001-01-03');
INSERT INTO t1 VALUES (3,' 2001-01-04');
INSERT INTO t1 VALUES (4,'2001-01-01');
INSERT INTO t1 VALUES (4,'2001-01-02');
INSERT INTO t1 VALUES (4,'2001-01-03');
INSERT INTO t1 VALUES (4,' 2001-01-04');
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;
{code}
correctly returns:
{noformat}
+----+-------------+-------------+
| id | MIN(a) | MAX(a) |
+----+-------------+-------------+
| 1 | 2001-01-04 | 2001-01-04 |
| 2 | 2001-01-04 | 2001-01-04 |
| 3 | 2001-01-04 | 2001-01-04 |
| 4 | 2001-01-04 | 2001-01-04 |
+----+-------------+-------------+
{noformat}
Notice, there are leading spaces in the records with '2001-01-04', .i.e. '<space>2001-01-04', and no leading spaces in the other records.

Now if I add an index and run the query again:

{code}
ALTER TABLE t1 ADD KEY(id,a);
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;
{code}
it returns empty set.

This EXPLAIN:
{code}
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;
{code}
tells that group-min-max optimization is used for the query
{noformat}
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
| 1 | SIMPLE | t1 | range | NULL | id | 27 | NULL | 9 | Using where; Using index for group-by |
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
{noformat}
This is wrong, because DATE comparison cannot correctly use a VARCHAR index.
GROUP_MIN_MAX optimization should not be used for this query.

This problem is similar to ~~MDEV-6991~~, but now for BETWEEN.

This script:
{code}
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT NOT NULL, a VARCHAR(20)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,'2001-01-01');
INSERT INTO t1 VALUES (1,'2001-01-02');
INSERT INTO t1 VALUES (1,'2001-01-03');
INSERT INTO t1 VALUES (1,' 2001-01-04');
INSERT INTO t1 VALUES (2,'2001-01-01');
INSERT INTO t1 VALUES (2,'2001-01-02');
INSERT INTO t1 VALUES (2,'2001-01-03');
INSERT INTO t1 VALUES (2,' 2001-01-04');
INSERT INTO t1 VALUES (3,'2001-01-01');
INSERT INTO t1 VALUES (3,'2001-01-02');
INSERT INTO t1 VALUES (3,'2001-01-03');
INSERT INTO t1 VALUES (3,' 2001-01-04');
INSERT INTO t1 VALUES (4,'2001-01-01');
INSERT INTO t1 VALUES (4,'2001-01-02');
INSERT INTO t1 VALUES (4,'2001-01-03');
INSERT INTO t1 VALUES (4,' 2001-01-04');
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;
{code}
correctly returns:
{noformat}
+----+-------------+-------------+
| id | MIN(a) | MAX(a) |
+----+-------------+-------------+
| 1 | 2001-01-04 | 2001-01-04 |
| 2 | 2001-01-04 | 2001-01-04 |
| 3 | 2001-01-04 | 2001-01-04 |
| 4 | 2001-01-04 | 2001-01-04 |
+----+-------------+-------------+
{noformat}
Notice, there are leading spaces in the records with '2001-01-04', .i.e. '<space>2001-01-04', and no leading spaces in the other records.

Now if I add an index and run the query again:

{code}
ALTER TABLE t1 ADD KEY(id,a);
SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;
{code}
it returns empty set.

This EXPLAIN:
{code}
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a BETWEEN DATE'2001-01-04' AND DATE'2001-01-05' GROUP BY id;
{code}
tells that group-min-max optimization is used for the query
{noformat}
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
| 1 | SIMPLE | t1 | range | NULL | id | 27 | NULL | 9 | Using where; Using index for group-by |
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
{noformat}
This is wrong, because DATE comparison cannot correctly use a VARCHAR index.
GROUP_MIN_MAX optimization should not be used for this query.

Sergei Golubchik made changes - 2015-06-02 16:12

Issue Type

Task [ 3 ]

Bug [ 1 ]

Alexander Barkov added a comment - 2015-06-02 16:30 - edited

In 5.5 one should use DATE('XXXX-XX-XX') syntax instead of DATE'XXXX-XX-XX' to reproduce the problem.

Alexander Barkov added a comment - 2015-06-02 16:30 - edited In 5.5 one should use DATE('XXXX-XX-XX') syntax instead of DATE'XXXX-XX-XX' to reproduce the problem.

Alexander Barkov made changes - 2015-06-02 16:30

Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		5.5 [ 15800 ]

Sergei Golubchik made changes - 2015-06-02 22:00

Fix Version/s	5.5 [ 15800 ]
Fix Version/s	10.0 [ 16000 ]

Sergei Golubchik made changes - 2015-06-18 12:49

Sprint

10.1.6-1 [ 6 ]

Alexander Barkov made changes - 2015-06-25 12:00

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2015-06-25 12:01

Fix Version/s		10.1.6 [ 19401 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

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

Workflow

MariaDB v3 [ 67715 ]

MariaDB v4 [ 149213 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2015-05-26 12:35

Updated:: 2015-06-25 12:01

Resolved:: 2015-06-25 12:01

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