Lists: | pgsql-bugs |
---|
From: | Martin Pitt <martin(at)piware(dot)de> |
---|---|
To: | PostgreSQL Bugs <pgsql-bugs(at)postgresql(dot)org> |
Cc: | ubuntubug(at)acrasis(dot)net |
Subject: | pg_dump produces invalid SQL for "group by cast(null as numeric)" |
Date: | 2008-01-05 20:43:57 |
Message-ID: | 20080105204357.GC6062@piware.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Martin Pitt <martin(at)piware(dot)de> |
Cc: | PostgreSQL Bugs <pgsql-bugs(at)postgresql(dot)org>, ubuntubug(at)acrasis(dot)net |
Subject: | Re: pg_dump produces invalid SQL for "group by cast(null as numeric)" |
Date: | 2008-01-05 22:13:04 |
Message-ID: | 4536.1199571184@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Martin Pitt <martin(at)piware(dot)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
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Martin Pitt <martin(at)piware(dot)de> |
Cc: | PostgreSQL Bugs <pgsql-bugs(at)postgresql(dot)org>, ubuntubug(at)acrasis(dot)net |
Subject: | Re: pg_dump produces invalid SQL for "group by cast(null as numeric)" |
Date: | 2008-03-06 21:29:51 |
Message-ID: | 200803062129.m26LTpD20981@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +