github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/expression/cte.sql (about)

     1  -- @suit
     2  
     3  -- @case
     4  -- @desc:test for with clause
     5  -- @label:bvt
     6  drop table if exists t1;
     7  create table t1(a int, b int, c int);
     8  insert into t1 values(null,null,null),(2,3,4);
     9  
    10  WITH qn AS (SELECT a FROM t1) SELECT * FROM qn;
    11  WITH qn AS (SELECT a FROM t1), qn2 as (select b from t1)
    12  SELECT * FROM qn;
    13  WITH qn AS (SELECT a FROM t1), qn2 as (select b from t1)
    14  SELECT * FROM qn2;
    15  -- error
    16  WITH qn AS (SELECT a FROM t1), qn as (select b from t1)
    17  SELECT 1 FROM qn;
    18  -- error parser
    19  with test.qn as (select "with") select * from test.qn;
    20  with qn as (select "with" as a)
    21  with qn2 as (select "with" as a)
    22  select a from qn;
    23  with qne as (select a from t1),
    24       qnm as (select a from t1),
    25       qnea as (select a from t1),
    26       qnma as (select a from t1)
    27  select qne.a,qnm.a,alias1.a,alias2.a
    28  from qne, qnm, qnea as alias1, qnma as alias2 limit 2;
    29  
    30  -- @case
    31  -- @desc:test for with multiple refs
    32  -- @label:bvt
    33  drop table if exists t1;
    34  create table t1(a int, b int, c int);
    35  insert into t1 values(null,null,null),(2,3,4);
    36  
    37  WITH qn AS (SELECT b as a FROM t1)
    38  SELECT qn.a, qn2.a  FROM qn, qn as qn2;
    39  WITH qn AS (SELECT b as a FROM t1),
    40  qn2 AS (SELECT c FROM t1 WHERE a IS NULL or a>0)
    41  SELECT qn.a, qn2.c  FROM qn, qn2;
    42  
    43  -- @case
    44  -- @desc:test for with multiple refs intersection
    45  -- @label:bvt
    46  drop table if exists t1;
    47  create table t1(a int, b int, c int);
    48  insert into t1 values(null,null,null),(2,3,4);
    49  WITH qn AS (SELECT 10*a as a FROM t1),qn2 AS (SELECT 3*a FROM qn)
    50  SELECT * from qn2;
    51  
    52  WITH qn AS (SELECT a FROM t1), qn2 AS (SELECT a FROM qn)
    53  SELECT * from qn2;
    54  
    55  WITH qn AS (SELECT b as a FROM t1),
    56  qn2 AS (SELECT a FROM qn WHERE a IS NULL or a>0)
    57  SELECT qn.a, qn2.a  FROM qn, qn2;
    58  
    59  with qn0 as (select 1), qn1 as (select * from qn0), qn2 as (select 1), qn3 as (select 1 from qn1, qn2) select 1 from qn3;
    60  
    61  -- error
    62  WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL or a>0),
    63  qn AS (SELECT b as a FROM t1)
    64  SELECT qn2.a  FROM qn2;
    65  
    66  -- error
    67  with qn1 as (with qn3 as (select * from qn2) select * from qn3),
    68       qn2 as (select 1)
    69  select * from qn1;
    70  
    71  -- error
    72  WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL or a>0),
    73  qn AS (SELECT b as a FROM qn2)
    74  SELECT qn.a  FROM qn;
    75  
    76  -- @case
    77  -- @desc:test for with no refs
    78  -- @label:bvt
    79  drop table if exists t1;
    80  create table t1(a int, b int, c int);
    81  insert into t1 values(null,null,null),(2,3,4);
    82  with qn as (select 1) select 2;
    83  
    84  -- @case
    85  -- @desc:test for with subquery
    86  -- @label:bvt
    87  drop table if exists t1;
    88  create table t1(a int, b int, c int);
    89  insert into t1 values(null,null,null),(2,3,4),(4,5,6);
    90  with qn as (select * from t1) select (select max(a) from qn);
    91  -- ref defined in subquery
    92  SELECT (WITH qn AS (SELECT 10*a as a FROM t1),
    93          qn2 AS (SELECT 3*a AS b FROM qn)
    94          SELECT * from qn2 LIMIT 1)
    95  FROM t1;
    96  
    97  SELECT *
    98  FROM (WITH qn AS (SELECT 10*a as a FROM t1),
    99        qn2 AS (SELECT 3*a AS b FROM qn)
   100        SELECT * from qn2)
   101  AS dt;
   102  
   103  with qn as (select * from t1 limit 10)
   104  select (select max(a) from qn where a=0),
   105         (select min(b) from qn where b=3);
   106  
   107  drop table if exists sales_days;
   108  create table sales_days(day_of_sale DATE, amount INT);
   109  insert into sales_days values('2015-01-02', 100), ('2015-01-05', 200),('2015-02-02', 10),  ('2015-02-10', 100),('2015-03-02', 10),  ('2015-03-18', 1);
   110  
   111  with sales_by_month(month,total) as
   112   (select month(day_of_sale), sum(amount) from sales_days
   113    where year(day_of_sale)=2015
   114    group by month(day_of_sale)),
   115   best_month(month, total, award) as
   116   (select month, total, "best" from sales_by_month
   117    where total=(select max(total) from sales_by_month)),
   118   worst_month(month, total, award) as
   119   (select month, total, "worst" from sales_by_month
   120    where total=(select min(total) from sales_by_month))
   121   select * from best_month union all select * from worst_month;
   122  
   123  drop table if exists sales_days;
   124  
   125  drop table if exists t1;
   126  create table t1(a int);
   127  insert into t1 values(1),(2);
   128  
   129  with qn(a) as (select 1 from t1 limit 2)
   130  select * from qn where qn.a=(select * from qn qn1 limit 1) union select 2;
   131  
   132  -- @case
   133  -- @desc:test for with  with-nested
   134  -- @label:bvt
   135  drop table if exists t1;
   136  create table t1(a int, b int, c int);
   137  insert into t1 values(null,null,null),(2,3,4),(4,5,6);
   138  with qn as
   139    (with qn2 as (select "qn2" as a from t1) select "qn", a from qn2)
   140  select * from qn;
   141  -- @bvt:issue#3307
   142  SELECT (WITH qn AS (SELECT t2.a*a as a FROM t1),
   143          qn2 AS (SELECT 3*a AS b FROM qn)
   144          SELECT * from qn2 LIMIT 1)
   145  FROM t1 as t2;
   146  -- @bvt:issue
   147  
   148  WITH qn AS (SELECT b as a FROM t1)
   149  SELECT (WITH qn2 AS (SELECT a FROM qn WHERE a IS NULL or a>0)
   150          SELECT qn2.a FROM qn2) FROM qn;
   151  
   152  WITH qn AS (select "outer" as a)
   153  SELECT (WITH qn AS (SELECT "inner" as a) SELECT a from qn),
   154         qn.a
   155  FROM qn;
   156  
   157  -- @case
   158  -- @desc:test for with insert select
   159  -- @label:bvt
   160  drop table if exists t1;
   161  drop table if exists t2;
   162  create table t1(a int, b int, c int);
   163  create table t2(a int);
   164  insert into t1 values(null,null,null),(2,3,4),(4,5,6);
   165  INSERT INTO t2
   166  WITH qn AS (SELECT 10*a as a FROM t1),
   167        qn2 AS (SELECT 3*a AS b FROM qn)
   168        SELECT * from qn2;
   169  SELECT * FROM t2;
   170  drop table if exists t1;
   171  drop table if exists t2;
   172  
   173  -- @case
   174  -- @desc:test for with order by ,limit .etc
   175  -- @label:bvt
   176  drop table if exists t1;
   177  drop table if exists t2;
   178  create table t1(a int, b int, c int);
   179  insert into t1 values(null,null,null),(2,3,4),(4,5,6);
   180  with qn as (select a from t1 order by 1)
   181  select a from qn;
   182  
   183  with qn as (select a from t1 order by 1)
   184  select qn.a from qn, t1 as t2;
   185  
   186  with qn as (select a from t1 order by 1 limit 10)
   187  select qn.a from qn, t1 as t2;
   188  
   189  -- @case
   190  -- @desc:test for with group by
   191  -- @label:bvt
   192  drop table if exists t1;
   193  drop table if exists t2;
   194  create table t1(a int, b int, c int);
   195  insert into t1 values(null,null,null),(2,3,4),(4,5,6);
   196  with qn as (select a, b from t1)
   197  select b from qn group by a;
   198  
   199  with qn as (select a, b from t1 where a=b)
   200  select b from qn group by a;
   201  
   202  with qn as (select a, sum(b) as s from t1 group by a)
   203  select s from qn group by a;
   204  
   205  -- @case
   206  -- @desc:test for with using column in name
   207  -- @label:bvt
   208  drop table if exists t1;
   209  drop table if exists t2;
   210  create table t1(a int, b int, c int);
   211  insert into t1 values(null,null,null),(2,3,4),(4,5,6),(4,5,6),(8,9,10);
   212  -- error
   213  with qn () as (select 1) select * from qn, qn qn1;
   214  with qn (foo, bar) as (select 1) select * from qn, qn qn1;
   215  with qn as (select 1,1) select * from qn;
   216  with qn as (select 1,1 from t1) select * from qn;
   217  with qn (foo, foo) as (select 1,2) select * from qn;
   218  
   219  with qn (foo, bar) as (select 1,1 from t1) select * from qn;
   220  with qn (foo, bar) as (select 1,1) select * from qn;
   221  with qn (foo, bar) as (select 1, 2 from t1 limit 2) select * from qn, qn qn1;
   222  with qn (foo, bar) as (select 1 as col, 2 as coll from t1 limit 2) select * from qn, qn qn1;
   223  with qn (foo, bar) as (select 1 as col, 2 as coll union
   224                         select a,b from t1 order by col) select qn1.bar from qn qn1;
   225  with qn (foo, bar) as (select a, b from t1 limit 2) select qn.bar,foo from qn;
   226  
   227  -- @case
   228  -- @desc:test for with-as with where filtler and in ,some ,any
   229  -- @label:bvt
   230  drop table if exists t1;
   231  drop table if exists t2;
   232  DROP TABLE IF EXISTS t3;
   233  create table t1 (s1 char(5), index s1(s1));
   234  create table t2 (s1 char(5), index s1(s1));
   235  insert into t1 values ('a1'),('a2'),('a3');
   236  insert into t2 values ('a1'),('a2');
   237  with qn as (SELECT s1 FROM t2)
   238  select s1, s1 = ANY (select * from qn) from t1;
   239  with qn as (SELECT s1 FROM t2)
   240  select s1, s1 < ANY (select * from qn) from t1;
   241  with qn as (SELECT s1 FROM t2)
   242  select s1, s1 = ANY (select * from qn) from t1;
   243  
   244  drop table if exists t1;
   245  drop table if exists t2;
   246  DROP TABLE IF EXISTS t3;
   247  create table t1 (a int);
   248  create table t2 (a int, b int);
   249  create table t3 (a int);
   250  create table t4 (a int not null, b int not null);
   251  insert into t1 values (2);
   252  insert into t2 values (1,7),(2,7),(2,9);
   253  insert into t4 values (4,8),(3,8),(5,9);
   254  insert into t3 values(1),(0),(2),(9);
   255  insert into t2 values (100, 5);
   256  with qn as (select b from t2)
   257  select * from t3 where a in (select * from qn);
   258  
   259  with qn as (select b from t2 where b > 7)
   260  select * from t3 where a in (select * from qn);
   261  
   262  with qn as (select b from t2 where b > 7)
   263  select * from t3 where a not in (select * from qn);
   264  
   265  drop table if exists t1;
   266  drop table if exists t2;
   267  DROP TABLE IF EXISTS t3;
   268  DROP TABLE IF EXISTS t4;
   269  DROP TABLE IF EXISTS t5;
   270  DROP TABLE IF EXISTS t6;
   271  DROP TABLE IF EXISTS t7;
   272  create table t1 (a int);
   273  create table t2 (a int, b int);
   274  create table t3 (a int);
   275  create table t4 (a int not null, b int not null);
   276  insert into t1 values (2);
   277  insert into t2 values (1,7),(2,7);
   278  insert into t4 values (4,8),(3,8),(5,9);
   279  insert into t3 values (6),(7),(3);
   280  with qn as (select * from t2 where t2.b=t3.a)
   281  select * from t3 where exists (select * from qn);
   282  
   283  with qn as (select * from t2 where t2.b=t3.a)
   284  select * from t3 where not exists (select * from qn);
   285  drop table if exists t1;
   286  drop table if exists t2;
   287  DROP TABLE IF EXISTS t3;
   288  DROP TABLE IF EXISTS t4;
   289  DROP TABLE IF EXISTS t5;
   290  DROP TABLE IF EXISTS t6;
   291  DROP TABLE IF EXISTS t7;
   292  
   293  -- @case
   294  -- @desc:test for with-as with lots of expression
   295  -- @label:bvt
   296  drop table if exists `t`;
   297  CREATE TABLE `t` (
   298    `c1` int(11) DEFAULT NULL,
   299    `c2` int(11) DEFAULT NULL,
   300    `c3` int(11) DEFAULT NULL,
   301    `c4` int(11) DEFAULT NULL,
   302    `c5` int(11) DEFAULT NULL,
   303    `c6` int(11) DEFAULT NULL,
   304    `c7` int(11) DEFAULT NULL,
   305    `c8` int(11) DEFAULT NULL,
   306    `c9` int(11) DEFAULT NULL,
   307    `c10` int(11) DEFAULT NULL,
   308    `c11` int(11) DEFAULT NULL,
   309    `c12` int(11) DEFAULT NULL,
   310    `c13` int(11) DEFAULT NULL,
   311    `c14` int(11) DEFAULT NULL,
   312    `c15` int(11) DEFAULT NULL,
   313    `c16` int(11) DEFAULT NULL,
   314    `c17` int(11) DEFAULT NULL,
   315    `c18` int(11) DEFAULT NULL,
   316    `c19` int(11) DEFAULT NULL,
   317    `c20` int(11) DEFAULT NULL,
   318    `c21` int(11) DEFAULT NULL,
   319    `c22` int(11) DEFAULT NULL,
   320    `c23` int(11) DEFAULT NULL,
   321    `c24` int(11) DEFAULT NULL,
   322    `c25` int(11) DEFAULT NULL,
   323    `c26` int(11) DEFAULT NULL,
   324    `c27` int(11) DEFAULT NULL,
   325    `c28` int(11) DEFAULT NULL,
   326    `c29` int(11) DEFAULT NULL,
   327    `c30` int(11) DEFAULT NULL,
   328    `c31` int(11) DEFAULT NULL
   329  );
   330  with qn as (select * from t limit 2)
   331  select
   332  (select max(c1) from qn where qn.c1=1),
   333  (select max(c2) from qn where qn.c2=1),
   334  (select max(c3) from qn where qn.c3=1),
   335  (select max(c4) from qn where qn.c4=1),
   336  (select max(c5) from qn where qn.c5=1),
   337  (select max(c6) from qn where qn.c6=1),
   338  (select max(c7) from qn where qn.c7=1),
   339  (select max(c8) from qn where qn.c8=1),
   340  (select max(c9) from qn where qn.c9=1),
   341  (select max(c10) from qn where qn.c10=1),
   342  (select max(c11) from qn where qn.c11=1),
   343  (select max(c12) from qn where qn.c12=1),
   344  (select max(c13) from qn where qn.c13=1),
   345  (select max(c14) from qn where qn.c14=1),
   346  (select max(c15) from qn where qn.c15=1),
   347  (select max(c16) from qn where qn.c16=1),
   348  (select max(c17) from qn where qn.c17=1),
   349  (select max(c18) from qn where qn.c18=1),
   350  (select max(c19) from qn where qn.c19=1),
   351  (select max(c20) from qn where qn.c20=1),
   352  (select max(c21) from qn where qn.c21=1),
   353  (select max(c22) from qn where qn.c22=1),
   354  (select max(c23) from qn where qn.c23=1),
   355  (select max(c24) from qn where qn.c24=1),
   356  (select max(c25) from qn where qn.c25=1),
   357  (select max(c26) from qn where qn.c26=1),
   358  (select max(c27) from qn where qn.c27=1),
   359  (select max(c28) from qn where qn.c28=1),
   360  (select max(c29) from qn where qn.c29=1),
   361  (select max(c30) from qn where qn.c30=1),
   362  (select max(c31) from qn where qn.c31=1) from qn;
   363  drop table if exists `t`;
   364  
   365  
   366  
   367