The fts5 MATCH operator doesn't recognize the table name when it is joined with an alias name. Came across this because of a policy to encode the reason for joining in the joined table name, as I'm working a system that can automatically assemble an SQL query from a table of requirements.

Minimal Example

Setup:

CREATE TABLE metadata(id, thing);
CREATE VIRTUAL TABLE t_fts USING fts5(id UNINDEXED, text);
INSERT INTO metadata(id, thing) VALUES (1,'one'),(2,'two'),(3,'three');
INSERT INTO t_fts(id, text) VALUES (1,'foo bar baz'),(2,'lorem ipsum something'),(3,'foo something');

Launching the following query, making a table-alias and trying to use it fails:

select *
from metadata
inner join t_fts as foo_fts
WHERE foo_fts MATCH 'foo';

Parse error: no such column: foo_fts
  * from metadata inner join t_fts as foo_fts WHERE foo_fts MATCH 'foo';
                                      error here ---^

("no such column" -> I guess that's a fallback mechanism at work?)

Creating a table-alias but not using it behaves as if there was no alias at all:

select *
from metadata
inner join t_fts as foo_fts on metadata.id = foo_fts.id
WHERE t_fts MATCH 'foo';

1|one|1|foo bar baz
3|three|3|foo something something

Bolting the match onto the table using … join t_fts('foo') as foo_fts … works too, even when joining the table multiple times (the query plan seems to behave as expected in that case).

Accessing the column name with it's alias works as expected:

select *
from metadata
inner join t_fts as foo_fts on metadata.id = foo_fts.id
WHERE foo_fts.text MATCH 'foo';

1|one|1|foo bar baz
3|three|3|foo something something

But that only works for one column.

What I'd like to know:

So question time for things I want to understand:

• Is not supporting table-aliases intentional?
• How would working around this by submitting identical MATCH operators for each column scale?

I'm trying to decide if it's worth trying to continue my current approach and treat the MATCH like any other SQL statement that just happens to filter in multiple column simultaneously and dislikes OR and NOT or if I should treat full text search as separate special case.

Thanks for the CTE hint I'll give that a try.

Don't join with the fts tables, rather, join with their search result set.

Seems inefficient at first, but I assume you mean that in the context of a non-materialized CTE or similar construct. I'd have quite some cases where other factors (in other tables) would severely limit which entries in the full text index would have to be considered.

(Correct me if I'm wrong on that)

The full text index cannot take those other constraints into account. The way it works is that each matched term has a list of ids attached to it, those lists are processed, ORed or ANDed or a mix of both based on the fts query, then when done a list of filtered ids is returned, only then can other constraints be considered.