[MDEV-16878] Functions ADDTIME and SUBTIME get wrongly removed from WHERE by the equal expression optimizer - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.3.9
Component/s: Optimizer
Labels:
None

Description

I create and populate a table like this:

DROP TABLE IF EXISTS t1;

CREATE OR REPLACE TABLE t1 (a TIME);

INSERT INTO t1 VALUES ('00:00:10'),('00:00:20');

Now I do some time arithmetic:

SELECT a, SUBTIME(a,TIME'00:00:01'), ADDTIME(a,TIME'00:00:01') FROM t1;

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

| a        | SUBTIME(a,TIME'00:00:01') | ADDTIME(a,TIME'00:00:01') |

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

| 00:00:10 | 00:00:09                  | 00:00:11                  |

| 00:00:20 | 00:00:19                  | 00:00:21                  |

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

Now I put the same arithmetic expressions into WHERE:

SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09';

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

| a        |

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

| 00:00:10 |

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

SELECT * FROM t1 WHERE ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';

Empty set (0.01 sec)

So far so good:

The first query correctly returned one row
The second query correctly returned empty set

Now I mix these two conditions using AND and expect to get empty set again:

SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';

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

| a        |

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

| 00:00:10 |

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

However, the server returned one row. This is wrong!

The following EXPLAIN script demonstrates that the second condition part was erroneously eliminated by the optimizer:

EXPLAIN EXTENDED SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';

SHOW WARNINGS;

...

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

| Level | Code | Message                                                                                                       |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where subtime(`test`.`t1`.`a`,TIME'00:00:01') = TIME'00:00:09' |

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

Notice, there is no the ADDTIME() condition in the optimzed query.

The problem happens because Item_func_add_time::func_name() erroneously always returns the same value, which makes the equal expression propagation code erroneously think ADDTIME() and SUBTIME() are equal to each other.

Item_func_add_time::func_name() should return three different names depending on the exact SQL function it handles:

timestamp
addtime
subtime

Attachments

Issue Links

relates to

MDEV-16398 Spider Creates Query With Non-Existent Function

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2018-08-02 05:27

Alexander Barkov made changes - 2018-08-02 05:28

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-16398~~ [ ~~MDEV-16398~~ ]

Alexander Barkov made changes - 2018-08-02 05:30

Affects Version/s		10.1 [ 16100 ]
Affects Version/s		10.2 [ 14601 ]

Alexander Barkov made changes - 2018-08-02 05:32

Description

I create and populate a table like this:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE OR REPLACE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:10'),('00:00:20');
{code}

Now I do some time arithmetic:
{code:sql}
SELECT a, SUBTIME(a,TIME'00:00:01'), ADDTIME(a,TIME'00:00:01') FROM t1;
{code}
{noformat}
+----------+---------------------------+---------------------------+
| a | SUBTIME(a,TIME'00:00:01') | ADDTIME(a,TIME'00:00:01') |
+----------+---------------------------+---------------------------+
| 00:00:10 | 00:00:09 | 00:00:11 |
| 00:00:20 | 00:00:19 | 00:00:21 |
+----------+---------------------------+---------------------------+
{noformat}

Now I put the same arithmetic expressions into WHERE:

{code:sql}
SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09'
{code}
{noformat}
+----------+
| a |
+----------+
| 00:00:10 |
+----------+
{noformat}

{code:sql}
SELECT * FROM t1 WHERE ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';
{code}
{noformat}
Empty set (0.01 sec)
{noformat}

So far so good:
- The first query correctly returned one row
- The second query correctly returned empty set

Now I mix these two conditions using AND and expect to get empty set again:
{code:sql}
SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';
{code}
{noformat}
+----------+
| a |
+----------+
| 00:00:10 |
+----------+
{noformat}

However, the server returned one row. This is wrong!

