PostgreSQL version: 8.4.0 / 8.3.1
Operating system: Red Hat 4.1.1-52
Description: Aggregate function with subquery in 8.3 and 8.4.
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)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
The Session 4.2.7. Aggregate Expressions in 8.3 document at
http://www.postgresql.org/docs/8.3/static/sql-expressions.html states "The
last form invokes the aggregate once for each input row regardless of null
or non-null values." I am wondering if the result I saw from 8.4.0 is a bug
fix for 8.3.1?
================= 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
