SQLite Forum

'values' with no args : has to be syntax error?
Login

'values' with no args : has to be syntax error?

(1) By midijohnny on 2021-10-29 12:20:49 [link] [source]

All things being equal - is there a reason why the no-arg version of 'values' isn't allowed?

This is just an academic question : no serious issue here - just one of consistency. (I'm writing a guide - and for that it is useful to show minimal examples).

The following will give exactly one row:

values(1)

Which is the exact equivalent of:

select 1

However; the next example is syntax error:

values()

But arguably should be the exact equivalent of:

select 1 where 0

(2) By Simon Slavin (slavin) on 2021-10-30 06:09:04 in reply to 1 [link] [source]

Think of it as the equivalent of

SELECT FROM MyTable;

It's not allowed in the SQL spec, which insists that at least one value must be returned. So I can see the argument for the same thing when supplying values for insertion.

(3) By midijohnny on 2021-11-03 22:11:09 in reply to 2 [link] [source]

Good explanation - makes sense. Thank you.

(4) By midijohnny on 2021-11-03 22:14:59 in reply to 2 [source]

Additionally: I was wrong about the exact equivalence of the "SELECT 1 WHERE 1=0". In that - this is actually doing the equivalent of this:

with a("1") as (values(1)) select * from a;

That is: it names the column - not as 'column1' (as 'values' does) but by the value of the row.

(5) By anonymous on 2021-11-05 18:20:02 in reply to 1 [link] [source]

I disagree. I think that maybe it ought to be allowed, but if so, it should not be the equivalent of select 1 where 0. Instead, what I think would make sense, should be:

VALUES; -- zero rows, zero columns
VALUES(); -- one row, zero columns
SELECT 1 WHERE 0; -- zero rows, one column
INSERT INTO X() VALUES(); -- same as INSERT INTO X DEFAULT VALUES;

(6) By Stephan Beal (stephan) on 2021-11-05 20:07:42 in reply to 5 [link] [source]

VALUES(); -- one row, zero columns

Those semantics are incompatible with how generic APIs which accept arbitrary SQL work: they prepare their SQL then ask if it has any result columns. If it does, it's a SELECT-ike statement (and therefore may generate output), otherwise it's not. Adding select-like statements which have no result columns would break all software which relies on that property.