Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17050

query optimizer - view status from information_schema

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.1.35, 10.2.17, 10.3.9
    • Fix Version/s: 5.5
    • Component/s: Information Schema, Views
    • Labels:
      None
    • Environment:

      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

        Attachments

          Activity

            People

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              jvetter Jochen Vetter
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: