logic bug when joining INFROMATION_SCHEMA tables using USING

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]>

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]>