'values' with no args : has to be syntax error?
(1) By midijohnny on 2021-10-29 12:20:49
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]
Think of it as the equivalent of <code> SELECT FROM MyTable;</code> 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]
Good explanation - makes sense. Thank you.
(4) By midijohnny on 2021-11-03 22:14:59 in reply to 2 [link]
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]
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: ```sql 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]
> `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.