Changeset: c7f4a90c1c78 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=c7f4a90c1c78
Modified Files:
sql/test/pg_regress/Tests/aggregates.stable.out
sql/test/pg_regress/Tests/alter_table.stable.out
sql/test/pg_regress/Tests/arrays.stable.out
sql/test/pg_regress/Tests/bit.stable.out
sql/test/pg_regress/Tests/boolean.stable.out
sql/test/pg_regress/Tests/box.stable.out
sql/test/pg_regress/Tests/btree_index.stable.out
sql/test/pg_regress/Tests/case.stable.out
sql/test/pg_regress/Tests/circle.stable.out
sql/test/pg_regress/Tests/cluster.stable.out
sql/test/pg_regress/Tests/comments.stable.out
sql/test/pg_regress/Tests/conversion.stable.out
sql/test/pg_regress/Tests/copy2.stable.out
sql/test/pg_regress/Tests/create_aggregate.stable.out
sql/test/pg_regress/Tests/create_index.stable.out
sql/test/pg_regress/Tests/create_misc.stable.out
sql/test/pg_regress/Tests/create_operator.stable.out
sql/test/pg_regress/Tests/create_table.stable.out
sql/test/pg_regress/Tests/create_type.stable.out
sql/test/pg_regress/Tests/create_view.stable.out
sql/test/pg_regress/Tests/date.stable.out
sql/test/pg_regress/Tests/domain.stable.out
sql/test/pg_regress/Tests/errors.stable.out
sql/test/pg_regress/Tests/float4.stable.out
sql/test/pg_regress/Tests/float8.stable.out
sql/test/pg_regress/Tests/foreign_key.stable.out
sql/test/pg_regress/Tests/geometry.stable.out
sql/test/pg_regress/Tests/hash_index.stable.out
sql/test/pg_regress/Tests/horology.stable.out
sql/test/pg_regress/Tests/inet.stable.out
sql/test/pg_regress/Tests/inherit.stable.out
sql/test/pg_regress/Tests/insert.stable.out
sql/test/pg_regress/Tests/int2.stable.out
sql/test/pg_regress/Tests/int4.stable.out
sql/test/pg_regress/Tests/int8.stable.out
sql/test/pg_regress/Tests/interval.stable.out
sql/test/pg_regress/Tests/join.stable.out
sql/test/pg_regress/Tests/limit.stable.out
sql/test/pg_regress/Tests/lseg.stable.out
sql/test/pg_regress/Tests/mule_internal.stable.out
sql/test/pg_regress/Tests/name.stable.out
sql/test/pg_regress/Tests/namespace.stable.out
sql/test/pg_regress/Tests/numeric.stable.out
sql/test/pg_regress/Tests/numeric_big.stable.out
sql/test/pg_regress/Tests/numerology.stable.out
sql/test/pg_regress/Tests/oid.stable.out
sql/test/pg_regress/Tests/oidjoins.stable.out
sql/test/pg_regress/Tests/opr_sanity.stable.out
sql/test/pg_regress/Tests/path.stable.out
sql/test/pg_regress/Tests/plpgsql.stable.out
sql/test/pg_regress/Tests/point.stable.out
sql/test/pg_regress/Tests/polygon.stable.out
sql/test/pg_regress/Tests/polymorphism.stable.out
sql/test/pg_regress/Tests/portals.stable.out
sql/test/pg_regress/Tests/portals_p2.stable.out
sql/test/pg_regress/Tests/prepare.stable.out
sql/test/pg_regress/Tests/privileges.stable.out
sql/test/pg_regress/Tests/random.stable.out
sql/test/pg_regress/Tests/rangefuncs.stable.out
sql/test/pg_regress/Tests/reltime.stable.out
sql/test/pg_regress/Tests/rowtypes.stable.out
sql/test/pg_regress/Tests/rules.stable.out
sql/test/pg_regress/Tests/sanity_check.stable.out
sql/test/pg_regress/Tests/select.stable.out
sql/test/pg_regress/Tests/select_distinct.stable.out
sql/test/pg_regress/Tests/select_distinct_on.stable.out
sql/test/pg_regress/Tests/select_having.stable.out
sql/test/pg_regress/Tests/select_implicit.stable.out
sql/test/pg_regress/Tests/select_into.stable.out
sql/test/pg_regress/Tests/select_views.stable.out
sql/test/pg_regress/Tests/sequence.stable.out
sql/test/pg_regress/Tests/sql_ascii.stable.out
sql/test/pg_regress/Tests/stats.stable.out
sql/test/pg_regress/Tests/strings.stable.out
sql/test/pg_regress/Tests/subselect.stable.out
sql/test/pg_regress/Tests/temp.stable.out
sql/test/pg_regress/Tests/time.stable.out
sql/test/pg_regress/Tests/timestamp.stable.out
sql/test/pg_regress/Tests/timestamptz.stable.out
sql/test/pg_regress/Tests/timetz.stable.out
sql/test/pg_regress/Tests/tinterval.stable.out
sql/test/pg_regress/Tests/transactions.stable.out
sql/test/pg_regress/Tests/triggers.stable.out
sql/test/pg_regress/Tests/truncate.stable.out
sql/test/pg_regress/Tests/type_sanity.stable.out
sql/test/pg_regress/Tests/union.stable.out
sql/test/pg_regress/Tests/update.stable.out
sql/test/pg_regress/Tests/vacuum.stable.out
sql/test/pg_regress/Tests/without_oid.stable.out
Branch: default
Log Message:
pg_regress: insert provided expected output into stable output of all new tests
executed:
for i in `tail -n+12 sql/test/pg_regress/Tests/All | egrep -v '^#'` ; do echo
$i ; ( head -n28 sql/test/pg_regress/Tests/$i.stable.out ; cat
sql/test/pg_regress/expected/$i.out ; tail -n7
sql/test/pg_regress/Tests/$i.stable.out ) >
sql/test/pg_regress/Tests/$i.stable.out. && \mv -f
sql/test/pg_regress/Tests/$i.stable.out{.,} ; done
diffs (truncated from 38714 to 300 lines):
diff --git a/sql/test/pg_regress/Tests/aggregates.stable.out
b/sql/test/pg_regress/Tests/aggregates.stable.out
--- a/sql/test/pg_regress/Tests/aggregates.stable.out
+++ b/sql/test/pg_regress/Tests/aggregates.stable.out
@@ -26,6 +26,301 @@ Ready.
= ! Correct / expected output still needs to be provided / verified / approved
! =
+--
+-- AGGREGATES
+--
+SELECT avg(four) AS avg_1 FROM onek;
+ avg_1
+--------------------
+ 1.5000000000000000
+(1 row)
+
+SELECT avg(a) AS avg_32 FROM aggtest WHERE a < 100;
+ avg_32
+---------------------
+ 32.6666666666666667
+(1 row)
+
+-- In 7.1, avg(float4) is computed using float8 arithmetic.
+-- Round the result to 3 digits to avoid platform-specific results.
+SELECT avg(b)::numeric(10,3) AS avg_107_943 FROM aggtest;
+ avg_107_943
+-------------
+ 107.943
+(1 row)
+
+SELECT avg(gpa) AS avg_3_4 FROM ONLY student;
+ avg_3_4
+---------
+ 3.4
+(1 row)
+
+SELECT sum(four) AS sum_1500 FROM onek;
+ sum_1500
+----------
+ 1500
+(1 row)
+
+SELECT sum(a) AS sum_198 FROM aggtest;
+ sum_198
+---------
+ 198
+(1 row)
+
+SELECT sum(b) AS avg_431_773 FROM aggtest;
+ avg_431_773
+-------------
+ 431.773
+(1 row)
+
+SELECT sum(gpa) AS avg_6_8 FROM ONLY student;
+ avg_6_8
+---------
+ 6.8
+(1 row)
+
+SELECT max(four) AS max_3 FROM onek;
+ max_3
+-------
+ 3
+(1 row)
+
+SELECT max(a) AS max_100 FROM aggtest;
+ max_100
+---------
+ 100
+(1 row)
+
+SELECT max(aggtest.b) AS max_324_78 FROM aggtest;
+ max_324_78
+------------
+ 324.78
+(1 row)
+
+SELECT max(student.gpa) AS max_3_7 FROM student;
+ max_3_7
+---------
+ 3.7
+(1 row)
+
+SELECT count(four) AS cnt_1000 FROM onek;
+ cnt_1000
+----------
+ 1000
+(1 row)
+
+SELECT count(DISTINCT four) AS cnt_4 FROM onek;
+ cnt_4
+-------
+ 4
+(1 row)
+
+select ten, count(*), sum(four) from onek
+group by ten order by ten;
+ ten | count | sum
+-----+-------+-----
+ 0 | 100 | 100
+ 1 | 100 | 200
+ 2 | 100 | 100
+ 3 | 100 | 200
+ 4 | 100 | 100
+ 5 | 100 | 200
+ 6 | 100 | 100
+ 7 | 100 | 200
+ 8 | 100 | 100
+ 9 | 100 | 200
+(10 rows)
+
+select ten, count(four), sum(DISTINCT four) from onek
+group by ten order by ten;
+ ten | count | sum
+-----+-------+-----
+ 0 | 100 | 2
+ 1 | 100 | 4
+ 2 | 100 | 2
+ 3 | 100 | 4
+ 4 | 100 | 2
+ 5 | 100 | 4
+ 6 | 100 | 2
+ 7 | 100 | 4
+ 8 | 100 | 2
+ 9 | 100 | 4
+(10 rows)
+
+SELECT newavg(four) AS avg_1 FROM onek;
+ avg_1
+--------------------
+ 1.5000000000000000
+(1 row)
+
+SELECT newsum(four) AS sum_1500 FROM onek;
+ sum_1500
+----------
+ 1500
+(1 row)
+
+SELECT newcnt(four) AS cnt_1000 FROM onek;
+ cnt_1000
+----------
+ 1000
+(1 row)
+
+-- test for outer-level aggregates
+-- this should work
+select ten, sum(distinct four) from onek a
+group by ten
+having exists (select 1 from onek b where sum(distinct a.four) = b.four);
+ ten | sum
+-----+-----
+ 0 | 2
+ 2 | 2
+ 4 | 2
+ 6 | 2
+ 8 | 2
+(5 rows)
+
+-- this should fail because subquery has an agg of its own in WHERE
+select ten, sum(distinct four) from onek a
+group by ten
+having exists (select 1 from onek b
+ where sum(distinct a.four + b.four) = b.four);
+ERROR: aggregates not allowed in WHERE clause
+--
+-- test for bitwise integer aggregates
+--
+CREATE TEMPORARY TABLE bitwise_test(
+ i2 INT2,
+ i4 INT4,
+ i8 INT8,
+ i INTEGER,
+ x INT2,
+ y BIT(4)
+);
+-- empty case
+SELECT
+ BIT_AND(i2) AS "?",
+ BIT_OR(i4) AS "?"
+FROM bitwise_test;
+ ? | ?
+---+---
+ |
+(1 row)
+
+COPY bitwise_test FROM STDIN NULL 'null';
+SELECT
+ BIT_AND(i2) AS "1",
+ BIT_AND(i4) AS "1",
+ BIT_AND(i8) AS "1",
+ BIT_AND(i) AS "?",
+ BIT_AND(x) AS "0",
+ BIT_AND(y) AS "0100",
+ BIT_OR(i2) AS "7",
+ BIT_OR(i4) AS "7",
+ BIT_OR(i8) AS "7",
+ BIT_OR(i) AS "?",
+ BIT_OR(x) AS "7",
+ BIT_OR(y) AS "1101"
+FROM bitwise_test;
+ 1 | 1 | 1 | ? | 0 | 0100 | 7 | 7 | 7 | ? | 7 | 1101
+---+---+---+---+---+------+---+---+---+---+---+------
+ 1 | 1 | 1 | 1 | 0 | 0100 | 7 | 7 | 7 | 3 | 7 | 1101
+(1 row)
+
+--
+-- test boolean aggregates
+--
+-- first test all possible transition and final states
+SELECT
+ -- boolean and transitions
+ -- null because strict
+ booland_statefunc(NULL, NULL) IS NULL AS "t",
+ booland_statefunc(TRUE, NULL) IS NULL AS "t",
+ booland_statefunc(FALSE, NULL) IS NULL AS "t",
+ booland_statefunc(NULL, TRUE) IS NULL AS "t",
+ booland_statefunc(NULL, FALSE) IS NULL AS "t",
+ -- and actual computations
+ booland_statefunc(TRUE, TRUE) AS "t",
+ NOT booland_statefunc(TRUE, FALSE) AS "t",
+ NOT booland_statefunc(FALSE, TRUE) AS "t",
+ NOT booland_statefunc(FALSE, FALSE) AS "t";
+ t | t | t | t | t | t | t | t | t
+---+---+---+---+---+---+---+---+---
+ t | t | t | t | t | t | t | t | t
+(1 row)
+
+SELECT
+ -- boolean or transitions
+ -- null because strict
+ boolor_statefunc(NULL, NULL) IS NULL AS "t",
+ boolor_statefunc(TRUE, NULL) IS NULL AS "t",
+ boolor_statefunc(FALSE, NULL) IS NULL AS "t",
+ boolor_statefunc(NULL, TRUE) IS NULL AS "t",
+ boolor_statefunc(NULL, FALSE) IS NULL AS "t",
+ -- actual computations
+ boolor_statefunc(TRUE, TRUE) AS "t",
+ boolor_statefunc(TRUE, FALSE) AS "t",
+ boolor_statefunc(FALSE, TRUE) AS "t",
+ NOT boolor_statefunc(FALSE, FALSE) AS "t";
+ t | t | t | t | t | t | t | t | t
+---+---+---+---+---+---+---+---+---
+ t | t | t | t | t | t | t | t | t
+(1 row)
+
+CREATE TEMPORARY TABLE bool_test(
+ b1 BOOL,
+ b2 BOOL,
+ b3 BOOL,
+ b4 BOOL);
+-- empty case
+SELECT
+ BOOL_AND(b1) AS "n",
+ BOOL_OR(b3) AS "n"
+FROM bool_test;
+ n | n
+---+---
+ |
+(1 row)
+
+COPY bool_test FROM STDIN NULL 'null';
+SELECT
+ BOOL_AND(b1) AS "f",
+ BOOL_AND(b2) AS "t",
+ BOOL_AND(b3) AS "f",
+ BOOL_AND(b4) AS "n",
+ BOOL_AND(NOT b2) AS "f",
+ BOOL_AND(NOT b3) AS "t"
+FROM bool_test;
+ f | t | f | n | f | t
+---+---+---+---+---+---
+ f | t | f | | f | t
+(1 row)
+
+SELECT
+ EVERY(b1) AS "f",
+ EVERY(b2) AS "t",
+ EVERY(b3) AS "f",
+ EVERY(b4) AS "n",
+ EVERY(NOT b2) AS "f",
+ EVERY(NOT b3) AS "t"
+FROM bool_test;
+ f | t | f | n | f | t
+---+---+---+---+---+---
+ f | t | f | | f | t
+(1 row)
+
+SELECT
+ BOOL_OR(b1) AS "t",
+ BOOL_OR(b2) AS "t",
+ BOOL_OR(b3) AS "f",
+ BOOL_OR(b4) AS "n",
+ BOOL_OR(NOT b2) AS "f",
+ BOOL_OR(NOT b3) AS "t"
+FROM bool_test;
+ t | t | f | n | f | t
+---+---+---+---+---+---
+ t | t | f | | f | t
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
