Re: [HACKERS] We are not following the spec for HAVING without GROUP
On Fri, 11 Mar 2005 10:37:13 +1300, Mark Kirkwood [EMAIL PROTECTED] wrote: Firebird 1.5.1 FreeBSD 5.3 [correct results] Interbase 6.0: SQL create table tab (col integer); SQL select 1 from tab having 1=0; SQL select 1 from tab having 1=1; 0---:-) SQL insert into tab values(1); SQL insert into tab values(2); SQL select 1 from tab having 1=0; SQL select 1 from tab having 1=1; 1 SQL Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
Just a quick test on the DBMS I have available at work. IBM Informix Dynamic Server Version 10.00.TC1TL Error: The column (id) must be in the GROUP BY list. Oracle 9.2.0.11 Returns the same records as if where would be used. MSSQL Express 2005 Beta February TP Error: Column 'tab.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. MySQL 5.0.2 Returns the same records as if where would be used. Sybase 12.5 Returns the same records as if where would be used. Firebird 1.5.2 Error: GDS Exception. 335544569. Dynamic SQL Error SQL error code = -104 No message for code 335544824 found. null Best regards, Michael Wimmer ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] [HACKERS] We are not following the spec for HAVING without GROUP BY
On Thu, Mar 10, 2005 at 12:44:50PM -0500, Tom Lane wrote: Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows from the 4 selects --- that is, the contents of tab make no difference at all. Sybase ASE version 12.5.2 returns 0, 0, 0, and 1 rows. A plain select 1 from tab returns zero rows when tab is empty. -- Mark Shewmaker [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] [HACKERS] We are not following the spec for HAVING without GROUP BY
Here is your Sql run in a DB2 database. connect to phoenix Database Connection Information Database server= DB2/LINUX 8.1.5 SQL authorization ID = GILL Local database alias = PHOENIX create table tab (col integer) DB2I The SQL command completed successfully. select 1 from tab having 1=0 1 --- 0 record(s) selected. select 1 from tab having 1=1 1 --- 1 1 record(s) selected. insert into tab values(1) DB2I The SQL command completed successfully. insert into tab values(2) DB2I The SQL command completed successfully. select 1 from tab having 1=0 1 --- 0 record(s) selected. select 1 from tab having 1=1 1 --- 1 1 record(s) selected. Hope that helps. -Jgill -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane Sent: Thursday, March 10, 2005 11:45 AM To: pgsql-hackers@postgresql.org; pgsql-bugs@postgresql.org Subject: Re: [BUGS] [HACKERS] We are not following the spec for HAVING without GROUP BY I wrote: This is quite clear that the output of a HAVING clause is a grouped table no matter whether the query uses GROUP BY or aggregates or not. What that means is that neither the HAVING clause nor the targetlist can use any ungrouped columns except within aggregate calls; that is, select col from tab having 21 is in fact illegal per SQL spec, because col isn't a grouping column (there are no grouping columns in this query). Actually, it's even more than that: a query with HAVING and no GROUP BY should always return 1 row (if the HAVING succeeds) or 0 rows (if not). If there are no aggregates, the entire from/where clause can be thrown away, because it can have no impact on the result! Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows from the 4 selects --- that is, the contents of tab make no difference at all. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely copying our mistake...) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] We are not following the spec for HAVING without GROUP
Results from Oracle 9.2.0.3 (9.2 patch 2) === Connected to Oracle9i Release 9.2.0.3.0 Connected as system SQL SQL DROP TABLE TESTTAB; DROP TABLE TESTTAB ORA-00942: table or view does not exist SQL create table TESTtab (col integer); Table created SQL select 1 as col from TESTtab having 1=0; COL -- SQL select 1 as col from TESTtab having 1=1; COL -- SQL insert into TESTtab values(1); 1 row inserted SQL insert into TESTtab values(2); 1 row inserted SQL select 1 as col from TESTtab having 1=0; COL -- SQL select 1 as col from TESTtab having 1=1; COL -- 1 1 SQL DROP TABLE TESTTAB; Table dropped SQL === ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] We are not following the spec for HAVING without GROUP
On Thu, 10 Mar 2005, Tom Lane wrote: Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows from the 4 selects --- that is, the contents of tab make no difference at all. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely copying our mistake...) DB2 (version 8.1) gives 0, 1, 0, 1. - Heikki ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
What that means is that neither the HAVING clause nor the targetlist can use any ungrouped columns except within aggregate calls; that is, select col from tab having 21 Informix: select tabname from systables having 2 1; 294: The column (tabname) must be in the GROUP BY list. select tabname from systables group by 1 having 2 1; all rows returned select tabname from systables group by 1 having 1 2; no rows found Andreas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
I wrote: This is quite clear that the output of a HAVING clause is a grouped table no matter whether the query uses GROUP BY or aggregates or not. What that means is that neither the HAVING clause nor the targetlist can use any ungrouped columns except within aggregate calls; that is, select col from tab having 21 is in fact illegal per SQL spec, because col isn't a grouping column (there are no grouping columns in this query). Actually, it's even more than that: a query with HAVING and no GROUP BY should always return 1 row (if the HAVING succeeds) or 0 rows (if not). If there are no aggregates, the entire from/where clause can be thrown away, because it can have no impact on the result! Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows from the 4 selects --- that is, the contents of tab make no difference at all. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely copying our mistake...) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] We are not following the spec for HAVING without GROUP
Tom Lane wrote: Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows from the 4 selects --- that is, the contents of tab make no difference at all. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely copying our mistake...) regards, tom lane From SQL server 2000 with a service pack, I get: zero rows from the first query (having 1=0); one row, col value 1, from second query (having 1=1); ...run inserts... zero rows from the third query (having 1=0); one row, col value 1, from forth query (having 1=1); K. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
On Oracle 9.2 you get 0, 0, 0, and 2 rows. --Barry SQL create table tab (col integer); Table created. SQL select 1 from tab having 1=0; no rows selected SQL select 1 from tab having 1=1; no rows selected SQL insert into tab values (1); 1 row created. SQL insert into tab values (2); 1 row created. SQL select 1 from tab having 1=0; no rows selected SQL select 1 from tab having 1=1; 1 -- 1 1 SQL exit Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production JServer Release 9.2.0.1.0 - Production -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Thursday, March 10, 2005 9:45 AM To: pgsql-hackers@postgresql.org; pgsql-bugs@postgresql.org Subject: Re: [HACKERS] We are not following the spec for HAVING without GROUP BY I wrote: This is quite clear that the output of a HAVING clause is a grouped table no matter whether the query uses GROUP BY or aggregates or not. What that means is that neither the HAVING clause nor the targetlist can use any ungrouped columns except within aggregate calls; that is, select col from tab having 21 is in fact illegal per SQL spec, because col isn't a grouping column (there are no grouping columns in this query). Actually, it's even more than that: a query with HAVING and no GROUP BY should always return 1 row (if the HAVING succeeds) or 0 rows (if not). If there are no aggregates, the entire from/where clause can be thrown away, because it can have no impact on the result! Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows from the 4 selects --- that is, the contents of tab make no difference at all. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely copying our mistake...) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
On Thu, Mar 10, 2005 at 12:44:50PM -0500, Tom Lane wrote: Would those of you with access to other DBMSes try this: DB2/LINUX 8.1.6 create table tab (col integer); select 1 from tab having 1=0; 1 --- 0 record(s) selected. select 1 from tab having 1=1; 1 --- 1 1 record(s) selected. insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; 1 --- 0 record(s) selected. select 1 from tab having 1=1; 1 --- 1 1 record(s) selected. -joh ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
On Thu, 10 Mar 2005 12:44:50 -0500, Tom Lane [EMAIL PROTECTED] wrote: Would those of you with access to other DBMSes try this: On informix 9.21.UC4 create table tab (col integer); select 1 from tab having 1=0; returns no rows select 1 from tab having 1=1; returns no rows insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; returns no rows select 1 from tab having 1=1; returns 2 rows regards, Jaime Casanova ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] We are not following the spec for HAVING without GROUP
Tom Lane wrote: Would those of you with access to other DBMSes try this: create table tab (col integer); select 1 from tab having 1=0; select 1 from tab having 1=1; insert into tab values(1); insert into tab values(2); select 1 from tab having 1=0; select 1 from tab having 1=1; I claim that a SQL-conformant database will return 0, 1, 0, and 1 rows from the 4 selects --- that is, the contents of tab make no difference at all. (MySQL returns 0, 0, 0, and 2 rows, so they are definitely copying our mistake...) Firebird 1.5.1 FreeBSD 5.3 Database: test SQL drop table tab; SQL create table tab (col integer); SQL select 1 from tab having 1=0; SQL select 1 from tab having 1=1; 1 SQL insert into tab values(1); SQL insert into tab values(2); SQL select 1 from tab having 1=0; SQL select 1 from tab having 1=1; 1 SQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
Barry Lind [EMAIL PROTECTED] writes: On Oracle 9.2 you get 0, 0, 0, and 2 rows. Really!? Well, we always knew they were a bit standards-challenged ;-). I have more faith in DB2 being an accurate implementation of the spec. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] We are not following the spec for HAVING without GROUP BY
I wrote in reference to bug#1528: What the spec actually says, or at least implies, is that a HAVING clause is to be evaluated only once per group --- where the group is the whole table if there's no GROUP BY clause. In fact, reading the spec more closely, it is clear that the presence of HAVING turns the query into a grouped query even if there is no GROUP BY. I quote SQL92 7.8 again: 7.8 having clause Function Specify a grouped table derived by the elimination of groups from ^^^ the result of the previously specified clause that do not meet the search condition. ... 1) Let T be the result of the preceding from clause, where clause, or group by clause. If that clause is not a group by clause, then T consists of a single group and does not have a grouping column. 2) The search condition is applied to each group of T. The result of the having clause is a grouped table of those groups of T ^^ for which the result of the search condition is true. This is quite clear that the output of a HAVING clause is a grouped table no matter whether the query uses GROUP BY or aggregates or not. What that means is that neither the HAVING clause nor the targetlist can use any ungrouped columns except within aggregate calls; that is, select col from tab having 21 is in fact illegal per SQL spec, because col isn't a grouping column (there are no grouping columns in this query). What we are currently doing with this construct is pretending that it means select col from tab where 21 but it does not mean that according to the spec. As I look into this, I find that several warty special cases in the parser and planner arise from our misunderstanding of this point, and could be eliminated if we enforced the spec's interpretation. In particular this whole business of moving HAVING into WHERE is wrong and should go away. Comments? Can anyone confirm whether DB2 or other databases allow ungrouped column references with HAVING? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
Tom Lane wrote: What that means is that neither the HAVING clause nor the targetlist can use any ungrouped columns except within aggregate calls; that is, select col from tab having 21 is in fact illegal per SQL spec, because col isn't a grouping column (there are no grouping columns in this query). [...] Comments? Can anyone confirm whether DB2 or other databases allow ungrouped column references with HAVING? Oracle does not allow such references. It issues ORA-00979: not a GROUP BY expression when you try to hand it such a reference. MS SQL Server does not allow such references either, yielding columnname is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.. Can't comment about DB2. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
Tom Lane [EMAIL PROTECTED] writes: In particular this whole business of moving HAVING into WHERE is wrong and should go away. It sort of seems like select aggregate(col) from tab with no GROUP BY clause is a bit of a special case. The consistent thing to do would be to return no records. It's only due to the special case that SQL returns a single record for this case. It seems like this special case is the only way to expose this difference between a WHERE clause and a HAVING clause with an aggregate-free expression. It seems like all that's needed is a simple flag on the Aggregate node that says whether to output a single record if there are no input records or to output no records. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] We are not following the spec for HAVING without GROUP
Comments? Can anyone confirm whether DB2 or other databases allow ungrouped column references with HAVING? Oracle does not allow such references. It issues ORA-00979: not a GROUP BY expression when you try to hand it such a reference. MS SQL Server does not allow such references either, yielding columnname is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.. Can't comment about DB2. MySQL allows it: mysql create table tab (col integer); Query OK, 0 rows affected (0.01 sec) mysql select col from tab having 2 1; Empty set (0.00 sec) mysql insert into tab values (1); Query OK, 1 row affected (0.00 sec) mysql select col from tab having 2 1; +--+ | col | +--+ |1 | +--+ 1 row in set (0.00 sec) Of course, that's not saying much! Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] We are not following the spec for HAVING without GROUP
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Comments? Can anyone confirm whether DB2 or other databases allow ungrouped column references with HAVING? MySQL allows it: A slightly tighter experiment shows that they treat HAVING like WHERE in this case: mysql create table tab(col int); Query OK, 0 rows affected (0.00 sec) mysql insert into tab values(1); Query OK, 1 row affected (0.00 sec) mysql insert into tab values(2); Query OK, 1 row affected (0.01 sec) mysql select col from tab having col1; +--+ | col | +--+ |2 | +--+ 1 row in set (0.00 sec) I think it's fairly likely that they copied our misinterpretation ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] We are not following the spec for HAVING without GROUP BY
Greg Stark [EMAIL PROTECTED] writes: It sort of seems like select aggregate(col) from tab with no GROUP BY clause is a bit of a special case. The consistent thing to do would be to return no records. I don't think so. SQL99 defines this stuff in a way that might make you feel better: it says that the presence of either HAVING or any aggregate functions in the target list implies GROUP BY (), which is the case that they identify as grand total in the group by clause syntax. Basically this legitimizes the concept of turning the whole input table into one group, which is what's really going on here. We get this right in the case where it's driven by the appearance of aggregate functions, but not when it's just driven by HAVING. It seems like all that's needed is a simple flag on the Aggregate node that says whether to output a single record if there are no input records or to output no records. The implementation problem is that there *is* no aggregate node if there are no aggregates. The definitional problem is that we are allowing cases that are illegal per spec and are going to be difficult to continue to support if we handle all the spec-required cases properly. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] We are not following the spec for HAVING without GROUP
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Wednesday, March 09, 2005 8:45 PM To: Christopher Kings-Lynne Cc: Kevin Brown; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] We are not following the spec for HAVING without GROUP Christopher Kings-Lynne [EMAIL PROTECTED] writes: Comments? Can anyone confirm whether DB2 or other databases allow ungrouped column references with HAVING? DB2 does not like it. This runs and returns data: SELECT INFO5FILES.APAMT.DEBAMT FROM INFO5FILES.APAMT SELECT statement run complete. This fails to prepare: SELECT INFO5FILES.APAMT.DEBAMT FROM INFO5FILES.APAMT having 1 2 Column DEBAMT or function specified in SELECT list not valid. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
