Обсуждение: pg_dump produces invalid SQL for "group by cast(null as numeric)"
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 --=20 Martin Pitt http://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org
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. +