summaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/xc_groupby.out140
-rw-r--r--src/test/regress/expected/xc_having.out514
2 files changed, 280 insertions, 374 deletions
diff --git a/src/test/regress/expected/xc_groupby.out b/src/test/regress/expected/xc_groupby.out
index 08f8da53c4..36a2697cd1 100644
--- a/src/test/regress/expected/xc_groupby.out
+++ b/src/test/regress/expected/xc_groupby.out
@@ -19,14 +19,14 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_gro
(3 rows)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_groupby_tab1 group by val2;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1.03..1.05 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_groupby_tab1.val))), pg_catalog.avg((avg(xc_groupby_tab1.val))), ((pg_catalog.sum((sum(xc_groupby_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_groupby_tab1.val2
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2
-> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2
(6 rows)
-- joins and group by
@@ -127,9 +127,9 @@ select val + val2, val, val2 from xc_groupby_tab1 group by val, val2;
----------+-----+------
7 | 4 | 3
4 | 3 | 1
- 4 | 1 | 3
4 | 2 | 2
3 | 2 | 1
+ 4 | 1 | 3
2 | 1 | 1
8 | 6 | 2
9 | 6 | 3
@@ -198,14 +198,14 @@ select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2;
(3 rows)
explain verbose select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2;
- QUERY PLAN
-----------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1.02..1.04 rows=1 width=8)
- Output: (((count(*) + sum(val)))::numeric + avg(val)), val2
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
+ Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_groupby_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_groupby_tab1.val)))), xc_groupby_tab1.val2
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2
-> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2
(6 rows)
-- group by with expressions in group by clause
@@ -284,12 +284,12 @@ select avg(a) from xc_groupby_def group by a;
6.0000000000000000
8.0000000000000000
5.0000000000000000
- 2.0000000000000000
1.00000000000000000000
9.0000000000000000
- 3.0000000000000000
+ 2.0000000000000000
10.0000000000000000
7.0000000000000000
+ 3.0000000000000000
4.0000000000000000
(11 rows)
@@ -387,9 +387,9 @@ select count(*) from xc_groupby_def where a is not null group by a;
1
1
1
+ 2
1
1
- 2
1
1
1
@@ -593,14 +593,14 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_gro
(3 rows)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_groupby_tab1 group by val2;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1.03..1.05 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_groupby_tab1.val))), pg_catalog.avg((avg(xc_groupby_tab1.val))), ((pg_catalog.sum((sum(xc_groupby_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_groupby_tab1.val2
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2
-> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2
(6 rows)
-- joins and group by
@@ -772,14 +772,14 @@ select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2;
(3 rows)
explain verbose select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2;
- QUERY PLAN
-----------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1.02..1.04 rows=1 width=8)
- Output: (((count(*) + sum(val)))::numeric + avg(val)), val2
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
+ Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_groupby_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_groupby_tab1.val)))), xc_groupby_tab1.val2
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2
-> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2
(6 rows)
-- group by with expressions in group by clause
@@ -1179,20 +1179,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_gro
(3 rows)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_groupby_tab1 group by val2;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1.02..1.06 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- -> Sort (cost=1.02..1.03 rows=1 width=8)
- Output: val, val2
- Sort Key: xc_groupby_tab1.val2
- -> Result (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(11 rows)
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_groupby_tab1.val))), pg_catalog.avg((avg(xc_groupby_tab1.val))), ((pg_catalog.sum((sum(xc_groupby_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_groupby_tab1.val2
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2
+ -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2
+(6 rows)
-- joins and group by
select count(*), sum(xc_groupby_tab1.val * xc_groupby_tab2.val), avg(xc_groupby_tab1.val*xc_groupby_tab2.val), sum(xc_groupby_tab1.val*xc_groupby_tab2.val)::float8/count(*), xc_groupby_tab1.val2, xc_groupby_tab2.val2 from xc_groupby_tab1 full outer join xc_groupby_tab2 on xc_groupby_tab1.val2 = xc_groupby_tab2.val2 group by xc_groupby_tab1.val2, xc_groupby_tab2.val2;
@@ -1371,20 +1366,15 @@ select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2;
(3 rows)
explain verbose select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1.02..1.06 rows=1 width=8)
- Output: (((count(*) + sum(val)))::numeric + avg(val)), val2
- -> Sort (cost=1.02..1.03 rows=1 width=8)
- Output: val, val2
- Sort Key: xc_groupby_tab1.val2
- -> Result (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(11 rows)
+ Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_groupby_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_groupby_tab1.val)))), xc_groupby_tab1.val2
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2
+ -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2
+(6 rows)
-- group by with expressions in group by clause
select sum(val), avg(val), 2 * val2 from xc_groupby_tab1 group by 2 * val2;
@@ -1771,20 +1761,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_gro
(3 rows)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_groupby_tab1 group by val2;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1.02..1.06 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- -> Sort (cost=1.02..1.03 rows=1 width=8)
- Output: val, val2
- Sort Key: xc_groupby_tab1.val2
- -> Result (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(11 rows)
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_groupby_tab1.val))), pg_catalog.avg((avg(xc_groupby_tab1.val))), ((pg_catalog.sum((sum(xc_groupby_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_groupby_tab1.val2
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2
+ -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2
+(6 rows)
-- joins and group by
select count(*), sum(xc_groupby_tab1.val * xc_groupby_tab2.val), avg(xc_groupby_tab1.val*xc_groupby_tab2.val), sum(xc_groupby_tab1.val*xc_groupby_tab2.val)::float8/count(*), xc_groupby_tab1.val2, xc_groupby_tab2.val2 from xc_groupby_tab1 full outer join xc_groupby_tab2 on xc_groupby_tab1.val2 = xc_groupby_tab2.val2 group by xc_groupby_tab1.val2, xc_groupby_tab2.val2;
@@ -1963,20 +1948,15 @@ select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2;
(3 rows)
explain verbose select count(*) + sum(val) + avg(val), val2 from xc_groupby_tab1 group by val2;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1.02..1.06 rows=1 width=8)
- Output: (((count(*) + sum(val)))::numeric + avg(val)), val2
- -> Sort (cost=1.02..1.03 rows=1 width=8)
- Output: val, val2
- Sort Key: xc_groupby_tab1.val2
- -> Result (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(11 rows)
+ Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_groupby_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_groupby_tab1.val)))), xc_groupby_tab1.val2
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_groupby_tab1.val)), (avg(xc_groupby_tab1.val)), xc_groupby_tab1.val2
+ -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: count(*), sum(xc_groupby_tab1.val), avg(xc_groupby_tab1.val), xc_groupby_tab1.val2
+(6 rows)
-- group by with expressions in group by clause
select sum(val), avg(val), 2 * val2 from xc_groupby_tab1 group by 2 * val2;
diff --git a/src/test/regress/expected/xc_having.out b/src/test/regress/expected/xc_having.out
index 6dc95d43e0..b10d4f81b7 100644
--- a/src/test/regress/expected/xc_having.out
+++ b/src/test/regress/expected/xc_having.out
@@ -18,14 +18,14 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(1 row)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having val2 + 1 > 3;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1.03..1.05 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- -> Materialize (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
-> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
(6 rows)
-- having clause containing aggregate
@@ -36,15 +36,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(1 row)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1.03..1.06 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- Filter: (avg(xc_having_tab1.val) > 3.75)
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
-> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
(7 rows)
select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2;
@@ -55,15 +55,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(2 rows)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1.03..1.06 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- Filter: ((avg(xc_having_tab1.val) > 3.75) OR (xc_having_tab1.val2 > 2))
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ Filter: ((pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) OR (xc_having_tab1.val2 > 2))
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
-> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
(7 rows)
select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2;
@@ -72,15 +72,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(0 rows)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1.03..1.06 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- Filter: (avg(xc_having_tab1.val) > 3.75)
- -> Materialize (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
-> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
(7 rows)
-- joins and group by and having
@@ -119,12 +119,12 @@ explain verbose select val2 from xc_having_tab1 group by val2 having sum(val) >
QUERY PLAN
----------------------------------------------------------------------------------
HashAggregate (cost=1.02..1.03 rows=1 width=8)
- Output: val2
- Filter: (sum(xc_having_tab1.val) > 8)
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
+ Output: xc_having_tab1.val2
+ Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 8)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: xc_having_tab1.val2, (sum(xc_having_tab1.val))
-> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: xc_having_tab1.val2, sum(xc_having_tab1.val)
(7 rows)
select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5;
@@ -136,18 +136,16 @@ select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5;
(3 rows)
explain verbose select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5;
- QUERY PLAN
-----------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
HashAggregate (cost=1.02..1.04 rows=1 width=8)
- Output: ((val + val2))
- Filter: (sum(xc_having_tab1.val) > 5)
- -> Result (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2, (val + val2)
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(9 rows)
+ Output: ((xc_having_tab1.val + xc_having_tab1.val2))
+ Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 5)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: ((xc_having_tab1.val + xc_having_tab1.val2)), (sum(xc_having_tab1.val))
+ -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: (xc_having_tab1.val + xc_having_tab1.val2), sum(xc_having_tab1.val)
+(7 rows)
-- group by with aggregates in expression
select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2;
@@ -157,15 +155,15 @@ select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 ha
(1 row)
explain verbose select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2;
- QUERY PLAN
-----------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1.03..1.05 rows=1 width=8)
- Output: (((count(*) + sum(val)))::numeric + avg(val)), val2
- Filter: (min(xc_having_tab1.val) < xc_having_tab1.val2)
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
+ Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_having_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_having_tab1.val)))), xc_having_tab1.val2
+ Filter: (min((min(xc_having_tab1.val))) < xc_having_tab1.val2)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2, (min(xc_having_tab1.val))
-> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2, min(xc_having_tab1.val)
(7 rows)
drop table xc_having_tab1;
@@ -185,14 +183,14 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(1 row)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having val2 + 1 > 3;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1.03..1.05 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- -> Materialize (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
-> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
(6 rows)
-- having clause containing aggregate
@@ -203,15 +201,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(1 row)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1.03..1.06 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- Filter: (avg(xc_having_tab1.val) > 3.75)
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
-> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
(7 rows)
select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2;
@@ -222,15 +220,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(2 rows)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1.03..1.06 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- Filter: ((avg(xc_having_tab1.val) > 3.75) OR (xc_having_tab1.val2 > 2))
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ Filter: ((pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) OR (xc_having_tab1.val2 > 2))
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
-> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
(7 rows)
select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2;
@@ -239,15 +237,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(0 rows)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1.03..1.06 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- Filter: (avg(xc_having_tab1.val) > 3.75)
- -> Materialize (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
-> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
(7 rows)
-- joins and group by and having
@@ -286,12 +284,12 @@ explain verbose select val2 from xc_having_tab1 group by val2 having sum(val) >
QUERY PLAN
----------------------------------------------------------------------------------
HashAggregate (cost=1.02..1.03 rows=1 width=8)
- Output: val2
- Filter: (sum(xc_having_tab1.val) > 8)
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
+ Output: xc_having_tab1.val2
+ Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 8)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: xc_having_tab1.val2, (sum(xc_having_tab1.val))
-> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: xc_having_tab1.val2, sum(xc_having_tab1.val)
(7 rows)
select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5;
@@ -303,18 +301,16 @@ select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5;
(3 rows)
explain verbose select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5;
- QUERY PLAN
-----------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
HashAggregate (cost=1.02..1.04 rows=1 width=8)
- Output: ((val + val2))
- Filter: (sum(xc_having_tab1.val) > 5)
- -> Result (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2, (val + val2)
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(9 rows)
+ Output: ((xc_having_tab1.val + xc_having_tab1.val2))
+ Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 5)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: ((xc_having_tab1.val + xc_having_tab1.val2)), (sum(xc_having_tab1.val))
+ -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: (xc_having_tab1.val + xc_having_tab1.val2), sum(xc_having_tab1.val)
+(7 rows)
-- group by with aggregates in expression
select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2;
@@ -324,15 +320,15 @@ select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 ha
(1 row)
explain verbose select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2;
- QUERY PLAN
-----------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1.03..1.05 rows=1 width=8)
- Output: (((count(*) + sum(val)))::numeric + avg(val)), val2
- Filter: (min(xc_having_tab1.val) < xc_having_tab1.val2)
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
+ Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_having_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_having_tab1.val)))), xc_having_tab1.val2
+ Filter: (min((min(xc_having_tab1.val))) < xc_having_tab1.val2)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2, (min(xc_having_tab1.val))
-> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2, min(xc_having_tab1.val)
(7 rows)
drop table xc_having_tab1;
@@ -352,20 +348,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(1 row)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having val2 + 1 > 3;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1.03..1.07 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- -> Sort (cost=1.03..1.03 rows=1 width=8)
- Output: val, val2
- Sort Key: xc_having_tab1.val2
- -> Result (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2
- -> Materialize (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(11 rows)
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
+ -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
+(6 rows)
-- having clause containing aggregate
select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75;
@@ -375,21 +366,16 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(1 row)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1.02..1.07 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- Filter: (avg(xc_having_tab1.val) > 3.75)
- -> Sort (cost=1.02..1.03 rows=1 width=8)
- Output: val, val2
- Sort Key: xc_having_tab1.val2
- -> Result (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(12 rows)
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
+ -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
+(7 rows)
select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2;
count | sum | avg | ?column? | val2
@@ -399,21 +385,16 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(2 rows)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1.02..1.07 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- Filter: ((avg(xc_having_tab1.val) > 3.75) OR (xc_having_tab1.val2 > 2))
- -> Sort (cost=1.02..1.03 rows=1 width=8)
- Output: val, val2
- Sort Key: xc_having_tab1.val2
- -> Result (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(12 rows)
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ Filter: ((pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) OR (xc_having_tab1.val2 > 2))
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
+ -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
+(7 rows)
select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2;
count | sum | avg | ?column? | val2
@@ -421,21 +402,16 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(0 rows)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1.03..1.07 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- Filter: (avg(xc_having_tab1.val) > 3.75)
- -> Sort (cost=1.03..1.03 rows=1 width=8)
- Output: val, val2
- Sort Key: xc_having_tab1.val2
- -> Result (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2
- -> Materialize (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(12 rows)
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
+ -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
+(7 rows)
-- joins and group by and having
select count(*), sum(xc_having_tab1.val * xc_having_tab2.val), avg(xc_having_tab1.val*xc_having_tab2.val), sum(xc_having_tab1.val*xc_having_tab2.val)::float8/count(*), xc_having_tab1.val2, xc_having_tab2.val2 from xc_having_tab1 full outer join xc_having_tab2 on xc_having_tab1.val2 = xc_having_tab2.val2 group by xc_having_tab1.val2, xc_having_tab2.val2 having xc_having_tab1.val2 + xc_having_tab2.val2 > 2;
@@ -473,21 +449,16 @@ select val2 from xc_having_tab1 group by val2 having sum(val) > 8;
(1 row)
explain verbose select val2 from xc_having_tab1 group by val2 having sum(val) > 8;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------
GroupAggregate (cost=1.02..1.05 rows=1 width=8)
- Output: val2
- Filter: (sum(xc_having_tab1.val) > 8)
- -> Sort (cost=1.02..1.03 rows=1 width=8)
- Output: val2, val
- Sort Key: xc_having_tab1.val2
- -> Result (cost=0.00..1.01 rows=1 width=8)
- Output: val2, val
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(12 rows)
+ Output: xc_having_tab1.val2
+ Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 8)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: xc_having_tab1.val2, (sum(xc_having_tab1.val))
+ -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: xc_having_tab1.val2, sum(xc_having_tab1.val)
+(7 rows)
select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5;
?column?
@@ -498,21 +469,16 @@ select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5;
(3 rows)
explain verbose select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
GroupAggregate (cost=1.03..1.05 rows=1 width=8)
- Output: ((val + val2))
- Filter: (sum(xc_having_tab1.val) > 5)
- -> Sort (cost=1.03..1.03 rows=1 width=8)
- Output: val, val2, ((val + val2))
- Sort Key: ((xc_having_tab1.val + xc_having_tab1.val2))
- -> Result (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2, (val + val2)
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(12 rows)
+ Output: ((xc_having_tab1.val + xc_having_tab1.val2))
+ Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 5)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: ((xc_having_tab1.val + xc_having_tab1.val2)), (sum(xc_having_tab1.val))
+ -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: (xc_having_tab1.val + xc_having_tab1.val2), sum(xc_having_tab1.val)
+(7 rows)
-- group by with aggregates in expression
select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2;
@@ -522,21 +488,16 @@ select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 ha
(1 row)
explain verbose select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1.02..1.06 rows=1 width=8)
- Output: (((count(*) + sum(val)))::numeric + avg(val)), val2
- Filter: (min(xc_having_tab1.val) < xc_having_tab1.val2)
- -> Sort (cost=1.02..1.03 rows=1 width=8)
- Output: val, val2
- Sort Key: xc_having_tab1.val2
- -> Result (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(12 rows)
+ Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_having_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_having_tab1.val)))), xc_having_tab1.val2
+ Filter: (min((min(xc_having_tab1.val))) < xc_having_tab1.val2)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2, (min(xc_having_tab1.val))
+ -> Data Node Scan (Node Count [2]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2, min(xc_having_tab1.val)
+(7 rows)
drop table xc_having_tab1;
drop table xc_having_tab2;
@@ -555,20 +516,15 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(1 row)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having val2 + 1 > 3;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1.03..1.07 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- -> Sort (cost=1.03..1.03 rows=1 width=8)
- Output: val, val2
- Sort Key: xc_having_tab1.val2
- -> Result (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2
- -> Materialize (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(11 rows)
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
+ -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
+(6 rows)
-- having clause containing aggregate
select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75;
@@ -578,21 +534,16 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(1 row)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1.02..1.07 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- Filter: (avg(xc_having_tab1.val) > 3.75)
- -> Sort (cost=1.02..1.03 rows=1 width=8)
- Output: val, val2
- Sort Key: xc_having_tab1.val2
- -> Result (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(12 rows)
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
+ -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
+(7 rows)
select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2;
count | sum | avg | ?column? | val2
@@ -602,21 +553,16 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(2 rows)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 or val2 > 2;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1.02..1.07 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- Filter: ((avg(xc_having_tab1.val) > 3.75) OR (xc_having_tab1.val2 > 2))
- -> Sort (cost=1.02..1.03 rows=1 width=8)
- Output: val, val2
- Sort Key: xc_having_tab1.val2
- -> Result (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(12 rows)
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ Filter: ((pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75) OR (xc_having_tab1.val2 > 2))
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
+ -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
+(7 rows)
select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2;
count | sum | avg | ?column? | val2
@@ -624,21 +570,16 @@ select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_hav
(0 rows)
explain verbose select count(*), sum(val), avg(val), sum(val)::float8/count(*), val2 from xc_having_tab1 group by val2 having avg(val) > 3.75 and val2 > 2;
- QUERY PLAN
--------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1.03..1.07 rows=1 width=8)
- Output: count(*), sum(val), avg(val), ((sum(val))::double precision / (count(*))::double precision), val2
- Filter: (avg(xc_having_tab1.val) > 3.75)
- -> Sort (cost=1.03..1.03 rows=1 width=8)
- Output: val, val2
- Sort Key: xc_having_tab1.val2
- -> Result (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2
- -> Materialize (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(12 rows)
+ Output: pg_catalog.count(*), pg_catalog.sum((sum(xc_having_tab1.val))), pg_catalog.avg((avg(xc_having_tab1.val))), ((pg_catalog.sum((sum(xc_having_tab1.val))))::double precision / (pg_catalog.count(*))::double precision), xc_having_tab1.val2
+ Filter: (pg_catalog.avg((avg(xc_having_tab1.val))) > 3.75)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2
+ -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2
+(7 rows)
-- joins and group by and having
select count(*), sum(xc_having_tab1.val * xc_having_tab2.val), avg(xc_having_tab1.val*xc_having_tab2.val), sum(xc_having_tab1.val*xc_having_tab2.val)::float8/count(*), xc_having_tab1.val2, xc_having_tab2.val2 from xc_having_tab1 full outer join xc_having_tab2 on xc_having_tab1.val2 = xc_having_tab2.val2 group by xc_having_tab1.val2, xc_having_tab2.val2 having xc_having_tab1.val2 + xc_having_tab2.val2 > 2;
@@ -676,21 +617,16 @@ select val2 from xc_having_tab1 group by val2 having sum(val) > 8;
(1 row)
explain verbose select val2 from xc_having_tab1 group by val2 having sum(val) > 8;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------
GroupAggregate (cost=1.02..1.05 rows=1 width=8)
- Output: val2
- Filter: (sum(xc_having_tab1.val) > 8)
- -> Sort (cost=1.02..1.03 rows=1 width=8)
- Output: val2, val
- Sort Key: xc_having_tab1.val2
- -> Result (cost=0.00..1.01 rows=1 width=8)
- Output: val2, val
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(12 rows)
+ Output: xc_having_tab1.val2
+ Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 8)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: xc_having_tab1.val2, (sum(xc_having_tab1.val))
+ -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: xc_having_tab1.val2, sum(xc_having_tab1.val)
+(7 rows)
select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5;
?column?
@@ -701,21 +637,16 @@ select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5;
(3 rows)
explain verbose select val + val2 from xc_having_tab1 group by val + val2 having sum(val) > 5;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------------------------
GroupAggregate (cost=1.03..1.05 rows=1 width=8)
- Output: ((val + val2))
- Filter: (sum(xc_having_tab1.val) > 5)
- -> Sort (cost=1.03..1.03 rows=1 width=8)
- Output: val, val2, ((val + val2))
- Sort Key: ((xc_having_tab1.val + xc_having_tab1.val2))
- -> Result (cost=0.00..1.02 rows=1 width=8)
- Output: val, val2, (val + val2)
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(12 rows)
+ Output: ((xc_having_tab1.val + xc_having_tab1.val2))
+ Filter: (pg_catalog.sum((sum(xc_having_tab1.val))) > 5)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: ((xc_having_tab1.val + xc_having_tab1.val2)), (sum(xc_having_tab1.val))
+ -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: (xc_having_tab1.val + xc_having_tab1.val2), sum(xc_having_tab1.val)
+(7 rows)
-- group by with aggregates in expression
select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2;
@@ -725,21 +656,16 @@ select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 ha
(1 row)
explain verbose select count(*) + sum(val) + avg(val), val2 from xc_having_tab1 group by val2 having min(val) < val2;
- QUERY PLAN
-----------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1.02..1.06 rows=1 width=8)
- Output: (((count(*) + sum(val)))::numeric + avg(val)), val2
- Filter: (min(xc_having_tab1.val) < xc_having_tab1.val2)
- -> Sort (cost=1.02..1.03 rows=1 width=8)
- Output: val, val2
- Sort Key: xc_having_tab1.val2
- -> Result (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Materialize (cost=0.00..1.01 rows=1 width=8)
- Output: val, val2
- -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
- Output: val, val2
-(12 rows)
+ Output: (((pg_catalog.count(*) + pg_catalog.sum((sum(xc_having_tab1.val)))))::numeric + pg_catalog.avg((avg(xc_having_tab1.val)))), xc_having_tab1.val2
+ Filter: (min((min(xc_having_tab1.val))) < xc_having_tab1.val2)
+ -> Materialize (cost=0.00..0.00 rows=0 width=0)
+ Output: (count(*)), (sum(xc_having_tab1.val)), (avg(xc_having_tab1.val)), xc_having_tab1.val2, (min(xc_having_tab1.val))
+ -> Data Node Scan (Node Count [1]) (cost=0.00..1.01 rows=1000 width=8)
+ Output: count(*), sum(xc_having_tab1.val), avg(xc_having_tab1.val), xc_having_tab1.val2, min(xc_having_tab1.val)
+(7 rows)
drop table xc_having_tab1;
drop table xc_having_tab2;
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-14 17:36:40 +0000