row value misuse

(1) By beetlejuice (coleifer) on 2021-07-05 18:08:59 [link]

I was hoping someone could help me understand the rules around using row values and the VALUES keyword. I am having a little trouble understanding what is and isn't allowed.

Starting with a simple table containing a key, value:

    create table kv (key text, val integer);
    insert into kv (key, val) values ('k1', 1), ('k2', 2), ('k3', 3);

The following works with Sqlite:

    select * from kv
    where (key, val) = ('k1', 1);

The following seems to work in Postgres, but Sqlite is reporting "row value misused":

    select * from kv
    where (key, val) in (('k1', 1), ('k3', 3));

The following works in both Postgres and Sqlite:

    select * from kv
    where (key, val) in (values ('k1', 1), ('k3', 3));

--------

Another thing I have seen done is to join on a list of values, which works well in Postgres, but Sqlite reports a syntax error near "(":

    select * 
    from kv 
    inner join (values ('k1', 1), ('k3', 3)) as bar("k", "v")
      on kv.key = bar.k and kv.val = bar.v;

The following, unsurprising, also works in Postgres but not Sqlite:

    select *
    from kv, (values ('k1', 1), ('k3', 3)) as bar(k, v)
    where (kv.key = bar.k AND kv.val = bar.v);

Moving it into a CTE works in both Postgres and Sqlite:

    with bar(k, v) as (values ('k1', 1), ('k3', 3))
    select * from kv
    inner join bar 
      on (kv.key = bar.k and kv.val = bar.v);

---------

My questions are:

* When should one use plain row-values versus VALUES?
* Why does (key, val) = ('k1', 1) work but (key, val) IN (('k1', 1)...) not?
* Are there ways of constructing the above type of query which would be better or more idiomatic with Sqlite?

Thank you in advance for the help.

(2) By Harald Hanche-Olsen (hanche) on 2021-07-06 13:31:03 in reply to 1

I am not an expert or authority, but here is my understanding of the issue, as far as it goes. If I am wildly off target, someone will hopefully correct me.

- A comma-separated list of expressions in parenthesis usually signifies a row value, or tuple.
- A `VALUES` expression is a special [select statement](https://sqlite.org/syntax/select-stmt.html), and as such produces a *sequence* of rows. Even if that sequence contains precisely one row, it is something different from a row value.
- A row value can contain any of the [datatypes](https://sqlite.org/datatype3.html) allowed by SQLite. Note that a row value is not among these; hence `((1,2,),(3,4))` is illegal.

> The following seems to work in Postgres, but Sqlite is reporting "row value misused":
> 
> ```
> select * from kv
> where (key, val) in (('k1', 1), ('k3', 3));
> ```

That is not so clear-cut. What's on the right side of `WHERE … IN (…)` is not an expression but [a parenthesised list of expressions](https://sqlite.org/syntax/expr.html), so arguably, a list of row values *could* have been legitimate according to these rules. Perhaps it would be useful; but OTOH, the alternative to a list of expressions is a select statement, and `VALUES` is of course a select statement, so that will always work.

> Another thing I have seen done is to join on a list of values, which works well in Postgres, but Sqlite reports a syntax error near "(":
> 
> ```
> select * 
> from kv 
> inner join (values ('k1', 1), ('k3', 3)) as bar("k", "v")
>   on kv.key = bar.k and kv.val = bar.v;
> ```

I am not sure why that does not work in SQLite. The syntax diagrams seem to allow it.

(3) By mzm2021 on 2021-07-06 14:13:41 in reply to 2 [link]

SQLite does not accept temporary column names in a table/view alias.

That's why it is reporting a syntax error near the left parenthesis of:

> … as bar("k", "v")

PostgresSQL docs describe aliases with column names in:

[](https://www.postgresql.org/docs/13/queries-table-expressions.html#id-1.5.6.6.5.7.3)

But the SQLite syntax diagram of the FROM clause does not allow such a syntax:

[](https://sqlite.org/syntax/table-or-subquery.html)

(4) By Harald Hanche-Olsen (hanche) on 2021-07-06 15:27:55 in reply to 3 [link]

Ah, that explains it.

I do wish for more context in SQLite error messages sometimes. I mean, just adding a few characters, say  “`near "("k","`” would be a lot more helpful than “`near "("`”.