为何SQLite中同时使用WHERE与ORDER BY子句查询视图无结果？

背景 #

为适配依赖MySQL INFORMATION_SCHEMA视图的工具，我在SQLite中复刻了INFORMATION_SCHEMA.COLUMNS视图，对应的查询语句如下：

WITH RECURSIVE table_list AS 
(
    SELECT name, schema
    FROM pragma_table_list()
    UNION
    SELECT name, 'main'
    FROM pragma_module_list()
    WHERE name NOT LIKE 'fts%'
      AND name NOT LIKE 'rtree%'
),
table_info AS 
(
    SELECT
        'def' AS TABLE_CATALOG,
        tl.schema AS TABLE_SCHEMA,
        tl.name AS TABLE_NAME,
        ti.name AS COLUMN_NAME,
        ti.cid AS ORDINAL_POSITION,
        ti.dflt_value AS COLUMN_DEFAULT,
        IIF(ti."notnull" AND ti.pk, 'YES', 'NO') AS IS_NULLABLE,
        CASE UPPER(ti.type)
            WHEN 'TEXT' THEN 'varchar'
            WHEN 'INT' THEN 'bigint'
            WHEN 'REAL' THEN 'float'
            WHEN 'BLOB' THEN 'blob'
            WHEN 'INTEGER' THEN 'bigint'
            WHEN 'TINYINT' THEN 'bigint'
            WHEN 'SMALLINT' THEN 'bigint'
            WHEN 'MEDIUMINT' THEN 'bigint'
            WHEN 'BIGINT' THEN 'bigint'
            WHEN 'UNSIGNED BIG INT' THEN 'bigint'
            WHEN 'INT2' THEN 'bigint'
            WHEN 'INT8' THEN 'bigint'
            WHEN 'VARCHAR' THEN 'varchar'
            WHEN 'VARCHAR(255)' THEN 'varchar'
            ELSE 'varchar'
        END AS DATA_TYPE,
        65535 AS CHARACTER_MAXIMUM_LENGTH,
        65535 AS CHARACTER_OCTET_LENGTH,
        NULL AS NUMERIC_PRECISION,
        NULL AS NUMERIC_SCALE,
        NULL AS DATETIME_PRECISION,
        'utf8mb3' AS CHARACTER_SET_NAME,
        'BINARY' AS COLLATION_NAME,
        CASE UPPER(ti.type)
            WHEN 'TEXT' THEN 'varchar(65535)'
            WHEN 'INT' THEN 'int'
            WHEN 'REAL' THEN 'double'
            WHEN 'BLOB' THEN 'blob'
            WHEN 'INTEGER' THEN 'int'
            WHEN 'TINYINT' THEN 'int'
            WHEN 'SMALLINT' THEN 'int'
            WHEN 'MEDIUMINT' THEN 'int'
            WHEN 'BIGINT' THEN 'int'
            WHEN 'UNSIGNED BIG INT' THEN 'int'
            WHEN 'INT2' THEN 'int'
            WHEN 'INT8' THEN 'int'
            WHEN 'VARCHAR' THEN 'varchar(65535)'
            WHEN 'VARCHAR(255)' THEN 'varchar(65535)'
            ELSE 'varchar(65535)'
        END AS COLUMN_TYPE,
        IIF(ti.pk, 'PRI', '') AS COLUMN_KEY,
        IIF(tl.schema='information_schema', 'select', 'select,insert,update,references') AS PRIVILEGES,
        '' AS COLUMN_COMMENT,
        '' AS GENERATION_EXPRESSION,
        NULL AS SRS_ID,
        '' AS EXTRA
    FROM
        table_list tl,
        pragma_table_info(tl.name) ti
)
SELECT * 
FROM table_info;

该查询可正常返回所有表的列信息。

问题 #

单独添加WHERE子句过滤main schema时，查询正常返回结果：

...
SELECT * 
FROM table_info
WHERE TABLE_SCHEMA = 'main';

单独添加ORDER BY子句按表名和列顺序排序时，也能正常运行：

