[MDEV-11362] True condition elimination does not work for DECIMAL and temporal dynamic SQL parameters - Jira

Details

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

Description

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a INT);

INSERT INTO t1 VALUES (1),(2),(3);

EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1,1;

SHOW WARNINGS;

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

| Level | Code | Message                                                |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 |

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

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DOUBLE);

INSERT INTO t1 VALUES (1),(2),(3);

EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1e0,1e0;

SHOW WARNINGS;

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

| Level | Code | Message                                                |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 |

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

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DECIMAL(10,1));

INSERT INTO t1 VALUES (1),(2),(3);

EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>?+a' USING 1.0,1.0;

SHOW WARNINGS;

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

| Level | Code | Message                                                                                                    |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((1.0 + `test`.`t1`.`a`) <=> (1.0 + `test`.`t1`.`a`)) |

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

Notice, the true conditions where correctly eliminated for INT and DOUBLE, but not for DECIMAL.
This happens because DECIMAL_VALUE is not handled in Item_param::eq() or Item_param::value_eq() in 10.3

The problem is also repeatable with a parameter on the left side and a literal on the right side:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DECIMAL(10,1));

INSERT INTO t1 VALUES (1),(2),(3);

EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE ?+a<=>1.0+a' USING 1.0;

SHOW WARNINGS;

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

| Level | Code | Message                                                                                              |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1.0 + `test`.`t1`.`a` <=> 1.0 + `test`.`t1`.`a` |

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

This happens for the same reason: DECIMAL_VALUE is not handler in Item_param::eq() / Item_param::value_eq().

Note, if I now put the listeral on the left side and the parameter in the right side, it works fine:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DECIMAL(10,1));

INSERT INTO t1 VALUES (1),(2),(3);

EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE 1.0+a<=>?+a' USING 1.0;

SHOW WARNINGS;

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

| Level | Code | Message                                                |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 |

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

Now execution goes through Item_decimal::eq() which correctly detects an Item_param with the same decimal value as equal.

Summary:

Propagation does not work for two parameters
Propagation works with a literal on the left side and a parameter on the right side
Propagation does not work with a parameter on the left side and a literal on the right side.

This happens because Item_param::eq() and Item_literal::eq() are not symmetric for DECIMAL.

Attachments

Issue Links

relates to

MDEV-4912 Data type plugin API version 1

Closed

MDEV-11361 Equal condition propagation does not work for DECIMAL and temporal dynamic SQL parameters

Open

MDEV-16426 Optimizer erroneously treats equal constants of different formats as same

Closed

Activity

Alexander Barkov added a comment - 2018-06-07 18:22 - edited

The same problem is repeatable for temporal data types:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a TIME);

INSERT INTO t1 VALUES (1),(2),(3);

-- This works fine

EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIME'10:20:30',a)<=>COALESCE(TIME'10:20:30',a);

SHOW WARNINGS;

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

| Level | Code | Message                                                |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 |

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

-- This does not work

EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING TIME'10:20:30',TIME'10:20:30';

SHOW WARNINGS;

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

| Level | Code | Message                                                                                                                                                      |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(<cache>(TIME'10:20:30'),`test`.`t1`.`a`) <=> coalesce(<cache>(TIME'10:20:30'),`test`.`t1`.`a`) |

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

-- This does not work

EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIME''10:20:30'',a)<=>COALESCE(?,a)' USING TIME'10:20:30';

SHOW WARNINGS;

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

| Level | Code | Message                                                                                                                                             |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(TIME'10:20:30',`test`.`t1`.`a`) <=> coalesce(<cache>(TIME'10:20:30'),`test`.`t1`.`a`) |

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

-- This does not work

EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(TIME''10:20:30'',a)' USING TIME'10:20:30';

SHOW WARNINGS;

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

| Level | Code | Message                                                                                                                                             |

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

| Note  | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(<cache>(TIME'10:20:30'),`test`.`t1`.`a`) <=> coalesce(TIME'10:20:30',`test`.`t1`.`a`) |

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

Summary:

With two literals propagation works fine
If there is a parameter, propagation does not work

This happens because Item_temporal_literal::basic_const_item() returns true, while Item_param::basic_const_item::basic_const_item() returns false when bound to a temporal value.

Alexander Barkov added a comment - 2018-06-07 18:22 - edited The same problem is repeatable for temporal data types: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a TIME ); INSERT INTO t1 VALUES (1),(2),(3); -- This works fine EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIME'10:20:30',a)<=>COALESCE(TIME'10:20:30',a); SHOW WARNINGS; +-------+------+--------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1 | +-------+------+--------------------------------------------------------+ -- This does not work EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(?,a)' USING TIME '10:20:30' , TIME '10:20:30' ; SHOW WARNINGS; +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(<cache>(TIME'10:20:30'),`test`.`t1`.`a`) <=> coalesce(<cache>(TIME'10:20:30'),`test`.`t1`.`a`) | +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ -- This does not work EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(TIME' '10:20:30' ',a)<=>COALESCE(?,a)' USING TIME '10:20:30' ; SHOW WARNINGS; +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(TIME'10:20:30',`test`.`t1`.`a`) <=> coalesce(<cache>(TIME'10:20:30'),`test`.`t1`.`a`) | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ -- This does not work EXECUTE IMMEDIATE 'EXPLAIN EXTENDED SELECT * FROM t1 WHERE COALESCE(?,a)<=>COALESCE(TIME' '10:20:30' ',a)' USING TIME '10:20:30' ; SHOW WARNINGS; +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where coalesce(<cache>(TIME'10:20:30'),`test`.`t1`.`a`) <=> coalesce(TIME'10:20:30',`test`.`t1`.`a`) | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ Summary: With two literals propagation works fine If there is a parameter, propagation does not work This happens because Item_temporal_literal::basic_const_item() returns true , while Item_param::basic_const_item::basic_const_item() returns false when bound to a temporal value.

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2016-11-27 11:51

Updated:: 2018-06-08 08:49

Resolved:: 2018-06-08 08:49

Git Integration

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

MariaDB Server