INFORMATION_SCHEMA.COLUMNS Incorrect subquery filtering results

[cloudWang001]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

select * from
(select 6 id from INFORMATION_SCHEMA.COLUMNS) t
where t.id =1 ;

2. What did you expect to see? (Required)

null

3. What did you see instead (Required)

a lot of 6

4. What is your TiDB version? (Required)

6.5.3

Release Version: v6.5.3
Edition: Community
Git Commit Hash: 71e6696
Git Branch: heads/refs/tags/v6.5.3
UTC Build Time: 2023-06-12 13:52:32
GoVersion: go1.19.9
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

---

Since upgrading from 4.0.15 to 6.5.3, .net's ef framework update model failed.
The reason is that there is a sql query result in 4.0.15 is correct, but the query result in 6.5.3 is wrong.
The original sql statement is:
SELECT UnionAll1.C5 AS C1, Extent1.Catalog, Extent1.Schema, Extent1.Name, UnionAll1.Name AS C2, UnionAll1.C1 AS C3, UnionAll1.IsNullable AS C4, UnionAll1.TypeName AS C5, UnionAll1.C2 AS C6, UnionAll1.C3 AS C7, UnionAll1.DateTimePrecision AS C8, UnionAll1.C4 AS C9, UnionAll1.IsIdentity AS C10, UnionAll1.IsStoreGenerated AS C11, CASE WHEN (Project5.C2 IS NOT NULL) THEN (Project5.C2) ELSE (0) END AS C12 FROM ( SELECT /* Tables / CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS Id, TABLE_CATALOG AS Catalog, TABLE_SCHEMA AS Schema, TABLE_NAME AS Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA=schema() ) AS Extent1 INNER JOIN ((SELECT Extent2.Id, Extent2.Name, Extent2.Ordinal AS C1, Extent2.IsNullable, Extent2.TypeName, Extent2.MaxLength AS C2, Extent2.Precision AS C3, Extent2.DateTimePrecision, Extent2.Scale AS C4, Extent2.IsIdentity, Extent2.IsStoreGenerated, 0 AS C5, Extent2.ParentId FROM ( SELECT / Table columns / CONCAT(TABLE_SCHEMA, '.', TABLE_NAME, '.', COLUMN_NAME) AS Id, CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS ParentId, COLUMN_NAME AS Name, ORDINAL_POSITION AS Ordinal, CASE IS_NULLABLE WHEN 'YES' THEN 1 ELSE 0 END AS IsNullable, IF(LEFT(COLUMN_TYPE,10) = 'tinyint(1)', 'bool', IF (LEFT(COLUMN_TYPE,10) = 'binary(16)' OR LEFT(COLUMN_TYPE,8) = 'char(36)', 'guid', IF (INSTR(COLUMN_TYPE, 'unsigned') = 0, DATA_TYPE, CONCAT('u', DATA_TYPE)))) AS TypeName, IF (CHARACTER_MAXIMUM_LENGTH > 2147483647, 2147483647, CHARACTER_MAXIMUM_LENGTH) AS MaxLength, CASE WHEN NUMERIC_PRECISION > 0 THEN CAST(NUMERIC_PRECISION AS CHAR) WHEN DATETIME_PRECISION > 0 THEN CAST(DATETIME_PRECISION AS CHAR) ELSE 0 END AS Precision, DATETIME_PRECISION AS DateTimePrecision, NUMERIC_SCALE AS Scale, NULL AS CollationCatalog, NULL AS CollationSchema, COLLATION_NAME AS CollationName, NULL AS CharacterSetCatalog, NULL AS CharacterSetSchema, CHARACTER_SET_NAME AS CharacterSetName, 0 AS IsMultiSet, CASE WHEN COLUMN_DEFAULT LIKE 'CURRENT_TIMESTAMP' THEN 1 WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS IsIdentity, CASE WHEN COLUMN_DEFAULT LIKE 'CURRENT_TIMESTAMP' THEN 1 WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS IsStoreGenerated, COLUMN_DEFAULT AS Default FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=schema() ) AS Extent2) UNION ALL (SELECT Extent3.Id, Extent3.Name, Extent3.Ordinal AS C1, Extent3.IsNullable, Extent3.TypeName, Extent3.MaxLength AS C2, Extent3.Precision AS C3, Extent3.DateTimePrecision, Extent3.Scale AS C4, Extent3.IsIdentity, Extent3.IsStoreGenerated, 6 AS C5, Extent3.ParentId FROM ( SELECT / View columns / CONCAT(TABLE_SCHEMA, '.', TABLE_NAME, '.', COLUMN_NAME) AS Id, CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS ParentId, COLUMN_NAME AS Name, ORDINAL_POSITION AS Ordinal, CASE IS_NULLABLE WHEN 'YES' THEN 1 ELSE 0 END AS IsNullable, IF(LEFT(COLUMN_TYPE,10) = 'tinyint(1)', 'bool', IF (LEFT(COLUMN_TYPE,10) = 'binary(16)' OR LEFT(COLUMN_TYPE,8) = 'char(36)', 'guid', IF (INSTR(COLUMN_TYPE, 'unsigned') = 0, DATA_TYPE, CONCAT('u', DATA_TYPE)))) AS TypeName, IF (CHARACTER_MAXIMUM_LENGTH > 2147483647, 2147483647, CHARACTER_MAXIMUM_LENGTH) AS MaxLength, CASE WHEN NUMERIC_PRECISION > 0 THEN CAST(NUMERIC_PRECISION AS UNSIGNED INTEGER) WHEN DATETIME_PRECISION > 0 THEN CAST(DATETIME_PRECISION AS UNSIGNED INTEGER) ELSE 0 END AS Precision, 0 AS DateTimePrecision, NUMERIC_SCALE AS Scale, NULL AS CollationCatalog, NULL AS CollationSchema, COLLATION_NAME AS CollationName, NULL AS CharacterSetCatalog, NULL AS CharacterSetSchema, CHARACTER_SET_NAME AS CharacterSetName, 0 AS IsMultiSet, CASE WHEN COLUMN_DEFAULT LIKE 'CURRENT_TIMESTAMP' THEN 1 WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS IsIdentity, CASE WHEN COLUMN_DEFAULT LIKE 'CURRENT_TIMESTAMP' THEN 1 WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS IsStoreGenerated, COLUMN_DEFAULT AS Default FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=schema() ) AS Extent3)) AS UnionAll1 ON (0 = UnionAll1.C5) AND (Extent1.Id = UnionAll1.ParentId) LEFT OUTER JOIN (SELECT UnionAll2.Id AS C1, 1 AS C2 FROM ( SELECT / Constraints / CONCAT(CONSTRAINT_SCHEMA, '.', TABLE_NAME, '.', CONSTRAINT_NAME) AS Id, CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS ParentId, CONSTRAINT_NAME AS Name, CONSTRAINT_TYPE AS ConstraintType, 0 AS IsDeferrable, 0 AS IsInitiallyDeferred FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE != 'CHECK' AND TABLE_SCHEMA=schema() ) AS Extent4 INNER JOIN ((SELECT 7 AS C1, Extent5.ConstraintId, Extent6.Id FROM ( SELECT / Constraint columns / CONCAT(CONSTRAINT_SCHEMA, '.', TABLE_NAME, '.', CONSTRAINT_NAME) AS ConstraintId, CONCAT(TABLE_SCHEMA, '.', TABLE_NAME, '.', COLUMN_NAME) AS ColumnId FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA=schema() ) AS Extent5 INNER JOIN ( SELECT / Table columns / CONCAT(TABLE_SCHEMA, '.', TABLE_NAME, '.', COLUMN_NAME) AS Id, CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS ParentId, COLUMN_NAME AS Name, ORDINAL_POSITION AS Ordinal, CASE IS_NULLABLE WHEN 'YES' THEN 1 ELSE 0 END AS IsNullable, IF(LEFT(COLUMN_TYPE,10) = 'tinyint(1)', 'bool', IF (LEFT(COLUMN_TYPE,10) = 'binary(16)' OR LEFT(COLUMN_TYPE,8) = 'char(36)', 'guid', IF (INSTR(COLUMN_TYPE, 'unsigned') = 0, DATA_TYPE, CONCAT('u', DATA_TYPE)))) AS TypeName, IF (CHARACTER_MAXIMUM_LENGTH > 2147483647, 2147483647, CHARACTER_MAXIMUM_LENGTH) AS MaxLength, CASE WHEN NUMERIC_PRECISION > 0 THEN CAST(NUMERIC_PRECISION AS CHAR) WHEN DATETIME_PRECISION > 0 THEN CAST(DATETIME_PRECISION AS CHAR) ELSE 0 END AS Precision, DATETIME_PRECISION AS DateTimePrecision, NUMERIC_SCALE AS Scale, NULL AS CollationCatalog, NULL AS CollationSchema, COLLATION_NAME AS CollationName, NULL AS CharacterSetCatalog, NULL AS CharacterSetSchema, CHARACTER_SET_NAME AS CharacterSetName, 0 AS IsMultiSet, CASE WHEN COLUMN_DEFAULT LIKE 'CURRENT_TIMESTAMP' THEN 1 WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS IsIdentity, CASE WHEN COLUMN_DEFAULT LIKE 'CURRENT_TIMESTAMP' THEN 1 WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS IsStoreGenerated, COLUMN_DEFAULT AS Default FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=schema() ) AS Extent6 ON Extent6.Id = Extent5.ColumnId) UNION ALL (SELECT 11 AS C1, Extent7.ConstraintId, Extent8.Id FROM ( SELECT / View constraint columns / NULL AS ConstraintId, NULL AS ColumnId ) AS Extent7 INNER JOIN ( SELECT / View columns */ CONCAT(TABLE_SCHEMA, '.', TABLE_NAME, '.', COLUMN_NAME) AS Id, CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS ParentId, COLUMN_NAME AS Name, ORDINAL_POSITION AS Ordinal, CASE IS_NULLABLE WHEN 'YES' THEN 1 ELSE 0 END AS IsNullable, IF(LEFT(COLUMN_TYPE,10) = 'tinyint(1)', 'bool', IF (LEFT(COLUMN_TYPE,10) = 'binary(16)' OR LEFT(COLUMN_TYPE,8) = 'char(36)', 'guid', IF (INSTR(COLUMN_TYPE, 'unsigned') = 0, DATA_TYPE, CONCAT('u', DATA_TYPE)))) AS TypeName, IF (CHARACTER_MAXIMUM_LENGTH > 2147483647, 2147483647, CHARACTER_MAXIMUM_LENGTH) AS MaxLength, CASE WHEN NUMERIC_PRECISION > 0 THEN CAST(NUMERIC_PRECISION AS UNSIGNED INTEGER) WHEN DATETIME_PRECISION > 0 THEN CAST(DATETIME_PRECISION AS UNSIGNED INTEGER) ELSE 0 END AS Precision, 0 AS DateTimePrecision, NUMERIC_SCALE AS Scale, NULL AS CollationCatalog, NULL AS CollationSchema, COLLATION_NAME AS CollationName, NULL AS CharacterSetCatalog, NULL AS CharacterSetSchema, CHARACTER_SET_NAME AS CharacterSetName, 0 AS IsMultiSet, CASE WHEN COLUMN_DEFAULT LIKE 'CURRENT_TIMESTAMP' THEN 1 WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS IsIdentity, CASE WHEN COLUMN_DEFAULT LIKE 'CURRENT_TIMESTAMP' THEN 1 WHEN EXTRA LIKE '%auto%' THEN 1 ELSE 0 END AS IsStoreGenerated, COLUMN_DEFAULT AS Default FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=schema() ) AS Extent8 ON Extent8.Id = Extent7.ColumnId)) AS UnionAll2 ON (7 = UnionAll2.C1) AND (Extent4.Id = UnionAll2.ConstraintId) WHERE Extent4.ConstraintType = 'PRIMARY KEY') AS Project5 ON UnionAll1.Id = Project5.C1 WHERE Extent1.Name LIKE '%'

[lcwangchao]

The plan seems not right:

TiDB root@127.0.0.1:test> desc select * from (select 6 id from information_schema.columns) t where id = 1;
+------------------+----------+------+---------------+---------------+
| id               | estRows  | task | access object | operator info |
+------------------+----------+------+---------------+---------------+
| Projection_6     | 10000.00 | root |               | 6->Column#22  |
| └─MemTableScan_7 | 10000.00 | root | table:COLUMNS |               |
+------------------+----------+------+---------------+---------------+

Comparing with information_schema.tables , its plan is:

TiDB root@127.0.0.1:test> desc select * from (select 6 id from information_schema.tables) t where id = 1;
+---------------+---------+------+---------------+---------------+
| id            | estRows | task | access object | operator info |
+---------------+---------+------+---------------+---------------+
| Projection_7  | 0.00    | root |               | 6->Column#26  |
| └─TableDual_8 | 0.00    | root |               | rows:0        |
+---------------+---------+------+---------------+---------------+
2 rows in set
Time: 0.005s

[fixdb]

/assign @hawkingrei

[hawkingrei]

It has been fixed by #40935