Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL)
-
None
-
10.1.6-1
Description
This script:
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
|
INSERT INTO t1 VALUES ('1e1'),('1é1');
|
SELECT * FROM t1 WHERE a=10;
|
SELECT * FROM t1 WHERE a='1e1';
|
SELECT * FROM t1 WHERE a=10 AND a='1e1';
|
returns:
MariaDB [test]> SELECT * FROM t1 WHERE a=10;
|
+------+
|
| a |
|
+------+
|
| 1e1 |
|
+------+
|
1 row in set, 1 warning (0.00 sec)
|
|
|
MariaDB [test]> SELECT * FROM t1 WHERE a='1e1';
|
+------+
|
| a |
|
+------+
|
| 1e1 |
|
| 1é1 |
|
+------+
|
2 rows in set (0.00 sec)
|
|
|
MariaDB [test]> SELECT * FROM t1 WHERE a=10 AND a='1e1';
|
+------+
|
| a |
|
+------+
|
| 1e1 |
|
+------+
|
1 row in set, 1 warning (0.00 sec)
|
Looks good so far.
Now if I rewrite the last query using ROW syntax:
SELECT * FROM t1 WHERE (a,a)=(10,'1e1');
|
It erroneously starts to return both rows:
+------+
|
| a |
|
+------+
|
| 1e1 |
|
| 1é1 |
|
+------+
|
This EXPLAIN EXTENDED:
MariaDB [test]> EXPLAIN SELECT * FROM t1 WHERE (a,a)=(10,'1e1');
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [test]> SHOW WARNINGS;
|
Empty set (0.00 sec)
|
|
|
MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a,a)=(10,'1e1');
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
1 row in set, 1 warning (0.00 sec)
|
|
|
MariaDB [test]> SHOW WARNINGS;
|
+-------+------+--------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = '1e1') |
|
+-------+------+--------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
tells that the a=10 part was moved away from the condition.
This is wrong. It should be preserved.