Why is SQLite unable to perform a WHERE and an ORDER BY clause simultaneously in a view?

Question

Context

I'm trying to replicate the the INFORMATION_SCHEMA views from MySQL in SQLite (I have tools that expect these views).

For the INFORMATION_SCHEMA.COLUMNS view, I have the following query

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;

which returns all columns of all tables.

Issue

To this query, I can add a WHERE clause:

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

and it works.

I can also add an ORDER BY clause:

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

and it works as well.

However, when I combine the WHERE clause with the ORDER BY clause, the query does not return any rows:

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

I have run each subquery alone, and they return rows. I have also tried to run the query with only the WHERE clause or only the ORDER BY clause, and it returns rows.

Therefore, I expected that combining the WHERE clause and the ORDER BY clause would work - but it didn't.

SQLite version : 3.45.1

Answer 1

Upgrading SQLite to 3.46.0 resolved the issue on my end.

I was using mattn/go-sqlite3, which currently stands at version 3.45.1