Warning 1356 is returned by querying information_schema.columns when a scalar subquery is used as a column in view definition

[pcqz]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> create table t1(id int);
Query OK, 0 rows affected (0.06 sec)

mysql> create view v1 as select (select id from t1) as col from dual;
Query OK, 0 rows affected (0.07 sec)

mysql> select column_name, table_name from information_schema.columns where table_name='v1';
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                   |
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
| Warning | 1356 | View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. What did you expect to see? (Required)

+-------------+------------+
| column_name | table_name |
+-------------+------------+
| col         | v1         |
+-------------+------------+

3. What did you see instead (Required)

The warning 1356 is reported.

4. What is your TiDB version? (Required)

v6.1.0 and above

[pcqz]

Possibly related to the use of sys session after https://github.com/pingcap/tidb/pull/33946.

[lance6716]

not only there are warnings. The result set is empty before the fix

6.5.8

mysql> use test;
Database changed
mysql> create table t(a int);
Query OK, 0 rows affected (0.12 sec)

mysql> create view v as select * from t where a = (select max(t2.a) from t t2) or a = (select min(t2.a) from t t2);
Query OK, 0 rows affected (0.10 sec)

mysql> select * from information_schema.columns where table_name = 'v' and table_schema = 'test';
Empty set, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                  |
+---------+------+--------------------------------------------------------------------------------------------------------------------------+
| Warning | 1356 | View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
+---------+------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

6.5.11 (include the fix)

mysql> use test;
Database changed
mysql> create table t(a int);
Query OK, 0 rows affected (0.08 sec)

mysql> create view v as select * from t where a = (select max(t2.a) from t t2) or a = (select min(t2.a) from t t2);
Query OK, 0 rows affected (0.12 sec)

mysql> select * from information_schema.columns where table_name = 'v' and table_schema = 'test';
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+-----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES                      | COLUMN_COMMENT | GENERATION_EXPRESSION |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+-----------------------+
| def           | test         | v          | a           |                1 | NULL           | YES         | int       |                     NULL |                   NULL |                11 |             0 |               NULL | NULL               | NULL           | int(11)     |            |       | select,insert,update,references |                |                       |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+-----------------------+
1 row in set (0.01 sec)