Bug #97692 Querying information_schema.TABLES issue

• Submitted: 19 Nov 2019 15:31
• Modified: 20 Nov 2019 14:02
• Reporter: Vinicius Malvestio Grippa
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 5.7.27, 8.0.19
• OS: Any
• CPU Architecture: Any

[19 Nov 2019 15:31] Vinicius Malvestio Grippa

Description:
Querying information_schema.TABLES shows inconsistent behavior when there are views that reference invalid tables.

How to repeat:
Test case:

use test;
create table test1 (i int primary key);
create table test2 (i int primary key);
create view v as select t1.i from test1 t1 join test2 t2 on t2.i=t1.i;
drop table test2;

SELECT ts.*
FROM information_schema.TABLES ts
WHERE ts.TABLE_TYPE ='VIEW'
AND ts.TABLE_SCHEMA NOT IN ('sys')
AND ts.TABLE_COMMENT LIKE '%invalid%';

SELECT ts.TABLE_SCHEMA 
FROM information_schema.TABLES ts
WHERE ts.TABLE_TYPE ='VIEW'
AND ts.TABLE_SCHEMA NOT IN ('sys')
AND ts.TABLE_COMMENT LIKE '%invalid%';

On the SELECTs above it is possible to observe that the first query returns a row while the second one returns empty.

Suggested fix:
Return the same behavior.

[20 Nov 2019 14:02] MySQL Verification Team

Hi Mr. Grippa,

Thank you for your bug report.

I have managed to repeat the behaviour that you are reporting:

s.* results
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	ENGINE	VERSION	ROW_FORMAT	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	AUTO_INCREMENT	CREATE_TIME	UPDATE_TIME	CHECK_TIME	TABLE_COLLATION	CHECKSUM	CREATE_OPTIONS	TABLE_COMMENT
def	test	v	VIEW	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
ts.TABLE_SCHEMA results

Verified as reported.

[18 Jan 2020 18:43] Valeriy Kravchuk

I'd say that MySQL 8.0.19 is also affected (even though last query produces a warning, so it's somewhat "better"):

mysql> SELECT ts.*
    -> FROM information_schema.TABLES ts
    -> WHERE ts.TABLE_TYPE ='VIEW'
    -> AND ts.TABLE_SCHEMA NOT IN ('sys')
    -> AND ts.TABLE_COMMENT LIKE '%invalid%';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+--------------------------------------------------------------------------------------------------------------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT                                                                                                            |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+--------------------------------------------------------------------------------------------------------------------------+
| def           | test         | v          | VIEW       | NULL   |    NULL | NULL       |       NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | 2020-01-18 20:41:28 | NULL        | NULL       | NULL            |     NULL | NULL           | 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, 2 warnings (0,08 sec)

mysql>
mysql> SELECT ts.TABLE_SCHEMA
    -> FROM information_schema.TABLES ts
    -> WHERE ts.TABLE_TYPE ='VIEW'
    -> AND ts.TABLE_SCHEMA NOT IN ('sys')
    -> AND ts.TABLE_COMMENT LIKE '%invalid%';
+--------------+
| TABLE_SCHEMA |
+--------------+
| test         |
+--------------+
1 row in set, 1 warning (0,00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1356
Message: 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)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0,00 sec)

[20 Jan 2020 12:51] MySQL Verification Team

Thank you, Valeriy.

Turned out that it is true.