Repository: incubator-hawq Updated Branches: refs/heads/master ca5d8de05 -> 093330fe6
HAWQ-858. Fix parser to understand case / when expression in group by Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/093330fe Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/093330fe Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/093330fe Branch: refs/heads/master Commit: 093330fe604871c50e4bcfc459d33e57cb9985a9 Parents: ca5d8de Author: amyrazz44 <[email protected]> Authored: Tue Nov 15 15:43:38 2016 +0800 Committer: ivan <[email protected]> Committed: Fri Dec 9 10:20:18 2016 +0800 ---------------------------------------------------------------------- src/backend/parser/parse_expr.c | 6 + src/test/feature/full_tests.txt | 2 +- .../feature/query/ans/parser-casegroupby.ans | 206 +++++++++++++++++++ .../feature/query/sql/parser-casegroupby.sql | 109 ++++++++++ src/test/feature/query/test_parser.cpp | 28 +++ 5 files changed, 350 insertions(+), 1 deletion(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/093330fe/src/backend/parser/parse_expr.c ---------------------------------------------------------------------- diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index da199e5..eab5678 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -1385,6 +1385,12 @@ transformCaseExpr(ParseState *pstate, CaseExpr *c) */ if (isWhenIsNotDistinctFromExpr(warg)) { + /* + * Make a copy before we change warg. + * In transformation we don't want to change source (CaseExpr* Node). + * Always create new node and do the transformation + */ + warg = copyObject(warg); A_Expr *top = (A_Expr *) warg; A_Expr *expr = (A_Expr *) top->rexpr; expr->lexpr = (Node *) placeholder; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/093330fe/src/test/feature/full_tests.txt ---------------------------------------------------------------------- diff --git a/src/test/feature/full_tests.txt b/src/test/feature/full_tests.txt index e7dcc0c..7aa3f3d 100644 --- a/src/test/feature/full_tests.txt +++ b/src/test/feature/full_tests.txt @@ -2,5 +2,5 @@ #SERIAL=* are the serial tests to run, optional but should not be empty #you can have several PARALLEL or SRRIAL -PARALLEL=TestErrorTable.*:TestPreparedStatement.*:TestUDF.*:TestAOSnappy.*:TestAlterOwner.*:TestAlterTable.*:TestCreateTable.*:TestGuc.*:TestType.*:TestDatabase.*:TestParquet.*:TestPartition.*:TestSubplan.*:TestAggregate.*:TestCreateTypeComposite.*:TestGpDistRandom.*:TestInformationSchema.*:TestQueryInsert.*:TestQueryNestedCaseNull.*:TestQueryPolymorphism.*:TestQueryPortal.*:TestQueryPrepare.*:TestQuerySequence.*:TestCommonLib.*:TestToast.*:TestTransaction.*:TestCommand.*:TestCopy.* +PARALLEL=TestErrorTable.*:TestPreparedStatement.*:TestUDF.*:TestAOSnappy.*:TestAlterOwner.*:TestAlterTable.*:TestCreateTable.*:TestGuc.*:TestType.*:TestDatabase.*:TestParquet.*:TestPartition.*:TestSubplan.*:TestAggregate.*:TestCreateTypeComposite.*:TestGpDistRandom.*:TestInformationSchema.*:TestQueryInsert.*:TestQueryNestedCaseNull.*:TestQueryPolymorphism.*:TestQueryPortal.*:TestQueryPrepare.*:TestQuerySequence.*:TestCommonLib.*:TestToast.*:TestTransaction.*:TestCommand.*:TestCopy.*:TestParser.* SERIAL=TestHawqRegister.*:TestExternalOid.TestExternalOidAll:TestExternalTable.TestExternalTableAll:TestTemp.BasicTest:TestRowTypes.* http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/093330fe/src/test/feature/query/ans/parser-casegroupby.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/query/ans/parser-casegroupby.ans b/src/test/feature/query/ans/parser-casegroupby.ans new file mode 100755 index 0000000..f1a8e69 --- /dev/null +++ b/src/test/feature/query/ans/parser-casegroupby.ans @@ -0,0 +1,206 @@ +-- start_ignore +SET SEARCH_PATH=TestParser_TestParserCaseGroupBy; +SET +-- end_ignore +-- +-- CASE ... WHEN IS NOT DISTINCT FROM ... +-- +INSERT INTO mytable values (1,2,'t'), + (2,3,'e'), + (3,4,'o'), + (4,5,'o'), + (4,4,'o'), + (5,5,'t'), + (6,6,'t'), + (7,6,'a'), + (8,7,'t'), + (9,8,'a'); +INSERT 0 10 +CREATE OR REPLACE FUNCTION negate(int) RETURNS int +AS 'SELECT $1 * (-1)' +LANGUAGE sql +IMMUTABLE +RETURNS null ON null input; +CREATE FUNCTION +DROP VIEW IF EXISTS myview; +psql:/tmp/TestParser_TestParserCaseGroupBy.sql:24: NOTICE: view "myview" does not exist, skipping +DROP VIEW +CREATE VIEW myview AS + SELECT a,b, CASE a WHEN IS NOT DISTINCT FROM b THEN b*10 + WHEN IS NOT DISTINCT FROM b+1 THEN b*100 + WHEN b-1 THEN b*1000 + WHEN b*10 THEN b*10000 + WHEN negate(b) THEN b*(-1.0) + ELSE b END AS newb + FROM mytable; +CREATE VIEW +SELECT * FROM myview ORDER BY a,b; + a | b | newb +---+---+------ + 1 | 2 | 2000 + 2 | 3 | 3000 + 3 | 4 | 4000 + 4 | 4 | 40 + 4 | 5 | 5000 + 5 | 5 | 50 + 6 | 6 | 60 + 7 | 6 | 600 + 8 | 7 | 700 + 9 | 8 | 800 +(10 rows) + +-- Test deparse +select pg_get_viewdef('myview',true); + pg_get_viewdef +------------------------------------------------------------------------------------- + SELECT mytable.a, mytable.b, + CASE mytable.a + WHEN IS NOT DISTINCT FROM mytable.b THEN (mytable.b * 10)::numeric + WHEN IS NOT DISTINCT FROM mytable.b + 1 THEN (mytable.b * 100)::numeric + WHEN mytable.b - 1 THEN (mytable.b * 1000)::numeric + WHEN mytable.b * 10 THEN (mytable.b * 10000)::numeric + WHEN negate(mytable.b) THEN mytable.b::numeric * (-1.0) + ELSE mytable.b::numeric + END AS newb + FROM mytable; +(1 row) + +-- User-defined DECODE function +CREATE OR REPLACE FUNCTION "decode"(int, int, int) RETURNS int +AS 'select $1 * $2 - $3;' +LANGUAGE sql +IMMUTABLE +RETURNS null ON null input; +CREATE FUNCTION +SELECT decode(11,8,11); + case +------ + +(1 row) + +SELECT "decode"(11,8,11); + decode +-------- + 77 +(1 row) + +-- Test CASE x WHEN IS NOT DISTINCT FROM y with DECODE +SELECT a,b,decode(a,1,1), + CASE decode(a,1,1) WHEN IS NOT DISTINCT FROM 1 THEN b*100 + WHEN IS NOT DISTINCT FROM 4 THEN b*1000 ELSE b END as newb + FROM mytable ORDER BY a,b; + a | b | case | newb +---+---+------+------ + 1 | 2 | 1 | 200 + 2 | 3 | | 3 + 3 | 4 | | 4 + 4 | 4 | | 4 + 4 | 5 | | 5 + 5 | 5 | | 5 + 6 | 6 | | 6 + 7 | 6 | | 6 + 8 | 7 | | 7 + 9 | 8 | | 8 +(10 rows) + +-- Test CASE WHEN x IS NOT DISTINCT FROM y with DECODE +SELECT a,b,decode(a,1,1), + CASE WHEN decode(a,1,1) IS NOT DISTINCT FROM 1 THEN b*100 + WHEN decode(a,1,1) IS NOT DISTINCT FROM 4 THEN b*1000 ELSE b END as newb + FROM mytable ORDER BY a,b; + a | b | case | newb +---+---+------+------ + 1 | 2 | 1 | 200 + 2 | 3 | | 3 + 3 | 4 | | 4 + 4 | 4 | | 4 + 4 | 5 | | 5 + 5 | 5 | | 5 + 6 | 6 | | 6 + 7 | 6 | | 6 + 8 | 7 | | 7 + 9 | 8 | | 8 +(10 rows) + +SELECT a,b,"decode"(a,1,1), + CASE WHEN "decode"(a,1,1) IS NOT DISTINCT FROM 1 THEN b*100 + WHEN "decode"(a,1,1) IS NOT DISTINCT FROM 4 THEN b*1000 ELSE b END as newb + FROM mytable ORDER BY a,b; + a | b | decode | newb +---+---+--------+------ + 1 | 2 | 0 | 2 + 2 | 3 | 1 | 300 + 3 | 4 | 2 | 4 + 4 | 4 | 3 | 4 + 4 | 5 | 3 | 5 + 5 | 5 | 4 | 5000 + 6 | 6 | 5 | 6 + 7 | 6 | 6 | 6 + 8 | 7 | 7 | 7 + 9 | 8 | 8 | 8 +(10 rows) + +-- +-- Case expression in group by +-- +SELECT + CASE t.field1 + WHEN IS NOT DISTINCT FROM ''::text THEN 'Undefined'::character varying + ELSE t.field1 + END AS field1 + FROM ( SELECT 'test value'::text AS field1) t + GROUP BY + CASE t.field1 + WHEN IS NOT DISTINCT FROM ''::text THEN 'Undefined'::character varying + ELSE t.field1 + END; + field1 +------------ + test value +(1 row) + +-- +-- Variant of case expression in group by +-- +SELECT + CASE t.field1 + WHEN IS NOT DISTINCT FROM ''::text THEN 'Undefined'::character varying + ELSE t.field1 + END AS field1 + FROM ( SELECT 'test value'::text AS field1) t + GROUP BY 1; + field1 +------------ + test value +(1 row) + +-- +-- decode in group by +-- +SELECT + decode(t.field1, ''::text, 'Undefined'::character varying, t.field1) as field1 + FROM ( SELECT 'test value'::text AS field1) t + GROUP BY + decode(t.field1, ''::text, 'Undefined'::character varying, t.field1); + field1 +------------ + test value +(1 row) + +-- +-- variant of decode in group by +-- + SELECT + decode(t.field1, ''::text, 'Undefined'::character varying, t.field1) as field1 + FROM ( SELECT 'test value'::text AS field1) t + GROUP BY 1; + field1 +------------ + test value +(1 row) + +-- +-- clean up +-- +DROP FUNCTION "decode"(int, int, int); +DROP FUNCTION http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/093330fe/src/test/feature/query/sql/parser-casegroupby.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/query/sql/parser-casegroupby.sql b/src/test/feature/query/sql/parser-casegroupby.sql new file mode 100644 index 0000000..3ae7f21 --- /dev/null +++ b/src/test/feature/query/sql/parser-casegroupby.sql @@ -0,0 +1,109 @@ +-- +-- CASE ... WHEN IS NOT DISTINCT FROM ... +-- +INSERT INTO mytable values (1,2,'t'), + (2,3,'e'), + (3,4,'o'), + (4,5,'o'), + (4,4,'o'), + (5,5,'t'), + (6,6,'t'), + (7,6,'a'), + (8,7,'t'), + (9,8,'a'); + +CREATE OR REPLACE FUNCTION negate(int) RETURNS int +AS 'SELECT $1 * (-1)' +LANGUAGE sql +IMMUTABLE +RETURNS null ON null input; + +DROP VIEW IF EXISTS myview; +CREATE VIEW myview AS + SELECT a,b, CASE a WHEN IS NOT DISTINCT FROM b THEN b*10 + WHEN IS NOT DISTINCT FROM b+1 THEN b*100 + WHEN b-1 THEN b*1000 + WHEN b*10 THEN b*10000 + WHEN negate(b) THEN b*(-1.0) + ELSE b END AS newb + FROM mytable; +SELECT * FROM myview ORDER BY a,b; + + +-- Test deparse +select pg_get_viewdef('myview',true); + +-- User-defined DECODE function +CREATE OR REPLACE FUNCTION "decode"(int, int, int) RETURNS int +AS 'select $1 * $2 - $3;' +LANGUAGE sql +IMMUTABLE +RETURNS null ON null input; + +SELECT decode(11,8,11); +SELECT "decode"(11,8,11); + +-- Test CASE x WHEN IS NOT DISTINCT FROM y with DECODE +SELECT a,b,decode(a,1,1), + CASE decode(a,1,1) WHEN IS NOT DISTINCT FROM 1 THEN b*100 + WHEN IS NOT DISTINCT FROM 4 THEN b*1000 ELSE b END as newb + FROM mytable ORDER BY a,b; + +-- Test CASE WHEN x IS NOT DISTINCT FROM y with DECODE +SELECT a,b,decode(a,1,1), + CASE WHEN decode(a,1,1) IS NOT DISTINCT FROM 1 THEN b*100 + WHEN decode(a,1,1) IS NOT DISTINCT FROM 4 THEN b*1000 ELSE b END as newb + FROM mytable ORDER BY a,b; + +SELECT a,b,"decode"(a,1,1), + CASE WHEN "decode"(a,1,1) IS NOT DISTINCT FROM 1 THEN b*100 + WHEN "decode"(a,1,1) IS NOT DISTINCT FROM 4 THEN b*1000 ELSE b END as newb + FROM mytable ORDER BY a,b; + +-- +-- Case expression in group by +-- +SELECT + CASE t.field1 + WHEN IS NOT DISTINCT FROM ''::text THEN 'Undefined'::character varying + ELSE t.field1 + END AS field1 + FROM ( SELECT 'test value'::text AS field1) t + GROUP BY + CASE t.field1 + WHEN IS NOT DISTINCT FROM ''::text THEN 'Undefined'::character varying + ELSE t.field1 + END; + +-- +-- Variant of case expression in group by +-- +SELECT + CASE t.field1 + WHEN IS NOT DISTINCT FROM ''::text THEN 'Undefined'::character varying + ELSE t.field1 + END AS field1 + FROM ( SELECT 'test value'::text AS field1) t + GROUP BY 1; + +-- +-- decode in group by +-- +SELECT + decode(t.field1, ''::text, 'Undefined'::character varying, t.field1) as field1 + FROM ( SELECT 'test value'::text AS field1) t + GROUP BY + decode(t.field1, ''::text, 'Undefined'::character varying, t.field1); + +-- +-- variant of decode in group by +-- + SELECT + decode(t.field1, ''::text, 'Undefined'::character varying, t.field1) as field1 + FROM ( SELECT 'test value'::text AS field1) t + GROUP BY 1; + +-- +-- clean up +-- +DROP FUNCTION "decode"(int, int, int); http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/093330fe/src/test/feature/query/test_parser.cpp ---------------------------------------------------------------------- diff --git a/src/test/feature/query/test_parser.cpp b/src/test/feature/query/test_parser.cpp new file mode 100644 index 0000000..c2aec26 --- /dev/null +++ b/src/test/feature/query/test_parser.cpp @@ -0,0 +1,28 @@ +#include <pwd.h> +#include <sys/types.h> +#include <unistd.h> +#include <vector> +#include <stdio.h> +#include <stdlib.h> +#include "lib/sql_util.h" +#include "gtest/gtest.h" + +class TestParser : public ::testing::Test { + public: + TestParser() {} + ~TestParser() {} +}; + + +TEST_F(TestParser, TestParserCaseGroupBy) { + hawq::test::SQLUtility util; + // prepare + util.execute("DROP TABLE IF EXISTS mytable CASCADE"); + util.execute("CREATE TABLE mytable (a int, b int, c varchar(1))"); + // test + util.execSQLFile("query/sql/parser-casegroupby.sql", + "query/ans/parser-casegroupby.ans"); + util.execute("DROP TABLE mytable CASCADE"); + util.execute("DROP FUNCTION negate(int)"); +} +
