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