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

logic bug when joining INFROMATION_SCHEMA tables using USING

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.3.13
    • N/A
    • Information Schema
    • None

    Description

      the last query shows the problem, the expected output is above. I find it interesting that SELECT * doesn't exhibit the bug, but SELECTing some columns does.

      MariaDB [test]> USE test;
      Database changed
      MariaDB [test]> SELECT @@version;
      +---------------------+
      | @@version           |
      +---------------------+
      | 10.3.13-MariaDB-log |
      +---------------------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> DROP TABLE IF EXISTS tester;
      Query OK, 0 rows affected (0.004 sec)
       
      MariaDB [test]> CREATE TABLE tester (
          ->   tester_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
          ->   name VARCHAR(50) NOT NULL,
          ->   PRIMARY KEY (tester_id)
          -> ) ENGINE=INNODB;
      Query OK, 0 rows affected (0.006 sec)
       
      MariaDB [test]> SELECT * FROM information_schema.TABLES AS t JOIN information_schema.COLUMNS AS c 
          ->   ON t.TABLE_CATALOG = c.TABLE_CATALOG AND t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
          -> WHERE t.TABLE_SCHEMA = 'test' AND t.TABLE_NAME = 'tester';
      +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------------+------------------+-----------+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+------------------+------------+----------------+---------------------------------+----------------+--------------+-----------------------+
      | 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 | MAX_INDEX_LENGTH | TEMPORARY | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME    | COLUMN_TYPE      | COLUMN_KEY | EXTRA          | PRIVILEGES                      | COLUMN_COMMENT | IS_GENERATED | GENERATION_EXPRESSION |
      +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------------+------------------+-----------+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+------------------+------------+----------------+---------------------------------+----------------+--------------+-----------------------+
      | def           | test         | tester     | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2020-12-21 17:48:13 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |               |                0 | N         | def           | test         | tester     | tester_id   |                1 | NULL           | NO          | int       |                     NULL |                   NULL |                10 |             0 |               NULL | NULL               | NULL              | int(11) unsigned | PRI        | auto_increment | select,insert,update,references |                | NEVER        | NULL                  |
      | def           | test         | tester     | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2020-12-21 17:48:13 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |               |                0 | N         | def           | test         | tester     | name        |                2 | NULL           | NO          | varchar   |                       50 |                     50 |              NULL |          NULL |               NULL | latin1             | latin1_swedish_ci | varchar(50)      |            |                | select,insert,update,references |                | NEVER        | NULL                  |
      +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------------+------------------+-----------+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+------------------+------------+----------------+---------------------------------+----------------+--------------+-----------------------+
      2 rows in set (0.054 sec)
       
      MariaDB [test]> SELECT t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME FROM information_schema.TABLES AS t JOIN information_schema.COLUMNS AS c
          ->   ON t.TABLE_CATALOG = c.TABLE_CATALOG AND t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
          -> WHERE t.TABLE_SCHEMA = 'test' AND t.TABLE_NAME = 'tester';
      +--------------+------------+-------------+
      | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME |
      +--------------+------------+-------------+
      | test         | tester     | tester_id   |
      | test         | tester     | name        |
      +--------------+------------+-------------+
      2 rows in set (0.049 sec)
       
      MariaDB [test]> SELECT * FROM information_schema.TABLES AS t JOIN information_schema.COLUMNS AS c
          ->   USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
          -> WHERE t.TABLE_SCHEMA = 'test' AND t.TABLE_NAME = 'tester';
      +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------------+------------------+-----------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+------------------+------------+----------------+---------------------------------+----------------+--------------+-----------------------+
      | 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 | MAX_INDEX_LENGTH | TEMPORARY | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME    | COLUMN_TYPE      | COLUMN_KEY | EXTRA          | PRIVILEGES                      | COLUMN_COMMENT | IS_GENERATED | GENERATION_EXPRESSION |
      +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------------+------------------+-----------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+------------------+------------+----------------+---------------------------------+----------------+--------------+-----------------------+
      | def           | test         | tester     | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2020-12-21 17:48:13 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |               |                0 | N         | tester_id   |                1 | NULL           | NO          | int       |                     NULL |                   NULL |                10 |             0 |               NULL | NULL               | NULL              | int(11) unsigned | PRI        | auto_increment | select,insert,update,references |                | NEVER        | NULL                  |
      | def           | test         | tester     | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |               0 |            0 |         0 |              1 | 2020-12-21 17:48:13 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |               |                0 | N         | name        |                2 | NULL           | NO          | varchar   |                       50 |                     50 |              NULL |          NULL |               NULL | latin1             | latin1_swedish_ci | varchar(50)      |            |                | select,insert,update,references |                | NEVER        | NULL                  |
      +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------------+------------------+-----------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-------------------+------------------+------------+----------------+---------------------------------+----------------+--------------+-----------------------+
      2 rows in set (0.054 sec)
       
      MariaDB [test]> SELECT t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME FROM information_schema.TABLES AS t JOIN information_schema.COLUMNS AS c
          ->   USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
          -> WHERE t.TABLE_SCHEMA = 'test' AND t.TABLE_NAME = 'tester';
      Empty set (0.035 sec)
       
      MariaDB [test]>
      

      The explain of the last query showing `Skip_open_table` used.

      MariaDB [test]> EXPLAIN SELECT t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME FROM information_schema.TABLES AS t JOIN information_schema.COLUMNS AS c
          ->   USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME)
          -> WHERE t.TABLE_SCHEMA = 'test' AND t.TABLE_NAME = 'tester';
      +------+-------------+-------+------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key                     | key_len | ref  | rows | Extra                                             |
      +------+-------------+-------+------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
      |    1 | SIMPLE      | t     | ALL  | NULL          | TABLE_SCHEMA,TABLE_NAME | NULL    | NULL | NULL | Using where; Skip_open_table; Scanned 0 databases |
      |    1 | SIMPLE      | c     | ALL  | NULL          | NULL                    | NULL    | NULL | NULL | Using where; Open_frm_only; Scanned all databases |
      +------+-------------+-------+------+---------------+-------------------------+---------+------+------+---------------------------------------------------+
      2 rows in set (0.001 sec)
       
      MariaDB [test]>
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              CSTobey Christopher Tobey
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.