other_config_regression.diffs
text/x-diff
Filename: other_config_regression.diffs
Type: text/x-diff
Part: 0
Patch
Format: context
| File | + | − |
|---|---|---|
| /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/results/groupingsets.out | 28 | 28 |
| /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/results/random.out | 4 | 3 |
| /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/results/select_parallel.out | 3 | 3 |
| /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/results/stats.out | 22 | 25 |
| /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/results/tsrf.out | 3 | 3 |
| /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/results/window.out | 158 | 158 |
| /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/results/with.out | 1 | 1 |
*** /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/expected/random.out Fri Jan 12 10:59:09 2018
--- /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/results/random.out Fri Jan 12 11:23:56 2018
***************
*** 46,52 ****
SELECT AVG(random) FROM RANDOM_TBL
HAVING AVG(random) NOT BETWEEN 80 AND 120;
! avg
! -----
! (0 rows)
--- 46,53 ----
SELECT AVG(random) FROM RANDOM_TBL
HAVING AVG(random) NOT BETWEEN 80 AND 120;
! avg
! ----------------------
! 13835058055282163712
! (1 row)
======================================================================
*** /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/expected/groupingsets.out Fri Jan 12 10:59:08 2018
--- /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/results/groupingsets.out Fri Jan 12 11:24:35 2018
***************
*** 143,156 ****
-- nesting with window functions
select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
from gstest2 group by rollup (a,b) order by rsum, a, b;
! a | b | sum | rsum
! ---+---+-----+------
! 1 | 1 | 8 | 8
! 1 | 2 | 2 | 10
! 1 | | 10 | 20
! 2 | 2 | 2 | 22
! 2 | | 2 | 24
! | | 12 | 36
(6 rows)
-- nesting with grouping sets
--- 143,156 ----
-- nesting with window functions
select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
from gstest2 group by rollup (a,b) order by rsum, a, b;
! a | b | sum | rsum
! ---+---+-----+--------------------------------
! 1 | 1 | 8 | 0
! 1 | 2 | 2 | 79507068872943528402422333440
! 1 | | 10 | 159014137745887056804844666880
! 2 | 2 | 2 | 238521206618830585207267000320
! 2 | | 2 | 318028275491774113609689333760
! | | 12 | 397535344364717642012111667200
(6 rows)
-- nesting with grouping sets
***************
*** 544,559 ****
select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
from gstest2 group by cube (a,b) order by rsum, a, b;
! a | b | sum | rsum
! ---+---+-----+------
! 1 | 1 | 8 | 8
! 1 | 2 | 2 | 10
! 1 | | 10 | 20
! 2 | 2 | 2 | 22
! 2 | | 2 | 24
! | 1 | 8 | 32
! | 2 | 4 | 36
! | | 12 | 48
(8 rows)
select a, b, sum(c) from (values (1,1,10),(1,1,11),(1,2,12),(1,2,13),(1,3,14),(2,3,15),(3,3,16),(3,4,17),(4,1,18),(4,1,19)) v(a,b,c) group by rollup (a,b);
--- 544,559 ----
select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
from gstest2 group by cube (a,b) order by rsum, a, b;
! a | b | sum | rsum
! ---+---+-----+--------------------------------
! 1 | 1 | 8 | 0
! 1 | 2 | 2 | 79523145874486552930199535616
! 1 | | 10 | 159046291748973105860399071232
! 2 | 2 | 2 | 238569437623459658790598606848
! 2 | | 2 | 318092583497946211720798142464
! | 1 | 8 | 397615729372432764650997678080
! | 2 | 4 | 477138875246919317581197213696
! | | 12 | 556662021121405870511396749312
(8 rows)
select a, b, sum(c) from (values (1,1,10),(1,1,11),(1,2,12),(1,2,13),(1,3,14),(2,3,15),(3,3,16),(3,4,17),(4,1,18),(4,1,19)) v(a,b,c) group by rollup (a,b);
***************
*** 1219,1234 ****
select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
from gstest2 group by cube (a,b) order by rsum, a, b;
! a | b | sum | rsum
! ---+---+-----+------
! 1 | 1 | 8 | 8
! 1 | 2 | 2 | 10
! 1 | | 10 | 20
! 2 | 2 | 2 | 22
! 2 | | 2 | 24
! | 1 | 8 | 32
! | 2 | 4 | 36
! | | 12 | 48
(8 rows)
explain (costs off)
--- 1219,1234 ----
select a, b, sum(c), sum(sum(c)) over (order by a,b) as rsum
from gstest2 group by cube (a,b) order by rsum, a, b;
! a | b | sum | rsum
! ---+---+-----+--------------------------------
! 1 | 1 | 8 | 0
! 1 | 2 | 2 | 79525570219379696134311116800
! 1 | | 10 | 159051140438759392268622233600
! 2 | 2 | 2 | 238576710658139088402933350400
! 2 | | 2 | 318102280877518784537244467200
! | 1 | 8 | 397627851096898480671555584000
! | 2 | 4 | 477153421316278176805866700800
! | | 12 | 556678991535657872940177817600
(8 rows)
explain (costs off)
======================================================================
*** /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/expected/tsrf.out Fri Jan 12 10:59:10 2018
--- /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/results/tsrf.out Fri Jan 12 11:24:55 2018
***************
*** 251,259 ****
SELECT SUM(count(*)) OVER(PARTITION BY generate_series(1,3) ORDER BY generate_series(1,3)), generate_series(1,3) g FROM few GROUP BY g;
sum | g
-----+---
! 3 | 1
! 3 | 2
! 3 | 3
(3 rows)
-- sorting + grouping
--- 251,259 ----
SELECT SUM(count(*)) OVER(PARTITION BY generate_series(1,3) ORDER BY generate_series(1,3)), generate_series(1,3) g FROM few GROUP BY g;
sum | g
-----+---
! 0 | 1
! 0 | 2
! 0 | 3
(3 rows)
-- sorting + grouping
======================================================================
*** /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/expected/select_parallel.out Fri Jan 12 10:59:09 2018
--- /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/results/select_parallel.out Fri Jan 12 11:25:27 2018
***************
*** 722,730 ****
(5 rows)
select avg(unique1::int8) from tenk1;
! avg
! -----------------------
! 4999.5000000000000000
(1 row)
-- gather merge test with a LIMIT
--- 722,730 ----
(5 rows)
select avg(unique1::int8) from tenk1;
! avg
! -------------------------------
! 79295891141614032158731083029
(1 row)
-- gather merge test with a LIMIT
======================================================================
*** /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/expected/window.out Fri Jan 12 10:59:10 2018
--- /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/results/window.out Fri Jan 12 11:25:38 2018
***************
*** 51,78 ****
-- with GROUP BY
SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten) FROM tenk1
GROUP BY four, ten ORDER BY four, ten;
! four | ten | sum | avg
! ------+-----+------+------------------------
! 0 | 0 | 0 | 0.00000000000000000000
! 0 | 2 | 0 | 2.0000000000000000
! 0 | 4 | 0 | 4.0000000000000000
! 0 | 6 | 0 | 6.0000000000000000
! 0 | 8 | 0 | 8.0000000000000000
! 1 | 1 | 2500 | 1.00000000000000000000
! 1 | 3 | 2500 | 3.0000000000000000
! 1 | 5 | 2500 | 5.0000000000000000
! 1 | 7 | 2500 | 7.0000000000000000
! 1 | 9 | 2500 | 9.0000000000000000
! 2 | 0 | 5000 | 0.00000000000000000000
! 2 | 2 | 5000 | 2.0000000000000000
! 2 | 4 | 5000 | 4.0000000000000000
! 2 | 6 | 5000 | 6.0000000000000000
! 2 | 8 | 5000 | 8.0000000000000000
! 3 | 1 | 7500 | 1.00000000000000000000
! 3 | 3 | 7500 | 3.0000000000000000
! 3 | 5 | 7500 | 5.0000000000000000
! 3 | 7 | 7500 | 7.0000000000000000
! 3 | 9 | 7500 | 9.0000000000000000
(20 rows)
SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname);
--- 51,78 ----
-- with GROUP BY
SELECT four, ten, SUM(SUM(four)) OVER (PARTITION BY four), AVG(ten) FROM tenk1
GROUP BY four, ten ORDER BY four, ten;
! four | ten | sum | avg
! ------+-----+--------------------------------+------------------------
! 0 | 0 | 318029204617379618212385128448 | 0.00000000000000000000
! 0 | 2 | 318029204617379618212385128448 | 2.0000000000000000
! 0 | 4 | 318029204617379618212385128448 | 4.0000000000000000
! 0 | 6 | 318029204617379618212385128448 | 6.0000000000000000
! 0 | 8 | 318029204617379618212385128448 | 8.0000000000000000
! 1 | 1 | 318029204617379618212385128448 | 1.00000000000000000000
! 1 | 3 | 318029204617379618212385128448 | 3.0000000000000000
! 1 | 5 | 318029204617379618212385128448 | 5.0000000000000000
! 1 | 7 | 318029204617379618212385128448 | 7.0000000000000000
! 1 | 9 | 318029204617379618212385128448 | 9.0000000000000000
! 2 | 0 | 318029204617379618212385128448 | 0.00000000000000000000
! 2 | 2 | 318029204617379618212385128448 | 2.0000000000000000
! 2 | 4 | 318029204617379618212385128448 | 4.0000000000000000
! 2 | 6 | 318029204617379618212385128448 | 6.0000000000000000
! 2 | 8 | 318029204617379618212385128448 | 8.0000000000000000
! 3 | 1 | 318029204617379618212385128448 | 1.00000000000000000000
! 3 | 3 | 318029204617379618212385128448 | 3.0000000000000000
! 3 | 5 | 318029204617379618212385128448 | 5.0000000000000000
! 3 | 7 | 318029204617379618212385128448 | 7.0000000000000000
! 3 | 9 | 318029204617379618212385128448 | 9.0000000000000000
(20 rows)
SELECT depname, empno, salary, sum(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname);
***************
*** 411,428 ****
SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER (PARTITION BY two ORDER BY ten) AS wsum
FROM tenk1 GROUP BY ten, two;
! ten | two | gsum | wsum
! -----+-----+-------+--------
! 0 | 0 | 45000 | 45000
! 2 | 0 | 47000 | 92000
! 4 | 0 | 49000 | 141000
! 6 | 0 | 51000 | 192000
! 8 | 0 | 53000 | 245000
! 1 | 1 | 46000 | 46000
! 3 | 1 | 48000 | 94000
! 5 | 1 | 50000 | 144000
! 7 | 1 | 52000 | 196000
! 9 | 1 | 54000 | 250000
(10 rows)
SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10;
--- 411,428 ----
SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER (PARTITION BY two ORDER BY ten) AS wsum
FROM tenk1 GROUP BY ten, two;
! ten | two | gsum | wsum
! -----+-----+-------+--------------------------------
! 0 | 0 | 45000 | 0
! 2 | 0 | 47000 | 79507287872689171482219118592
! 4 | 0 | 49000 | 159014575745378342964438237184
! 6 | 0 | 51000 | 238521863618067514446657355776
! 8 | 0 | 53000 | 318029151490756685928876474368
! 1 | 1 | 46000 | 0
! 3 | 1 | 48000 | 79507287872689171482219118592
! 5 | 1 | 50000 | 159014575745378342964438237184
! 7 | 1 | 52000 | 238521863618067514446657355776
! 9 | 1 | 54000 | 318029151490756685928876474368
(10 rows)
SELECT count(*) OVER (PARTITION BY four), four FROM (SELECT * FROM tenk1 WHERE two = 1)s WHERE unique2 < 10;
***************
*** 483,500 ****
SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER win AS wsum
FROM tenk1 GROUP BY ten, two WINDOW win AS (PARTITION BY two ORDER BY ten);
! ten | two | gsum | wsum
! -----+-----+-------+--------
! 0 | 0 | 45000 | 45000
! 2 | 0 | 47000 | 92000
! 4 | 0 | 49000 | 141000
! 6 | 0 | 51000 | 192000
! 8 | 0 | 53000 | 245000
! 1 | 1 | 46000 | 46000
! 3 | 1 | 48000 | 94000
! 5 | 1 | 50000 | 144000
! 7 | 1 | 52000 | 196000
! 9 | 1 | 54000 | 250000
(10 rows)
-- more than one window with GROUP BY
--- 483,500 ----
SELECT ten, two, sum(hundred) AS gsum, sum(sum(hundred)) OVER win AS wsum
FROM tenk1 GROUP BY ten, two WINDOW win AS (PARTITION BY two ORDER BY ten);
! ten | two | gsum | wsum
! -----+-----+-------+--------------------------------
! 0 | 0 | 45000 | 0
! 2 | 0 | 47000 | 79507287872689171482219118592
! 4 | 0 | 49000 | 159014575745378342964438237184
! 6 | 0 | 51000 | 238521863618067514446657355776
! 8 | 0 | 53000 | 318029151490756685928876474368
! 1 | 1 | 46000 | 0
! 3 | 1 | 48000 | 79507287872689171482219118592
! 5 | 1 | 50000 | 159014575745378342964438237184
! 7 | 1 | 52000 | 238521863618067514446657355776
! 9 | 1 | 54000 | 318029151490756685928876474368
(10 rows)
-- more than one window with GROUP BY
***************
*** 502,512 ****
row_number() OVER (ORDER BY depname),
sum(sum(salary)) OVER (ORDER BY depname DESC)
FROM empsalary GROUP BY depname;
! sum | row_number | sum
! -------+------------+-------
! 14600 | 3 | 14600
! 7400 | 2 | 22000
! 25100 | 1 | 47100
(3 rows)
-- identical windows with different names
--- 502,512 ----
row_number() OVER (ORDER BY depname),
sum(sum(salary)) OVER (ORDER BY depname DESC)
FROM empsalary GROUP BY depname;
! sum | row_number | sum
! -------+------------+--------------------------------
! 14600 | 3 | 0
! 7400 | 2 | 79507287872689171482219118592
! 25100 | 1 | 159014575745378342964438237184
(3 rows)
-- identical windows with different names
***************
*** 1027,1037 ****
) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum",
depname
FROM empsalary GROUP BY depname;
! sum | row_number | filtered_sum | depname
! -------+------------+--------------+-----------
! 14600 | 3 | | sales
! 7400 | 2 | 3500 | personnel
! 25100 | 1 | 22600 | develop
(3 rows)
-- Test pushdown of quals into a subquery containing window functions
--- 1027,1037 ----
) FILTER (WHERE depname <> 'sales') OVER (ORDER BY depname DESC) AS "filtered_sum",
depname
FROM empsalary GROUP BY depname;
! sum | row_number | filtered_sum | depname
! -------+------------+-------------------------------+-----------
! 14600 | 3 | | sales
! 7400 | 2 | 0 | personnel
! 25100 | 1 | 79507287872689171482219118592 | develop
(3 rows)
-- Test pushdown of quals into a subquery containing window functions
***************
*** 1341,1352 ****
-- test inverse transition functions handle NULLs properly
SELECT i,AVG(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
! i | avg
! ---+--------------------
! 1 | 1.5000000000000000
! 2 | 2.0000000000000000
! 3 |
! 4 |
(4 rows)
SELECT i,AVG(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
--- 1341,1352 ----
-- test inverse transition functions handle NULLs properly
SELECT i,AVG(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
! i | avg
! ---+-------------------------------
! 1 | 39753643936344585741109559296
! 2 | 0.00000000000000000000
! 3 |
! 4 |
(4 rows)
SELECT i,AVG(v::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
***************
*** 1411,1422 ****
SELECT i,SUM(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
! i | sum
! ---+-----
! 1 | 3
! 2 | 2
! 3 |
! 4 |
(4 rows)
SELECT i,SUM(v::money) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
--- 1411,1422 ----
SELECT i,SUM(v::bigint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,1),(2,2),(3,NULL),(4,NULL)) t(i,v);
! i | sum
! ---+-------------------------------
! 1 | 79507287872689171482219118592
! 2 | 0
! 3 |
! 4 |
(4 rows)
SELECT i,SUM(v::money) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
***************
*** 1491,1514 ****
SELECT VAR_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! var_pop
! -----------------------
! 21704.000000000000
! 13868.750000000000
! 11266.666666666667
! 4225.0000000000000000
! 0
(5 rows)
SELECT VAR_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! var_pop
! -----------------------
! 21704.000000000000
! 13868.750000000000
! 11266.666666666667
! 4225.0000000000000000
! 0
(5 rows)
SELECT VAR_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
--- 1491,1514 ----
SELECT VAR_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! var_pop
! ---------
! 0
! 0
! 0
! 0
! 0
(5 rows)
SELECT VAR_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! var_pop
! ---------
! 0
! 0
! 0
! 0
! 0
(5 rows)
SELECT VAR_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
***************
*** 1535,1558 ****
SELECT VAR_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! var_samp
! -----------------------
! 27130.000000000000
! 18491.666666666667
! 16900.000000000000
! 8450.0000000000000000
!
(5 rows)
SELECT VAR_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! var_samp
! -----------------------
! 27130.000000000000
! 18491.666666666667
! 16900.000000000000
! 8450.0000000000000000
!
(5 rows)
SELECT VAR_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
--- 1535,1558 ----
SELECT VAR_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! var_samp
! ----------
! 0
! 0
! 0
! 0
!
(5 rows)
SELECT VAR_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! var_samp
! ----------
! 0
! 0
! 0
! 0
!
(5 rows)
SELECT VAR_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
***************
*** 1579,1602 ****
SELECT VARIANCE(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! variance
! -----------------------
! 27130.000000000000
! 18491.666666666667
! 16900.000000000000
! 8450.0000000000000000
!
(5 rows)
SELECT VARIANCE(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! variance
! -----------------------
! 27130.000000000000
! 18491.666666666667
! 16900.000000000000
! 8450.0000000000000000
!
(5 rows)
SELECT VARIANCE(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
--- 1579,1602 ----
SELECT VARIANCE(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! variance
! ----------
! 0
! 0
! 0
! 0
!
(5 rows)
SELECT VARIANCE(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! variance
! ----------
! 0
! 0
! 0
! 0
!
(5 rows)
SELECT VARIANCE(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
***************
*** 1624,1649 ****
SELECT STDDEV_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
! stddev_pop
! ---------------------
! 147.322774885623
! 147.322774885623
! 117.765657133139
! 106.144555520604
! 65.0000000000000000
! 0
(6 rows)
SELECT STDDEV_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
! stddev_pop
! ---------------------
! 147.322774885623
! 147.322774885623
! 117.765657133139
! 106.144555520604
! 65.0000000000000000
! 0
(6 rows)
SELECT STDDEV_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
--- 1624,1649 ----
SELECT STDDEV_POP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
! stddev_pop
! ------------
! 0
! 0
! 0
! 0
! 0
! 0
(6 rows)
SELECT STDDEV_POP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
! stddev_pop
! ------------
! 0
! 0
! 0
! 0
! 0
! 0
(6 rows)
SELECT STDDEV_POP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
***************
*** 1672,1697 ****
SELECT STDDEV_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
! stddev_samp
! ---------------------
! 164.711869639076
! 164.711869639076
! 135.984067694222
! 130.000000000000
! 91.9238815542511782
!
(6 rows)
SELECT STDDEV_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
! stddev_samp
! ---------------------
! 164.711869639076
! 164.711869639076
! 135.984067694222
! 130.000000000000
! 91.9238815542511782
!
(6 rows)
SELECT STDDEV_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
--- 1672,1697 ----
SELECT STDDEV_SAMP(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
! stddev_samp
! -------------
! 0
! 0
! 0
! 0
! 0
!
(6 rows)
SELECT STDDEV_SAMP(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(1,NULL),(2,600),(3,470),(4,170),(5,430),(6,300)) r(i,n);
! stddev_samp
! -------------
! 0
! 0
! 0
! 0
! 0
!
(6 rows)
SELECT STDDEV_SAMP(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
***************
*** 1720,1745 ****
SELECT STDDEV(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! stddev
! ---------------------
! 164.711869639076
! 164.711869639076
! 135.984067694222
! 130.000000000000
! 91.9238815542511782
!
(6 rows)
SELECT STDDEV(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! stddev
! ---------------------
! 164.711869639076
! 164.711869639076
! 135.984067694222
! 130.000000000000
! 91.9238815542511782
!
(6 rows)
SELECT STDDEV(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
--- 1720,1745 ----
SELECT STDDEV(n::int) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! stddev
! --------
! 0
! 0
! 0
! 0
! 0
!
(6 rows)
SELECT STDDEV(n::smallint) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM (VALUES(0,NULL),(1,600),(2,470),(3,170),(4,430),(5,300)) r(i,n);
! stddev
! --------
! 0
! 0
! 0
! 0
! 0
!
(6 rows)
SELECT STDDEV(n::numeric) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
======================================================================
*** /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/expected/with.out Fri Jan 12 10:59:10 2018
--- /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/results/with.out Fri Jan 12 11:25:57 2018
***************
*** 285,291 ****
SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub);
count
-------
! 50
(1 row)
-- via a VIEW
--- 285,291 ----
SELECT count(*) FROM q1 WHERE y > (SELECT sum(y)/100 FROM q1 qsub);
count
-------
! 0
(1 row)
-- via a VIEW
======================================================================
*** /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/expected/stats.out Fri Jan 12 10:59:10 2018
--- /home/buildfarm/mpolyakova/postgresql_master_other_config/src/test/regress/results/stats.out Fri Jan 12 11:26:24 2018
***************
*** 25,30 ****
--- 25,31 ----
FROM pg_catalog.pg_stat_user_tables AS t,
pg_catalog.pg_statio_user_tables AS b
WHERE t.relname='tenk2' AND b.relname='tenk2';
+ ERROR: bigint out of range
-- function to wait for counters to advance
create function wait_for_stats() returns void as $$
declare
***************
*** 154,164 ****
\c -
-- wait for stats collector to update
SELECT wait_for_stats();
! wait_for_stats
! ----------------
!
! (1 row)
!
-- check effects
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
--- 155,166 ----
\c -
-- wait for stats collector to update
SELECT wait_for_stats();
! ERROR: relation "prevstats" does not exist
! LINE 1: ... FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats ...
! ^
! QUERY: SELECT (st.seq_scan >= pr.seq_scan + 1) FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
! WHERE st.relname='tenk2' AND cl.relname='tenk2'
! CONTEXT: PL/pgSQL function wait_for_stats() line 18 at SQL statement
-- check effects
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
***************
*** 165,175 ****
WHERE relname like 'trunc_stats_test%' order by relname;
relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
-------------------+-----------+-----------+-----------+------------+------------
! trunc_stats_test | 3 | 0 | 0 | 0 | 0
! trunc_stats_test1 | 4 | 2 | 1 | 1 | 0
! trunc_stats_test2 | 1 | 0 | 0 | 1 | 0
! trunc_stats_test3 | 4 | 0 | 0 | 2 | 2
! trunc_stats_test4 | 2 | 0 | 0 | 0 | 2
(5 rows)
SELECT st.seq_scan >= pr.seq_scan + 1,
--- 167,177 ----
WHERE relname like 'trunc_stats_test%' order by relname;
relname | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_dead_tup
-------------------+-----------+-----------+-----------+------------+------------
! trunc_stats_test | 0 | 0 | 0 | 0 | 0
! trunc_stats_test1 | 0 | 0 | 0 | 0 | 0
! trunc_stats_test2 | 0 | 0 | 0 | 0 | 0
! trunc_stats_test3 | 0 | 0 | 0 | 0 | 0
! trunc_stats_test4 | 0 | 0 | 0 | 0 | 0
(5 rows)
SELECT st.seq_scan >= pr.seq_scan + 1,
***************
*** 178,204 ****
st.idx_tup_fetch >= pr.idx_tup_fetch + 1
FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
WHERE st.relname='tenk2' AND cl.relname='tenk2';
! ?column? | ?column? | ?column? | ?column?
! ----------+----------+----------+----------
! t | t | t | t
! (1 row)
!
SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
WHERE st.relname='tenk2' AND cl.relname='tenk2';
! ?column? | ?column?
! ----------+----------
! t | t
! (1 row)
!
SELECT pr.snap_ts < pg_stat_get_snapshot_timestamp() as snapshot_newer
FROM prevstats AS pr;
! snapshot_newer
! ----------------
! t
! (1 row)
!
DROP TABLE trunc_stats_test, trunc_stats_test1, trunc_stats_test2, trunc_stats_test3, trunc_stats_test4;
DROP TABLE prevstats;
-- End of Stats Test
--- 180,201 ----
st.idx_tup_fetch >= pr.idx_tup_fetch + 1
FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
WHERE st.relname='tenk2' AND cl.relname='tenk2';
! ERROR: relation "prevstats" does not exist
! LINE 5: FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats ...
! ^
SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
WHERE st.relname='tenk2' AND cl.relname='tenk2';
! ERROR: relation "prevstats" does not exist
! LINE 3: ...FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats ...
! ^
SELECT pr.snap_ts < pg_stat_get_snapshot_timestamp() as snapshot_newer
FROM prevstats AS pr;
! ERROR: relation "prevstats" does not exist
! LINE 2: FROM prevstats AS pr;
! ^
DROP TABLE trunc_stats_test, trunc_stats_test1, trunc_stats_test2, trunc_stats_test3, trunc_stats_test4;
DROP TABLE prevstats;
+ ERROR: table "prevstats" does not exist
-- End of Stats Test
======================================================================