...
SELECT * 
FROM table_info
ORDER BY TABLE_NAME, ORDINAL_POSITION;

但同时添加WHERE和ORDER BY子句时，查询无法返回任何行：

...
SELECT * 
FROM table_info 
WHERE TABLE_SCHEMA = 'main' 
ORDER BY TABLE_NAME, ORDINAL_POSITION;

使用的SQLite版本为3.45.1。

原因分析 #

这是SQLite查询优化器处理包含pragma函数的CTE时的特殊问题：当同时应用过滤和排序操作时，优化器会调整执行顺序，导致pragma_table_info()与table_list的关联逻辑异常，无法正确匹配符合条件的数据。

解决方案 #

方案1：将过滤条件提前到CTE中 #

把TABLE_SCHEMA = 'main'的过滤逻辑放到table_info CTE内，避免外部过滤和排序的冲突：

WITH RECURSIVE table_list AS 
(
    SELECT name, schema
    FROM pragma_table_list()
    UNION
    SELECT name, 'main'
    FROM pragma_module_list()
    WHERE name NOT LIKE 'fts%'
      AND name NOT LIKE 'rtree%'
),
table_info AS 
(
    SELECT
        'def' AS TABLE_CATALOG,
        tl.schema AS TABLE_SCHEMA,
        tl.name AS TABLE_NAME,
        ti.name AS COLUMN_NAME,
        ti.cid AS ORDINAL_POSITION,
        ti.dflt_value AS COLUMN_DEFAULT,
        IIF(ti."notnull" AND ti.pk, 'YES', 'NO') AS IS_NULLABLE,
        CASE UPPER(ti.type)
            WHEN 'TEXT' THEN 'varchar'
            WHEN 'INT' THEN 'bigint'
            WHEN 'REAL' THEN 'float'
            WHEN 'BLOB' THEN 'blob'
            WHEN 'INTEGER' THEN 'bigint'
            WHEN 'TINYINT' THEN 'bigint'
            WHEN 'SMALLINT' THEN 'bigint'
            WHEN 'MEDIUMINT' THEN 'bigint'
            WHEN 'BIGINT' THEN 'bigint'
            WHEN 'UNSIGNED BIG INT' THEN 'bigint'
            WHEN 'INT2' THEN 'bigint'
            WHEN 'INT8' THEN 'bigint'
            WHEN 'VARCHAR' THEN 'varchar'
            WHEN 'VARCHAR(255)' THEN 'varchar'
            ELSE 'varchar'
        END AS DATA_TYPE,
        65535 AS CHARACTER_MAXIMUM_LENGTH,
        65535 AS CHARACTER_OCTET_LENGTH,
        NULL AS NUMERIC_PRECISION,
        NULL AS NUMERIC_SCALE,
        NULL AS DATETIME_PRECISION,
        'utf8mb3' AS CHARACTER_SET_NAME,
        'BINARY' AS COLLATION_NAME,
        CASE UPPER(ti.type)
            WHEN 'TEXT' THEN 'varchar(65535)'
            WHEN 'INT' THEN 'int'
            WHEN 'REAL' THEN 'double'
            WHEN 'BLOB' THEN 'blob'
            WHEN 'INTEGER' THEN 'int'
            WHEN 'TINYINT' THEN 'int'
            WHEN 'SMALLINT' THEN 'int'
            WHEN 'MEDIUMINT' THEN 'int'
            WHEN 'BIGINT' THEN 'int'
            WHEN 'UNSIGNED BIG INT' THEN 'int'
            WHEN 'INT2' THEN 'int'
            WHEN 'INT8' THEN 'int'
            WHEN 'VARCHAR' THEN 'varchar(65535)'
            WHEN 'VARCHAR(255)' THEN 'varchar(65535)'
            ELSE 'varchar(65535)'
        END AS COLUMN_TYPE,
        IIF(ti.pk, 'PRI', '') AS COLUMN_KEY,
        IIF(tl.schema='information_schema', 'select', 'select,insert,update,references') AS PRIVILEGES,
        '' AS COLUMN_COMMENT,
        '' AS GENERATION_EXPRESSION,
        NULL AS SRS_ID,
        '' AS EXTRA
    FROM
        table_list tl,
        pragma_table_info(tl.name) ti
    WHERE tl.schema = 'main' -- 提前过滤
)
SELECT * 
FROM table_info
ORDER BY TABLE_NAME, ORDINAL_POSITION;

