Row values with IN

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Row values with IN

Charles Leifer
Let's say I have a simple table with a composite primary key (key, value)
and an extra field:

CREATE TABLE IF NOT EXISTS "bu" (
  "key" TEXT NOT NULL,
  "value" INTEGER NOT NULL,
  "extra" INTEGER NOT NULL,
  PRIMARY KEY ("key", "value"));

I'll put 3 rows in the table:

INSERT INTO "bu" ("key", "value", "extra")
VALUES ('k1', 1, 1), ('k2', 2, 2), ('k3', 3, 3);

Now -- I want to do a "bulk update":

UPDATE "bu" SET "extra" = CASE ("key", "value")
  WHEN ('k1', 1) THEN 100
  WHEN ('k2', 2) THEN -200
  WHEN ('k3', 3) THEN 30
END
WHERE ("key", "value") IN (('k1', 1), ('k2', 2), ('k3', 3));

I run into a "row value misused" error triggered by the WHERE clause, which
is comparing the (key, value) tuple to a set of row-values.

I've found that I can get this to work by using explicitly using VALUES:

UPDATE "bu" SET "extra" = CASE ("key", "value") ... END
WHERE ("key", "value") IN (VALUES ('k1', 1), ('k2', 2), ('k3', 3));

My question, though, is why is the VALUES bit needed for the WHERE clause
in this case? Reading the docs on row values, it seemed to me that
comparing two row values should work fine:

Two row values of the same size can be compared using operators <, <=, >,
>=, =, <>, IS, IS NOT, IN, NOT IN, BETWEEN, or CASE.

Is there a reason I need to specify VALUES instead of simply passing in a
set of row tuples? It seems a little bit inconsistent to me. Thanks!

Charlie
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Row values with IN

Simon Slavin-3
On 23 Apr 2019, at 3:11pm, Charles Leifer <[hidden email]> wrote:

> UPDATE "bu" SET "extra" = CASE ("key", "value")
>  WHEN ('k1', 1) THEN 100
>  WHEN ('k2', 2) THEN -200
>  WHEN ('k3', 3) THEN 30
> END
> WHERE ("key", "value") IN (('k1', 1), ('k2', 2), ('k3', 3));

