HAWQ-750. Test prepare and sequence using new test library
Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/ec5a9bad Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/ec5a9bad Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/ec5a9bad Branch: refs/heads/master Commit: ec5a9bad8eeacb421327920df5e720a5f6df6c7c Parents: 120ee70 Author: YI JIN <[email protected]> Authored: Mon May 23 18:51:28 2016 +1000 Committer: YI JIN <[email protected]> Committed: Mon May 23 18:51:28 2016 +1000 ---------------------------------------------------------------------- .../feature/query/ans/prepare-parameters.ans | 112 + .../feature/query/ans/prepare-uniqueness.ans | 43 + .../feature/query/ans/sequence-dependency.ans | 27 + .../query/ans/sequence-serialcol-null.ans | 2 + .../query/ans/sequence-serialcol-query.ans | 7 + .../query/ans/sequence-serialcol-query2.ans | 8 + src/test/feature/query/data/streets.data | 5124 +++++++++ src/test/feature/query/data/tenk.data | 10000 +++++++++++++++++ .../feature/query/sql/prepare-parameters.sql | 31 + .../feature/query/sql/prepare-uniqueness.sql | 18 + .../feature/query/sql/sequence-dependency.sql | 14 + .../query/sql/sequence-serialcol-null.sql | 1 + .../query/sql/sequence-serialcol-query.sql | 1 + src/test/feature/query/test-prepare.cpp | 72 + src/test/feature/query/test-sequence.cpp | 122 + 15 files changed, 15582 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/ec5a9bad/src/test/feature/query/ans/prepare-parameters.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/query/ans/prepare-parameters.ans b/src/test/feature/query/ans/prepare-parameters.ans new file mode 100644 index 0000000..b94963e --- /dev/null +++ b/src/test/feature/query/ans/prepare-parameters.ans @@ -0,0 +1,112 @@ +PREPARE q2(text) AS + SELECT datname, datistemplate, datallowconn + FROM pg_database WHERE datname = $1; +PREPARE +EXECUTE q2('regression'); + datname | datistemplate | datallowconn +------------+---------------+-------------- + regression | f | t +(1 row) + +PREPARE q3(text, int, float, boolean, oid, smallint) AS + SELECT * FROM test1 WHERE string4 = $1 AND (four = $2 OR + ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int) ORDER BY 1,2,3,4; +PREPARE +EXECUTE q3('AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint); + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx + 102 | 612 | 0 | 2 | 2 | 2 | 2 | 102 | 102 | 102 | 102 | 4 | 5 | YDAAAA | OXAAAA | AAAAxx + 802 | 2908 | 0 | 2 | 2 | 2 | 2 | 802 | 802 | 802 | 802 | 4 | 5 | WEAAAA | WHEAAA | AAAAxx + 902 | 1104 | 0 | 2 | 2 | 2 | 2 | 902 | 902 | 902 | 902 | 4 | 5 | SIAAAA | MQBAAA | AAAAxx + 1002 | 2580 | 0 | 2 | 2 | 2 | 2 | 2 | 1002 | 1002 | 1002 | 4 | 5 | OMAAAA | GVDAAA | AAAAxx + 1602 | 8148 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 1602 | 1602 | 4 | 5 | QJAAAA | KBMAAA | AAAAxx + 1702 | 7940 | 0 | 2 | 2 | 2 | 2 | 702 | 1702 | 1702 | 1702 | 4 | 5 | MNAAAA | KTLAAA | AAAAxx + 2102 | 6184 | 0 | 2 | 2 | 2 | 2 | 102 | 102 | 2102 | 2102 | 4 | 5 | WCAAAA | WDJAAA | AAAAxx + 2202 | 8028 | 0 | 2 | 2 | 2 | 2 | 202 | 202 | 2202 | 2202 | 4 | 5 | SGAAAA | UWLAAA | AAAAxx + 2302 | 7112 | 0 | 2 | 2 | 2 | 2 | 302 | 302 | 2302 | 2302 | 4 | 5 | OKAAAA | ONKAAA | AAAAxx + 2902 | 6816 | 0 | 2 | 2 | 2 | 2 | 902 | 902 | 2902 | 2902 | 4 | 5 | QHAAAA | ECKAAA | AAAAxx + 3202 | 7128 | 0 | 2 | 2 | 2 | 2 | 202 | 1202 | 3202 | 3202 | 4 | 5 | ETAAAA | EOKAAA | AAAAxx + 3902 | 9224 | 0 | 2 | 2 | 2 | 2 | 902 | 1902 | 3902 | 3902 | 4 | 5 | CUAAAA | UQNAAA | AAAAxx + 4102 | 7676 | 0 | 2 | 2 | 2 | 2 | 102 | 102 | 4102 | 4102 | 4 | 5 | UBAAAA | GJLAAA | AAAAxx + 4202 | 6628 | 0 | 2 | 2 | 2 | 2 | 202 | 202 | 4202 | 4202 | 4 | 5 | QFAAAA | YUJAAA | AAAAxx + 4502 | 412 | 0 | 2 | 2 | 2 | 2 | 502 | 502 | 4502 | 4502 | 4 | 5 | ERAAAA | WPAAAA | AAAAxx + 4702 | 2520 | 0 | 2 | 2 | 2 | 2 | 702 | 702 | 4702 | 4702 | 4 | 5 | WYAAAA | YSDAAA | AAAAxx + 4902 | 1600 | 0 | 2 | 2 | 2 | 2 | 902 | 902 | 4902 | 4902 | 4 | 5 | OGAAAA | OJCAAA | AAAAxx + 5602 | 8796 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 602 | 5602 | 4 | 5 | MHAAAA | IANAAA | AAAAxx + 6002 | 8932 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 1002 | 6002 | 4 | 5 | WWAAAA | OFNAAA | AAAAxx + 6402 | 3808 | 0 | 2 | 2 | 2 | 2 | 402 | 402 | 1402 | 6402 | 4 | 5 | GMAAAA | MQFAAA | AAAAxx + 7602 | 1040 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 2602 | 7602 | 4 | 5 | KGAAAA | AOBAAA | AAAAxx + 7802 | 7508 | 0 | 2 | 2 | 2 | 2 | 802 | 1802 | 2802 | 7802 | 4 | 5 | COAAAA | UCLAAA | AAAAxx + 8002 | 9980 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 3002 | 8002 | 4 | 5 | UVAAAA | WTOAAA | AAAAxx + 8302 | 7800 | 0 | 2 | 2 | 2 | 2 | 302 | 302 | 3302 | 8302 | 4 | 5 | IHAAAA | AOLAAA | AAAAxx + 8402 | 5708 | 0 | 2 | 2 | 2 | 2 | 402 | 402 | 3402 | 8402 | 4 | 5 | ELAAAA | OLIAAA | AAAAxx + 8602 | 5440 | 0 | 2 | 2 | 2 | 2 | 602 | 602 | 3602 | 8602 | 4 | 5 | WSAAAA | GBIAAA | AAAAxx + 9502 | 1812 | 0 | 2 | 2 | 2 | 2 | 502 | 1502 | 4502 | 9502 | 4 | 5 | MBAAAA | SRCAAA | AAAAxx + 9602 | 9972 | 0 | 2 | 2 | 2 | 2 | 602 | 1602 | 4602 | 9602 | 4 | 5 | IFAAAA | OTOAAA | AAAAxx +(29 rows) + +EXECUTE q3('bool'); +psql:/tmp/TestQueryPrepare_TestPrepareParameters.sql:16: ERROR: wrong number of parameters for prepared statement "q3" +DETAIL: Expected 6 parameters but got 1. +EXECUTE q3('bytea', 5::smallint, 10.5::float, false, 500::oid, 4::bigint, true); +psql:/tmp/TestQueryPrepare_TestPrepareParameters.sql:18: ERROR: wrong number of parameters for prepared statement "q3" +DETAIL: Expected 6 parameters but got 7. +EXECUTE q3(5::smallint, 10.5::float, false, 500::oid, 4::bigint, 'bytea'); +psql:/tmp/TestQueryPrepare_TestPrepareParameters.sql:20: ERROR: parameter $3 of type boolean cannot be coerced to the expected type double precision +LINE 1: EXECUTE q3(5::smallint, 10.5::float, false, 500::oid, 4::big... + ^ +HINT: You will need to rewrite or cast the expression. +PREPARE q4(nonexistenttype) AS SELECT $1; +psql:/tmp/TestQueryPrepare_TestPrepareParameters.sql:22: ERROR: type "nonexistenttype" does not exist +LINE 1: PREPARE q4(nonexistenttype) AS SELECT $1; + ^ +PREPARE q5(int, text) AS + SELECT * FROM test1 WHERE unique1 = $1 OR stringu1 = $2; +PREPARE +CREATE TEMPORARY TABLE q5_prep_results AS EXECUTE q5(200, 'DTAAAA'); +SELECT 16 +SELECT * FROM q5_prep_results ORDER BY 1,2,3,4; + unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 +---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- + 200 | 9441 | 0 | 0 | 0 | 0 | 0 | 200 | 200 | 200 | 200 | 0 | 1 | SHAAAA | DZNAAA | HHHHxx + 497 | 9092 | 1 | 1 | 7 | 17 | 97 | 497 | 497 | 497 | 497 | 194 | 195 | DTAAAA | SLNAAA | AAAAxx + 1173 | 6699 | 1 | 1 | 3 | 13 | 73 | 173 | 1173 | 1173 | 1173 | 146 | 147 | DTAAAA | RXJAAA | VVVVxx + 1849 | 8143 | 1 | 1 | 9 | 9 | 49 | 849 | 1849 | 1849 | 1849 | 98 | 99 | DTAAAA | FBMAAA | VVVVxx + 2525 | 64 | 1 | 1 | 5 | 5 | 25 | 525 | 525 | 2525 | 2525 | 50 | 51 | DTAAAA | MCAAAA | AAAAxx + 3201 | 7309 | 1 | 1 | 1 | 1 | 1 | 201 | 1201 | 3201 | 3201 | 2 | 3 | DTAAAA | DVKAAA | HHHHxx + 3877 | 4060 | 1 | 1 | 7 | 17 | 77 | 877 | 1877 | 3877 | 3877 | 154 | 155 | DTAAAA | EAGAAA | AAAAxx + 4553 | 4113 | 1 | 1 | 3 | 13 | 53 | 553 | 553 | 4553 | 4553 | 106 | 107 | DTAAAA | FCGAAA | HHHHxx + 5229 | 6407 | 1 | 1 | 9 | 9 | 29 | 229 | 1229 | 229 | 5229 | 58 | 59 | DTAAAA | LMJAAA | VVVVxx + 5905 | 9537 | 1 | 1 | 5 | 5 | 5 | 905 | 1905 | 905 | 5905 | 10 | 11 | DTAAAA | VCOAAA | HHHHxx + 6581 | 4686 | 1 | 1 | 1 | 1 | 81 | 581 | 581 | 1581 | 6581 | 162 | 163 | DTAAAA | GYGAAA | OOOOxx + 7257 | 1895 | 1 | 1 | 7 | 17 | 57 | 257 | 1257 | 2257 | 7257 | 114 | 115 | DTAAAA | XUCAAA | VVVVxx + 7933 | 4514 | 1 | 1 | 3 | 13 | 33 | 933 | 1933 | 2933 | 7933 | 66 | 67 | DTAAAA | QRGAAA | OOOOxx + 8609 | 5918 | 1 | 1 | 9 | 9 | 9 | 609 | 609 | 3609 | 8609 | 18 | 19 | DTAAAA | QTIAAA | OOOOxx + 9285 | 8469 | 1 | 1 | 5 | 5 | 85 | 285 | 1285 | 4285 | 9285 | 170 | 171 | DTAAAA | TNMAAA | HHHHxx + 9961 | 2058 | 1 | 1 | 1 | 1 | 61 | 961 | 1961 | 4961 | 9961 | 122 | 123 | DTAAAA | EBDAAA | OOOOxx +(16 rows) + +PREPARE q6 AS + SELECT * FROM test1 WHERE unique1 = $1 AND stringu1 = $2; +PREPARE +PREPARE q7(unknown) AS + SELECT * FROM test2 WHERE thepath = $1; +PREPARE +SELECT name, statement, parameter_types FROM pg_prepared_statements ORDER BY name; + name | statement | parameter_types +------+---------------------------------------------------------------------------------------+-------------------------------------------------------- + q2 | PREPARE q2(text) AS | {text} + : SELECT datname, datistemplate, datallowconn + : FROM pg_database WHERE datname = $1; + q3 | PREPARE q3(text, int, float, boolean, oid, smallint) AS | {text,integer,"double precision",boolean,oid,smallint} + : SELECT * FROM test1 WHERE string4 = $1 AND (four = $2 OR + : ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int) ORDER BY 1,2,3,4; + q5 | PREPARE q5(int, text) AS | {integer,text} + : SELECT * FROM test1 WHERE unique1 = $1 OR stringu1 = $2; + q6 | PREPARE q6 AS | {integer,name} + : SELECT * FROM test1 WHERE unique1 = $1 AND stringu1 = $2; + q7 | PREPARE q7(unknown) AS | {path} + : SELECT * FROM test2 WHERE thepath = $1; +(5 rows) + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/ec5a9bad/src/test/feature/query/ans/prepare-uniqueness.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/query/ans/prepare-uniqueness.ans b/src/test/feature/query/ans/prepare-uniqueness.ans new file mode 100644 index 0000000..fe34a18 --- /dev/null +++ b/src/test/feature/query/ans/prepare-uniqueness.ans @@ -0,0 +1,43 @@ +SELECT name, statement, parameter_types FROM pg_prepared_statements ORDER BY 1,2,3; + name | statement | parameter_types +------+-----------+----------------- +(0 rows) + +PREPARE q1 AS SELECT 1 AS a; +PREPARE +EXECUTE q1; + a +--- + 1 +(1 row) + +PREPARE q1 AS SELECT 2; +psql:/tmp/TestQueryPrepare_TestPrepareUniqueness.sql:9: ERROR: prepared statement "q1" already exists +DEALLOCATE q1; +DEALLOCATE +PREPARE q1 AS SELECT 2; +PREPARE +EXECUTE q1; + ?column? +---------- + 2 +(1 row) + +PREPARE q2 AS SELECT 2 AS b; +PREPARE +SELECT name, statement, parameter_types FROM pg_prepared_statements ORDER BY 1,2,3; + name | statement | parameter_types +------+------------------------------+----------------- + q1 | PREPARE q1 AS SELECT 2; | {} + q2 | PREPARE q2 AS SELECT 2 AS b; | {} +(2 rows) + +DEALLOCATE PREPARE q1; +DEALLOCATE +DEALLOCATE PREPARE q2; +DEALLOCATE +SELECT name, statement, parameter_types FROM pg_prepared_statements ORDER BY 1,2,3; + name | statement | parameter_types +------+-----------+----------------- +(0 rows) + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/ec5a9bad/src/test/feature/query/ans/sequence-dependency.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/query/ans/sequence-dependency.ans b/src/test/feature/query/ans/sequence-dependency.ans new file mode 100644 index 0000000..9df5778 --- /dev/null +++ b/src/test/feature/query/ans/sequence-dependency.ans @@ -0,0 +1,27 @@ +CREATE TEMP SEQUENCE myseq2; +CREATE SEQUENCE +CREATE TEMPORARY SEQUENCE myseq3; +CREATE SEQUENCE +CREATE TEMP TABLE t1 (f1 SERIAL, + f2 INT DEFAULT NEXTVAL('myseq2'), + f3 INT DEFAULT NEXTVAL('myseq3'::text) +); +psql:/tmp/TestQuerySequence_TestSequenceDependency.sql:9: NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1" +CREATE TABLE +DROP SEQUENCE t1_f1_seq; +psql:/tmp/TestQuerySequence_TestSequenceDependency.sql:11: NOTICE: default for append only table t1 column f1 depends on sequence t1_f1_seq +psql:/tmp/TestQuerySequence_TestSequenceDependency.sql:11: ERROR: cannot drop sequence t1_f1_seq because other objects depend on it +HINT: Use DROP ... CASCADE to drop the dependent objects too. +DROP SEQUENCE myseq2; +psql:/tmp/TestQuerySequence_TestSequenceDependency.sql:12: NOTICE: default for append only table t1 column f2 depends on sequence myseq2 +psql:/tmp/TestQuerySequence_TestSequenceDependency.sql:12: ERROR: cannot drop sequence myseq2 because other objects depend on it +HINT: Use DROP ... CASCADE to drop the dependent objects too. +DROP SEQUENCE myseq3; +DROP SEQUENCE +DROP TABLE t1; +DROP TABLE +DROP SEQUENCE t1_f1_seq; +psql:/tmp/TestQuerySequence_TestSequenceDependency.sql:15: ERROR: sequence "t1_f1_seq" does not exist +DROP SEQUENCE myseq2; +DROP SEQUENCE + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/ec5a9bad/src/test/feature/query/ans/sequence-serialcol-null.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/query/ans/sequence-serialcol-null.ans b/src/test/feature/query/ans/sequence-serialcol-null.ans new file mode 100644 index 0000000..f54365c --- /dev/null +++ b/src/test/feature/query/ans/sequence-serialcol-null.ans @@ -0,0 +1,2 @@ +INSERT INTO serialtest VALUES('wrong',NULL); +psql:/tmp/TestQuerySequence_TestSequenceCreateSerialColumn.sql:4: ERROR: null value in column "f2" violates not-null constraint (seg0 dummy:40000 pid=32346) http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/ec5a9bad/src/test/feature/query/ans/sequence-serialcol-query.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/query/ans/sequence-serialcol-query.ans b/src/test/feature/query/ans/sequence-serialcol-query.ans new file mode 100644 index 0000000..bacd31c --- /dev/null +++ b/src/test/feature/query/ans/sequence-serialcol-query.ans @@ -0,0 +1,7 @@ +select * from serialtest order by f2; + f1 | f2 +-------+----- + foo | 1 + force | 100 +(2 rows) + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/ec5a9bad/src/test/feature/query/ans/sequence-serialcol-query2.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/query/ans/sequence-serialcol-query2.ans b/src/test/feature/query/ans/sequence-serialcol-query2.ans new file mode 100644 index 0000000..73f439b --- /dev/null +++ b/src/test/feature/query/ans/sequence-serialcol-query2.ans @@ -0,0 +1,8 @@ +select * from serialtest order by f2; + f1 | f2 +-------+----- + foo | 1 + more | 2 + force | 100 +(3 rows) +
