Bug #86930 Wrong results for queries with row constructors and information_schema
Submitted: 4 Jul 2017 13:31 Modified: 4 Jul 2017 15:14
Reporter: Valeriy Kravchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.6.29, 5.7.18 OS:Any
Assigned to: CPU Architecture:Any
Tags: columns, IN, information_schema, row constructor

[4 Jul 2017 13:31] Valeriy Kravchuk
Description:
It seems row constructors are evaluated somehow wrong for the queries to information_schema.columns table:

openxs@ao756:~/dbs/5.7$ bin/mysql --host=127.0.0.1 --port=3308 -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table tt1(c1 int);
Query OK, 0 rows affected (0,27 sec)

mysql> create table tt2(c2 int);
Query OK, 0 rows affected (0,25 sec)

mysql> select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt1', 'c1'));
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0,00 sec)

mysql> select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt2', 'c2'));
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0,00 sec)

mysql> select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt1','c1'),('tt2', 'c2'));
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0,00 sec)

If we rewrite the query using subquery it works as expected:

mysql> select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (select 'tt1','c1' from dual union select 'tt2', 'c2' from dual);
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0,02 sec)

If we rewrite it to the equivalent one based on the manual (https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#row-constructor-range-opti...) it also works as expected:

mysql> select count(*) from information_schema.columns where table_schema='test' and (table_name='tt1' and column_name='c1') or (table_name='tt2' and column_name='c2');
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0,00 sec)

This is not the case for a normal table where IN expression with row constructors work as expected:

mysql> create table ttt(c1 varchar(10), c2 varchar(10));
Query OK, 0 rows affected (0,55 sec)

mysql> insert into ttt values ('a','b'), ('c', 'd');
Query OK, 2 rows affected (0,33 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select count(*) from ttt where (c1,c2) in (('a','b'),('c','d'));
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0,00 sec)

How to repeat:
use test;
create table tt1(c1 int);
create table tt2(c2 int);
select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt1', 'c1'));
select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt2', 'c2'));

-- compare these results with the above

select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (('tt1','c1'),('tt2', 'c2'));

-- and the following results from equivalent queries

select count(*) from information_schema.columns where table_schema='test' and (table_name, column_name) in (select 'tt1','c1' from dual union select 'tt2', 'c2' from dual);

select count(*) from information_schema.columns where table_schema='test' and (table_name='tt1' and column_name='c1') or (table_name='tt2' and column_name='c2');

Suggested fix:
Process queries with row constructors properly even in the information_schema.
[4 Jul 2017 15:14] MySQL Verification Team
Thank you for the bug report. Verified as described.
[11 Jul 2017 19:14] Erlend Dahl
On 8.0.3, with the new data dictionary

select count(*) from information_schema.columns where table_schema='test' and
(table_name, column_name) in (('tt1','c1'),('tt2','c2'));

returns 2, as expected.