Thread: pgsql: Allow empty target list in SELECT.

Allow empty target list in SELECT.

This fixes a problem noted as a followup to bug #8648: if a query has a
semantically-empty target list, e.g. SELECT * FROM zero_column_table,
ruleutils.c will dump it as a syntactically-empty target list, which was
not allowed.  There doesn't seem to be any reliable way to fix this by
hacking ruleutils (note in particular that the originally zero-column table
might since have had columns added to it); and even if we had such a fix,
it would do nothing for existing dump files that might contain bad syntax.
The best bet seems to be to relax the syntactic restriction.

Also, add parse-analysis errors for SELECT DISTINCT with no columns (after
*-expansion) and RETURNING with no columns.  These cases previously
produced unexpected behavior because the parsed Query looked like it had
no DISTINCT or RETURNING clause, respectively.  If anyone ever offers
a plausible use-case for this, we could work a bit harder on making the
situation distinguishable.

Arguably this is a bug fix that should be back-patched, but I'm worried
that there may be client apps or PLs that expect "SELECT ;" to throw a
syntax error.  The issue doesn't seem important enough to risk changing
behavior in minor releases.

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/1b4f7f93b4693858cb983af3cd557f6097dab67b

Modified Files
--------------
doc/src/sgml/ref/select.sgml         |   28 +++++++++++++++++++++-------
src/backend/parser/analyze.c         |   13 +++++++++++++
src/backend/parser/gram.y            |    8 ++++++--
src/backend/parser/parse_clause.c    |   19 +++++++++++++++++++
src/test/regress/expected/errors.out |   23 ++++++++---------------
src/test/regress/sql/errors.sql      |   15 ++++++---------
6 files changed, 73 insertions(+), 33 deletions(-)

Thom Brown <thom@linux.com> writes:
> On 15 December 2013 02:23, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Allow empty target list in SELECT.

> This commit introduces another bug:

> # create table colours (id serial, name text, visible boolean);
> CREATE TABLE

> # insert into colours (name, visible) values
> ('blue',true),('yellow',true),('ultraviolet',false),('green',true),('infrared',false);
> INSERT 0 5

> # select into colours2 from colours;
> SELECT 5

> # select * from colours2;
> (No rows)

That's just a pre-existing issue in psql that it's not terribly smart
about displaying zero-column rows.  But what psql are you using?
I see

regression=# select * from colours2;
--
(5 rows)


            regards, tom lane

Thom Brown <thom@linux.com> writes:
> I can see the same output as you if I get it to ignore my .psqlrc file:

> # select * from colours2;
> --
> (5 rows)

> Turns out it's "\x auto" causing it.

Ah.  Seems like a bug in the expanded-mode printout logic then.
But, in any case, it's been like that a very long time.  You
could do this at least as far back as 8.4:

regression=# create table foo();
CREATE TABLE
regression=# insert into foo default values;
INSERT 0 1
regression=# insert into foo default values;
INSERT 0 1
regression=# select * from foo;
--
(2 rows)

regression=# \x on
Expanded display is on.
regression=# select * from foo;
(No rows)

            regards, tom lane

On Wed, Oct 22, 2014 at 04:19:10PM -0400, Tom Lane wrote:
> Thom Brown <thom@linux.com> writes:
> > I can see the same output as you if I get it to ignore my .psqlrc file:
>
> > # select * from colours2;
> > --
> > (5 rows)
>
> > Turns out it's "\x auto" causing it.
>
> Ah.  Seems like a bug in the expanded-mode printout logic then.
> But, in any case, it's been like that a very long time.  You
> could do this at least as far back as 8.4:
>
> regression=# create table foo();
> CREATE TABLE
> regression=# insert into foo default values;
> INSERT 0 1
> regression=# insert into foo default values;
> INSERT 0 1
> regression=# select * from foo;
> --
> (2 rows)
>
> regression=# \x on
> Expanded display is on.
> regression=# select * from foo;
> (No rows)

I have developed a patch to fix this, e.g.:

    test=> select * from foo;
    --
    (2 rows)

    test=> \x
    Expanded display is on.
    test=> select * from foo;
    (2 rows)

I used the same footer function every other output format was using.
Patch attached.

I also found that the normal non-expanded output was forcing the use of
a pager for these tests, which seemed odd considering there were no
rows. The problem is that the variable col_count is an unsigned integer,
and there were a few places we were subtracting one from it.  When
col_count was zero, subtracting one returned a very larger positive
number, and triggered the pager.  That is fixed in this patch as well.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +

On Fri, Mar 20, 2015 at 08:25:50AM -0400, Bruce Momjian wrote:
> > regression=# create table foo();
> > CREATE TABLE
> > regression=# insert into foo default values;
> > INSERT 0 1
> > regression=# insert into foo default values;
> > INSERT 0 1
> > regression=# select * from foo;
> > --
> > (2 rows)
> >
> > regression=# \x on
> > Expanded display is on.
> > regression=# select * from foo;
> > (No rows)
>
> I have developed a patch to fix this, e.g.:
>
>     test=> select * from foo;
>     --
>     (2 rows)
>
>     test=> \x
>     Expanded display is on.
>     test=> select * from foo;
>     (2 rows)
>
> I used the same footer function every other output format was using.
> Patch attached.

Patch applied.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +