| drop table if exists with_test1 cascade; |
| NOTICE: table "with_test1" does not exist, skipping |
| create table with_test1 (i int, t text, value int) distributed by (i); |
| insert into with_test1 select i%10, 'text' || i%20, i%30 from generate_series(0, 99) i; |
| analyze with_test1; |
| drop table if exists with_test2 cascade; |
| NOTICE: table "with_test2" does not exist, skipping |
| create table with_test2 (i int, t text, value int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into with_test2 select i%100, 'text' || i%200, i%300 from generate_series(0, 999) i; |
| analyze with_test2; |
| -- With clause with one common table expression |
| --begin_equivalent |
| with my_sum(total) as (select sum(value) from with_test1) |
| select * |
| from my_sum; |
| total |
| ------- |
| 1350 |
| (1 row) |
| |
| select sum(value) as total from with_test1; |
| total |
| ------- |
| 1350 |
| (1 row) |
| |
| --end_equivalent |
| -- With clause with two common table expression |
| --begin_equivalent |
| with my_sum(total) as (select sum(value) from with_test1), |
| my_count(cnt) as (select count(*) from with_test1) |
| select cnt, total |
| from my_sum, my_count; |
| cnt | total |
| -----+------- |
| 100 | 1350 |
| (1 row) |
| |
| select cnt, total |
| from (select sum(value) as total from with_test1) tmp1, |
| (select count(*) as cnt from with_test1) tmp2; |
| cnt | total |
| -----+------- |
| 100 | 1350 |
| (1 row) |
| |
| --end_equivalent |
| -- With clause with one common table expression that is referenced twice |
| --begin_equivalent |
| with my_group_sum(i, total) as (select i, sum(value) from with_test1 group by i) |
| select gs1.i, gs1.total, gs2.total |
| from my_group_sum gs1, my_group_sum gs2 |
| where gs1.i = gs2.i + 1; |
| i | total | total |
| ---+-------+------- |
| 4 | 130 | 120 |
| 2 | 110 | 100 |
| 8 | 170 | 160 |
| 6 | 150 | 140 |
| 3 | 120 | 110 |
| 7 | 160 | 150 |
| 1 | 100 | 90 |
| 9 | 180 | 170 |
| 5 | 140 | 130 |
| (9 rows) |
| |
| select gs1.i, gs1.total, gs2.total |
| from (select i, sum(value) as total from with_test1 group by i) gs1, |
| (select i, sum(value) as total from with_test1 group by i) gs2 |
| where gs1.i = gs2.i + 1; |
| i | total | total |
| ---+-------+------- |
| 3 | 120 | 110 |
| 7 | 160 | 150 |
| 1 | 100 | 90 |
| 9 | 180 | 170 |
| 5 | 140 | 130 |
| 4 | 130 | 120 |
| 2 | 110 | 100 |
| 8 | 170 | 160 |
| 6 | 150 | 140 |
| (9 rows) |
| |
| --end_equivalent |
| -- With clause with one common table expression that contains the other common table expression |
| --begin_equivalent |
| with my_count(i, cnt) as (select i, count(*) from with_test1 group by i), |
| my_sum(total) as (select sum(cnt) from my_count) |
| select * |
| from my_sum; |
| total |
| ------- |
| 100 |
| (1 row) |
| |
| select sum(cnt) as total from (select i, count(*) as cnt from with_test1 group by i) my_count; |
| total |
| ------- |
| 100 |
| (1 row) |
| |
| --end_equivalent |
| -- WITH query contains WITH |
| --begin_equivalent |
| with my_sum(total) as ( |
| with my_group_sum(total) as (select sum(value) from with_test1 group by i) |
| select sum(total) from my_group_sum) |
| select * |
| from my_sum; |
| total |
| ------- |
| 1350 |
| (1 row) |
| |
| select sum(total) from (select sum(value) as total from with_test1 group by i) my_group_sum; |
| sum |
| ------ |
| 1350 |
| (1 row) |
| |
| --end_equivalent |
| -- pathkeys |
| explain (costs off) |
| with my_order as (select * from with_test1 order by i) |
| select i, count(*) |
| from my_order |
| group by i order by i; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| Merge Key: with_test1.i |
| -> GroupAggregate |
| Group Key: with_test1.i |
| -> Sort |
| Sort Key: with_test1.i |
| -> Seq Scan on with_test1 |
| Optimizer: Postgres query optimizer |
| (8 rows) |
| |
| with my_order as (select * from with_test1 order by i) |
| select i, count(*) |
| from my_order |
| group by i order by i; |
| i | count |
| ---+------- |
| 0 | 10 |
| 1 | 10 |
| 2 | 10 |
| 3 | 10 |
| 4 | 10 |
| 5 | 10 |
| 6 | 10 |
| 7 | 10 |
| 8 | 10 |
| 9 | 10 |
| (10 rows) |
| |
| -- WITH query used in InitPlan |
| --begin_equivalent |
| with my_max(maximum) as (select max(value) from with_test1) |
| select * from with_test2 |
| where value < (select * from my_max); |
| i | t | value |
| ----+---------+------- |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 11 | text11 | 11 |
| 13 | text13 | 13 |
| 15 | text15 | 15 |
| 17 | text17 | 17 |
| 19 | text19 | 19 |
| 21 | text21 | 21 |
| 23 | text23 | 23 |
| 25 | text25 | 25 |
| 27 | text27 | 27 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 11 | text111 | 11 |
| 13 | text113 | 13 |
| 15 | text115 | 15 |
| 17 | text117 | 17 |
| 19 | text119 | 19 |
| 21 | text121 | 21 |
| 23 | text123 | 23 |
| 25 | text125 | 25 |
| 27 | text127 | 27 |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 11 | text11 | 11 |
| 13 | text13 | 13 |
| 15 | text15 | 15 |
| 17 | text17 | 17 |
| 19 | text19 | 19 |
| 21 | text21 | 21 |
| 23 | text23 | 23 |
| 25 | text25 | 25 |
| 27 | text27 | 27 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 11 | text111 | 11 |
| 13 | text113 | 13 |
| 15 | text115 | 15 |
| 17 | text117 | 17 |
| 19 | text119 | 19 |
| 21 | text121 | 21 |
| 23 | text123 | 23 |
| 25 | text125 | 25 |
| 27 | text127 | 27 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 10 | text10 | 10 |
| 12 | text12 | 12 |
| 14 | text14 | 14 |
| 16 | text16 | 16 |
| 18 | text18 | 18 |
| 20 | text20 | 20 |
| 22 | text22 | 22 |
| 24 | text24 | 24 |
| 26 | text26 | 26 |
| 28 | text28 | 28 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| 10 | text110 | 10 |
| 12 | text112 | 12 |
| 14 | text114 | 14 |
| 16 | text116 | 16 |
| 18 | text118 | 18 |
| 20 | text120 | 20 |
| 22 | text122 | 22 |
| 24 | text124 | 24 |
| 26 | text126 | 26 |
| 28 | text128 | 28 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 10 | text10 | 10 |
| 12 | text12 | 12 |
| 14 | text14 | 14 |
| 16 | text16 | 16 |
| 18 | text18 | 18 |
| 20 | text20 | 20 |
| 22 | text22 | 22 |
| 24 | text24 | 24 |
| 26 | text26 | 26 |
| 28 | text28 | 28 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| 10 | text110 | 10 |
| 12 | text112 | 12 |
| 14 | text114 | 14 |
| 16 | text116 | 16 |
| 18 | text118 | 18 |
| 20 | text120 | 20 |
| 22 | text122 | 22 |
| 24 | text124 | 24 |
| 26 | text126 | 26 |
| 28 | text128 | 28 |
| (116 rows) |
| |
| select * from with_test2 |
| where value < (with my_max(maximum) as (select max(value) from with_test1) |
| select * from my_max); |
| i | t | value |
| ----+---------+------- |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 11 | text11 | 11 |
| 13 | text13 | 13 |
| 15 | text15 | 15 |
| 17 | text17 | 17 |
| 19 | text19 | 19 |
| 21 | text21 | 21 |
| 23 | text23 | 23 |
| 25 | text25 | 25 |
| 27 | text27 | 27 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 11 | text111 | 11 |
| 13 | text113 | 13 |
| 15 | text115 | 15 |
| 17 | text117 | 17 |
| 19 | text119 | 19 |
| 21 | text121 | 21 |
| 23 | text123 | 23 |
| 25 | text125 | 25 |
| 27 | text127 | 27 |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 11 | text11 | 11 |
| 13 | text13 | 13 |
| 15 | text15 | 15 |
| 17 | text17 | 17 |
| 19 | text19 | 19 |
| 21 | text21 | 21 |
| 23 | text23 | 23 |
| 25 | text25 | 25 |
| 27 | text27 | 27 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 11 | text111 | 11 |
| 13 | text113 | 13 |
| 15 | text115 | 15 |
| 17 | text117 | 17 |
| 19 | text119 | 19 |
| 21 | text121 | 21 |
| 23 | text123 | 23 |
| 25 | text125 | 25 |
| 27 | text127 | 27 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 10 | text10 | 10 |
| 12 | text12 | 12 |
| 14 | text14 | 14 |
| 16 | text16 | 16 |
| 18 | text18 | 18 |
| 20 | text20 | 20 |
| 22 | text22 | 22 |
| 24 | text24 | 24 |
| 26 | text26 | 26 |
| 28 | text28 | 28 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| 10 | text110 | 10 |
| 12 | text112 | 12 |
| 14 | text114 | 14 |
| 16 | text116 | 16 |
| 18 | text118 | 18 |
| 20 | text120 | 20 |
| 22 | text122 | 22 |
| 24 | text124 | 24 |
| 26 | text126 | 26 |
| 28 | text128 | 28 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 10 | text10 | 10 |
| 12 | text12 | 12 |
| 14 | text14 | 14 |
| 16 | text16 | 16 |
| 18 | text18 | 18 |
| 20 | text20 | 20 |
| 22 | text22 | 22 |
| 24 | text24 | 24 |
| 26 | text26 | 26 |
| 28 | text28 | 28 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| 10 | text110 | 10 |
| 12 | text112 | 12 |
| 14 | text114 | 14 |
| 16 | text116 | 16 |
| 18 | text118 | 18 |
| 20 | text120 | 20 |
| 22 | text122 | 22 |
| 24 | text124 | 24 |
| 26 | text126 | 26 |
| 28 | text128 | 28 |
| (116 rows) |
| |
| select * from with_test2 |
| where value < (select max(value) from with_test1); |
| i | t | value |
| ----+---------+------- |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 11 | text11 | 11 |
| 13 | text13 | 13 |
| 15 | text15 | 15 |
| 17 | text17 | 17 |
| 19 | text19 | 19 |
| 21 | text21 | 21 |
| 23 | text23 | 23 |
| 25 | text25 | 25 |
| 27 | text27 | 27 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 11 | text111 | 11 |
| 13 | text113 | 13 |
| 15 | text115 | 15 |
| 17 | text117 | 17 |
| 19 | text119 | 19 |
| 21 | text121 | 21 |
| 23 | text123 | 23 |
| 25 | text125 | 25 |
| 27 | text127 | 27 |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 11 | text11 | 11 |
| 13 | text13 | 13 |
| 15 | text15 | 15 |
| 17 | text17 | 17 |
| 19 | text19 | 19 |
| 21 | text21 | 21 |
| 23 | text23 | 23 |
| 25 | text25 | 25 |
| 27 | text27 | 27 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 11 | text111 | 11 |
| 13 | text113 | 13 |
| 15 | text115 | 15 |
| 17 | text117 | 17 |
| 19 | text119 | 19 |
| 21 | text121 | 21 |
| 23 | text123 | 23 |
| 25 | text125 | 25 |
| 27 | text127 | 27 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 10 | text10 | 10 |
| 12 | text12 | 12 |
| 14 | text14 | 14 |
| 16 | text16 | 16 |
| 18 | text18 | 18 |
| 20 | text20 | 20 |
| 22 | text22 | 22 |
| 24 | text24 | 24 |
| 26 | text26 | 26 |
| 28 | text28 | 28 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| 10 | text110 | 10 |
| 12 | text112 | 12 |
| 14 | text114 | 14 |
| 16 | text116 | 16 |
| 18 | text118 | 18 |
| 20 | text120 | 20 |
| 22 | text122 | 22 |
| 24 | text124 | 24 |
| 26 | text126 | 26 |
| 28 | text128 | 28 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 10 | text10 | 10 |
| 12 | text12 | 12 |
| 14 | text14 | 14 |
| 16 | text16 | 16 |
| 18 | text18 | 18 |
| 20 | text20 | 20 |
| 22 | text22 | 22 |
| 24 | text24 | 24 |
| 26 | text26 | 26 |
| 28 | text28 | 28 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| 10 | text110 | 10 |
| 12 | text112 | 12 |
| 14 | text114 | 14 |
| 16 | text116 | 16 |
| 18 | text118 | 18 |
| 20 | text120 | 20 |
| 22 | text122 | 22 |
| 24 | text124 | 24 |
| 26 | text126 | 26 |
| 28 | text128 | 28 |
| (116 rows) |
| |
| --end_equivalent |
| -- WITH query used in InitPlan and the main query at the same time |
| --begin_equivalent |
| with my_max(maximum) as (select max(value) from with_test1) |
| select with_test2.* from with_test2, my_max |
| where value < (select * from my_max) |
| and i < maximum and i > maximum - 10; |
| i | t | value |
| ----+---------+------- |
| 21 | text21 | 21 |
| 23 | text23 | 23 |
| 25 | text25 | 25 |
| 27 | text27 | 27 |
| 21 | text121 | 21 |
| 23 | text123 | 23 |
| 25 | text125 | 25 |
| 27 | text127 | 27 |
| 21 | text21 | 21 |
| 23 | text23 | 23 |
| 25 | text25 | 25 |
| 27 | text27 | 27 |
| 21 | text121 | 21 |
| 23 | text123 | 23 |
| 25 | text125 | 25 |
| 27 | text127 | 27 |
| 20 | text20 | 20 |
| 22 | text22 | 22 |
| 24 | text24 | 24 |
| 26 | text26 | 26 |
| 28 | text28 | 28 |
| 20 | text120 | 20 |
| 22 | text122 | 22 |
| 24 | text124 | 24 |
| 26 | text126 | 26 |
| 28 | text128 | 28 |
| 20 | text20 | 20 |
| 22 | text22 | 22 |
| 24 | text24 | 24 |
| 26 | text26 | 26 |
| 28 | text28 | 28 |
| 20 | text120 | 20 |
| 22 | text122 | 22 |
| 24 | text124 | 24 |
| 26 | text126 | 26 |
| 28 | text128 | 28 |
| (36 rows) |
| |
| select with_test2.* from with_test2, (select max(value) as maximum from with_test1) as my_max |
| where value < (select max(value) from with_test1) |
| and i < maximum and i > maximum - 10; |
| i | t | value |
| ----+---------+------- |
| 20 | text20 | 20 |
| 22 | text22 | 22 |
| 24 | text24 | 24 |
| 26 | text26 | 26 |
| 28 | text28 | 28 |
| 20 | text120 | 20 |
| 22 | text122 | 22 |
| 24 | text124 | 24 |
| 26 | text126 | 26 |
| 28 | text128 | 28 |
| 20 | text20 | 20 |
| 22 | text22 | 22 |
| 24 | text24 | 24 |
| 26 | text26 | 26 |
| 28 | text28 | 28 |
| 20 | text120 | 20 |
| 22 | text122 | 22 |
| 24 | text124 | 24 |
| 26 | text126 | 26 |
| 28 | text128 | 28 |
| 21 | text21 | 21 |
| 23 | text23 | 23 |
| 25 | text25 | 25 |
| 27 | text27 | 27 |
| 21 | text121 | 21 |
| 23 | text123 | 23 |
| 25 | text125 | 25 |
| 27 | text127 | 27 |
| 21 | text21 | 21 |
| 23 | text23 | 23 |
| 25 | text25 | 25 |
| 27 | text27 | 27 |
| 21 | text121 | 21 |
| 23 | text123 | 23 |
| 25 | text125 | 25 |
| 27 | text127 | 27 |
| (36 rows) |
| |
| --end_equivalent |
| -- WITH query used in subplan |
| --begin_equivalent |
| with my_groupmax(i, maximum) as (select i, max(value) from with_test1 group by i) |
| select * from with_test2 |
| where value < all (select maximum from my_groupmax); |
| i | t | value |
| ----+---------+------- |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 11 | text11 | 11 |
| 13 | text13 | 13 |
| 15 | text15 | 15 |
| 17 | text17 | 17 |
| 19 | text19 | 19 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 11 | text111 | 11 |
| 13 | text113 | 13 |
| 15 | text115 | 15 |
| 17 | text117 | 17 |
| 19 | text119 | 19 |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 11 | text11 | 11 |
| 13 | text13 | 13 |
| 15 | text15 | 15 |
| 17 | text17 | 17 |
| 19 | text19 | 19 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 11 | text111 | 11 |
| 13 | text113 | 13 |
| 15 | text115 | 15 |
| 17 | text117 | 17 |
| 19 | text119 | 19 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 10 | text10 | 10 |
| 12 | text12 | 12 |
| 14 | text14 | 14 |
| 16 | text16 | 16 |
| 18 | text18 | 18 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| 10 | text110 | 10 |
| 12 | text112 | 12 |
| 14 | text114 | 14 |
| 16 | text116 | 16 |
| 18 | text118 | 18 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 10 | text10 | 10 |
| 12 | text12 | 12 |
| 14 | text14 | 14 |
| 16 | text16 | 16 |
| 18 | text18 | 18 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| 10 | text110 | 10 |
| 12 | text112 | 12 |
| 14 | text114 | 14 |
| 16 | text116 | 16 |
| 18 | text118 | 18 |
| (80 rows) |
| |
| select * from with_test2 |
| where value < all (select max(value) from with_test1 group by i); |
| i | t | value |
| ----+---------+------- |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 11 | text11 | 11 |
| 13 | text13 | 13 |
| 15 | text15 | 15 |
| 17 | text17 | 17 |
| 19 | text19 | 19 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 11 | text111 | 11 |
| 13 | text113 | 13 |
| 15 | text115 | 15 |
| 17 | text117 | 17 |
| 19 | text119 | 19 |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 11 | text11 | 11 |
| 13 | text13 | 13 |
| 15 | text15 | 15 |
| 17 | text17 | 17 |
| 19 | text19 | 19 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 11 | text111 | 11 |
| 13 | text113 | 13 |
| 15 | text115 | 15 |
| 17 | text117 | 17 |
| 19 | text119 | 19 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 10 | text10 | 10 |
| 12 | text12 | 12 |
| 14 | text14 | 14 |
| 16 | text16 | 16 |
| 18 | text18 | 18 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| 10 | text110 | 10 |
| 12 | text112 | 12 |
| 14 | text114 | 14 |
| 16 | text116 | 16 |
| 18 | text118 | 18 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 10 | text10 | 10 |
| 12 | text12 | 12 |
| 14 | text14 | 14 |
| 16 | text16 | 16 |
| 18 | text18 | 18 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| 10 | text110 | 10 |
| 12 | text112 | 12 |
| 14 | text114 | 14 |
| 16 | text116 | 16 |
| 18 | text118 | 18 |
| (80 rows) |
| |
| --end_equivalent |
| -- WITH query used in subplan and the main query at the same time |
| --begin_equivalent |
| with my_groupmax(i, maximum) as (select i, max(value) from with_test1 group by i) |
| select * from with_test2, my_groupmax |
| where with_test2.i = my_groupmax.i |
| and value < all (select maximum from my_groupmax); |
| i | t | value | i | maximum |
| ---+---------+-------+---+--------- |
| 1 | text1 | 1 | 1 | 21 |
| 3 | text3 | 3 | 3 | 23 |
| 5 | text5 | 5 | 5 | 25 |
| 7 | text7 | 7 | 7 | 27 |
| 9 | text9 | 9 | 9 | 29 |
| 1 | text101 | 1 | 1 | 21 |
| 3 | text103 | 3 | 3 | 23 |
| 5 | text105 | 5 | 5 | 25 |
| 7 | text107 | 7 | 7 | 27 |
| 9 | text109 | 9 | 9 | 29 |
| 1 | text1 | 1 | 1 | 21 |
| 3 | text3 | 3 | 3 | 23 |
| 5 | text5 | 5 | 5 | 25 |
| 7 | text7 | 7 | 7 | 27 |
| 9 | text9 | 9 | 9 | 29 |
| 1 | text101 | 1 | 1 | 21 |
| 3 | text103 | 3 | 3 | 23 |
| 5 | text105 | 5 | 5 | 25 |
| 7 | text107 | 7 | 7 | 27 |
| 9 | text109 | 9 | 9 | 29 |
| 0 | text0 | 0 | 0 | 20 |
| 2 | text2 | 2 | 2 | 22 |
| 4 | text4 | 4 | 4 | 24 |
| 6 | text6 | 6 | 6 | 26 |
| 8 | text8 | 8 | 8 | 28 |
| 0 | text100 | 0 | 0 | 20 |
| 2 | text102 | 2 | 2 | 22 |
| 4 | text104 | 4 | 4 | 24 |
| 6 | text106 | 6 | 6 | 26 |
| 8 | text108 | 8 | 8 | 28 |
| 0 | text0 | 0 | 0 | 20 |
| 2 | text2 | 2 | 2 | 22 |
| 4 | text4 | 4 | 4 | 24 |
| 6 | text6 | 6 | 6 | 26 |
| 8 | text8 | 8 | 8 | 28 |
| 0 | text100 | 0 | 0 | 20 |
| 2 | text102 | 2 | 2 | 22 |
| 4 | text104 | 4 | 4 | 24 |
| 6 | text106 | 6 | 6 | 26 |
| 8 | text108 | 8 | 8 | 28 |
| (40 rows) |
| |
| select * from with_test2, (select i, max(value) as maximum from with_test1 group by i) as my_groupmax |
| where with_test2.i = my_groupmax.i |
| and value < all (select max(value) from with_test1 group by i); |
| i | t | value | i | maximum |
| ---+---------+-------+---+--------- |
| 1 | text1 | 1 | 1 | 21 |
| 3 | text3 | 3 | 3 | 23 |
| 5 | text5 | 5 | 5 | 25 |
| 7 | text7 | 7 | 7 | 27 |
| 9 | text9 | 9 | 9 | 29 |
| 1 | text101 | 1 | 1 | 21 |
| 3 | text103 | 3 | 3 | 23 |
| 5 | text105 | 5 | 5 | 25 |
| 7 | text107 | 7 | 7 | 27 |
| 9 | text109 | 9 | 9 | 29 |
| 1 | text1 | 1 | 1 | 21 |
| 3 | text3 | 3 | 3 | 23 |
| 5 | text5 | 5 | 5 | 25 |
| 7 | text7 | 7 | 7 | 27 |
| 9 | text9 | 9 | 9 | 29 |
| 1 | text101 | 1 | 1 | 21 |
| 3 | text103 | 3 | 3 | 23 |
| 5 | text105 | 5 | 5 | 25 |
| 7 | text107 | 7 | 7 | 27 |
| 9 | text109 | 9 | 9 | 29 |
| 0 | text0 | 0 | 0 | 20 |
| 2 | text2 | 2 | 2 | 22 |
| 4 | text4 | 4 | 4 | 24 |
| 6 | text6 | 6 | 6 | 26 |
| 8 | text8 | 8 | 8 | 28 |
| 0 | text100 | 0 | 0 | 20 |
| 2 | text102 | 2 | 2 | 22 |
| 4 | text104 | 4 | 4 | 24 |
| 6 | text106 | 6 | 6 | 26 |
| 8 | text108 | 8 | 8 | 28 |
| 0 | text0 | 0 | 0 | 20 |
| 2 | text2 | 2 | 2 | 22 |
| 4 | text4 | 4 | 4 | 24 |
| 6 | text6 | 6 | 6 | 26 |
| 8 | text8 | 8 | 8 | 28 |
| 0 | text100 | 0 | 0 | 20 |
| 2 | text102 | 2 | 2 | 22 |
| 4 | text104 | 4 | 4 | 24 |
| 6 | text106 | 6 | 6 | 26 |
| 8 | text108 | 8 | 8 | 28 |
| (40 rows) |
| |
| --end_equivalent |
| --begin_equivalent |
| with my_groupmax(i, maximum) as (select i, max(value) from with_test1 group by i) |
| SELECT count(*) FROM my_groupmax WHERE maximum > (SELECT sum(maximum)/100 FROM my_groupmax); |
| count |
| ------- |
| 10 |
| (1 row) |
| |
| select count(*) from (select i, max(value) as maximum from with_test1 group by i) as my_groupmax |
| where maximum > (SELECT sum(maximum)/100 FROM (select i, max(value) as maximum from with_test1 group by i) as tmp); |
| count |
| ------- |
| 10 |
| (1 row) |
| |
| --end_equivalent |
| -- name resolution |
| --begin_equivalent |
| with my_max(maximum) as (select max(value) from with_test2) |
| select * from with_test1, my_max |
| where value < (with my_max(maximum) as (select max(i) from with_test1) |
| select * from my_max); |
| i | t | value | maximum |
| ---+--------+-------+--------- |
| 1 | text1 | 1 | 299 |
| 3 | text3 | 3 | 299 |
| 5 | text5 | 5 | 299 |
| 7 | text7 | 7 | 299 |
| 1 | text11 | 1 | 299 |
| 3 | text13 | 3 | 299 |
| 5 | text15 | 5 | 299 |
| 7 | text17 | 7 | 299 |
| 1 | text1 | 1 | 299 |
| 3 | text3 | 3 | 299 |
| 5 | text5 | 5 | 299 |
| 7 | text7 | 7 | 299 |
| 1 | text11 | 1 | 299 |
| 3 | text13 | 3 | 299 |
| 5 | text15 | 5 | 299 |
| 7 | text17 | 7 | 299 |
| 0 | text0 | 0 | 299 |
| 2 | text2 | 2 | 299 |
| 4 | text4 | 4 | 299 |
| 6 | text6 | 6 | 299 |
| 8 | text8 | 8 | 299 |
| 0 | text10 | 0 | 299 |
| 2 | text12 | 2 | 299 |
| 4 | text14 | 4 | 299 |
| 6 | text16 | 6 | 299 |
| 8 | text18 | 8 | 299 |
| 0 | text0 | 0 | 299 |
| 2 | text2 | 2 | 299 |
| 4 | text4 | 4 | 299 |
| 6 | text6 | 6 | 299 |
| 8 | text8 | 8 | 299 |
| 0 | text10 | 0 | 299 |
| 2 | text12 | 2 | 299 |
| 4 | text14 | 4 | 299 |
| 6 | text16 | 6 | 299 |
| 8 | text18 | 8 | 299 |
| (36 rows) |
| |
| select * from with_test1, (select max(value) as maximum from with_test2) as my_max |
| where value < (select max(i) from with_test1); |
| i | t | value | maximum |
| ---+--------+-------+--------- |
| 1 | text1 | 1 | 299 |
| 3 | text3 | 3 | 299 |
| 5 | text5 | 5 | 299 |
| 7 | text7 | 7 | 299 |
| 1 | text11 | 1 | 299 |
| 3 | text13 | 3 | 299 |
| 5 | text15 | 5 | 299 |
| 7 | text17 | 7 | 299 |
| 1 | text1 | 1 | 299 |
| 3 | text3 | 3 | 299 |
| 5 | text5 | 5 | 299 |
| 7 | text7 | 7 | 299 |
| 1 | text11 | 1 | 299 |
| 3 | text13 | 3 | 299 |
| 5 | text15 | 5 | 299 |
| 7 | text17 | 7 | 299 |
| 0 | text0 | 0 | 299 |
| 2 | text2 | 2 | 299 |
| 4 | text4 | 4 | 299 |
| 6 | text6 | 6 | 299 |
| 8 | text8 | 8 | 299 |
| 0 | text10 | 0 | 299 |
| 2 | text12 | 2 | 299 |
| 4 | text14 | 4 | 299 |
| 6 | text16 | 6 | 299 |
| 8 | text18 | 8 | 299 |
| 0 | text0 | 0 | 299 |
| 2 | text2 | 2 | 299 |
| 4 | text4 | 4 | 299 |
| 6 | text6 | 6 | 299 |
| 8 | text8 | 8 | 299 |
| 0 | text10 | 0 | 299 |
| 2 | text12 | 2 | 299 |
| 4 | text14 | 4 | 299 |
| 6 | text16 | 6 | 299 |
| 8 | text18 | 8 | 299 |
| (36 rows) |
| |
| --end_equivalent |
| -- INSERT |
| insert into with_test2 |
| with my_sum(i, total) as (select i, sum(value) from with_test1 group by i) |
| select i, i || '', total |
| from my_sum; |
| -- CREATE TABLE AS |
| drop table if exists with_test3; |
| NOTICE: table "with_test3" does not exist, skipping |
| create table with_test3 as |
| with my_sum(i, total) as (select i, sum(value) from with_test1 group by i) |
| select * |
| from my_sum; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'i' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| -- view |
| drop view if exists my_view; |
| NOTICE: view "my_view" does not exist, skipping |
| create view my_view (total) as |
| with my_sum(total) as (select sum(value) from with_test1) |
| select * |
| from my_sum; |
| SELECT pg_get_viewdef('my_view'::regclass); |
| pg_get_viewdef |
| ---------------------------------------------- |
| WITH my_sum(total) AS ( + |
| SELECT sum(with_test1.value) AS sum+ |
| FROM with_test1 + |
| ) + |
| SELECT my_sum.total + |
| FROM my_sum; |
| (1 row) |
| |
| SELECT pg_get_viewdef('my_view'::regclass, true); |
| pg_get_viewdef |
| ---------------------------------------------- |
| WITH my_sum(total) AS ( + |
| SELECT sum(with_test1.value) AS sum+ |
| FROM with_test1 + |
| ) + |
| SELECT my_sum.total + |
| FROM my_sum; |
| (1 row) |
| |
| drop view if exists my_view; |
| create view my_view(total) as |
| with my_sum(total) as ( |
| with my_group_sum(total) as (select sum(value) from with_test1 group by i) |
| select sum(total) from my_group_sum) |
| select * |
| from my_sum; |
| SELECT pg_get_viewdef('my_view'::regclass); |
| pg_get_viewdef |
| ------------------------------------------------------ |
| WITH my_sum(total) AS ( + |
| WITH my_group_sum(total) AS ( + |
| SELECT sum(with_test1.value) AS sum+ |
| FROM with_test1 + |
| GROUP BY with_test1.i + |
| ) + |
| SELECT sum(my_group_sum.total) AS sum + |
| FROM my_group_sum + |
| ) + |
| SELECT my_sum.total + |
| FROM my_sum; |
| (1 row) |
| |
| SELECT pg_get_viewdef('my_view'::regclass, true); |
| pg_get_viewdef |
| ------------------------------------------------------ |
| WITH my_sum(total) AS ( + |
| WITH my_group_sum(total) AS ( + |
| SELECT sum(with_test1.value) AS sum+ |
| FROM with_test1 + |
| GROUP BY with_test1.i + |
| ) + |
| SELECT sum(my_group_sum.total) AS sum + |
| FROM my_group_sum + |
| ) + |
| SELECT my_sum.total + |
| FROM my_sum; |
| (1 row) |
| |
| drop view if exists my_view; |
| create view my_view(i, total) as ( |
| select i, sum(value) from with_test1 group by i); |
| with my_sum(total) as (select sum(total) from my_view) |
| select * from my_sum; |
| total |
| ------- |
| 1350 |
| (1 row) |
| |
| -- WITH query not used in the main query |
| --begin_equivalent |
| with my_sum(total) as (select sum(value) from with_test1) |
| select count(*) from with_test2; |
| count |
| ------- |
| 1010 |
| (1 row) |
| |
| select count(*) from with_test2; |
| count |
| ------- |
| 1010 |
| (1 row) |
| |
| --end_equivalent |
| -- WITH used in CURSOR query |
| begin; |
| declare c cursor for with my_sum(total) as (select sum(value) from with_test1 group by i) select * from my_sum order by 1; |
| fetch 10 from c; |
| total |
| ------- |
| 90 |
| 100 |
| 110 |
| 120 |
| 130 |
| 140 |
| 150 |
| 160 |
| 170 |
| 180 |
| (10 rows) |
| |
| close c; |
| end; |
| -- Returning |
| create temporary table y (i int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into y |
| with t as (select i from with_test1) |
| select i+20 from t returning *; |
| i |
| ---- |
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
| 21 |
| 21 |
| 21 |
| 21 |
| 21 |
| 21 |
| 21 |
| 21 |
| 21 |
| 21 |
| 22 |
| 22 |
| 22 |
| 22 |
| 22 |
| 22 |
| 22 |
| 22 |
| 22 |
| 22 |
| 23 |
| 23 |
| 23 |
| 23 |
| 23 |
| 23 |
| 23 |
| 23 |
| 23 |
| 23 |
| 24 |
| 24 |
| 24 |
| 24 |
| 24 |
| 24 |
| 24 |
| 24 |
| 24 |
| 24 |
| 25 |
| 25 |
| 25 |
| 25 |
| 25 |
| 25 |
| 25 |
| 25 |
| 25 |
| 25 |
| 26 |
| 26 |
| 26 |
| 26 |
| 26 |
| 26 |
| 26 |
| 26 |
| 26 |
| 26 |
| 27 |
| 27 |
| 27 |
| 27 |
| 27 |
| 27 |
| 27 |
| 27 |
| 27 |
| 27 |
| 28 |
| 28 |
| 28 |
| 28 |
| 28 |
| 28 |
| 28 |
| 28 |
| 28 |
| 28 |
| 29 |
| 29 |
| 29 |
| 29 |
| 29 |
| 29 |
| 29 |
| 29 |
| 29 |
| 29 |
| (100 rows) |
| |
| select * from y; |
| i |
| ---- |
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
| 21 |
| 21 |
| 21 |
| 21 |
| 21 |
| 21 |
| 21 |
| 21 |
| 21 |
| 21 |
| 22 |
| 22 |
| 22 |
| 22 |
| 22 |
| 22 |
| 22 |
| 22 |
| 22 |
| 22 |
| 23 |
| 23 |
| 23 |
| 23 |
| 23 |
| 23 |
| 23 |
| 23 |
| 23 |
| 23 |
| 24 |
| 24 |
| 24 |
| 24 |
| 24 |
| 24 |
| 24 |
| 24 |
| 24 |
| 24 |
| 25 |
| 25 |
| 25 |
| 25 |
| 25 |
| 25 |
| 25 |
| 25 |
| 25 |
| 25 |
| 26 |
| 26 |
| 26 |
| 26 |
| 26 |
| 26 |
| 26 |
| 26 |
| 26 |
| 26 |
| 27 |
| 27 |
| 27 |
| 27 |
| 27 |
| 27 |
| 27 |
| 27 |
| 27 |
| 27 |
| 28 |
| 28 |
| 28 |
| 28 |
| 28 |
| 28 |
| 28 |
| 28 |
| 28 |
| 28 |
| 29 |
| 29 |
| 29 |
| 29 |
| 29 |
| 29 |
| 29 |
| 29 |
| 29 |
| 29 |
| (100 rows) |
| |
| drop table y; |
| -- WITH used in SETOP |
| with my_sum(total) as (select sum(value) from with_test1) |
| select * from my_sum |
| union all |
| select * from my_sum; |
| total |
| ------- |
| 1350 |
| 1350 |
| (2 rows) |
| |
| -- ERROR cases |
| -- duplicate CTE name |
| with my_sum(total) as (select sum(value) from with_test1), |
| my_sum(group_total) as (select sum(value) from with_test1 group by i) |
| select * |
| from my_sum; |
| ERROR: WITH query name "my_sum" specified more than once |
| LINE 2: my_sum(group_total) as (select sum(value) from with_tes... |
| ^ |
| -- INTO clause |
| with my_sum(total) as (select sum(value) from with_test1 into total_value) |
| select * |
| from my_sum; |
| ERROR: syntax error at or near "into" |
| LINE 1: ..._sum(total) as (select sum(value) from with_test1 into total... |
| ^ |
| -- name resolution |
| select * from with_test1, my_max |
| where value < (with my_max(maximum) as (select max(i) from with_test1) |
| select * from my_max); |
| ERROR: relation "my_max" does not exist |
| LINE 1: select * from with_test1, my_max |
| ^ |
| with my_sum(total) as (select sum(total) from my_group_sum), |
| my_group_sum(i, total) as (select i, sum(total) from with_test1 group by i) |
| select * |
| from my_sum; |
| ERROR: relation "my_group_sum" does not exist |
| LINE 1: with my_sum(total) as (select sum(total) from my_group_sum), |
| ^ |
| DETAIL: There is a WITH item named "my_group_sum", but it cannot be referenced from this part of the query. |
| HINT: Re-order the WITH items to remove forward references. |
| -- two WITH clauses |
| with my_sum(total) as (select sum(total) from with_test1), |
| with my_group_sum(i, total) as (select i, sum(total) from with_test1 group by i) |
| select * |
| from my_sum; |
| ERROR: syntax error at or near "with" |
| LINE 2: with my_group_sum(i, total) as (select i, sum(total) from wi... |
| ^ |
| -- Test behavior with an unknown-type literal in the WITH |
| -- Remove undocumented IS [NOT] OF syntax |
| -- WITH q AS (SELECT 'foo' AS x) |
| -- SELECT x, x IS OF (unknown) as is_unknown, x IS OF (text) as is_text FROM q; |
| with cte(foo) as ( select 42 ) select * from ((select foo from cte)) q; |
| foo |
| ----- |
| 42 |
| (1 row) |
| |
| select ( with cte(foo) as ( values(i) ) |
| select (select foo from cte) ) |
| from with_test1 |
| order by 1 limit 10; |
| foo |
| ----- |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| (10 rows) |
| |
| select ( with cte(foo) as ( values(i) ) |
| values((select foo from cte)) ) |
| from with_test1 |
| order by 1 limit 10; |
| column1 |
| --------- |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| (10 rows) |
| |
| -- WITH query using Window functions |
| --begin_equivalent |
| with my_rank as (select i, t, value, rank() over (order by value) from with_test1) |
| select my_rank.* from with_test2, my_rank |
| where with_test2.i = my_rank.i |
| order by my_rank.i, my_rank.t, my_rank.value limit 100; -- order 1,2,3 |
| i | t | value | rank |
| ---+--------+-------+------ |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 20 | 71 |
| (100 rows) |
| |
| select my_rank.* from with_test2, (select i, t, value, rank() over (order by value) from with_test1) as my_rank |
| where with_test2.i = my_rank.i |
| order by my_rank.i, my_rank.t, my_rank.value limit 100; -- order 1,2,3 |
| i | t | value | rank |
| ---+--------+-------+------ |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 0 | 1 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 10 | 41 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text0 | 20 | 71 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 0 | 1 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 10 | 41 |
| 0 | text10 | 20 | 71 |
| (100 rows) |
| |
| --end_equivalent |
| -- WITH query and CSQ |
| --begin_equivalent |
| with my_group_sum(i, total) as (select i, sum(value) from with_test1 group by i) |
| select with_test2.* from with_test2 |
| where value < any (select total from my_group_sum where my_group_sum.i = with_test2.i); |
| i | t | value |
| ---+---------+------- |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 3 | text103 | 103 |
| 5 | text105 | 105 |
| 7 | text107 | 107 |
| 9 | text109 | 109 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 3 | text3 | 103 |
| 5 | text5 | 105 |
| 7 | text7 | 107 |
| 9 | text9 | 109 |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 3 | text103 | 103 |
| 5 | text105 | 105 |
| 7 | text107 | 107 |
| 9 | text109 | 109 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 2 | text102 | 102 |
| 4 | text104 | 104 |
| 6 | text106 | 106 |
| 8 | text108 | 108 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| 2 | text2 | 102 |
| 4 | text4 | 104 |
| 6 | text6 | 106 |
| 8 | text8 | 108 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 2 | text102 | 102 |
| 4 | text104 | 104 |
| 6 | text106 | 106 |
| 8 | text108 | 108 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| (64 rows) |
| |
| select with_test2.* from with_test2 |
| where value < any (select total from (select i, sum(value) as total from with_test1 group by i) as tmp where tmp.i = with_test2.i); |
| i | t | value |
| ---+---------+------- |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 3 | text103 | 103 |
| 5 | text105 | 105 |
| 7 | text107 | 107 |
| 9 | text109 | 109 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 3 | text3 | 103 |
| 5 | text5 | 105 |
| 7 | text7 | 107 |
| 9 | text9 | 109 |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 3 | text103 | 103 |
| 5 | text105 | 105 |
| 7 | text107 | 107 |
| 9 | text109 | 109 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 2 | text102 | 102 |
| 4 | text104 | 104 |
| 6 | text106 | 106 |
| 8 | text108 | 108 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| 2 | text2 | 102 |
| 4 | text4 | 104 |
| 6 | text6 | 106 |
| 8 | text8 | 108 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 2 | text102 | 102 |
| 4 | text104 | 104 |
| 6 | text106 | 106 |
| 8 | text108 | 108 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| (64 rows) |
| |
| --end_equivalent |
| --begin_equivalent |
| with my_group_sum(i, total) as (select i, sum(value) from with_test1 group by i) |
| select with_test2.* from with_test2, my_group_sum |
| where value < any (select total from my_group_sum where my_group_sum.i = with_test2.i) |
| and with_test2.i = my_group_sum.i; |
| i | t | value |
| ---+---------+------- |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 3 | text103 | 103 |
| 5 | text105 | 105 |
| 7 | text107 | 107 |
| 9 | text109 | 109 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 3 | text3 | 103 |
| 5 | text5 | 105 |
| 7 | text7 | 107 |
| 9 | text9 | 109 |
| 1 | text1 | 1 |
| 3 | text3 | 3 |
| 5 | text5 | 5 |
| 7 | text7 | 7 |
| 9 | text9 | 9 |
| 3 | text103 | 103 |
| 5 | text105 | 105 |
| 7 | text107 | 107 |
| 9 | text109 | 109 |
| 1 | text101 | 1 |
| 3 | text103 | 3 |
| 5 | text105 | 5 |
| 7 | text107 | 7 |
| 9 | text109 | 9 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 2 | text102 | 102 |
| 4 | text104 | 104 |
| 6 | text106 | 106 |
| 8 | text108 | 108 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| 2 | text2 | 102 |
| 4 | text4 | 104 |
| 6 | text6 | 106 |
| 8 | text8 | 108 |
| 0 | text0 | 0 |
| 2 | text2 | 2 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 8 | text8 | 8 |
| 2 | text102 | 102 |
| 4 | text104 | 104 |
| 6 | text106 | 106 |
| 8 | text108 | 108 |
| 0 | text100 | 0 |
| 2 | text102 | 2 |
| 4 | text104 | 4 |
| 6 | text106 | 6 |
| 8 | text108 | 8 |
| (64 rows) |
| |
| select with_test2.* from with_test2, (select i, sum(value) from with_test1 group by i) as my_group_sum |
| where value < any (select total from (select i, sum(value) as total from with_test1 group by i) as tmp where tmp.i = with_test2.i) |
| and with_test2.i = my_group_sum.i; |
| i | t | value |
| ---+---------+------- |
| 4 | text104 | 104 |
| 6 | text106 | 6 |
| 6 | text106 | 6 |
| 4 | text104 | 104 |
| 8 | text8 | 108 |
| 0 | text0 | 0 |
| 4 | text4 | 104 |
| 4 | text104 | 4 |
| 0 | text100 | 0 |
| 2 | text2 | 102 |
| 8 | text108 | 8 |
| 4 | text4 | 4 |
| 6 | text6 | 6 |
| 6 | text106 | 106 |
| 2 | text102 | 102 |
| 4 | text104 | 4 |
| 8 | text8 | 8 |
| 6 | text6 | 106 |
| 2 | text102 | 2 |
| 0 | text0 | 0 |
| 8 | text8 | 8 |
| 0 | text100 | 0 |
| 6 | text6 | 6 |
| 8 | text108 | 108 |
| 6 | text106 | 106 |
| 4 | text4 | 4 |
| 2 | text2 | 2 |
| 2 | text102 | 102 |
| 8 | text108 | 108 |
| 8 | text108 | 8 |
| 2 | text2 | 2 |
| 2 | text102 | 2 |
| 7 | text7 | 107 |
| 7 | text7 | 7 |
| 5 | text105 | 105 |
| 9 | text9 | 9 |
| 9 | text109 | 109 |
| 5 | text105 | 105 |
| 1 | text1 | 1 |
| 5 | text5 | 105 |
| 5 | text105 | 5 |
| 1 | text101 | 1 |
| 3 | text3 | 103 |
| 7 | text107 | 107 |
| 5 | text5 | 5 |
| 9 | text109 | 9 |
| 3 | text103 | 103 |
| 5 | text105 | 5 |
| 3 | text103 | 3 |
| 1 | text1 | 1 |
| 1 | text101 | 1 |
| 9 | text9 | 9 |
| 7 | text107 | 107 |
| 9 | text109 | 9 |
| 5 | text5 | 5 |
| 3 | text3 | 3 |
| 7 | text107 | 7 |
| 7 | text7 | 7 |
| 3 | text103 | 103 |
| 3 | text3 | 3 |
| 7 | text107 | 7 |
| 9 | text109 | 109 |
| 3 | text103 | 3 |
| 9 | text9 | 109 |
| (64 rows) |
| |
| --end_equivalent |
| --begin_equivalent |
| with my_group_sum(i, total) as (select i, sum(value) from with_test1 group by i) |
| select with_test2.* from with_test2 |
| where value < all (select total from my_group_sum where my_group_sum.i = with_test2.i) |
| order by 1,2,3 |
| limit 60; --order 1,2,3 |
| i | t | value |
| ---+---------+------- |
| 0 | text0 | 0 |
| 0 | text0 | 0 |
| 0 | text100 | 0 |
| 0 | text100 | 0 |
| 1 | text1 | 1 |
| 1 | text1 | 1 |
| 1 | text101 | 1 |
| 1 | text101 | 1 |
| 2 | text102 | 2 |
| 2 | text102 | 2 |
| 2 | text102 | 102 |
| 2 | text102 | 102 |
| 2 | text2 | 2 |
| 2 | text2 | 2 |
| 2 | text2 | 102 |
| 3 | text103 | 3 |
| 3 | text103 | 3 |
| 3 | text103 | 103 |
| 3 | text103 | 103 |
| 3 | text3 | 3 |
| 3 | text3 | 3 |
| 3 | text3 | 103 |
| 4 | text104 | 4 |
| 4 | text104 | 4 |
| 4 | text104 | 104 |
| 4 | text104 | 104 |
| 4 | text4 | 4 |
| 4 | text4 | 4 |
| 4 | text4 | 104 |
| 5 | text105 | 5 |
| 5 | text105 | 5 |
| 5 | text105 | 105 |
| 5 | text105 | 105 |
| 5 | text5 | 5 |
| 5 | text5 | 5 |
| 5 | text5 | 105 |
| 6 | text106 | 6 |
| 6 | text106 | 6 |
| 6 | text106 | 106 |
| 6 | text106 | 106 |
| 6 | text6 | 6 |
| 6 | text6 | 6 |
| 6 | text6 | 106 |
| 7 | text107 | 7 |
| 7 | text107 | 7 |
| 7 | text107 | 107 |
| 7 | text107 | 107 |
| 7 | text7 | 7 |
| 7 | text7 | 7 |
| 7 | text7 | 107 |
| 8 | text108 | 8 |
| 8 | text108 | 8 |
| 8 | text108 | 108 |
| 8 | text108 | 108 |
| 8 | text8 | 8 |
| 8 | text8 | 8 |
| 8 | text8 | 108 |
| 9 | text109 | 9 |
| 9 | text109 | 9 |
| 9 | text109 | 109 |
| (60 rows) |
| |
| select with_test2.* from with_test2 |
| where value < all (select total from (select i, sum(value) as total from with_test1 group by i) as tmp where tmp.i = with_test2.i) |
| order by 1,2,3 |
| limit 60; --order 1,2,3 |
| i | t | value |
| ---+---------+------- |
| 0 | text0 | 0 |
| 0 | text0 | 0 |
| 0 | text100 | 0 |
| 0 | text100 | 0 |
| 1 | text1 | 1 |
| 1 | text1 | 1 |
| 1 | text101 | 1 |
| 1 | text101 | 1 |
| 2 | text102 | 2 |
| 2 | text102 | 2 |
| 2 | text102 | 102 |
| 2 | text102 | 102 |
| 2 | text2 | 2 |
| 2 | text2 | 2 |
| 2 | text2 | 102 |
| 3 | text103 | 3 |
| 3 | text103 | 3 |
| 3 | text103 | 103 |
| 3 | text103 | 103 |
| 3 | text3 | 3 |
| 3 | text3 | 3 |
| 3 | text3 | 103 |
| 4 | text104 | 4 |
| 4 | text104 | 4 |
| 4 | text104 | 104 |
| 4 | text104 | 104 |
| 4 | text4 | 4 |
| 4 | text4 | 4 |
| 4 | text4 | 104 |
| 5 | text105 | 5 |
| 5 | text105 | 5 |
| 5 | text105 | 105 |
| 5 | text105 | 105 |
| 5 | text5 | 5 |
| 5 | text5 | 5 |
| 5 | text5 | 105 |
| 6 | text106 | 6 |
| 6 | text106 | 6 |
| 6 | text106 | 106 |
| 6 | text106 | 106 |
| 6 | text6 | 6 |
| 6 | text6 | 6 |
| 6 | text6 | 106 |
| 7 | text107 | 7 |
| 7 | text107 | 7 |
| 7 | text107 | 107 |
| 7 | text107 | 107 |
| 7 | text7 | 7 |
| 7 | text7 | 7 |
| 7 | text7 | 107 |
| 8 | text108 | 8 |
| 8 | text108 | 8 |
| 8 | text108 | 108 |
| 8 | text108 | 108 |
| 8 | text8 | 8 |
| 8 | text8 | 8 |
| 8 | text8 | 108 |
| 9 | text109 | 9 |
| 9 | text109 | 9 |
| 9 | text109 | 109 |
| (60 rows) |
| |
| --end_equivalent |
| drop table if exists d; |
| drop table if exists b; |
| NOTICE: table "b" does not exist, skipping |
| create table with_b (i integer) distributed by (i); |
| insert into with_b values (1), (2); |
| --begin_equivalent |
| with b1 as (select * from with_b) select * from (select * from b1 where b1.i =1) AS FOO, b1 FOO2; |
| i | i |
| ---+--- |
| 1 | 1 |
| 1 | 2 |
| (2 rows) |
| |
| select * from (select * from (select * from with_b) as b1 where b1.i = 1) AS FOO, (select * from with_b) as foo2; |
| i | i |
| ---+--- |
| 1 | 1 |
| 1 | 2 |
| (2 rows) |
| |
| --end_equivalent |
| -- qual push down test |
| explain (costs off) with t as (select * from with_test1) select * from t where i = 10; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Seq Scan on with_test1 |
| Filter: (i = 10) |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| |
| -- Test to validate an old bug which caused incorrect results when a subquery |
| -- in the WITH clause appears under a nested-loop join in the query plan when |
| -- gp_cte_sharing was set to off. (MPP-17848) |
| CREATE TABLE x (a integer) DISTRIBUTED BY (a); |
| insert into x values(1), (2); |
| CREATE TABLE y (m integer NOT NULL, n smallint) DISTRIBUTED BY (m); |
| insert into y values(10, 1); |
| insert into y values(20, 1); |
| with yy as ( |
| select m |
| from y, |
| (select 1 as p) iv |
| where n = iv.p |
| ) |
| select * from x, yy; |
| a | m |
| ---+---- |
| 1 | 10 |
| 1 | 20 |
| 2 | 10 |
| 2 | 20 |
| (4 rows) |
| |
| -- Check that WITH query is run to completion even if outer query isn't. |
| -- This is a test which exists in the upstream 'with' test suite in a section |
| -- which is currently under an ignore block. It has been copied here to avoid |
| -- merge conflicts since enabling it in the upstream test suite would require |
| -- altering the test output (as it depends on earlier tests which are failing |
| -- in GPDB currently). |
| DELETE FROM y; |
| INSERT INTO y SELECT generate_series(1,15) m; |
| WITH t AS ( |
| UPDATE y SET m = m * 100 RETURNING * |
| ) |
| SELECT m BETWEEN 100 AND 1500 FROM t LIMIT 1; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| SELECT * FROM y; |
| m | n |
| ------+--- |
| 600 | |
| 900 | |
| 1200 | |
| 300 | |
| 1000 | |
| 400 | |
| 700 | |
| 800 | |
| 100 | |
| 1300 | |
| 1400 | |
| 1500 | |
| 200 | |
| 500 | |
| 1100 | |
| (15 rows) |
| |
| -- Nested RECURSIVE queries with double self-referential joins are planned by |
| -- joining two WorkTableScans, which GPDB cannot do yet. Ensure that we error |
| -- out with a descriptive message. |
| WITH RECURSIVE r1 AS ( |
| SELECT 1 AS a |
| UNION ALL |
| ( |
| WITH RECURSIVE r2 AS ( |
| SELECT 2 AS b |
| UNION ALL |
| SELECT b FROM r1, r2 |
| ) |
| SELECT b FROM r2 |
| ) |
| ) |
| SELECT * FROM r1 LIMIT 1; |
| ERROR: joining nested RECURSIVE clauses is not supported |
| -- GPDB |
| -- Greenplum does not support window functions in recursive part's target list |
| -- See issue https://github.com/greenplum-db/gpdb/issues/13299 for details. |
| -- Previously the following SQL will PANIC or Assert Fail if compiled with assert. |
| create table t_window_ordered_set_agg_rte(a bigint, b bigint, c bigint); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| insert into t_window_ordered_set_agg_rte select i,i,i from generate_series(1, 10)i; |
| -- should error out during parse-analyze |
| with recursive rcte(x,y) as |
| ( |
| select a, b from t_window_ordered_set_agg_rte |
| union all |
| select (first_value(c) over (partition by b))::int, a+x |
| from rcte, |
| t_window_ordered_set_agg_rte as t |
| where t.b = x |
| ) |
| select * from rcte limit 10; |
| ERROR: window functions in the target list of a recursive query is not supported |
| LINE 5: select (first_value(c) over (partition by b))::int, a+x |
| ^ |
| -- should error out during parse-analyze |
| with recursive rcte(x,y) as |
| ( |
| select a, b from t_window_ordered_set_agg_rte |
| union all |
| select first_value(c) over (partition by b), a+x |
| from rcte, |
| t_window_ordered_set_agg_rte as t |
| where t.b = x |
| ) |
| select * from rcte limit 10; |
| ERROR: window functions in the target list of a recursive query is not supported |
| LINE 5: select first_value(c) over (partition by b), a+x |
| ^ |
| -- This used to deadlock, before the IPC between ShareInputScans across |
| -- slices was rewritten. |
| set gp_cte_sharing=on; |
| CREATE TEMP TABLE foo (i int, j int); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| INSERT INTO foo SELECT g, g FROM generate_series(1, 2) g; |
| ANALYZE foo; |
| WITH a1 as (select * from foo), |
| a2 as (select * from foo) |
| SELECT a1.i |
| FROM a1 |
| INNER JOIN a2 ON a2.i = a1.i |
| UNION ALL |
| SELECT count(a1.i) |
| FROM a1 |
| INNER JOIN a2 ON a2.i = a1.i; |
| i |
| --- |
| 2 |
| 2 |
| 1 |
| (3 rows) |
| |
| explain (costs off) |
| WITH a1 as (select * from foo), |
| a2 as (select * from foo) |
| SELECT a1.i |
| FROM a1 |
| INNER JOIN a2 ON a2.i = a1.i |
| UNION ALL |
| SELECT count(a1.i) |
| FROM a1 |
| INNER JOIN a2 ON a2.i = a1.i; |
| QUERY PLAN |
| ---------------------------------------------------------------------- |
| Append |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Subquery Scan on "*SELECT* 1" |
| -> Hash Join |
| Hash Cond: (share1_ref1.i = a2.i) |
| -> Shared Scan (share slice:id 1:1) |
| -> Seq Scan on foo |
| -> Hash |
| -> Subquery Scan on a2 |
| -> Shared Scan (share slice:id 1:0) |
| -> Seq Scan on foo foo_1 |
| -> Aggregate |
| -> Gather Motion 3:1 (slice2; segments: 3) |
| -> Hash Join |
| Hash Cond: (share1_ref2.i = a2_1.i) |
| -> Shared Scan (share slice:id 2:1) |
| -> Hash |
| -> Subquery Scan on a2_1 |
| -> Shared Scan (share slice:id 2:0) |
| Optimizer: Postgres query optimizer |
| (20 rows) |
| |
| -- Another cross-slice ShareInputScan test. There is one producing slice, |
| -- and two consumers in second slice. Make sure the Share Input Scan |
| -- consumer slice doesn't prematurely notify the producer that it's done, |
| -- when one of the Scans in the consumer slice finishes, but there are still |
| -- Scans left in the same slice. |
| explain (costs off) |
| WITH cte AS (SELECT * FROM foo) |
| -- This branch runs on different slice. It is the producer slice. |
| (SELECT DISTINCT 'a' as branch, j FROM cte) |
| UNION ALL |
| -- This branch runs in the consumer slice. It contains a join. A join |
| -- causes the input to be squelched when it reaches the end. |
| (SELECT 'b', x.i FROM cte x, cte y WHERE x.i = y.i) |
| UNION ALL |
| -- Sleep a bit between executing the previous slice and the next slice, |
| -- so that if the squelch from the join incorrectly sent a "done" message |
| -- to the producer slice, the producer has a chance to finish and remove |
| -- the tuplestore, before the next branch tries to open the shared |
| -- tuplestore again. |
| SELECT 'sleep', 1 where pg_sleep(1) is not null |
| UNION ALL |
| -- Consumer, runs in same slice as the join above. |
| SELECT 'c', j FROM cte; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Append |
| -> HashAggregate |
| Group Key: 'a'::text, cte.j |
| -> Redistribute Motion 3:3 (slice2; segments: 3) |
| Hash Key: 'a'::text, cte.j |
| -> Subquery Scan on cte |
| -> Shared Scan (share slice:id 2:0) |
| -> Seq Scan on foo |
| -> Hash Join |
| Hash Cond: (share0_ref3.i = y.i) |
| -> Shared Scan (share slice:id 1:0) |
| -> Hash |
| -> Subquery Scan on y |
| -> Shared Scan (share slice:id 1:0) |
| -> Result |
| One-Time Filter: (gp_execution_segment() = 2) |
| -> Result |
| One-Time Filter: (pg_sleep('1'::double precision) IS NOT NULL) |
| -> Subquery Scan on cte_1 |
| -> Shared Scan (share slice:id 1:0) |
| Optimizer: Postgres query optimizer |
| (22 rows) |
| |
| WITH cte AS (SELECT * FROM foo) |
| (SELECT DISTINCT 'a' as branch, j FROM cte) |
| UNION ALL |
| (SELECT 'b', x.i FROM cte x, cte y WHERE x.i = y.i) |
| UNION ALL |
| SELECT 'sleep', 1 where pg_sleep(1) is not null |
| UNION ALL |
| SELECT 'c', j FROM cte; |
| branch | j |
| --------+--- |
| b | 2 |
| c | 2 |
| a | 1 |
| a | 2 |
| b | 1 |
| sleep | 1 |
| c | 1 |
| (7 rows) |
| |