The following EXPLAIN script demonstrates that the second condition part was erroneously eliminated by the optimizer:
{code:sql}
EXPLAIN EXTENDED SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';
SHOW WARNINGS;
{code}
{noformat}
...
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where subtime(`test`.`t1`.`a`,TIME'00:00:01') = TIME'00:00:09' |
+-------+------+---------------------------------------------------------------------------------------------------------------+
{noformat}

Notice, there is no the ADDTIME() condition in the optimzed query.

The problem happens because Item_func_add_time erroneously always returns the same value in func_name(). It should return three different names depending on the exact function it handles:
- timestamp
- addtime
- subtime

I create and populate a table like this:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE OR REPLACE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:10'),('00:00:20');
{code}

Now I do some time arithmetic:
{code:sql}
SELECT a, SUBTIME(a,TIME'00:00:01'), ADDTIME(a,TIME'00:00:01') FROM t1;
{code}
{noformat}
+----------+---------------------------+---------------------------+
| a | SUBTIME(a,TIME'00:00:01') | ADDTIME(a,TIME'00:00:01') |
+----------+---------------------------+---------------------------+
| 00:00:10 | 00:00:09 | 00:00:11 |
| 00:00:20 | 00:00:19 | 00:00:21 |
+----------+---------------------------+---------------------------+
{noformat}

Now I put the same arithmetic expressions into WHERE:

{code:sql}
SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09'
{code}
{noformat}
+----------+
| a |
+----------+
| 00:00:10 |
+----------+
{noformat}

{code:sql}
SELECT * FROM t1 WHERE ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';
{code}
{noformat}
Empty set (0.01 sec)
{noformat}

So far so good:
- The first query correctly returned one row
- The second query correctly returned empty set

Now I mix these two conditions using AND and expect to get empty set again:
{code:sql}
SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';
{code}
{noformat}
+----------+
| a |
+----------+
| 00:00:10 |
+----------+
{noformat}

However, the server returned one row. This is wrong!

The following EXPLAIN script demonstrates that the second condition part was erroneously eliminated by the optimizer:
{code:sql}
EXPLAIN EXTENDED SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';
SHOW WARNINGS;
{code}
{noformat}
...
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where subtime(`test`.`t1`.`a`,TIME'00:00:01') = TIME'00:00:09' |
+-------+------+---------------------------------------------------------------------------------------------------------------+
{noformat}

Notice, there is no the ADDTIME() condition in the optimzed query.

The problem happens because Item_func_add_time::func_name() erroneously always returns the same value, which makes the equal expression propagation code erroneously think ADDTIME() and SUBTIME() are equal to each other.

Item_func_add_time::func_name() should return three different names depending on the exact SQL function it handles:
- timestamp
- addtime
- subtime

Alexander Barkov made changes - 2018-08-02 05:47

Description

I create and populate a table like this:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE OR REPLACE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:10'),('00:00:20');
{code}

Now I do some time arithmetic:
{code:sql}
SELECT a, SUBTIME(a,TIME'00:00:01'), ADDTIME(a,TIME'00:00:01') FROM t1;
{code}
{noformat}
+----------+---------------------------+---------------------------+
| a | SUBTIME(a,TIME'00:00:01') | ADDTIME(a,TIME'00:00:01') |
+----------+---------------------------+---------------------------+
| 00:00:10 | 00:00:09 | 00:00:11 |
| 00:00:20 | 00:00:19 | 00:00:21 |
+----------+---------------------------+---------------------------+
{noformat}

Now I put the same arithmetic expressions into WHERE:

{code:sql}
SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09'
{code}
{noformat}
+----------+
| a |
+----------+
| 00:00:10 |
+----------+
{noformat}

{code:sql}
SELECT * FROM t1 WHERE ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';
{code}
{noformat}
Empty set (0.01 sec)
{noformat}

So far so good:
- The first query correctly returned one row
- The second query correctly returned empty set

Now I mix these two conditions using AND and expect to get empty set again:
{code:sql}
SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';
{code}
{noformat}
+----------+
| a |
+----------+
| 00:00:10 |
+----------+
{noformat}

