trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From lium...@apache.org
Subject [1/3] incubator-trafodion git commit: TRAFODION-2163 support MYSQL style function GROUP_CONCAT
Date Thu, 25 Aug 2016 01:17:56 GMT
Repository: incubator-trafodion
Updated Branches:
  refs/heads/master 48a2a10dc -> ca3215c4f


TRAFODION-2163 support MYSQL style function GROUP_CONCAT


Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/1696410e
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/1696410e
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/1696410e

Branch: refs/heads/master
Commit: 1696410e6bff746a3f64ec1574f20fc0bbb93376
Parents: 9f3542d
Author: Liu Ming <ovis_poly@sina.com>
Authored: Fri Aug 19 06:11:27 2016 +0000
Committer: Liu Ming <ovis_poly@sina.com>
Committed: Fri Aug 19 06:11:27 2016 +0000

----------------------------------------------------------------------
 core/sql/common/NAString.cpp             |  1 +
 core/sql/optimizer/ImplRule.cpp          |  4 ++
 core/sql/parser/ParKeyWords.cpp          |  2 +
 core/sql/parser/sqlparser.y              | 62 +++++++++++++++++++++++++++
 core/sql/regress/executor/EXPECTED002.SB | 57 ++++++++++++++++++++++++
 core/sql/regress/executor/TEST002        | 20 +++++++++
 6 files changed, 146 insertions(+)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1696410e/core/sql/common/NAString.cpp
