trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ansha...@apache.org
Subject [1/6] incubator-trafodion git commit: groupby rollup commit #1
Date Fri, 30 Sep 2016 00:15:18 GMT
Repository: incubator-trafodion
Updated Branches:
  refs/heads/master b583e9009 -> 056903f2c


http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/daeb4ae8/core/sql/regress/seabase/EXPECTED033
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/EXPECTED033 b/core/sql/regress/seabase/EXPECTED033
new file mode 100644
index 0000000..f1ec8e0
--- /dev/null
+++ b/core/sql/regress/seabase/EXPECTED033
@@ -0,0 +1,423 @@
+>>
+>>drop table if exists t033t1 cascade;
+
+--- SQL operation complete.
+>>drop table if exists t033t2 cascade;
+
+--- SQL operation complete.
+>>drop table if exists t033t3 cascade;
+
+--- SQL operation complete.
+>>
+>>create table t033t1 (a int, b int, c int, d int not null);
+
+--- SQL operation complete.
+>>
+>>insert into t033t1 values (1,2,3,4),(2,3,4,5),(3,3,3,3),(3,3,4,5),(1,3,3,3),
++>   (null,null,null,6);
+
+--- 6 row(s) inserted.
+>>
+>>select * from t033t1 order by 1,2,3,4;
+
+A            B            C            D          
+-----------  -----------  -----------  -----------
+
+          1            2            3            4
+          1            3            3            3
+          2            3            4            5
+          3            3            3            3
+          3            3            4            5
+          ?            ?            ?            6
+
+--- 6 row(s) selected.
+>>
+>>explain options 'f' select a,b,c,sum(d) from t033t1 
++>                 group by rollup(a,b,c) order by 1,2,3;
+
+LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
+---- ---- ---- --------------------  --------  --------------------  ---------
+
+3    .    4    root                                                  8.00E+000
+2    .    3    sort_groupby_rollup                                   8.00E+000
+1    .    2    sort                                                  1.00E+002
+.    .    1    trafodion_scan                  T033T1                1.00E+002
+
+--- SQL operation complete.
+>>select a,b,c,sum(d) from t033t1 group by rollup(a,b,c) order by 1,2,3;
+
+A            B            C            (EXPR)              
+-----------  -----------  -----------  --------------------
+
+          1            2            3                     4
+          1            2            ?                     4
+          1            3            3                     3
+          1            3            ?                     3
+          1            ?            ?                     7
+          2            3            4                     5
+          2            3            ?                     5
+          2            ?            ?                     5
+          3            3            3                     3
+          3            3            4                     5
+          3            3            ?                     8
+          3            ?            ?                     8
+          ?            ?            ?                     6
+          ?            ?            ?                     6
+          ?            ?            ?                     6
+          ?            ?            ?                    26
+
+--- 16 row(s) selected.
+>>
+>>select * from (
++>select a,b,c,sum(d) from t033t1 group by (a,b,c)
++>union all
++>select a,b,cast(null as int),sum(d) from t033t1 group by (a,b)
++>union all
++>select a,cast(null as int),cast(null as int),sum(d) from t033t1 group by (a)
++>union all
++>select cast(null as int),cast(null as int),cast(null as int),sum(d) from t033t1
++>) x(a,b,c,d)
++>order by a,b,c;
+
+A            B            C            D                   
+-----------  -----------  -----------  --------------------
+
+          1            2            3                     4
+          1            2            ?                     4
+          1            3            3                     3
+          1            3            ?                     3
+          1            ?            ?                     7
+          2            3            4                     5
+          2            3            ?                     5
+          2            ?            ?                     5
+          3            3            3                     3
+          3            3            4                     5
+          3            3            ?                     8
+          3            ?            ?                     8
+          ?            ?            ?                     6
+          ?            ?            ?                     6
+          ?            ?            ?                     6
+          ?            ?            ?                    26
+
+--- 16 row(s) selected.
+>>
+>>select b,c,a,sum(d) from t033t1 group by rollup(b,c,a) order by 1,2,3;
+
+B            C            A            (EXPR)              
+-----------  -----------  -----------  --------------------
+
+          2            3            1                     4
+          2            3            ?                     4
+          2            ?            ?                     4
+          3            3            1                     3
+          3            3            3                     3
+          3            3            ?                     6
+          3            4            2                     5
+          3            4            3                     5
+          3            4            ?                    10
+          3            ?            ?                    16
+          ?            ?            ?                     6
+          ?            ?            ?                     6
+          ?            ?            ?                     6
+          ?            ?            ?                    26
+
+--- 14 row(s) selected.
+>>
+>>select * from (
++>select b,c,a,sum(d) from t033t1 group by (b,c,a)
++>union all
++>select b,c,cast(null as int),sum(d) from t033t1 group by (b,c)
++>union all
++>select b,cast(null as int),cast(null as int),sum(d) from t033t1 group by (b)
++>union all
++>select cast(null as int),cast(null as int),cast(null as int),sum(d) from t033t1
++>) x(a,b,c,d)
++>order by a,b,c;
+
+A            B            C            D                   
+-----------  -----------  -----------  --------------------
+
+          2            3            1                     4
+          2            3            ?                     4
+          2            ?            ?                     4
+          3            3            1                     3
+          3            3            3                     3
+          3            3            ?                     6
+          3            4            2                     5
+          3            4            3                     5
+          3            4            ?                    10
+          3            ?            ?                    16
+          ?            ?            ?                     6
+          ?            ?            ?                     6
+          ?            ?            ?                     6
+          ?            ?            ?                    26
+
+--- 14 row(s) selected.
+>>
+>>select a,b,c from t033t1 group by rollup (a,b,c) order by 1,2,3;
+
+A            B            C          
+-----------  -----------  -----------
+
+          1            2            3
+          1            2            ?
+          1            3            3
+          1            3            ?
+          1            ?            ?
+          2            3            4
+          2            3            ?
+          2            ?            ?
+          3            3            3
+          3            3            4
+          3            3            ?
+          3            ?            ?
+          ?            ?            ?
+          ?            ?            ?
+          ?            ?            ?
+          ?            ?            ?
+
+--- 16 row(s) selected.
+>>
+>>select a, count(distinct b) from t033t1 group by rollup (a);
+
+*** ERROR[4384] GROUP BY ROLLUP clause not allowed for this statement. Reason: Distinct rollup
aggregates not supported.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>select a,b,c,min(distinct b),sum(distinct d) from t033t1 group by rollup (a,b,c)
order by 1,2,3;
+
+*** ERROR[4384] GROUP BY ROLLUP clause not allowed for this statement. Reason: Distinct rollup
aggregates not supported.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select * from (
++>select a,b,c,min(distinct b), sum(distinct d) from t033t1 group by (a,b,c)
++>union all
++>select a,b,cast(null as int),min(distinct b), sum(distinct d) from t033t1 group by (a,b)
++>union all
++>select a,cast(null as int),cast(null as int),min(distinct b), sum(distinct d) from t033t1
group by (a)
++>union all
++>select cast(null as int),cast(null as int),cast(null as int),min(distinct b), sum(distinct
d) from t033t1
++>) x(a,b,c,d,e)
++>order by a,b,c;
+
+A            B            C            D            E                   
+-----------  -----------  -----------  -----------  --------------------
+
+          1            2            3            2                     4
+          1            2            ?            2                     4
+          1            3            3            3                     3
+          1            3            ?            3                     3
+          1            ?            ?            2                     7
+          2            3            4            3                     5
+          2            3            ?            3                     5
+          2            ?            ?            3                     5
+          3            3            3            3                     3
+          3            3            4            3                     5
+          3            3            ?            3                     8
+          3            ?            ?            3                     8
+          ?            ?            ?            ?                     6
+          ?            ?            ?            ?                     6
+          ?            ?            ?            ?                     6
+          ?            ?            ?            2                    18
+
+--- 16 row(s) selected.
+>>
+>>select a+1, sum(b) from t033t1 group by rollup (1);
+
+(EXPR)                (EXPR)              
+--------------------  --------------------
+
+                   2                     5
+                   3                     3
+                   4                     6
+                   ?                     ?
+                   ?                    14
+
+--- 5 row(s) selected.
+>>select cast(d as int), sum(a) from t033t1 group by rollup (cast(d as int));
+
+(EXPR)       (EXPR)              
+-----------  --------------------
+
+          3                     4
+          4                     1
+          5                     5
+          6                     ?
+          ?                    10
+
+--- 5 row(s) selected.
+>>select cast(d as nullable), sum(a) from t033t1 group by rollup (1);
+
+(EXPR)       (EXPR)              
+-----------  --------------------
+
+          3                     4
+          4                     1
+          5                     5
+          6                     ?
+          ?                    10
+
+--- 5 row(s) selected.
+>>select cast(d as nullable), sum(a) from t033t1 group by rollup (cast(d as nullable));
+
+(EXPR)       (EXPR)              
+-----------  --------------------
+
+          3                     4
+          4                     1
+          5                     5
+          6                     ?
+          ?                    10
+
+--- 5 row(s) selected.
+>>
+>>-- having clause
+>>select a, sum(b) from t033t1 group by rollup (a) having a is null;
+
+A            (EXPR)              
+-----------  --------------------
+
+          ?                     ?
+          ?                    14
+
+--- 2 row(s) selected.
+>>select a, sum(b) from t033t1 group by rollup (a) having a is not null;
+
+A            (EXPR)              
+-----------  --------------------
+
+          1                     5
+          2                     3
+          3                     6
+
+--- 3 row(s) selected.
+>>
+>>-- partitioned table with esp execution
+>>drop table if exists t033t2 cascade;
+
+--- SQL operation complete.
+>>create table t033t2 (z int not null primary key,
++>          a int, b int, c int, d int not null)
++>          salt using 4 partitions;
+
+--- SQL operation complete.
+>>insert into t033t2 values (10,1,2,3,4),(11,2,3,4,5),(12,3,3,3,3),
++>                          (13,3,3,4,5),(14,1,3,3,3),(15,null,null,null,6);
+
+--- 6 row(s) inserted.
+>>control query shape esp_exchange(cut);
+
+--- SQL operation complete.
+>>explain options 'f' select a,b,c,sum(d) from t033t2 
++>        group by rollup(a,b,c) order by 1,2,3;
+
+LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
+---- ---- ---- --------------------  --------  --------------------  ---------
+
+6    .    7    root                                                  8.00E+000
+5    .    6    esp_exchange                    1:2(hash2) (m)        8.00E+000
+4    .    5    sort_partial_groupby                                  8.00E+000
+3    .    4    sort                                                  8.00E+000
+2    .    3    esp_exchange                    2(hash2):2(hash2)     8.00E+000
+1    .    2    hash_partial_groupby                                  8.00E+000
+.    .    1    trafodion_scan                  T033T2                1.00E+002
+
+--- SQL operation complete.
+>>select a,b,c,sum(d) from t033t2 group by rollup(a,b,c) order by 1,2,3;
+
+A            B            C            (EXPR)              
+-----------  -----------  -----------  --------------------
+
+          1            2            3                     4
+          1            2            ?                     4
+          1            3            3                     3
+          1            3            ?                     3
+          1            ?            ?                     3
+          1            ?            ?                     4
+          2            3            4                     5
+          2            3            ?                     5
+          2            ?            ?                     5
+          3            3            3                     3
+          3            3            4                     5
+          3            3            ?                     5
+          3            3            ?                     3
+          3            ?            ?                     3
+          3            ?            ?                     5
+          ?            ?            ?                     6
+          ?            ?            ?                     6
+          ?            ?            ?                     6
+          ?            ?            ?                    14
+          ?            ?            ?                    12
+
+--- 20 row(s) selected.
+>>control query shape cut;
+
+--- SQL operation complete.
+>>
+>>-- do not eliminate sort groupby rollup on primary keys
+>>cqd allow_nullable_unique_key_constraint 'ON';
+
+--- SQL operation complete.
+>>create table t033t3 (a int primary key, b int);
+
+--- SQL operation complete.
+>>insert into t033t3 values (1,1), (2,2), (null,null);
+
+--- 3 row(s) inserted.
+>>prepare s from select a, sum(b) from t033t3 group by rollup (a) order by 1,2;
+
+--- SQL command prepared.
+>>explain options 'f' s;
+
+LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
+---- ---- ---- --------------------  --------  --------------------  ---------
+
+3    .    4    root                                                  1.00E+002
+2    .    3    sort                                                  1.00E+002
+1    .    2    sort_groupby_rollup                                   1.00E+002
+.    .    1    trafodion_scan                  T033T3                1.00E+002
+
+--- SQL operation complete.
+>>execute s;
+
+A            (EXPR)              
+-----------  --------------------
+
+          1                     1
+          2                     2
+          ?                     3
+          ?                     ?
+
+--- 4 row(s) selected.
+>>-- regular groupby will eliminate sort groupby
+>>prepare s from select a, sum(b) from t033t3 group by (a);
+
+--- SQL command prepared.
+>>explain options 'f' s;
+
+LC   RC   OP   OPERATOR              OPT       DESCRIPTION           CARD
+---- ---- ---- --------------------  --------  --------------------  ---------
+
+1    .    2    root                                                  1.00E+002
+.    .    1    trafodion_scan                  T033T3                1.00E+002
+
+--- SQL operation complete.
+>>
+>>
+>>-- error cases. Not allowed or currently not supported
+>>select a,a,sum(b) from t033t1 group by rollup (a,a);
+
+*** ERROR[4384] GROUP BY ROLLUP clause not allowed for this statement. Reason: Cannot have
duplicate entries.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select d, sum(b) from t033t1 group by rollup (d);
+
+*** ERROR[4384] GROUP BY ROLLUP clause not allowed for this statement. Reason: Grouped columns
must be nullable.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>log;

