Skip site navigation (1) Skip section navigation (2)

BUG #5024: Aggregate function FROM subquery

From: "Sheng Y(dot) Cheng" <scheng(at)adconion(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5024: Aggregate function FROM subquery
Date: 2009-08-31 22:22:49
Message-ID: 200908312222.n7VMMnQ2081717@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5024
Logged by:          Sheng Y. Cheng
Email address:      scheng(at)adconion(dot)com
PostgreSQL version: 8.4.0 / 8.3.1
Operating system:   Red Hat 4.1.1-52
Description:        Aggregate function FROM subquery
Details: 

Here are some facts and questions about the aggregate function with subquery
in 8.3 and 8.4. 

================= Question 1. ==================
I though the following query would give me the same results in 8.4.0 and
8.3.1. 

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

BEGIN;
SELECT version();
CREATE TEMPORARY TABLE t1 (f1 text ) on commit drop ;
CREATE TEMPORARY TABLE t2 (f1 text ) on commit drop ;
INSERT INTO t1 (f1) VALUES ('aaa');
INSERT INTO t1 (f1) VALUES ('bbb');
INSERT INTO t1 (f1) VALUES ('ccc');
INSERT INTO t2 (f1) VALUES ('bbb');

SELECT t1.f1, COUNT(ts.*) FROM
  t1
LEFT JOIN
  (SELECT
    CASE WHEN f1 = '111'
      THEN '111'
    ELSE
      f1
    END
   FROM t2) AS ts
ON
 t1.f1 = ts.f1
GROUP BY
 t1.f1;

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

However, In 8.3.1 I got the following. 


vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

BEGIN
                                                  version                   
                              
----------------------------------------------------------------------------
-------------------------------
 PostgreSQL 8.3.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.1.1 20070105 (Red Hat 4.1.1-52)
(1 row)

CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
 f1  | count 
-----+-------
 aaa |     0
 bbb |     1
 ccc |     0
(3 rows)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Whereas, in 8.4.0 I got the following. 

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

BEGIN
                                                      version               
                                      
----------------------------------------------------------------------------
---------------------------------------
 PostgreSQL 8.4.0 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.1.1 20070105 (Red Hat 4.1.1-52), 64-bit
(1 row)

CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
 f1  | count 
-----+-------
 aaa |     1
 bbb |     1
 ccc |     1
(3 rows)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

I am wondering if this is a bug fix in 8.4.0? 


================= Question 2. ==================
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

BEGIN;
SELECT version();
CREATE TEMPORARY TABLE t1 (f1 text ) on commit drop ;
CREATE TEMPORARY TABLE t2 (f1 text ) on commit drop ;
INSERT INTO t1 (f1) VALUES ('aaa');
INSERT INTO t1 (f1) VALUES ('bbb');
INSERT INTO t1 (f1) VALUES ('ccc');
INSERT INTO t2 (f1) VALUES ('bbb');

SELECT t1.f1, COUNT(ts.*) FROM
  t1
LEFT JOIN
  (SELECT
     f1
   FROM t2) AS ts
ON
  t1.f1 = ts.f1
GROUP BY
  t1.f1;

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

I though the result of the above query would be the following.  

 f1  | count 
-----+-------
 aaa |     0
 bbb |     1
 ccc |     0

however, I got the following in both 8.4.0 and 8.3.1.

Result from 8.3.1. 
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

BEGIN
                                                  version                   
                              
----------------------------------------------------------------------------
-------------------------------
 PostgreSQL 8.3.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.1.1 20070105 (Red Hat 4.1.1-52)
(1 row)

CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
 f1  | count 
-----+-------
 aaa |     1
 bbb |     1
 ccc |     1
(3 rows)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Result from 8.4.0. 

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv

BEGIN
                                                      version               
                                      
----------------------------------------------------------------------------
---------------------------------------
 PostgreSQL 8.4.0 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.1.1 20070105 (Red Hat 4.1.1-52), 64-bit
(1 row)

CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
 f1  | count 
-----+-------
 aaa |     1
 bbb |     1
 ccc |     1
(3 rows)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Is this how Postgres works for aggregate function?


Thank you,

Sheng

pgsql-bugs by date

Next:From: Sheng Y. ChengDate: 2009-08-31 22:53:31
Subject: BUG #5025: Aggregate function with subquery in 8.3 and 8.4.
Previous:From: Kevin GrittnerDate: 2009-08-31 22:20:43
Subject: Re: BUG #5023: pg_relation_size() is not case sensitive

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group