[MDEV-8256] A part of a ROW comparison is erroneously optimized away - Jira

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.

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

MariaDB Server