Your WHERE clause it not needed, and SQLite is not clever enough to realise it can be used to find specific rows in the table.  (At least that's what I think, I may be wrong.).

I'm not certain that SQLite understands your use of the bracketed terms.  This

UPDATE "bu" SET "extra" = CASE "key"||"value"
 WHEN 'k1'||1 THEN 100
 WHEN 'k2'||2 THEN -200
 WHEN 'k3'||3 THEN 30
END;

might work because || is the 'append' operator.  Of course this assumes that there are no ambiguities like 'k451' which might be key 'k4' or k45.  It might be better to use something like "key"|'x'|"value" .

But in fact this would be far faster

BEGIN;
UPDATE "bu" SET "extra" = 100 WHERE "key"='k1' AND "value"=1;
UPDATE "bu" SET "extra" = -200 WHERE "key"='k2' AND "value"=2;
UPDATE "bu" SET "extra" = 30 WHERE "key"='k3' AND "value"=3;
END;

because your WHERE clause matches a UNIQUE key, so SQLite could go straight to the correct row rather than iterating through the entire table.  So instead of one command that has to read every row of the table you have three commands which each execute almost instantly.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Row values with IN

Charles Leifer
Thanks Simon -- this is just a minimal example. The WHERE clause is needed
because, unless I would provide a CASE WHEN for every single (key, value),
then the UPDATE would set the "extra" value of any unmatched key, value to
NULL.

Please remember this is just an example. I'd like to generalize this kind
of approach. It works fine for scalar primary keys, but is having issues
when the key is composed of multiple columns.

On Tue, Apr 23, 2019 at 9:35 AM Simon Slavin <[hidden email]> wrote:

> On 23 Apr 2019, at 3:11pm, Charles Leifer <[hidden email]> wrote:
>
> > UPDATE "bu" SET "extra" = CASE ("key", "value")
> >  WHEN ('k1', 1) THEN 100
> >  WHEN ('k2', 2) THEN -200
> >  WHEN ('k3', 3) THEN 30
> > END
> > WHERE ("key", "value") IN (('k1', 1), ('k2', 2), ('k3', 3));
>
> Your WHERE clause it not needed, and SQLite is not clever enough to
> realise it can be used to find specific rows in the table.  (At least
> that's what I think, I may be wrong.).
>
> I'm not certain that SQLite understands your use of the bracketed terms.
> This
>
> UPDATE "bu" SET "extra" = CASE "key"||"value"
>  WHEN 'k1'||1 THEN 100
>  WHEN 'k2'||2 THEN -200
>  WHEN 'k3'||3 THEN 30
> END;
>
> might work because || is the 'append' operator.  Of course this assumes
> that there are no ambiguities like 'k451' which might be key 'k4' or k45.
> It might be better to use something like "key"|'x'|"value" .
>
> But in fact this would be far faster
>
> BEGIN;
> UPDATE "bu" SET "extra" = 100 WHERE "key"='k1' AND "value"=1;
> UPDATE "bu" SET "extra" = -200 WHERE "key"='k2' AND "value"=2;
> UPDATE "bu" SET "extra" = 30 WHERE "key"='k3' AND "value"=3;
> END;
>
> because your WHERE clause matches a UNIQUE key, so SQLite could go
> straight to the correct row rather than iterating through the entire
> table.  So instead of one command that has to read every row of the table
> you have three commands which each execute almost instantly.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Row values with IN

Simon Slavin-3
On 23 Apr 2019, at 3:42pm, Charles Leifer <[hidden email]> wrote:

> Thanks Simon -- this is just a minimal example. The WHERE clause is needed because, unless I would provide a CASE WHEN for every single (key, value), then the UPDATE would set the "extra" value of any unmatched key, value to NULL.

You can use ELSE:

UPDATE "bu" SET "extra" = CASE "key"||"value"
    WHEN 'k1'||1 THEN 100
    WHEN 'k2'||2 THEN -200
    WHEN 'k3'||3 THEN 30
    ELSE "extra"
END;

But I still think that using multiple UPDATE statements will still be far faster.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Row values with IN

Charles Leifer
This is a question about SQL language support and the handling of
row-values, and I thought my question was quite clear. I appreciate your
offers to help, but they do not have anything to do with the question I am
asking.

As I wrote:

My question, though, is why is the VALUES bit needed for the WHERE clause
in this case? Reading the docs on row values, it seemed to me that
comparing two row values should work fine:

Two row values of the same size can be compared using operators <, <=, >,
>=, =, <>, IS, IS NOT, IN, NOT IN, BETWEEN, or CASE.

Is there a reason I need to specify VALUES instead of simply passing in a
set of row tuples? It seems a little bit inconsistent to me. Thanks!

On Tue, Apr 23, 2019 at 9:49 AM Simon Slavin <[hidden email]> wrote:

> On 23 Apr 2019, at 3:42pm, Charles Leifer <[hidden email]> wrote:
>
> > Thanks Simon -- this is just a minimal example. The WHERE clause is
> needed because, unless I would provide a CASE WHEN for every single (key,
> value), then the UPDATE would set the "extra" value of any unmatched key,
> value to NULL.
>
> You can use ELSE:
>
> UPDATE "bu" SET "extra" = CASE "key"||"value"
>     WHEN 'k1'||1 THEN 100
>     WHEN 'k2'||2 THEN -200
>     WHEN 'k3'||3 THEN 30
>     ELSE "extra"
> END;
>
> But I still think that using multiple UPDATE statements will still be far
> faster.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Row values with IN

Simon Slavin-3
On 23 Apr 2019, at 3:52pm, Charles Leifer <[hidden email]> wrote:

> My question, though, is why is the VALUES bit needed for the WHERE clause in this case?

<https://sqlite.org/lang_expr.html#in_op>

" If the right operand of an IN or NOT IN operator is a list of values, each of those values must be scalars and the left expression must also be a scalar. "

So to use lists, you can't use pairs of values, you must use scalars.  Further up the same paragraph

" When the right operand of an IN or NOT IN operator is a subquery, the subquery must have the same number of columns as there are columns in the row value of the left operand. The subquery on the right of an IN or NOT IN operator must be a scalar subquery if the left expression is not a row value expression. "

So what seems to be happening is that your use of VALUES is turning the comparator into a subquery rather than a list of values.  And SQLite is testing to see whether your search term satisfies the subquery.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Row values with IN

Charles Leifer
Thanks, makes sense. I think what confused me is that in the doc for row
values, it states:

Two row values of the same size can be compared using operators <, <=, >,
>=, =, <>, IS, IS NOT, IN, NOT IN, BETWEEN, or CASE.

The row value doc example shows how to use BETWEEN, for instance:

SELECT * FROM info WHERE (year,month,day) BETWEEN (2015,9,12) AND
(2016,9,12);

Based on the above doc, I would have also thought it would be possible to
write something like:

SELECT * FROM info WHERE (year,month,day) IN ((2019, 1, 1), (2019, 2, 1));

Perhaps the row-value doc could clarify the behavior of IN with row values?

SELECT * FROM info WHERE (year,month,day) IN (VALUES (2019, 1, 1), (2019,
2, 1));

On Tue, Apr 23, 2019 at 10:03 AM Simon Slavin <[hidden email]> wrote:

> On 23 Apr 2019, at 3:52pm, Charles Leifer <[hidden email]> wrote:
>
> > My question, though, is why is the VALUES bit needed for the WHERE
> clause in this case?
>
> <https://sqlite.org/lang_expr.html#in_op>
>
> " If the right operand of an IN or NOT IN operator is a list of values,
> each of those values must be scalars and the left expression must also be a
> scalar. "
>
> So to use lists, you can't use pairs of values, you must use scalars.
> Further up the same paragraph
>
> " When the right operand of an IN or NOT IN operator is a subquery, the
> subquery must have the same number of columns as there are columns in the
> row value of the left operand. The subquery on the right of an IN or NOT IN
> operator must be a scalar subquery if the left expression is not a row
> value expression. "
>
> So what seems to be happening is that your use of VALUES is turning the
> comparator into a subquery rather than a list of values.  And SQLite is
> testing to see whether your search term satisfies the subquery.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Row values with IN

Simon Slavin-3
On 23 Apr 2019, at 4:14pm, Charles Leifer <[hidden email]> wrote:

> SELECT * FROM info WHERE (year,month,day) IN ((2019, 1, 1), (2019, 2, 1));
>
> Perhaps the row-value doc could clarify the behavior of IN with row values?

This

> ((2019, 1, 1), (2019, 2, 1))

is not a list of scalers, it's a list of lists of scalers.  So the documentation does explain it.  However, I think that the documentation doesn't sufficiently explain the problem.  Or maybe the use of IN or lists deserves its own page rather than being buried in the extremely long page on expressions.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Row values with IN

Luuk

On 23-4-2019 18:08, Simon Slavin wrote:
> On 23 Apr 2019, at 4:14pm, Charles Leifer <[hidden email]> wrote:
>
>> SELECT * FROM info WHERE (year,month,day) IN ((2019, 1, 1), (2019, 2, 1));
>>
>> Perhaps the row-value doc could clarify the behavior of IN with row values?
> This
>
>> ((2019, 1, 1), (2019, 2, 1))

This works:

SELECT * FROM info WHERE (year,month,day) = (2019, 1, 1) OR (year,month,day)
= (2019, 2, 1);

but it's not using IN, and more characters to type

> is not a list of scalers, it's a list of lists of scalers.  So the documentation does explain it.  However, I think that the documentation doesn't sufficiently explain the problem.  Or maybe the use of IN or lists deserves its own page rather than being buried in the extremely long page on expressions.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Row values with IN

Clemens Ladisch
In reply to this post by Simon Slavin-3
Simon Slavin wrote:
> I think that the documentation doesn't sufficiently explain the problem.
> Or maybe the use of IN or lists deserves its own page rather than being
> buried in the extremely long page on expressions.

<https://www.sqlite.org/rowvalue.html> says:
| For a row-value IN operator, the left-hand side … can be either
| a parenthesized list of values or a subquery with multiple columns. But
| the right-hand side … must be a subquery expression.

I'd guess this restriction makes parsing easier.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users