[MDEV-17050] query optimizer - view status from information_schema Created: 2018-08-23  Updated: 2022-01-28

Status: Open
Project: MariaDB Server
Component/s: Information Schema, Views
Affects Version/s: 5.5, 10.0, 10.1, 10.1.35, 10.2.17, 10.3.9, 10.2, 10.3
Fix Version/s: 5.5

Type: Bug Priority: Minor
Reporter: Jochen Vetter Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None
Environment:
  1. lsb_release -a
    LSB Version: :core-4.1-amd64:core-4.1-noarch
    Distributor ID: CentOS
    Description: CentOS Linux release 7.5.1804 (Core)
    Release: 7.5.1804
    Codename: Core

#uname -a
Linux marsupilami 3.10.0-862.3.3.el7.x86_64 #1 SMP Fri Jun 15 04:15:27 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux


 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



 Comments   
Comment by Alice Sherepa [ 2018-08-23 ]

Thanks for the report!
Repeatable on MariaDB 5.5-10.3, also the same on Mysql 5.5.56, 5.6.40, 5.7.22

create table t1(i int);
 
create view v1 as select * from t1;
 
drop table t1;
 
 
 
SELECT TABLE_COMMENT,TABLE_ROWS FROM information_schema.tables WHERE table_type='VIEW' ;
 
SELECT TABLE_COMMENT FROM information_schema.tables WHERE table_type='VIEW' ;


MariaDB [test]> SELECT TABLE_COMMENT,TABLE_ROWS FROM information_schema.tables WHERE table_type='VIEW' ;
 
+---------------------------------------------------------------------------------------------------------------------------+------------+
 
| TABLE_COMMENT                                                                                                             | TABLE_ROWS |
 
+---------------------------------------------------------------------------------------------------------------------------+------------+
 
| View 'test.v1' 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, 1 warning (0.00 sec)
 
 
 
Warning (Code 1356): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
 
MariaDB [test]> SELECT TABLE_COMMENT FROM information_schema.tables WHERE table_type='VIEW' ;
 
+---------------+
 
| TABLE_COMMENT |
 
+---------------+
 
| VIEW          |
 
+---------------+
 
1 row in set (0.01 sec)
 
 
 
 
 
MariaDB [test]> explain extended SELECT TABLE_COMMENT FROM information_schema.tables WHERE table_type='VIEW' ;
 
+------+-------------+--------+------+---------------+------+---------+------+------+----------+---------------------------------------------------+
 
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                             |
 
+------+-------------+--------+------+---------------+------+---------+------+------+----------+---------------------------------------------------+
 
|    1 | SIMPLE      | tables | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using where; Open_frm_only; Scanned all databases |
 
+------+-------------+--------+------+---------------+------+---------+------+------+----------+---------------------------------------------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
Note (Code 1003): select `information_schema`.`tables`.`TABLE_COMMENT` AS `TABLE_COMMENT` from `information_schema`.`tables` where (`information_schema`.`tables`.`TABLE_TYPE` = 'VIEW')
 
 
 
MariaDB [test]> explain extended 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 | filtered | Extra                                               |
 
+------+-------------+--------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
 
|    1 | SIMPLE      | tables | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using where; Open_full_table; Scanned all databases |
 
+------+-------------+--------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
Note (Code 1003): select `information_schema`.`tables`.`TABLE_COMMENT` AS `TABLE_COMMENT`,`information_schema`.`tables`.`TABLE_ROWS` AS `TABLE_ROWS` from `information_schema`.`tables` where (`information_schema`.`tables`.`TABLE_TYPE` = 'VIEW')

Generated at Thu Feb 08 08:33:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.