Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5, 10.0, 10.1, 10.1.35, 10.2.17, 10.3.9, 10.2, 10.3
-
None
Description
Hi,
we observed some unpleasant behaviour regarding the query optimizer and data dictionary in database schema "information_schema".
the following sql query should monitor our database instance for broken views
Query:
SELECT count(*) FROM information_schema.tables WHERE table_type='VIEW' AND table_comment LIKE '%invalid%' ;
|
in mysql 5.5 this query is working but in newer mysql/mariadb versions (reproducable from mariadb 10.1 up to 10.3) the above query always returns zero.
the cause for this problem seems to be located in the query optimizer, which uses the frm data to evaluate this query.
therefore the WHERE condition never matches LIKE because it only contains "VIEW"
MariaDB [(none)]> SELECT TABLE_COMMENT FROM information_schema.tables WHERE table_type='VIEW' ;
|
+---------------+
|
| TABLE_COMMENT |
|
+---------------+
|
| VIEW |
|
+---------------+
|
1 row in set, 1 warning (0.007 sec)
|
|
|
MariaDB [(none)]> EXPLAIN SELECT TABLE_COMMENT FROM information_schema.tables WHERE table_type='VIEW' ;
|
+------+-------------+--------+------+---------------+------+---------+------+------+---------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+------+---------------+------+---------+------+------+---------------------------------------------------+
|
| 1 | SIMPLE | tables | ALL | NULL | NULL | NULL | NULL | NULL | Using where; Open_frm_only; Scanned all databases |
|
+------+-------------+--------+------+---------------+------+---------+------+------+---------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
|
|
MariaDB [(none)]> explain SELECT count(*) FROM information_schema.tables WHERE table_type='VIEW' AND table_comment LIKE '%invalid%' ;
|
+------+-------------+--------+------+---------------+------+---------+------+------+---------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+------+---------------+------+---------+------+------+---------------------------------------------------+
|
| 1 | SIMPLE | tables | ALL | NULL | NULL | NULL | NULL | NULL | Using where; Open_frm_only; Scanned all databases |
|
+------+-------------+--------+------+---------------+------+---------+------+------+---------------------------------------------------+
|
1 row in set (0.001 sec)
|
the WHERE clause is evaluated if a full table scan ist performed:
MariaDB [(none)]> EXPLAIN SELECT TABLE_COMMENT,TABLE_ROWS FROM information_schema.tables WHERE table_type='VIEW' ;
|
+------+-------------+--------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
| 1 | SIMPLE | tables | ALL | NULL | NULL | NULL | NULL | NULL | Using where; Open_full_table; Scanned all databases |
|
+------+-------------+--------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [(none)]> SELECT TABLE_COMMENT,TABLE_ROWS FROM information_schema.tables WHERE table_type='VIEW' ;
|
+-----------------------------------------------------------------------------------------------------------------------------+------------+
|
| TABLE_COMMENT | TABLE_ROWS |
|
+-----------------------------------------------------------------------------------------------------------------------------+------------+
|
| View 'testj.bla' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them | NULL |
|
+-----------------------------------------------------------------------------------------------------------------------------+------------+
|
1 row in set, 2 warnings (0.009 sec)
|
is this working as designed or do we hit a bug? (maybe a status field for views could address this issue.)
kind regards,
Jochen