[#MDEV-26999] Misleading results upon querying INFORMATION

Note: InnoDB is only needed to avoid extra warnings upon querying I_S

--source include/have_innodb.inc

create table tm (a int) engine=mrg_myisam union (t);

--vertical_results

select * from information_schema.tables where table_comment = "Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist";

--echo ###########################

select table_name from information_schema.tables where table_comment = "Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist";

# Cleanup

drop table tm;

The first query selects all columns from the I_S table, and it returns the expected row, with a warning:

10.2 059797ed
MariaDB [test]> select * from information_schema.tables where table_comment = "Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist"
-> \G
************************* 1. row *************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: tm
TABLE_TYPE: BASE TABLE
ENGINE: NULL
VERSION: NULL
ROW_FORMAT: NULL
TABLE_ROWS: NULL
AVG_ROW_LENGTH: NULL
DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: NULL
DATA_FREE: NULL
AUTO_INCREMENT: NULL
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: NULL
CHECKSUM: NULL
CREATE_OPTIONS: NULL
TABLE_COMMENT: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
1 row in set, 1 warning (0.057 sec)

MariaDB [test]> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------+
\| Level \| Code \| Message \|
+---------+------+-----------------------------------------------------------------------------------------------------+
\| Warning \| 1168 \| Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist \|
+---------+------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

The second query, with the same WHERE condition, attempts to select certain fields (table name in this case), and it returns an empty result set, no warnings:

MariaDB [test]> select table_name from information_schema.tables where table_comment = "Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist";

Empty set (0.021 sec)

Same is true for views which suffer from "references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them".

It probably can be explained by the internals of I_S, but it's still misleading and potentially problematic.

In my somewhat realistic use case, I am trying to take periodic mysqldump from the schema. When broken views or merge tables exist (which can easily happen if an underlying table is carelessly dropped), the whole dump will fail. So, before creating the dump, I check the schema for such broken ("widow") views and tables and drop them. And it appears that I can easily find them via a query from I_S, but when my periodic job selects the table_schema/table_name, it comes up with nothing. It is of course easy to work around when you know the problem (select all and then only use the columns you need), but one would have to know that a workaround is needed.

Reproducible on all of 10.x and MySQL 5.7. MySQL 8.0 doesn't seem to return anything even to the SELECT * query.

[MDEV-26999] Misleading results upon querying INFORMATION_SCHEMA for broken tables/views Created: 2021-11-08 Updated: 2023-04-27
Status:	Open
Project:	MariaDB Server
Component/s:	Information Schema
Affects Version/s:	10.2, 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s:	10.4, 10.5, 10.6

[MDEV-26999] Misleading results upon querying INFORMATION_SCHEMA for broken tables/views Created: 2021-11-08 Updated: 2023-04-27