pgsql: Allow empty target list in SELECT.

On 15 December 2013 02:23, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

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)

--
Thom

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

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

On 22 October 2014 21:57, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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)

I'm using the one I built along with everything else:

$ which psql
/home/thom/Development/psql/bin/psql

--
Thom

On 22 October 2014 22:06, Thom Brown <thom@linux.com> wrote:

On 22 October 2014 21:57, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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)

I'm using the one I built along with everything else:

$ which psql
/home/thom/Development/psql/bin/psql

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.

--
Thom

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

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

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. +

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

On 25 March 2015 at 01:04, Bruce Momjian <bruce@momjian.us> wrote:

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.

Thanks.

--
Thom