http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/daeb4ae8/core/sql/regress/seabase/TEST033
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/TEST033 b/core/sql/regress/seabase/TEST033
new file mode 100644
index 0000000..0d0bfdb
--- /dev/null
+++ b/core/sql/regress/seabase/TEST033
@@ -0,0 +1,123 @@
+-- @@@ START COPYRIGHT @@@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements.  See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership.  The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License.  You may obtain a copy of the License at
+--
+--   http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied.  See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+-- @@@ END COPYRIGHT @@@
+--
+
+-- Tests for GROUP BY ROLLUP feature
+
+log LOG033 clear;
+
+drop table if exists t033t1 cascade;
+drop table if exists t033t2 cascade;
+drop table if exists t033t3 cascade;
+
+create table t033t1 (a int, b int, c int, d int not null);
+
+insert into t033t1 values (1,2,3,4),(2,3,4,5),(3,3,3,3),(3,3,4,5),(1,3,3,3),
+   (null,null,null,6);
+
+select * from t033t1 order by 1,2,3,4;
+
+explain options 'f' select a,b,c,sum(d) from t033t1 
+                 group by rollup(a,b,c) order by 1,2,3;
+select a,b,c,sum(d) from t033t1 group by rollup(a,b,c) order by 1,2,3;
+
+select * from (
+select a,b,c,sum(d) from t033t1 group by (a,b,c)
+union all
+select a,b,cast(null as int),sum(d) from t033t1 group by (a,b)
+union all
+select a,cast(null as int),cast(null as int),sum(d) from t033t1 group by (a)
+union all
+select cast(null as int),cast(null as int),cast(null as int),sum(d) from t033t1
+) x(a,b,c,d)
+order by a,b,c;
+
+select b,c,a,sum(d) from t033t1 group by rollup(b,c,a) order by 1,2,3;
+
+select * from (
+select b,c,a,sum(d) from t033t1 group by (b,c,a)
+union all
+select b,c,cast(null as int),sum(d) from t033t1 group by (b,c)
+union all
+select b,cast(null as int),cast(null as int),sum(d) from t033t1 group by (b)
+union all
+select cast(null as int),cast(null as int),cast(null as int),sum(d) from t033t1
+) x(a,b,c,d)
+order by a,b,c;
+
+select a,b,c from t033t1 group by rollup (a,b,c) order by 1,2,3;
+
+select a, count(distinct b) from t033t1 group by rollup (a);
+
+select a,b,c,min(distinct b),sum(distinct d) from t033t1 group by rollup (a,b,c) order by
1,2,3;
+select * from (
+select a,b,c,min(distinct b), sum(distinct d) from t033t1 group by (a,b,c)
+union all
+select a,b,cast(null as int),min(distinct b), sum(distinct d) from t033t1 group by (a,b)
+union all
+select a,cast(null as int),cast(null as int),min(distinct b), sum(distinct d) from t033t1
group by (a)
+union all
+select cast(null as int),cast(null as int),cast(null as int),min(distinct b), sum(distinct
d) from t033t1
+) x(a,b,c,d,e)
+order by a,b,c;
+
+select a+1, sum(b) from t033t1 group by rollup (1);
+select cast(d as int), sum(a) from t033t1 group by rollup (cast(d as int));
+select cast(d as nullable), sum(a) from t033t1 group by rollup (1);
+select cast(d as nullable), sum(a) from t033t1 group by rollup (cast(d as nullable));
+
+-- having clause
+select a, sum(b) from t033t1 group by rollup (a) having a is null;
+select a, sum(b) from t033t1 group by rollup (a) having a is not null;
+
+-- partitioned table with esp execution
+drop table if exists t033t2 cascade;
+create table t033t2 (z int not null primary key,
+          a int, b int, c int, d int not null)
+          salt using 4 partitions;
+insert into t033t2 values (10,1,2,3,4),(11,2,3,4,5),(12,3,3,3,3),
+                          (13,3,3,4,5),(14,1,3,3,3),(15,null,null,null,6);
+control query shape esp_exchange(cut);                          
+explain options 'f' select a,b,c,sum(d) from t033t2 
+        group by rollup(a,b,c) order by 1,2,3;
+select a,b,c,sum(d) from t033t2 group by rollup(a,b,c) order by 1,2,3;
+control query shape cut;
+
+-- do not eliminate sort groupby rollup on primary keys
+cqd allow_nullable_unique_key_constraint 'ON';
+create table t033t3 (a int primary key, b int);
+insert into t033t3 values (1,1), (2,2), (null,null);
+prepare s from select a, sum(b) from t033t3 group by rollup (a) order by 1,2;
+explain options 'f' s;
+execute s;
+-- regular groupby will eliminate sort groupby
+prepare s from select a, sum(b) from t033t3 group by (a);
+explain options 'f' s;
+
+
+-- error cases. Not allowed or currently not supported
+select a,a,sum(b) from t033t1 group by rollup (a,a);
+select d, sum(b) from t033t1 group by rollup (d);
+
+log;
+
+
+


Mime
View raw message