----------------------------------------------------------------------
diff --git a/core/sql/common/NAString.cpp b/core/sql/common/NAString.cpp
index 3b9830e..e31237d 100644
--- a/core/sql/common/NAString.cpp
+++ b/core/sql/common/NAString.cpp
@@ -1153,6 +1153,7 @@ static NABoolean tokIsFuncOrParenKeyword(const NAString &sqlText,
 	"FIRSTDAYOFYEAR ",     // Tandem-extension
 	"FLOAT ",              // Datatype with scales/precisions/length
 	"FLOOR ",              // Tandem-extension
+        "GROUP_CONCAT",        // MySQL-extension
 	"HASHPARTFUNC ",       // Tandem-extension
 	"HOUR ",               // Datatype with scales/precisions/length
 	"JULIANTIMESTAMP ",    // Tandem-extension

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1696410e/core/sql/optimizer/ImplRule.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ImplRule.cpp b/core/sql/optimizer/ImplRule.cpp
index 35877a8..db2454b 100644
--- a/core/sql/optimizer/ImplRule.cpp
+++ b/core/sql/optimizer/ImplRule.cpp
@@ -2034,6 +2034,10 @@ NABoolean HashGroupByRule::topMatch (RelExpr *relExpr,
 
       CMPASSERT(x.getItemExpr()->isAnAggregate());
 
+      //if it is pivot_group(), currently, hash groupby is not supported
+      if (agg->getOperatorType() == ITM_PIVOT_GROUP)
+        return FALSE;
+
       if (agg->isDistinct())
         {
           ValueIdSet uniqueSet = grbyagg->groupExpr();

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1696410e/core/sql/parser/ParKeyWords.cpp
----------------------------------------------------------------------
diff --git a/core/sql/parser/ParKeyWords.cpp b/core/sql/parser/ParKeyWords.cpp
index e7b55d2..29f8dba 100644
--- a/core/sql/parser/ParKeyWords.cpp
+++ b/core/sql/parser/ParKeyWords.cpp
@@ -466,6 +466,7 @@ ParKeyWord ParKeyWords::keyWords_[] = {
   ParKeyWord("GRANTED",            TOK_GRANTED,     NONRESTOKEN_),
   ParKeyWord("GREATEST",                  TOK_GREATEST,           NONRESTOKEN_),
   ParKeyWord("GROUP",              TOK_GROUP,       ANS_|RESWORD_|MPWORD_),
+  ParKeyWord("GROUP_CONCAT",       TOK_GROUP_CONCAT,   NONRESTOKEN_),
   ParKeyWord("GROUPING",           TOK_GROUPING,    COMPAQ_|RESWORD_),
   ParKeyWord("GZIP",               TOK_GZIP,         NONRESTOKEN_),
   ParKeyWord("HARDWARE",           TOK_HARDWARE,    NONRESTOKEN_),
@@ -947,6 +948,7 @@ ParKeyWord ParKeyWords::keyWords_[] = {
   ParKeyWord("SELECTIVITY",        TOK_SELECTIVITY, NONRESTOKEN_),
   ParKeyWord("SENSITIVE",          IDENTIFIER,      POTANS_|RESWORD_),
   ParKeyWord("SEPARATE",           TOK_SEPARATE,    NONRESTOKEN_),
+  ParKeyWord("SEPARATOR",          TOK_SEPARATOR,    NONRESTOKEN_),
   ParKeyWord("SEQNUM",           TOK_SEQNUM,    NONRESTOKEN_),
   ParKeyWord("SEQUENCE",           TOK_SEQUENCE,    FIRST_|NONRESTOKEN_|SECOND_),
   ParKeyWord("SEQUENCES",           TOK_SEQUENCES,    NONRESTOKEN_),

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1696410e/core/sql/parser/sqlparser.y
----------------------------------------------------------------------
diff --git a/core/sql/parser/sqlparser.y b/core/sql/parser/sqlparser.y
index 27c43fb..0aaa4fa 100755
--- a/core/sql/parser/sqlparser.y
+++ b/core/sql/parser/sqlparser.y
@@ -706,6 +706,7 @@ static void enableMakeQuotedStringISO88591Mechanism()
 %token <tokval> TOK_GREATER_EQUAL
 %token <tokval> TOK_GREATEST
 %token <tokval> TOK_GROUP
+%token <tokval> TOK_GROUP_CONCAT
 %token <tokval> TOK_GZIP
 %token <tokval> TOK_HAVING
 %token <tokval> TOK_HIVEMD
@@ -966,6 +967,7 @@ static void enableMakeQuotedStringISO88591Mechanism()
 %token <tokval> TOK_REAL_IEEE
 %token <tokval> TOK_RECOMPUTE             
 %token <tokval> TOK_RECORD_SEPARATOR
+%token <tokval> TOK_SEPARATOR
 %token <tokval> TOK_RECOVER
 %token <tokval> TOK_RECOVERY
 %token <tokval> TOK_RECURSIVE 
@@ -2891,6 +2893,10 @@ static void enableMakeQuotedStringISO88591Mechanism()
 %type <pivotOptionsList>        pivot_options
 %type <pivotOption>             pivot_option
 
+%type <pivotOptionsList>        concat_options_list
+%type <pivotOptionsList>        concat_options
+%type <pivotOption>             concat_option
+
 %type <maintainObjectOptionsList>    maintain_object_options
 %type <maintainObjectOptionsList>    maintain_object_options_list
 %type <maintainObjectOption>         maintain_object_option
@@ -7289,6 +7295,14 @@ set_function_specification : set_function_type '(' set_quantifier value_expressi
 					    '!');
                               }
 			   }
+              | TOK_GROUP_CONCAT '('  set_quantifier value_expression concat_options ')'

+               {
+                       //comehere
+                  CheckModeSpecial4;
+                  $$ = new (PARSERHEAP())
+                  PivotGroup(ITM_PIVOT_GROUP, $4, $5, $3);
+ 
+               }
               | TOK_PIVOT '(' set_quantifier value_expression pivot_options ')'
                   {
                     CheckModeSpecial4;
@@ -7325,6 +7339,15 @@ set_function_type :   TOK_AVG 		{ $$ = ITM_AVG; }
                     | TOK_VARIANCE 	{ $$ = ITM_VARIANCE; }
                     | TOK_STDDEV 	{ $$ = ITM_STDDEV; }
 
+concat_options : empty
+                       {
+                         $$ = NULL;
+                       }
+                    |  concat_options_list
+                      {
+                        $$ = $1;
+                      }
+
 pivot_options : empty
                        {
                          $$ = NULL;
@@ -7333,6 +7356,18 @@ pivot_options : empty
                       {
                         $$ = $2;
                       }
+concat_options_list : concat_option
+                      {
+                        NAList<PivotGroup::PivotOption*> * frol =
+                          new (PARSERHEAP ()) NAList<PivotGroup::PivotOption*>;
+                        frol->insert($1);
+                        $$ = frol;
+                      }
+                    | concat_option  concat_options_list
+                      {
+                        $2->insert($1);
+                        $$ = $2;
+                      }
 
 pivot_options_list : pivot_option
                       {
@@ -7348,6 +7383,33 @@ pivot_options_list : pivot_option
 		      }
 ;
 
+concat_option : TOK_SEPARATOR QUOTED_STRING
+                      {
+                        PivotGroup::PivotOption * po =
+                          new (PARSERHEAP ()) PivotGroup::PivotOption
+                          (PivotGroup::DELIMITER_, NULL, (char*)$2->data(), -1);
+
+                        $$ = po;
+                      }
+                   | TOK_ORDER TOK_BY sort_spec_list  
+                      {
+                        PivotGroup::PivotOption * po =
+                          new (PARSERHEAP ()) PivotGroup::PivotOption
+                          (PivotGroup::ORDER_BY_, $3, NULL, -1);
+
+                        $$ = po;
+                      }
+                   | TOK_MAX TOK_LENGTH NUMERIC_LITERAL_EXACT_NO_SCALE
+                      {
+                        Int64 value = atoInt64($3->data());
+
+                        PivotGroup::PivotOption * po =
+                          new (PARSERHEAP ()) PivotGroup::PivotOption
+                          (PivotGroup::MAX_LENGTH_, NULL, NULL, (Lng32)value);
+
+                        $$ = po;
+                      }
+
 pivot_option : TOK_DELIMITER QUOTED_STRING
                       {
 			PivotGroup::PivotOption * po = 

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1696410e/core/sql/regress/executor/EXPECTED002.SB
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/EXPECTED002.SB b/core/sql/regress/executor/EXPECTED002.SB
index fa9bd67..f733db8 100644
--- a/core/sql/regress/executor/EXPECTED002.SB
+++ b/core/sql/regress/executor/EXPECTED002.SB
@@ -6578,6 +6578,62 @@ A            B            C            E
 
 --- SQL operation complete.
 >>
+>>drop table if exists student;
+
+--- SQL operation complete.
+>>create table student( student_name char(10), test_score int );
+
+--- SQL operation complete.
+>>insert into student values('s1', 88);
+
+--- 1 row(s) inserted.
+>>insert into student values('s1', 79);
+
+--- 1 row(s) inserted.
+>>insert into student values('s2', 88);
+
+--- 1 row(s) inserted.
+>>insert into student values('s3', 90);
+
+--- 1 row(s) inserted.
+>>
+>>cqd mode_special_4 'on';
+
+--- SQL operation complete.
+>>SELECT student_name,
++>   GROUP_CONCAT(test_score)
++>   FROM student
++>   GROUP BY student_name;
+
+STUDENT_NAME  (EXPR)
+------------  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -----------------------------------------
+
+s2            88                                                                        
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                              
+s1            88,79                                                                     
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                              
+s3            90                                                                        
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                              
+
+--- 3 row(s) selected.
+>>
+>>SELECT student_name,
++>          GROUP_CONCAT(DISTINCT test_score
++>                    ORDER BY test_score SEPARATOR '-')
++>          FROM student
++>          GROUP BY student_name;
+
+STUDENT_NAME  (EXPR)
+------------  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 -----------------------------------------
+
+s2            88                                                                        
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                              
+s1            88-79                                                                     
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                              
+s3            90                                                                        
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                                                                         
                                              
+
+--- 3 row(s) selected.
+>>drop table student;
+
+--- SQL operation complete.
+>>cqd mode_special_4 reset;
+
+--- SQL operation complete.
 >>------------------------------------------------------------------------
 >>
 >>obey TEST002(BR0198_MULTI);
@@ -6900,6 +6956,7 @@ FOO
 --- 0 row(s) selected.
 >>		-- 0 rows EMPTY, err 8401 MULTI
 >>
+
 >>--------------------------------
 >>
 >>

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1696410e/core/sql/regress/executor/TEST002
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/TEST002 b/core/sql/regress/executor/TEST002
index 28fd2b0..f1c6f82 100755
--- a/core/sql/regress/executor/TEST002
+++ b/core/sql/regress/executor/TEST002
@@ -1087,6 +1087,26 @@ control query default hash_joins reset;
 control query default nested_joins reset;
 control query default merge_joins reset;
 
+drop table if exists student;
+create table student( student_name char(10), test_score int );
+insert into student values('s1', 88);
+insert into student values('s1', 79);
+insert into student values('s2', 88);
+insert into student values('s3', 90);
+
+cqd mode_special_4 'on';
+SELECT student_name,
+   GROUP_CONCAT(test_score)
+   FROM student
+   GROUP BY student_name;
+
+SELECT student_name,
+          GROUP_CONCAT(DISTINCT test_score
+                    ORDER BY test_score SEPARATOR '-')
+          FROM student
+          GROUP BY student_name;   
+drop table student;
+cqd mode_special_4 reset;
 ------------------------------------------------------------------------
 
 obey TEST002(BR0198_MULTI);


Mime
View raw message