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 <slav...@bigfraud.org> wrote: > On 23 Apr 2019, at 3:52pm, Charles Leifer <colei...@gmail.com> 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 > 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
