When is SELECT ... FROM with no table-or-subquery valid.

(1) By Richard PArkins (rparkins) on 2021-03-21 22:12:28 [source]

The syntax diagram for SELECT allows a FROM not followed by any table-or-subquery. The Determination of input data (FROM clause processing) section of the description says "If a FROM clause is specified, the data on which a simple SELECT query operates comes from the one or more tables or subqueries". What happens if there are zero tables or subqueries as apparently permitted by the syntax diagram?

(2.1) By Larry Brasfield (larrybr) on 2021-03-22 01:39:38 edited from 2.0 in reply to 1 [link] [source]

You get a syntax error from the parser during prepare. This is what the grammar in src/parse.y shows should happen. The railroad diagram is incorrect and will be fixed in conjunction with the next patch release.

(3) By Gunter Hick (gunter_hick) on 2021-03-23 10:12:54 in reply to 2.1 [link] [source]

I sincerely hope NOT. It has been perfectly legal (right up to release 3.24) to do 

SELECT 1,datetime(...),trim(...);

Since there is no FROM clause, the SELECT-list is limited to expressions that do not reference any table.

BTW: Oracle does not allow an empty FROM clause, so they provide an empty table named DUAL that exists solely to fulfill an otherwise empty FROM clause.

(4) By ddevienne on 2021-03-23 10:23:08 in reply to 3 [link] [source]

BTW: Oracle [...] provide an empty table named DUAL that
exists solely to fulfill an otherwise empty FROM clause

Actually, it's not empty but has a single row and column :)

(5) By John Dennis (jdennis) on 2021-03-23 10:29:42 in reply to 3 [link] [source]

I sincerely hope NOT. It has been perfectly legal (right up to release 3.24) to do

SELECT 1,datetime(...),trim(...);

There is no FROM there. This thread is about SELECT ... FROM without a table or subquery.

(6) By Larry Brasfield (larrybr) on 2021-03-23 12:47:34 in reply to 3 [link] [source]

Gunter, The syntax diagram as documented for SELECT still supports those foreshortened constructs. As Mr. Dennis says, the issue Mr. Parkins brought up is that the railroad chart allowed a FROM followed by nothing. That has always been, and still is, syntactically invalid.

(7) By David Raymond (dvdraymond) on 2021-03-23 13:28:02 in reply to 1 [link] [source]

And remember that at the top of the SELECT page it states:

Note that there are paths through the syntax diagrams that are not allowed in practice. Some examples:

A VALUES clause can be the first element in a compound SELECT that uses a WITH clause, but a simple SELECT that consists of just a VALUES clause cannot be preceded by a WITH clause.
The WITH clause must occur on the first SELECT of a compound SELECT. It cannot follow a compound-operator.

These and other similar syntax restrictions are described in the text.