Thread: BUG #1528: Rows returned that should be excluded by WHERE clause
The following bug has been logged online:
Bug reference: 1528
Logged by: Peter Wright
Email address: pete@flooble.net
PostgreSQL version: 7.4.7, 8.0.1
Operating system: Debian Linux (unstable)
Description: Rows returned that should be excluded by WHERE clause
Details:
Hopefully this example SQL will paste correctly -
I think this demonstrates the problem much better than I could explain in
words. The bug is shown in the two
SELECT queries with a WHERE clause. Very bizarre.
The same bug crops up on 7.4.6, 7.4.7 and 8.0.1.
pete@serf [07/Mar 6:28:50] pts/10 !19 ~ $ createdb test1
CREATE DATABASE
pete@serf [07/Mar 6:28:59] pts/10 !20 ~ $ psql test1
Welcome to psql 7.4.7, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test1=# create table t1 ( a smallint primary key, b smallint ) ;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for
table "t1"
CREATE TABLE
test1=# create table t2 ( a smallint primary key, b smallint ) ;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for
table "t2"
CREATE TABLE
test1=# insert into t1 values (1, 1);
INSERT 118413888 1
test1=# insert into t1 values (2, 2);
INSERT 118413889 1
test1=# insert into t2 values (1, 4);
INSERT 118413890 1
test1=# insert into t2 values (2, 8);
INSERT 118413891 1
test1=# select id, min(b) from ( select 1 as id, max(b) as b from t1 union
select 2 as id, max(b) from t2 ) as q1 group by id ;
id | min
----+-----
1 | 2
2 | 8
(2 rows)
test1=# create view qry1 as select id, min(b) from ( select 1 as id, max(b)
as b from t1 union select 2 as id, max(b) from t2 ) as q1 group by id ;
CREATE VIEW
test1=# select * from qry1 where id = 1;
id | min
----+-----
1 | 2
2 |
(2 rows)
test1=# select * from qry1 where id = 2;
id | min
----+-----
1 |
2 | 8
(2 rows)
test1=# select * from qry1;
id | min
----+-----
1 | 2
2 | 8
(2 rows)
test1=#
"Peter Wright" <pete@flooble.net> writes:
> Description: Rows returned that should be excluded by WHERE clause
Interesting point. The view and union don't seem to be the issue;
I think the problem can be expressed as
regression=# select 2 as id, max(b) from t2 having 2 = 1;
id | max
----+-----
2 |
(1 row)
Now, if this were a WHERE clause, I think the answer would be right:
regression=# select 2 as id, max(b) from t2 where 2 = 1;
id | max
----+-----
2 |
(1 row)
but since it's HAVING I think this is probably wrong. Looking at the
EXPLAIN output
regression=# explain select 2 as id, max(b) from t2 having 2 = 1;
QUERY PLAN
----------------------------------------------------------------
Aggregate (cost=3.68..3.68 rows=1 width=2)
-> Result (cost=0.00..3.14 rows=214 width=2)
One-Time Filter: false
-> Seq Scan on t2 (cost=0.00..3.14 rows=214 width=2)
(4 rows)
the issue is clearly that the known-false HAVING clause is pushed down
inside the aggregation, as though it were WHERE. The existing code
pushes down HAVING to WHERE if the clause contains no aggregates, but
evidently this is too simplistic. What are the correct conditions for
pushing down HAVING clauses to WHERE?
regards, tom lane
Just an interesting side note here, this behavior is identical to DB2. I am=
not sure if that makes it correct or not, but here is an example.
[gill@c2n2 gill]$ db2 "select 2 as id, max(apn3) from phoenix.client where =
2 =3D1"
ID 2
----------- ------
2 -
1 record(s) selected.
-jgill
-----Original Message-----
From: pgsql-bugs-owner@postgresql.org
[mailto:pgsql-bugs-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Tuesday, March 08, 2005 2:07 AM
To: Peter Wright
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #1528: Rows returned that should be excluded by
WHERE clause=20
"Peter Wright" <pete@flooble.net> writes:
> Description: Rows returned that should be excluded by WHERE clause
Interesting point. The view and union don't seem to be the issue;
I think the problem can be expressed as
regression=3D# select 2 as id, max(b) from t2 having 2 =3D 1;
id | max=20
----+-----
2 |=20=20=20=20
(1 row)
Now, if this were a WHERE clause, I think the answer would be right:
regression=3D# select 2 as id, max(b) from t2 where 2 =3D 1;
id | max=20
----+-----
2 |=20=20=20=20
(1 row)
but since it's HAVING I think this is probably wrong. Looking at the
EXPLAIN output=20
regression=3D# explain select 2 as id, max(b) from t2 having 2 =3D 1;
QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
----------------------------------------------------------------
Aggregate (cost=3D3.68..3.68 rows=3D1 width=3D2)
-> Result (cost=3D0.00..3.14 rows=3D214 width=3D2)
One-Time Filter: false
-> Seq Scan on t2 (cost=3D0.00..3.14 rows=3D214 width=3D2)
(4 rows)
the issue is clearly that the known-false HAVING clause is pushed down
inside the aggregation, as though it were WHERE. The existing code
pushes down HAVING to WHERE if the clause contains no aggregates, but
evidently this is too simplistic. What are the correct conditions for
pushing down HAVING clauses to WHERE?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
"Gill, Jerry T." <JTGill@west.com> writes:
> Just an interesting side note here, this behavior is identical to DB2. I am not sure if that makes it correct or not,
buthere is an example.
> [gill@c2n2 gill]$ db2 "select 2 as id, max(apn3) from phoenix.client where 2 =1"
> ID 2
> ----------- ------
> 2 -
> 1 record(s) selected.
In the WHERE case I think there's no question that the above is correct:
WHERE is defined to filter rows before application of aggregates, so
zero rows arrive at the MAX aggregate, and that means it produces a
NULL.
But HAVING is supposed to filter after aggregation, so I think probably
there should be no row out in that case.
What does DB2 do when you say HAVING 2 = 1?
regards, tom lane
Sorry Tom, I missed a sentence in you previous email. My understanding of t=
he having clause is that the row should be filtered. Here is the same examp=
le with the having clause in DB2.
[gill@c2n2 gill]$ db2 "select 2 as id, max(apn3) from phoenix.client having=
2 =3D1"
ID 2
----------- ------
0 record(s) selected.
[gill@c2n2 gill]$ db2 "select 2 as id, max(apn3) from phoenix.client where =
2 =3D1 having 2 =3D 1"
ID 2
----------- ------
0 record(s) selected.
-jgill
-----Original Message-----
From: pgsql-bugs-owner@postgresql.org
[mailto:pgsql-bugs-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Tuesday, March 08, 2005 11:15 AM
To: Gill, Jerry T.
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #1528: Rows returned that should be excluded by
WHERE clause=20
"Gill, Jerry T." <JTGill@west.com> writes:
> Just an interesting side note here, this behavior is identical to DB2. I =
am not sure if that makes it correct or not, but here is an example.
> [gill@c2n2 gill]$ db2 "select 2 as id, max(apn3) from phoenix.client wher=
e 2 =3D1"
> ID 2
> ----------- ------
> 2 -
> 1 record(s) selected.
In the WHERE case I think there's no question that the above is correct:
WHERE is defined to filter rows before application of aggregates, so
zero rows arrive at the MAX aggregate, and that means it produces a
NULL.
But HAVING is supposed to filter after aggregation, so I think probably
there should be no row out in that case.
What does DB2 do when you say HAVING 2 =3D 1?
regards, tom lane
---------------------------(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
I wrote:
> I think the problem can be expressed as
> regression=# select 2 as id, max(b) from t2 having 2 = 1;
> id | max
> ----+-----
> 2 |
> (1 row)
> the issue is clearly that the known-false HAVING clause is pushed down
> inside the aggregation, as though it were WHERE. The existing code
> pushes down HAVING to WHERE if the clause contains no aggregates, but
> evidently this is too simplistic. What are the correct conditions for
> pushing down HAVING clauses to WHERE?
After reading the spec a little, I think that we have oversimplified our
handling of aggregate-free HAVING clauses. If you look in planner.c
you'll find that such a clause is converted into a WHERE clause, but
this is not what the spec says to do, and you can tell the difference
in cases like the above.
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. The group is
to be discarded if the HAVING clause doesn't return true. SQL92 7.8:
1) Let T be the result of the preceding <from clause>, <where clause>, or <group by clause>. If that
clauseis 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
groupedtable of those groups of T for which the result of the <search condition> is true.
So it's clear that what the above case should return is a grouped table
having no groups ... ie, no rows out. What we are actually returning is
one group containing no rows, which is visibly different because of the
presence of the aggregate function in the SELECT list.
There are really four cases to think about, depending on whether the
query has GROUP BY and on whether it has any aggregates outside the
HAVING clause:
1. No GROUP BY, no aggregates
Per spec, the HAVING clause should be evaluated once and either we
return the whole input or none of it. Since there are no grouped
columns and (by assumption) no aggregates in the HAVING clause, the
HAVING clause must in fact be variable-free, ie, it's a pseudoconstant
clause. (Only pseudoconstant, because it might contain outer-level
variables or volatile functions.) I think the correct implementation
in this case is to generate a gating Result node with the HAVING clause
as a one-time filter, so that we don't evaluate any of the query if the
HAVING is false. The current code gets this almost right: it will make
a variable-free WHERE clause into a Result gating condition *if it
contains no volatile functions*. So it's wrong for the volatile
function case but right otherwise.
2. GROUP BY, no aggregates
In this case the HAVING clause might contain references to the grouping
columns. It is legitimate to push down the HAVING to become WHERE,
but *only* if it doesn't contain any volatile functions --- otherwise it
might be possible to tell that the HAVING clause was executed more than
once. It would be useful to push down the HAVING if, for example, it
could become an indexscan qualifier. However if the HAVING condition
is expensive to compute (eg it contains a subselect) we'd probably be
better off not to push it into WHERE, but to arrange to evaluate it
only once per group. Right now the executor cannot support testing
such a condition, but I think it would be easy enough to improve nodeGroup.c
to allow testing a qual condition for each group.
3. No GROUP BY, has aggregates
As in case 1, the HAVING clause must be variable-free, so the best
implementation would be to put it into a gating Result node. It would
be correct to treat it the same way as we do for a HAVING clause
containing aggregates (ie, attach it as a qual condition to the Agg plan
node) --- but that would mean computing and throwing away the aggregate
result when the HAVING fails, when we could skip computing it altogether.
4. GROUP BY and has aggregates
This is really the same as case 2: we could push down the HAVING
condition if it contains no volatile functions, but unless it is
cheap to evaluate we are probably best off to attach it as a qual
condition to the Agg node, ie, evaluate it only once per group.
The only difference is that we don't need an executor fix to support
this, since Agg does quals already.
So, aside from the originally reported bug, there are two other problems
in this logic: it isn't ensuring that volatile functions will be
evaluated only once per group, and it isn't considering evaluation
cost in deciding whether a clause that could be converted to WHERE
should be or not.
I haven't yet tried to make a patch that fixes all of these things.
It'll likely come out complex enough that we don't want to back-patch
it into 8.0 or before. If so, I'll try to make a simpler variant that
fixes the semantic bugs but doesn't try to be smart about evaluation
cost.
Comments?
regards, tom lane
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
resultof 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
clauseis 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
groupedtable 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 2>1
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 2>1
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
On Wed, 2005-03-09 at 21:21 -0500, Tom Lane wrote: > Comments? Can anyone confirm whether DB2 or other databases allow > ungrouped column references with HAVING? In Sybase: 1> select 2 as id, max(myfield) from mytable where 2=1 2> go id ----------- ---------- 2 NULL (1 row affected) 1> select 2 as id, max(myfield) from mytable having 2=1 2> go id ----------- ---------- (0 rows affected) -- Mark Shewmaker mark@primefactor.com
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 2>1
> 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
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 MS SQL Server 2000 returns 0, 1, 0 and 1 rows correctly. Cheers, Gary.
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 Not that this means much, but I'll mention it for the sake of completeness: SQLite 3.0.8 disallows all of the above SELECT statements: sqlite> create table tab (col integer); sqlite> select 1 from tab having 1=0; SQL error: a GROUP BY clause is required before HAVING sqlite> select 1 from tab having 1=1; SQL error: a GROUP BY clause is required before HAVING sqlite> insert into tab values(1); sqlite> insert into tab values(2); sqlite> select 1 from tab having 1=0; SQL error: a GROUP BY clause is required before HAVING sqlite> select 1 from tab having 1=1; SQL error: a GROUP BY clause is required before HAVING -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Thu, 10 Mar 2005 12:44:50 -0500, Tom Lane <tgl@sss.pgh.pa.us> 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
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 mark@primefactor.com
"Peter Wright" <pete@flooble.net> writes:
> I think this demonstrates the problem much better than I could explain in
> words. The bug is shown in the two
> SELECT queries with a WHERE clause. Very bizarre.
I've applied a patch that corrects this problem in CVS HEAD, but since
it changes the behavior of HAVING in a nontrivial way, I'm inclined to
think that we should not backpatch it into existing release branches.
regards, tom lane
>>select 1 from tab having 1=1;
>>
>
> returns 2 rows
I'm curious whats in those two rows... {{1} {1}} ?
Hi Tom, others, First I must say that I appreciate the effort you've invested already into finding the best "correct" solution. It's very encouraging. :) I think I understand your analysis of the problem being that HAVING is erroneously optimised/simplified to WHERE in some cases - and so the initial "bug" I reported is technically the correct behaviour(?). ....Okay, maybe I'm not completely sure I've understood you correctly. :) On 08/03 03:07:13, Tom Lane wrote: > "Peter Wright" <pete@flooble.net> writes: > > Description: Rows returned that should be excluded by WHERE clause > > Interesting point. The view and union don't seem to be the issue; I think the view _is_ the issue (well, at least for me and my (limited) understanding of how things should work :)). See below. > I think the problem can be expressed as [ snip ] > Now, if this were a WHERE clause, I think the answer would be right: > > regression=# select 2 as id, max(b) from t2 where 2 = 1; > id | max > ----+----- > 2 | > (1 row) > > but since it's HAVING I think this is probably wrong. [ ... ] On 08/03 12:14:35, Tom Lane wrote: > "Gill, Jerry T." <JTGill@west.com> writes: > > Just an interesting side note here, this behavior is identical to > > DB2. I am not sure if that makes it correct or not, but here is an > > example. > > [gill@c2n2 gill]$ db2 "select 2 as id, max(apn3) from phoenix.client where 2 =1" > > > ID 2 > > ----------- ------ > > 2 - > > > 1 record(s) selected. > > In the WHERE case I think there's no question that the above is > correct: WHERE is defined to filter rows before application of > aggregates, so zero rows arrive at the MAX aggregate, and that means > it produces a NULL. Now this _does_ make sense - but in the case of a view (or sub-select), how can it be correct that a WHERE _outside_ the view can affect the behaviour of that view? At the very least I'd call that grossly anti-intuitive: ---------------------------------------------------------------------- test1=# create table tab1 ( a integer, b integer ); CREATE TABLE test1=# insert into tab1 values ( 1, 1 ); INSERT 118421921 1 test1=# insert into tab1 values ( 2, 2 ); INSERT 118421922 1 test1=# create view qry1 as select 2 as id, max(b) as b from tab1; CREATE VIEW test1=# create table tab2 ( id integer, b integer ); CREATE TABLE test1=# insert into tab2 values ( 2, 2); INSERT 118421931 1 test1=# select * from tab2; id | b ----+--- 2 | 2 (1 row) test1=# select * from qry1; id | b ----+--- 2 | 2 (1 row) test1=# select * from qry1 where id = 1; id | b ----+--- 2 | (1 row) test1=# select * from tab2 where id = 1; id | b ----+--- (0 rows) test1=# ---------------------------------------------------------------------- You say, "WHERE is defined to filter rows before application of aggregates", but I'd _think_ that should be interpreted to apply only to aggregates in the _current_ query (ie. not in sub-queries). In my example just above, I'd _expect_ the view should be fully evaluated and the results (of that view) treated as though it were just another table. Perhaps I'm just showing my limited experience with database theory here :-), but if you can explain why it makes sense that WHERE must be applied before aggregation in _all_ subqueries, that'd be good *wry grin*. > But HAVING is supposed to filter after aggregation, so I think > probably there should be no row out in that case. I have no problem with this. > regards, tom lane Thanks again for your efforts with this issue, Tom. Currently I'm working around it by adding an extra kludge-clause, effectively "AND b is not null", but it'd be preferable to have Postgres do the Right Thing(tm), whatever that might be.... :) Pete. -- http://akira.apana.org.au/~pete/ And anyway, we know that 2 + 2 = 5, for very large values of two...
Peter Wright <pete@flooble.net> writes:
> [various stuff snipped]
> You say, "WHERE is defined to filter rows before application of
> aggregates", but I'd _think_ that should be interpreted to apply only
> to aggregates in the _current_ query (ie. not in sub-queries).
Well, the subtext of this discussion is that Postgres, like every other
DBMS on the planet, will aggressively push query restrictions down as
far as it's allowed to by the semantic rules. Consider
CREATE VIEW v1 AS SELECT c1, sum(c2) FROM tab GROUP BY c1;
SELECT * FROM v1 WHERE c1 = 42;
A naive implementation would compute every row of the view v1
(ie, every sum of c2 over each existing value of c1) and then
throw away each result except the one for c1 = 42. This is
obviously not acceptable. So we have to transform the query to
SELECT c1, sum(c2) FROM tab WHERE c1 = 42 GROUP BY c1;
which gives the execution engine a fair shot at doing something
reasonable, ie, pulling only the rows of tab that have c1 = 42,
which we could expect would be done with the aid of an index on c1.
(The GROUP BY step is actually redundant in this formulation,
but the cost of doing it is probably negligible; certainly it's
not the major problem compared to computing all the useless
sums over c1 groups other than 42.)
Point here is that to get reasonably efficient behavior we have to be
able to push the WHERE c1 = 42 condition down inside the view's
GROUP BY clause; and therefore we have to understand the exact
semantic conditions under which that is an allowable transformation.
Your bug report is essentially pointing out an error in our rules
for thinking that this transformation is allowable.
regards, tom lane
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
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>
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. -johnnnnnnnn
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.
On Fri, 11 Mar 2005 10:37:13 +1300, Mark Kirkwood
<markir@coretech.co.nz> 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>
ServusManfred