方案2：使用临时表存储中间结果 #

先将table_info的结果存入临时表，再对临时表执行过滤和排序，绕过优化器执行顺序问题：

WITH RECURSIVE table_list AS 
(
    SELECT name, schema
    FROM pragma_table_list()
    UNION
    SELECT name, 'main'
    FROM pragma_module_list()
    WHERE name NOT LIKE 'fts%'
      AND name NOT LIKE 'rtree%'
),
table_info AS 
(
    SELECT
        'def' AS TABLE_CATALOG,
        tl.schema AS TABLE_SCHEMA,
        tl.name AS TABLE_NAME,
        ti.name AS COLUMN_NAME,
        ti.cid AS ORDINAL_POSITION,
        ti.dflt_value AS COLUMN_DEFAULT,
        IIF(ti."notnull" AND ti.pk, 'YES', 'NO') AS IS_NULLABLE,
        CASE UPPER(ti.type)
            WHEN 'TEXT' THEN 'varchar'
            WHEN 'INT' THEN 'bigint'
            WHEN 'REAL' THEN 'float'
            WHEN 'BLOB' THEN 'blob'
            WHEN 'INTEGER' THEN 'bigint'
            WHEN 'TINYINT' THEN 'bigint'
            WHEN 'SMALLINT' THEN 'bigint'
            WHEN 'MEDIUMINT' THEN 'bigint'
            WHEN 'BIGINT' THEN 'bigint'
            WHEN 'UNSIGNED BIG INT' THEN 'bigint'
            WHEN 'INT2' THEN 'bigint'
            WHEN 'INT8' THEN 'bigint'
            WHEN 'VARCHAR' THEN 'varchar'
            WHEN 'VARCHAR(255)' THEN 'varchar'
            ELSE 'varchar'
        END AS DATA_TYPE,
        65535 AS CHARACTER_MAXIMUM_LENGTH,
        65535 AS CHARACTER_OCTET_LENGTH,
        NULL AS NUMERIC_PRECISION,
        NULL AS NUMERIC_SCALE,
        NULL AS DATETIME_PRECISION,
        'utf8mb3' AS CHARACTER_SET_NAME,
        'BINARY' AS COLLATION_NAME,
        CASE UPPER(ti.type)
            WHEN 'TEXT' THEN 'varchar(65535)'
            WHEN 'INT' THEN 'int'
            WHEN 'REAL' THEN 'double'
            WHEN 'BLOB' THEN 'blob'
            WHEN 'INTEGER' THEN 'int'
            WHEN 'TINYINT' THEN 'int'
            WHEN 'SMALLINT' THEN 'int'
            WHEN 'MEDIUMINT' THEN 'int'
            WHEN 'BIGINT' THEN 'int'
            WHEN 'UNSIGNED BIG INT' THEN 'int'
            WHEN 'INT2' THEN 'int'
            WHEN 'INT8' THEN 'int'
            WHEN 'VARCHAR' THEN 'varchar(65535)'
            WHEN 'VARCHAR(255)' THEN 'varchar(65535)'
            ELSE 'varchar(65535)'
        END AS COLUMN_TYPE,
        IIF(ti.pk, 'PRI', '') AS COLUMN_KEY,
        IIF(tl.schema='information_schema', 'select', 'select,insert,update,references') AS PRIVILEGES,
        '' AS COLUMN_COMMENT,
        '' AS GENERATION_EXPRESSION,
        NULL AS SRS_ID,
        '' AS EXTRA
    FROM
        table_list tl,
        pragma_table_info(tl.name) ti
)
CREATE TEMP TABLE temp_columns AS SELECT * FROM table_info;

