[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Table alias for FTS5
From:       Richard Hipp <drh () sqlite ! org>
Date:       2016-09-23 11:49:32
Message-ID: CALwJ=MwVnWEfS_A4JD5WRTxDKuxWmr4eX6gWX7746uX+sfREyQ () mail ! gmail ! com
[Download RAW message or body]

On 9/23/16, Alexei Gilchrist <te100@runbox.com> wrote:
> Hi,
>
> I've just come across an oddity using FTS5. I have an FTS table that
> may get joined multiple times in a query so each time I give it an
> alias. The problem is that the match operator generates a "no such
> column" error when I use the table alias.
>
> e.g.
>
> SELECT DISTINCT n.uid FROM nodes n
> JOIN nodefts n_fts ON n.uid = n_fts.uid
> WHERE n_fts MATCH "A*"

The LHS of the MATCH operator is really a column name.  For syntactic
convenience, there is a HIDDEN column name in each FTS5 table that is
the same as the table name.  But the AS operator only change the table
name, not the underlying column name.

Correct would be:

SELECT distinct .nuid
   FROM nodes AS n JOIN nodefts AS n_fts ON n.uid=n_fts.uid
 WHERE n_fts.nodefts MATCH 'A*';

Note also that strings in SQL are quoted with single quotes rather
than double quotes.


>
> gives "SQLError: no such column: n_fts"
>
> Giving the actual table name works but can't be done with multiple
> joins:
>
> SELECT DISTINCT n.uid FROM nodes n
> JOIN nodefts n_fts ON n.uid = n_fts.uid
> WHERE nodefts MATCH "A*"
>
> Giving the table name as a column name works but seems inconsistent in
> syntax:
>
> SELECT DISTINCT n.uid FROM nodes n
> JOIN nodefts n_fts ON n.uid = n_fts.uid
> WHERE n_fts.nodefts MATCH "A*"
>
> Have I misinterpreted the syntax or is the last form what is intended?
>
> Cheers,
>
> Alexei
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[prev in list] [next in list] [prev in thread] [next in thread]