Re: [sqlite] Row values with IN

[Clemens Ladisch]

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.

 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row values with IN

[Luuk]

On 23-4-2019 18:08, Simon Slavin wrote:

On 23 Apr 2019, at 4:14pm, Charles Leifer  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row values with IN

[Simon Slavin]

On 23 Apr 2019, at 4:14pm, Charles Leifer  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  wrote:

> On 23 Apr 2019, at 3:52pm, Charles Leifer  wrote:
>
> > My question, though, is why is the VALUES bit needed for the WHERE
> clause in this case?
>
> 
>
> " 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row values with IN

[Simon Slavin]

On 23 Apr 2019, at 3:52pm, Charles Leifer  wrote:

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



" 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  wrote:

> On 23 Apr 2019, at 3:42pm, Charles Leifer  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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row values with IN

[Simon Slavin]

On 23 Apr 2019, at 3:42pm, Charles Leifer  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  wrote:

> On 23 Apr 2019, at 3:11pm, Charles Leifer  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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row values with IN

[Simon Slavin]

On 23 Apr 2019, at 3:11pm, Charles Leifer  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users