|
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.
|