'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:
Which is the exact equivalent of:
However; the next example is syntax error:
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 [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 [link] [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.