Thread: 7.4 aggregate OR TRUE yields strange results

Dear Gurus,

============================================================================                       POSTGRESQL BUG
REPORTTEMPLATE 
============================================================================


Your name  : Gabor Szucs
Your email address : surrano@mailbox.hu


============================================================================
System Configuration
--------------------- Architecture (example: Intel Pentium)   : Intel Pentium 2
 Operating System (example: Linux 2.0.26 ELF)  : Debian "Woody" 2.4.18 Elf
 PostgreSQL version (example: PostgreSQL-7.4):   PostgreSQL-7.4.1
 Compiler used (example:  gcc 2.95.2)  : gcc 2.95.4


============================================================================
Please enter a FULL description of your problem:
------------------------------------------------

We compiled the source with one modification: max function params needed to
be raised to 64.
Dumped the db from 7.3.3 and fed it to psql 7.4.1. (dunno if this may be
relevant).

SELECT count(*)<0 OR TRUE FROM mytable WHERE condition

dumps the whole tuples meeting the condition. Same with other aggregates
such as

SELECT max(az)<5 OR true FROM mytable

I'd be honoured to get some info if this has been reported (haven't found
anything in latest weeks of BUGS) and what's the current status or
explanation of this behaviour.


============================================================================
Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

Below is the whole relevant dump of a simple example.
Note that the columns in the last query _do_not_have_names_!
I re-checked with \x.

TIA,
G.
%----------------------- cut here -----------------------%
\end


[local]:tir=# \d tetelcsoport                               Table "public.tetelcsoport"Column |       Type        |
                    Modifiers 
--------+-------------------+-----------------------------------------------
---------------az     | integer           | not null default
nextval('public.tetelcsoport_az_seq'::text)nev    | character varying |
Indexes:   "tetelcsoport_pkey" primary key, btree (az)

[local]:tir=# select * from tetelcsoport;az |    nev
----+----------- 1 | göngyöleg 2 | szerszám
(2 rows)

[local]:tir=# select (max(az)<5) from tetelcsoport;?column?
----------t
(1 row)

[local]:tir=# select (max(az)<5) or true from tetelcsoport;  |
---+-----------1 | göngyöleg2 | szerszám
(2 rows)

[local]:tir=# select (max(az)<5) or true from tetelcsoport where az=1;  |
---+-----------1 | göngyöleg
(1 row)

"SZŰCS Gábor" <surrano@mailbox.hu> writes:
> SELECT count(*)<0 OR TRUE FROM mytable WHERE condition
> dumps the whole tuples meeting the condition.

Wow, that's bizarre.  The "x OR TRUE" expression will get simplified to
just TRUE, so that COUNT isn't really there at all, but that shouldn't
change the query semantics.  And it seems to work correctly in 7.3.*
and before.  Looking into it ...
        regards, tom lane

"SZŰCS Gábor" <surrano@mailbox.hu> writes:
> SELECT count(*)<0 OR TRUE FROM mytable WHERE condition
> dumps the whole tuples meeting the condition.

Sigh, I'm an idiot.  I introduced this bug more than a year ago.
(Bit surprising that it wasn't caught already...)  Patch against 7.4.*
is attached.
        regards, tom lane

Index: planner.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/optimizer/plan/planner.c,v
retrieving revision 1.161
diff -c -r1.161 planner.c
*** planner.c    25 Sep 2003 06:58:00 -0000    1.161
--- planner.c    13 Feb 2004 22:22:26 -0000
***************
*** 701,719 ****          /*          * Will need actual number of aggregates for estimating costs.
-          * Also, it's possible that optimization has eliminated all
-          * aggregates, and we may as well check for that here.          *          * Note: we do not attempt to
detectduplicate aggregates here; a          * somewhat-overestimated count is okay for our present purposes.
*/        if (parse->hasAggs) 
-         {             numAggs = count_agg_clause((Node *) tlist) +
count_agg_clause(parse->havingQual);
-             if (numAggs == 0)
-                 parse->hasAggs = false;
-         }          /*          * Figure out whether we need a sorted result from query_planner.
--- 701,718 ----          /*          * Will need actual number of aggregates for estimating costs.          *
*Note: we do not attempt to detect duplicate aggregates here; a          * somewhat-overestimated count is okay for our
presentpurposes. 
+          *
+          * Note: think not that we can turn off hasAggs if we find no aggs.
+          * It is possible for constant-expression simplification to remove
+          * all explicit references to aggs, but we still have to follow the
+          * aggregate semantics (eg, producing only one output row).          */         if (parse->hasAggs)
 numAggs = count_agg_clause((Node *) tlist) +                 count_agg_clause(parse->havingQual);          /*
*Figure out whether we need a sorted result from query_planner.