Repository: incubator-hawq Updated Branches: refs/heads/master a3da49167 -> 51cf8634d
HAWQ-1016. Add feature test for rowtypes with new test framework Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/51cf8634 Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/51cf8634 Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/51cf8634 Branch: refs/heads/master Commit: 51cf8634d2ce920e2eeaa8e9b65bcc8acb584156 Parents: a3da491 Author: ivan <[email protected]> Authored: Wed Aug 31 11:31:28 2016 +0800 Committer: ivan <[email protected]> Committed: Fri Sep 9 09:50:51 2016 +0800 ---------------------------------------------------------------------- src/test/feature/query/ans/rowtypes.ans | 283 ++++++++++++++++++++++++++ src/test/feature/query/sql/rowtypes.sql | 111 ++++++++++ src/test/feature/query/test_rowtypes.cpp | 44 ++++ 3 files changed, 438 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/51cf8634/src/test/feature/query/ans/rowtypes.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/query/ans/rowtypes.ans b/src/test/feature/query/ans/rowtypes.ans new file mode 100755 index 0000000..d421581 --- /dev/null +++ b/src/test/feature/query/ans/rowtypes.ans @@ -0,0 +1,283 @@ +-- start_ignore +SET SEARCH_PATH=TestRowTypes_BasicTest; +SET +-- end_ignore +-- +-- ROWTYPES +-- +-- Make both a standalone composite type and a table rowtype +create type complex as (r float8, i float8); +CREATE TYPE +create temp table fullname (first text, last text); +CREATE TABLE +-- Nested composite +create type quad as (c1 complex, c2 complex); +CREATE TYPE +-- Some simple tests of I/O conversions and row construction +select (1.1,2.2)::complex, row((3.3,4.4),(5.5,null))::quad; + row | row +-----------+------------------------ + (1.1,2.2) | ("(3.3,4.4)","(5.5,)") +(1 row) + +select row('Joe', 'Blow')::fullname, '(Joe,Blow)'::fullname; + row | fullname +------------+------------ + (Joe,Blow) | (Joe,Blow) +(1 row) + +select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname; + fullname | fullname +------------------+-------------- + (Joe,"von Blow") | (Joe,d'Blow) +(1 row) + +select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fullname; + fullname | fullname +-------------------+----------------- + (Joe,"von""Blow") | (Joe,"d\\Blow") +(1 row) + +select '(Joe,"Blow,Jr")'::fullname; + fullname +----------------- + (Joe,"Blow,Jr") +(1 row) + +select '(Joe,)'::fullname; -- ok, null 2nd column + fullname +---------- + (Joe,) +(1 row) + +select '(Joe)'::fullname; -- bad +psql:/tmp/TestRowTypes_BasicTest.sql:31: ERROR: malformed record literal: "(Joe)" +LINE 1: select '(Joe)'::fullname; + ^ +DETAIL: Too few columns. +select '(Joe,,)'::fullname; -- bad +psql:/tmp/TestRowTypes_BasicTest.sql:32: ERROR: malformed record literal: "(Joe,,)" +LINE 1: select '(Joe,,)'::fullname; + ^ +DETAIL: Too many columns. +create temp table quadtable(f1 int, q quad); +CREATE TABLE +insert into quadtable values (1, ((3.3,4.4),(5.5,6.6))); +INSERT 0 1 +insert into quadtable values (2, ((null,4.4),(5.5,6.6))); +INSERT 0 1 +select * from quadtable; + f1 | q +----+--------------------------- + 1 | ("(3.3,4.4)","(5.5,6.6)") + 2 | ("(,4.4)","(5.5,6.6)") +(2 rows) + +begin; +BEGIN +set local add_missing_from = false; +SET +select f1, q.c1 from quadtable; -- fails, q is a table reference +psql:/tmp/TestRowTypes_BasicTest.sql:43: ERROR: missing FROM-clause entry for table "q" +LINE 1: select f1, q.c1 from quadtable; + ^ +rollback; +ROLLBACK +select f1, (q).c1, (qq.q).c1.i from quadtable qq; + f1 | c1 | i +----+-----------+----- + 1 | (3.3,4.4) | 4.4 + 2 | (,4.4) | 4.4 +(2 rows) + +create temp table people (fn fullname, bd date); +CREATE TABLE +insert into people values ('(Joe,Blow)', '1984-01-10'); +INSERT 0 1 +select * from people; + fn | bd +------------+------------ + (Joe,Blow) | 01-10-1984 +(1 row) + +-- at the moment this will not work due to ALTER TABLE inadequacy: +alter table fullname add column suffix text default ''; +psql:/tmp/TestRowTypes_BasicTest.sql:55: ERROR: cannot alter table "fullname" because column "people"."fn" uses its rowtype +-- Not supported in HAWQ +alter table fullname add column suffix text default null; +psql:/tmp/TestRowTypes_BasicTest.sql:58: ERROR: cannot alter table "fullname" because column "people"."fn" uses its rowtype +select * from people; + fn | bd +------------+------------ + (Joe,Blow) | 01-10-1984 +(1 row) + +-- test insertion/updating of subfields, not supported in HAWQ +update people set fn.suffix = 'Jr'; +psql:/tmp/TestRowTypes_BasicTest.sql:63: ERROR: cannot assign to field "suffix" of column "fn" because there is no such column in data type fullname +LINE 1: update people set fn.suffix = 'Jr'; + ^ +select * from people; + fn | bd +------------+------------ + (Joe,Blow) | 01-10-1984 +(1 row) + +insert into quadtable (f1, q.c1.r, q.c2.i) values(44,55,66); +INSERT 0 1 +select * from quadtable; + f1 | q +----+--------------------------- + 1 | ("(3.3,4.4)","(5.5,6.6)") + 2 | ("(,4.4)","(5.5,6.6)") + 44 | ("(55,)","(,66)") +(3 rows) + +-- The object here is to ensure that toasted references inside +-- composite values don't cause problems. The large f1 value will +-- be toasted inside pp, it must still work after being copied to people. +create temp table pp (f1 text); +CREATE TABLE +insert into pp values (repeat('abcdefghijkl', 100000)); +INSERT 0 1 +-- HAWQ does not support alter, so remove third column null +insert into people select ('Jim', f1)::fullname, current_date from pp; +INSERT 0 1 +select (fn).first, substr((fn).last, 1, 20), length((fn).last) from people; + first | substr | length +-------+----------------------+--------- + Joe | Blow | 4 + Jim | abcdefghijklabcdefgh | 1200000 +(2 rows) + +-- Test row comparison semantics. Prior to PG 8.2 we did this in a totally +-- non-spec-compliant way. +select ROW(1,2) < ROW(1,3) as true; + true +------ + t +(1 row) + +select ROW(1,2) < ROW(1,1) as false; + false +------- + f +(1 row) + +select ROW(1,2) < ROW(1,NULL) as null; + null +------ + +(1 row) + +select ROW(1,2,3) < ROW(1,3,NULL) as true; -- the NULL is not examined + true +------ + t +(1 row) + +select ROW(11,'ABC') < ROW(11,'DEF') as true; + true +------ + t +(1 row) + +select ROW(11,'ABC') > ROW(11,'DEF') as false; + false +------- + f +(1 row) + +select ROW(12,'ABC') > ROW(11,'DEF') as true; + true +------ + t +(1 row) + +-- = and <> have different NULL-behavior than < etc +select ROW(1,2,3) < ROW(1,NULL,4) as null; + null +------ + +(1 row) + +select ROW(1,2,3) = ROW(1,NULL,4) as false; + false +------- + f +(1 row) + +select ROW(1,2,3) <> ROW(1,NULL,4) as true; + true +------ + t +(1 row) + +-- We allow operators beyond the six standard ones, if they have btree +-- operator classes. +select ROW('ABC','DEF') ~<=~ ROW('DEF','ABC') as true; + true +------ + t +(1 row) + +select ROW('ABC','DEF') ~>=~ ROW('DEF','ABC') as false; + false +------- + f +(1 row) + +select ROW('ABC','DEF') ~~ ROW('DEF','ABC') as fail; +psql:/tmp/TestRowTypes_BasicTest.sql:102: ERROR: could not determine interpretation of row comparison operator ~~ +LINE 1: select ROW('ABC','DEF') ~~ ROW('DEF','ABC') as fail; + ^ +HINT: Row comparison operators must be associated with btree operator classes. +-- Check row comparison with a subselect +select unique1, unique2 from tenk1 +where (unique1, unique2) < any (select ten, ten from tenk1 where hundred < 3); + unique1 | unique2 +---------+--------- + 0 | 9998 + 1 | 2838 +(2 rows) + +-- Also check row comparison with an indexable condition +select thousand, tenthous from tenk1 +where (thousand, tenthous) >= (997, 5000) +order by thousand, tenthous; + thousand | tenthous +----------+---------- + 997 | 5997 + 997 | 6997 + 997 | 7997 + 997 | 8997 + 997 | 9997 + 998 | 998 + 998 | 1998 + 998 | 2998 + 998 | 3998 + 998 | 4998 + 998 | 5998 + 998 | 6998 + 998 | 7998 + 998 | 8998 + 998 | 9998 + 999 | 999 + 999 | 1999 + 999 | 2999 + 999 | 3999 + 999 | 4999 + 999 | 5999 + 999 | 6999 + 999 | 7999 + 999 | 8999 + 999 | 9999 +(25 rows) + +-- empty row constructor is valid +select ROW(); + row +----- + () +(1 row) + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/51cf8634/src/test/feature/query/sql/rowtypes.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/query/sql/rowtypes.sql b/src/test/feature/query/sql/rowtypes.sql new file mode 100644 index 0000000..66ad780 --- /dev/null +++ b/src/test/feature/query/sql/rowtypes.sql @@ -0,0 +1,111 @@ +-- +-- ROWTYPES +-- + +-- Make both a standalone composite type and a table rowtype + +create type complex as (r float8, i float8); + +create temp table fullname (first text, last text); + +-- Nested composite + +create type quad as (c1 complex, c2 complex); + +-- Some simple tests of I/O conversions and row construction + +select (1.1,2.2)::complex, row((3.3,4.4),(5.5,null))::quad; + +select row('Joe', 'Blow')::fullname, '(Joe,Blow)'::fullname; + +select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname; + +select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fullname; + +select '(Joe,"Blow,Jr")'::fullname; + +select '(Joe,)'::fullname; -- ok, null 2nd column +select '(Joe)'::fullname; -- bad +select '(Joe,,)'::fullname; -- bad + +create temp table quadtable(f1 int, q quad); + +insert into quadtable values (1, ((3.3,4.4),(5.5,6.6))); +insert into quadtable values (2, ((null,4.4),(5.5,6.6))); + +select * from quadtable; + +begin; +set local add_missing_from = false; +select f1, q.c1 from quadtable; -- fails, q is a table reference +rollback; + +select f1, (q).c1, (qq.q).c1.i from quadtable qq; + +create temp table people (fn fullname, bd date); + +insert into people values ('(Joe,Blow)', '1984-01-10'); + +select * from people; + +-- at the moment this will not work due to ALTER TABLE inadequacy: +alter table fullname add column suffix text default ''; + +-- Not supported in HAWQ +alter table fullname add column suffix text default null; + +select * from people; + +-- test insertion/updating of subfields, not supported in HAWQ +update people set fn.suffix = 'Jr'; + +select * from people; + +insert into quadtable (f1, q.c1.r, q.c2.i) values(44,55,66); + +select * from quadtable; + +-- The object here is to ensure that toasted references inside +-- composite values don't cause problems. The large f1 value will +-- be toasted inside pp, it must still work after being copied to people. + +create temp table pp (f1 text); +insert into pp values (repeat('abcdefghijkl', 100000)); +-- HAWQ does not support alter, so remove third column null +insert into people select ('Jim', f1)::fullname, current_date from pp; + +select (fn).first, substr((fn).last, 1, 20), length((fn).last) from people; + +-- Test row comparison semantics. Prior to PG 8.2 we did this in a totally +-- non-spec-compliant way. + +select ROW(1,2) < ROW(1,3) as true; +select ROW(1,2) < ROW(1,1) as false; +select ROW(1,2) < ROW(1,NULL) as null; +select ROW(1,2,3) < ROW(1,3,NULL) as true; -- the NULL is not examined +select ROW(11,'ABC') < ROW(11,'DEF') as true; +select ROW(11,'ABC') > ROW(11,'DEF') as false; +select ROW(12,'ABC') > ROW(11,'DEF') as true; + +-- = and <> have different NULL-behavior than < etc +select ROW(1,2,3) < ROW(1,NULL,4) as null; +select ROW(1,2,3) = ROW(1,NULL,4) as false; +select ROW(1,2,3) <> ROW(1,NULL,4) as true; + +-- We allow operators beyond the six standard ones, if they have btree +-- operator classes. +select ROW('ABC','DEF') ~<=~ ROW('DEF','ABC') as true; +select ROW('ABC','DEF') ~>=~ ROW('DEF','ABC') as false; +select ROW('ABC','DEF') ~~ ROW('DEF','ABC') as fail; + +-- Check row comparison with a subselect +select unique1, unique2 from tenk1 +where (unique1, unique2) < any (select ten, ten from tenk1 where hundred < 3); + +-- Also check row comparison with an indexable condition +select thousand, tenthous from tenk1 +where (thousand, tenthous) >= (997, 5000) +order by thousand, tenthous; + +-- empty row constructor is valid +select ROW(); http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/51cf8634/src/test/feature/query/test_rowtypes.cpp ---------------------------------------------------------------------- diff --git a/src/test/feature/query/test_rowtypes.cpp b/src/test/feature/query/test_rowtypes.cpp new file mode 100644 index 0000000..0b0c6fc --- /dev/null +++ b/src/test/feature/query/test_rowtypes.cpp @@ -0,0 +1,44 @@ +#include "gtest/gtest.h" + +#include "lib/sql_util.h" + +using std::string; + +class TestRowTypes : public ::testing::Test +{ + public: + TestRowTypes() { } + ~TestRowTypes() {} +}; + +TEST_F(TestRowTypes, BasicTest) +{ + hawq::test::SQLUtility util; + + util.execute("drop table if exists tenk1"); + util.execute("create table tenk1 (" + " unique1 int4," + " unique2 int4," + " two int4," + " four int4," + " ten int4," + " twenty int4," + " hundred int4," + " thousand int4," + " twothousand int4," + " fivethous int4," + " tenthous int4," + " odd int4," + " even int4," + " stringu1 name," + " stringu2 name," + " string4 name) with oids"); + + std::string pwd = util.getTestRootPath(); + std::string cmd = "COPY tenk1 FROM '" + pwd + "/query/data/tenk.data'"; + std::cout << cmd << std::endl; + util.execute(cmd); + + util.execSQLFile("query/sql/rowtypes.sql", + "query/ans/rowtypes.ans"); +}