SELECT * 
FROM temp_columns
WHERE TABLE_SCHEMA = 'main'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

DROP TABLE temp_columns; -- 临时表会在会话结束后自动删除，此语句可选

方案3：改用显式JOIN替代隐式连接 #

将table_list和pragma_table_info的隐式连接改为显式CROSS JOIN，帮助优化器正确处理关联逻辑：

WITH RECURSIVE table_list AS 
(
    SELECT name, schema
    FROM pragma_table_list()
    UNION
    SELECT name, 'main'
    FROM pragma_module_list()
    WHERE name NOT LIKE 'fts%'
      AND name NOT LIKE 'rtree%'
),
table_info AS 
(
    SELECT
        'def' AS TABLE_CATALOG,
        tl.schema AS TABLE_SCHEMA,
        tl.name AS TABLE_NAME,
        ti.name AS COLUMN_NAME,
        ti.cid AS ORDINAL_POSITION,
        ti.dflt_value AS COLUMN_DEFAULT,
        IIF(ti."notnull" AND ti.pk, 'YES', 'NO') AS IS_NULLABLE,
        CASE UPPER(ti.type)
            WHEN 'TEXT' THEN 'varchar'
            WHEN 'INT' THEN 'bigint'
            WHEN 'REAL' THEN 'float'
            WHEN 'BLOB' THEN 'blob'
            WHEN 'INTEGER' THEN 'bigint'
            WHEN 'TINYINT' THEN 'bigint'
            WHEN 'SMALLINT' THEN 'bigint'
            WHEN 'MEDIUMINT' THEN 'bigint'
            WHEN 'BIGINT' THEN 'bigint'
            WHEN 'UNSIGNED BIG INT' THEN 'bigint'
            WHEN 'INT2' THEN 'bigint'
            WHEN 'INT8' THEN 'bigint'
            WHEN 'VARCHAR' THEN 'varchar'
            WHEN 'VARCHAR(255)' THEN 'varchar'
            ELSE 'varchar'
        END AS DATA_TYPE,
        65535 AS CHARACTER_MAXIMUM_LENGTH,
        65535 AS CHARACTER_OCTET_LENGTH,
        NULL AS NUMERIC_PRECISION,
        NULL AS NUMERIC_SCALE,
        NULL AS DATETIME_PRECISION,
        'utf8mb3' AS CHARACTER_SET_NAME,
        'BINARY' AS COLLATION_NAME,
        CASE UPPER(ti.type)
            WHEN 'TEXT' THEN 'varchar(65535)'
            WHEN 'INT' THEN 'int'
            WHEN 'REAL' THEN 'double'
            WHEN 'BLOB' THEN 'blob'
            WHEN 'INTEGER' THEN 'int'
            WHEN 'TINYINT' THEN 'int'
            WHEN 'SMALLINT' THEN 'int'
            WHEN 'MEDIUMINT' THEN 'int'
            WHEN 'BIGINT' THEN 'int'
            WHEN 'UNSIGNED BIG INT' THEN 'int'
            WHEN 'INT2' THEN 'int'
            WHEN 'INT8' THEN 'int'
            WHEN 'VARCHAR' THEN 'varchar(65535)'
            WHEN 'VARCHAR(255)' THEN 'varchar(65535)'
            ELSE 'varchar(65535)'
        END AS COLUMN_TYPE,
        IIF(ti.pk, 'PRI', '') AS COLUMN_KEY,
        IIF(tl.schema='information_schema', 'select', 'select,insert,update,references') AS PRIVILEGES,
        '' AS COLUMN_COMMENT,
        '' AS GENERATION_EXPRESSION,
        NULL AS SRS_ID,
        '' AS EXTRA
    FROM table_list tl
    CROSS JOIN pragma_table_info(tl.name) ti -- 显式连接
)
SELECT * 
FROM table_info
WHERE TABLE_SCHEMA = 'main'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

内容的提问来源于stack exchange，提问作者Julien