information_schema.COLUMNS 表查询结果重复

问题描述

information_schema.COLUMNS 表通过 COLUMN_NAME in ('zone','a','b')过滤出重复行，与 in 内的条件个数相关。

1. 当查询语句 column_name 在 in 中列条件个数为 1个。

   obclient > SELECT * FROM information_schema.COLUMNS WHERE table_schema = 'oceanbase' AND table_name = '__all_server' and column_name in ('zone');
   

   输出结果为:

   +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+
   | 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 | GENERATION_EXPRESSION |
   +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+
   | def           | oceanbase    | __all_server | zone        |                6 | NULL           | NO          | varchar   |                      128 |                    512 |              NULL |          NULL |               NULL | utf8mb4            | utf8mb4_general_ci | varchar(128) |            |       | NULL       |                |                       |
   +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+
   1 row in set (0.00 sec)
   

2. 当查询语句 column_name 在 in 中列条件个数为 2个。

   obclient > SELECT * FROM information_schema.COLUMNS WHERE table_schema = 'oceanbase' AND table_name = '__all_server' AND column_name in ('zone','a');
   

   输出结果如下:

   +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+
   | 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 | GENERATION_EXPRESSION |
   +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+
   | def           | oceanbase    | __all_server | zone        |                6 | NULL           | NO          | varchar   |                      128 |                    512 |              NULL |          NULL |               NULL | utf8mb4            | utf8mb4_general_ci | varchar(128) |            |       | NULL       |                |                       |
   | def           | oceanbase    | __all_server | zone        |                6 | NULL           | NO          | varchar   |                      128 |                    512 |              NULL |          NULL |               NULL | utf8mb4            | utf8mb4_general_ci | varchar(128) |            |       | NULL       |                |                       |
   +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+
   2 rows in set (0.00 sec)
   

3. 当查询语句 column_name 在 in 中列条件个数为 3个。

   obclient > SELECT * FROM information_schema.COLUMNS WHERE table_schema = 'oceanbase' AND table_name = '__all_server' AND column_name in ('zone','a','b');
   

   输出结果如下:

   +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+
   | 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 | GENERATION_EXPRESSION |
   +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+
   | def           | oceanbase    | __all_server | zone        |                6 | NULL           | NO          | varchar   |                      128 |                    512 |              NULL |          NULL |               NULL | utf8mb4            | utf8mb4_general_ci | varchar(128) |            |       | NULL       |                |                       |
   | def           | oceanbase    | __all_server | zone        |                6 | NULL           | NO          | varchar   |                      128 |                    512 |              NULL |          NULL |               NULL | utf8mb4            | utf8mb4_general_ci | varchar(128) |            |       | NULL       |                |                       |
   | def           | oceanbase    | __all_server | zone        |                6 | NULL           | NO          | varchar   |                      128 |                    512 |              NULL |          NULL |               NULL | utf8mb4            | utf8mb4_general_ci | varchar(128) |            |       | NULL       |                |                       |
   +---------------+--------------+--------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+-------+------------+----------------+-----------------------+
   3 rows in set (0.00 sec)
   

适用版本

OceanBase 数据库 V2.2.x 、V3.2.x 版本。

问题原因

information_schema.COLUMNS 表的主键列包括 db_name、table_name、column_name，但实际上这张虚表的实现中只基于 db_name 和 table_name 实现了索引，因此对于 column_name 实际上还是个遍历的逻辑，这就导致 当优化器抽出 query range 为 [oceanbase, __all_server, zone] 以及 [ oceanbase, __all_server, a] 两个 range 时， 不仅 [oceanbase, __all_server, zone] 会扫描出 [oceanbase, all_server] 前缀的所有行，[oceanbase, __all_server, a] 也会扫描出包含 [oceanbase, all_server] 前缀的所有行，这就导致了 [oceanbase, __all_server, zone] 出现了两次，[oceanbase, __all_server, a,b] 也会扫描出包含 [oceanbase, all_server] 前缀的所有行，这就导致了 [oceanbase, __all_server, zone] 出现了三次，尽管优化器在该 Table scan 上保留了 Filter 作为兜底。但因为产生了两份相同数据，因此结果错误。

解决方法

升级至问题已修复版本。目前已修复的版本包括 OceanBase 数据库企业版 V3.2.4 BP3 Hotfix2 (oceanbase-3.2.4.3-103020012023051810) 及以上版本。