and Fixing JOIN Issues with SQLite PRAGMA Table-Valued Functions

Issue Overview: JOIN Behavior with PRAGMA Table-Valued Functions in SQLite

The core issue revolves around the unexpected behavior of JOIN operations when used with SQLite’s PRAGMA table-valued functions, specifically pragma_table_info(). The problem manifests when attempting to join pragma_table_info() with other PRAGMA functions like pragma_table_list() and pragma_foreign_key_list(). The query in question is designed to retrieve information about foreign key references in an SQLite database. However, the results vary significantly depending on the type of JOIN used.

When using an INNER JOIN or a simple JOIN (which is semantically equivalent to INNER JOIN in SQLite), the query returns zero rows. This is counterintuitive because the tables involved in the join are known to exist, and the pragma_table_info() function should return rows when provided with valid table names. On the other hand, using LEFT JOIN, RIGHT JOIN, FULL JOIN, or CROSS JOIN produces the expected results, with all relevant rows being returned.

This discrepancy is particularly puzzling because SQLite’s documentation states that CROSS JOIN should produce the same results as INNER JOIN, JOIN, and the comma operator. However, in this specific scenario, the behavior diverges, leading to confusion and the suspicion of a potential bug in SQLite’s implementation.

Possible Causes: Hidden and Input-Only Columns in PRAGMA Table-Valued Functions

The root cause of this issue lies in the interaction between SQLite’s query planner and the hidden, input-only columns of the PRAGMA table-valued functions. Specifically, the pragma_table_info() function has two hidden columns: tablename and schema. These columns are input-only, meaning that the virtual table cannot discover their values unless explicitly provided through constraints or arguments.

When the query planner invokes the xBestIndex method for the pragma_table_info() virtual table, it does so multiple times, each time with different combinations of known and unknown values for tablename and schema. The xBestIndex method is responsible for determining the most efficient way to access the data, and it does so by estimating the cost of different access patterns.

In the problematic scenario, the query planner might choose an access pattern where only tablename is known, but schema is not. This leads to the planner adding a conditional check (pragma_table_info.schema == t2.s) before returning each row. However, since schema is an input-only column, pragma_table_info.schema always returns NULL, causing the conditional check to fail and resulting in zero rows being returned.

This behavior is exacerbated by the fact that the cost estimates for different access patterns were not sufficiently differentiated. As a result, the query planner might choose a suboptimal access pattern that leads to incorrect results. This is why using LEFT JOIN, RIGHT JOIN, FULL JOIN, or CROSS JOIN works—these join types do not rely on the same conditional checks as INNER JOIN, and thus avoid the issue.

Troubleshooting Steps, Solutions & Fixes: Adjusting xBestIndex and Cost Estimates

To address this issue, the SQLite development team implemented a fix that adjusts the cost estimates in the xBestIndex method for PRAGMA table-valued functions. The fix ensures that the query planner always chooses the access pattern where both tablename and schema are known, thereby avoiding the problematic conditional check.

The fix involves increasing the cost estimate for the access pattern where only tablename is known, making it less attractive to the query planner. This ensures that the planner will prefer the access pattern where both tablename and schema are known, leading to correct results.

Additionally, the team considered an alternative solution where the xBestIndex method would return SQLITE_CONSTRAINT when only tablename is known, effectively preventing the query planner from choosing that access pattern altogether. This approach was also implemented and provides a more robust solution to the problem.

For users experiencing this issue, the recommended workaround is to use LEFT JOIN, RIGHT JOIN, FULL JOIN, or CROSS JOIN when joining PRAGMA table-valued functions. These join types do not rely on the same conditional checks as INNER JOIN and will produce the correct results. However, it is important to note that this workaround is only necessary until the fix is applied to the SQLite version in use.

In summary, the issue with JOIN operations and PRAGMA table-valued functions in SQLite is caused by the interaction between hidden, input-only columns and the query planner’s cost estimates. The fix involves adjusting these cost estimates to ensure that the query planner chooses the correct access pattern, thereby producing the expected results. Users can apply the workaround of using non-INNER JOIN types until the fix is available in their SQLite version.