However, the server returned one row. This is wrong!

The following EXPLAIN script demonstrates that the second condition part was erroneously eliminated by the optimizer:
{code:sql}
EXPLAIN EXTENDED SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';
SHOW WARNINGS;
{code}
{noformat}
...
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where subtime(`test`.`t1`.`a`,TIME'00:00:01') = TIME'00:00:09' |
+-------+------+---------------------------------------------------------------------------------------------------------------+
{noformat}

Notice, there is no the ADDTIME() condition in the optimzed query.

The problem happens because Item_func_add_time::func_name() erroneously always returns the same value, which makes the equal expression propagation code erroneously think ADDTIME() and SUBTIME() are equal to each other.

Item_func_add_time::func_name() should return three different names depending on the exact SQL function it handles:
- timestamp
- addtime
- subtime

I create and populate a table like this:

{code:sql}
DROP TABLE IF EXISTS t1;
CREATE OR REPLACE TABLE t1 (a TIME);
INSERT INTO t1 VALUES ('00:00:10'),('00:00:20');
{code}

Now I do some time arithmetic:
{code:sql}
SELECT a, SUBTIME(a,TIME'00:00:01'), ADDTIME(a,TIME'00:00:01') FROM t1;
{code}
{noformat}
+----------+---------------------------+---------------------------+
| a | SUBTIME(a,TIME'00:00:01') | ADDTIME(a,TIME'00:00:01') |
+----------+---------------------------+---------------------------+
| 00:00:10 | 00:00:09 | 00:00:11 |
| 00:00:20 | 00:00:19 | 00:00:21 |
+----------+---------------------------+---------------------------+
{noformat}

Now I put the same arithmetic expressions into WHERE:

{code:sql}
SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09';
{code}
{noformat}
+----------+
| a |
+----------+
| 00:00:10 |
+----------+
{noformat}

{code:sql}
SELECT * FROM t1 WHERE ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';
{code}
{noformat}
Empty set (0.01 sec)
{noformat}

So far so good:
- The first query correctly returned one row
- The second query correctly returned empty set

Now I mix these two conditions using AND and expect to get empty set again:
{code:sql}
SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';
{code}
{noformat}
+----------+
| a |
+----------+
| 00:00:10 |
+----------+
{noformat}

However, the server returned one row. This is wrong!

The following EXPLAIN script demonstrates that the second condition part was erroneously eliminated by the optimizer:
{code:sql}
EXPLAIN EXTENDED SELECT * FROM t1 WHERE SUBTIME(a,TIME'00:00:01')=TIME'00:00:09' AND ADDTIME(a,TIME'00:00:01')<=TIME'00:00:09';
SHOW WARNINGS;
{code}
{noformat}
...
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where subtime(`test`.`t1`.`a`,TIME'00:00:01') = TIME'00:00:09' |
+-------+------+---------------------------------------------------------------------------------------------------------------+
{noformat}

Notice, there is no the ADDTIME() condition in the optimzed query.

The problem happens because Item_func_add_time::func_name() erroneously always returns the same value, which makes the equal expression propagation code erroneously think ADDTIME() and SUBTIME() are equal to each other.

Item_func_add_time::func_name() should return three different names depending on the exact SQL function it handles:
- timestamp
- addtime
- subtime

Alexander Barkov made changes - 2018-08-02 06:49

issue.field.resolutiondate

2018-08-02 06:49:31.0

2018-08-02 06:49:31.261

Alexander Barkov made changes - 2018-08-02 06:49

Component/s		Optimizer [ 10200 ]
Fix Version/s		10.3.9 [ 23114 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

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

Workflow

MariaDB v3 [ 88705 ]

MariaDB v4 [ 154742 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2018-08-02 05:27

Updated:: 2018-08-13 21:03

Resolved:: 2018-08-02 06:49

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration