fix(sqlite): allow using fts5 table name in the where clause

[naspeh]

Fixes: https://github.com/sqlc-dev/sqlc/issues/3204

Allow using table name to the left of MATCH or equality operator.

Related:

• https://github.com/sqlc-dev/sqlc/issues/1797
• https://github.com/sqlc-dev/sqlc/pull/2531

[naspeh]

@kyleconroy Thank you for sqlc !

Could you look at this PR, please?

I have to use my fork as a workaround. Would love to get this in the official package.

[kyleconroy]

Could you please rebase this on the latest main? The tests didn't run when it was originally opened.

[naspeh]

@kyleconroy Done 🟢!

[ororsatti]

is this going to be merged anytime soon?

[awgaan]

@naspeh I am joining the FTS5 table in a query and use it in WHERE. This does not seem to work on your branch. Expected behavior?

[naspeh]

@awgaan it should work, I think. I can't check, I lost context for this branch as I stopped using sqlc on the project.

[awgaan]

Turns out sqlc treats fts5 as a case sensitive token. Creating a virtual table with USING FTS5 does not generate the correct models. That was hard af to debug.

[flamendless]

Does this fix this?

-- WORKING
-- name: GetProductsBySearchQuery :many
SELECT * FROM tbl_products_fts
WHERE
	serial MATCH ? OR
	name MATCH ?
LIMIT ?;

-- Not working " column "tbl_products_fts" does not exist"
SELECT * FROM tbl_products_fts
WHERE tbl_products_fts MATCH ?
LIMIT ?;

[naspeh]

Yep, it should fix this.

On Mon, Jun 23, 2025, 08:04 flamendless @.***> wrote:

flamendless left a comment (sqlc-dev/sqlc#3498) https://github.com/sqlc-dev/sqlc/pull/3498#issuecomment-2994935717

Does this fix this?

-- WORKING-- name: GetProductsBySearchQuery :manySELECT * FROM tbl_products_ftsWHERE serial MATCH ? OR name MATCH ?LIMIT ?; -- Not working " column "tbl_products_fts" does not exist"SELECT * FROM tbl_products_ftsWHERE tbl_products_fts MATCH ?LIMIT ?;

— Reply to this email directly, view it on GitHub https://github.com/sqlc-dev/sqlc/pull/3498#issuecomment-2994935717, or unsubscribe https://github.com/notifications/unsubscribe-auth/AABHMCI3HJTL35HYTPED4TT3E6DGJAVCNFSM6AAAAABLIAM2QOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDSOJUHEZTKNZRG4 . You are receiving this because you were mentioned.Message ID: @.***>

[flamendless]

Yep, it should fix this.

On Mon, Jun 23, 2025, 08:04 flamendless @.***> wrote:

flamendless left a comment (sqlc-dev/sqlc#3498) https://github.com/sqlc-dev/sqlc/pull/3498#issuecomment-2994935717

Does this fix this?

-- WORKING-- name: GetProductsBySearchQuery :manySELECT * FROM tbl_products_ftsWHERE serial MATCH ? OR name MATCH ?LIMIT ?; -- Not working " column "tbl_products_fts" does not exist"SELECT * FROM tbl_products_ftsWHERE tbl_products_fts MATCH ?LIMIT ?;

— Reply to this email directly, view it on GitHub https://github.com/sqlc-dev/sqlc/pull/3498#issuecomment-2994935717, or unsubscribe https://github.com/notifications/unsubscribe-auth/AABHMCI3HJTL35HYTPED4TT3E6DGJAVCNFSM6AAAAABLIAM2QOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDSOJUHEZTKNZRG4 . You are receiving this because you were mentioned.Message ID: @.***>

Awesome! I cant wait for this to get merged and released