Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8709

Row equality elements do not get propagated

        Details

          Description

          DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b INT);
INSERT INTO t1 VALUES (10,10),(20,20);
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10 AND b=10 AND a>=10;
SHOW WARNINGS;

          returns:

          +-------+------+---------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                         |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 10) and (`test`.`t1`.`b` = 10)) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------+

          Notice, the a>=10 part was removed from the condition.

          Now if I rewrite the same query using ROW comparison syntax:

          EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a,b)=(10,10) AND a>=10;
SHOW WARNINGS;

          It returns:

          +-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                     |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = 10) and (`test`.`t1`.`b` = 10) and (`test`.`t1`.`a` >= 10)) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+

          Notice, the a>=10 part is still there.

          The problem was that the items that participate in the (a,b)=(10,10) equality did not have cmp_context set properly.
          With the changes made under the terms of MDEV-8728 this problem does not seem to exists any more in 10.1.8.

            Gliffy Diagrams

              Attachments

                Issue Links

                blocks

                Bug - A problem which impairs or prevents the functions of the product. MDEV-8728 Fix a number of problems in equal field and equal expression propagation

                • Critical - Crashes, loss of data, severe memory leak.
                • Closed

                  Activity

                  There are no comments yet on this issue.

                    People

                    • Assignee:
                      bar Alexander Barkov
                      Reporter:
                      bar Alexander Barkov
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      1 Start watching this issue

                      Dates

                      • Created:
                        Updated:
                        Resolved: