Bug #97692 Querying information_schema.TABLES issue
Submitted: 19 Nov 15:31 Modified: 20 Nov 14:02
Reporter: Vinicius Malvestio Grippa Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.7.27 OS:Any
Assigned to: CPU Architecture:Any

[19 Nov 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 14:02] Sinisa Milivojevic
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.