pg_dump produces invalid SQL for "group by cast(null as numeric)"

Martin Pitt <martin@piware.de> writes:

create view bar as select count(*) from foo group by cast(null as numeric);

3. pg_dump the database to a text file. The file contains

'CREATE VIEW bar AS
SELECT count(*) AS count FROM foo GROUP BY 2;'

Actually, this seems to be provoking an Assert failure, if you use
an assert-enabled backend:

$ pg_dump d1
pg_dump: SQL command failed
pg_dump: Error message from server: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_dump: The command was: SELECT pg_catalog.pg_get_viewdef('40965'::pg_catalog.oid) as viewdef

Server log shows

TRAP: FailedAssertion("!(!tle->resjunk)", File: "ruleutils.c", Line: 2267)
LOG: server process (PID 4507) was terminated by signal 6

So it's a backend problem not pg_dump's fault.

regards, tom lane

FYI, this was fixed in 8.3.0; not sure you got the report of the fix.

---------------------------------------------------------------------------

Martin Pitt wrote:
-- Start of PGP signed section.

Hi PostgreSQL developers,

in [1], a user reported a failure of pg_dump:

-------- snip ----------
1. Create an empty database.

2. Connect to the database and create these views:

create view foo as select 3;
create view bar as select count(*) from foo group by cast(null as numeric);

3. pg_dump the database to a text file. The file contains

'CREATE VIEW bar AS
SELECT count(*) AS count FROM foo GROUP BY 2;'

4. Drop view bar from the database.

5. Run the CREATE VIEW bar..; statement from the text file.

6. The statement fails with

'ERROR: GROUP BY position 2 is not in select list'
-------- snip ----------

I verified that this is still an issue on 8.3 CVS head.

However, I admit that I'm not sure why "group by cast(null as
numeric)" is useful. However, it actually works in the DB and fails in
pg_dump, so either it is valid and should be handled by pg_dump, or it
is invalid and should not be accepted in the first place.

Thank you!

Martin

[1] https://bugs.launchpad.net/bugs/177382

--
Martin Pitt http://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org

-- End of PGP section, PGP failed!

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

+ If your life is a hard drive, Christ can be your backup. +