lateral.iq

# lateral.iq - Tests for LATERAL and TABLE keywords
#
# 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.
#
!use scott
!set outputformat mysql

# Bad: LATERAL tableName
select * from "scott".emp join lateral "scott".dept using (deptno);
parse failed: Encountered "join lateral \"scott\"" at line 1, column 27.
Was expecting one of:
    "AS" ...
    "CROSS" ...
    "EXTEND" ...
    "FOR" ...
    "MATCH_RECOGNIZE" ...
    "OUTER" ...
    "TABLESAMPLE" ...
!error

# Bad: LATERAL TABLE
select * from "scott".emp join lateral table "scott".dept using (deptno);
parse failed: Encountered "join lateral table \"scott\"" at line 1, column 27.
Was expecting one of:
    "AS" ...
    "CROSS" ...
    "EXTEND" ...
    "FOR" ...
    "MATCH_RECOGNIZE" ...
    "OUTER" ...
    "TABLESAMPLE" ...
!error

# Good: LATERAL (subQuery)
# OK even as first item in FROM clause
select * from lateral (select * from "scott".emp) where deptno = 10;
+-------+--------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |      |     10 |
|  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |      |     10 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |      |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+
(3 rows)

!ok

select * from lateral (select * from "scott".emp) as e where deptno = 10;
+-------+--------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |      |     10 |
|  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |      |     10 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |      |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+
(3 rows)

!ok

# Good: Explicit TABLE in parentheses
select * from (table "scott".emp) where deptno = 10;
+-------+--------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+------+--------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |      |     10 |
|  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |      |     10 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |      |     10 |
+-------+--------+-----------+------+------------+---------+------+--------+
(3 rows)

!ok

# Bad: Explicit TABLE
select * from table "scott".emp;
parse failed: Encountered "\"scott\"" at line 1, column 21.
Was expecting:
    "(" ...
!error

select * from lateral (select * from "scott".emp) as e
join (table "scott".dept) using (deptno)
where e.deptno = 10;
+--------+-------+--------+-----------+------+------------+---------+------+------------+----------+
| DEPTNO | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM | DNAME      | LOC      |
+--------+-------+--------+-----------+------+------------+---------+------+------------+----------+
|     10 |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |      | ACCOUNTING | NEW YORK |
|     10 |  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |      | ACCOUNTING | NEW YORK |
|     10 |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |      | ACCOUNTING | NEW YORK |
+--------+-------+--------+-----------+------+------------+---------+------+------------+----------+
(3 rows)

!ok

select *
from "scott".dept,
  lateral (select * from "scott".emp where emp.deptno = dept.deptno) as e;
+--------+------------+----------+-------+--------+-----------+------+------------+---------+---------+---------+
| DEPTNO | DNAME      | LOC      | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO0 |
+--------+------------+----------+-------+--------+-----------+------+------------+---------+---------+---------+
|     10 | ACCOUNTING | NEW YORK |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |         |      10 |
|     10 | ACCOUNTING | NEW YORK |  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |         |      10 |
|     10 | ACCOUNTING | NEW YORK |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |         |      10 |
|     20 | RESEARCH   | DALLAS   |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |         |      20 |
|     20 | RESEARCH   | DALLAS   |  7566 | JONES  | MANAGER   | 7839 | 1981-02-04 | 2975.00 |         |      20 |
|     20 | RESEARCH   | DALLAS   |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |         |      20 |
|     20 | RESEARCH   | DALLAS   |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |         |      20 |
|     20 | RESEARCH   | DALLAS   |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |         |      20 |
|     30 | SALES      | CHICAGO  |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |      30 |
|     30 | SALES      | CHICAGO  |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |      30 |
|     30 | SALES      | CHICAGO  |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |      30 |
|     30 | SALES      | CHICAGO  |  7698 | BLAKE  | MANAGER   | 7839 | 1981-01-05 | 2850.00 |         |      30 |
|     30 | SALES      | CHICAGO  |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |      30 |
|     30 | SALES      | CHICAGO  |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |         |      30 |
+--------+------------+----------+-------+--------+-----------+------+------------+---------+---------+---------+
(14 rows)

!ok

# [CALCITE-2391] Aggregate query with UNNEST or LATERAL fails with ClassCastException
select count(*) as c
from "scott".dept,
  lateral (select * from "scott".emp where emp.deptno = dept.deptno) as e;
+----+
| C  |
+----+
| 14 |
+----+
(1 row)

!ok

# LATERAL plus CTEs (WITH)
with dept (deptno, dname)
    as (values (10, 'ACCOUNTING'), (20, 'RESEARCH')),
  emp (empno, deptno, ename)
    as (values (7369, 20, 'SMITH'), (7782, 10, 'CLARK'), (7499, 30, 'ALLEN'))
select *
from dept,
  lateral (select * from emp where emp.deptno = dept.deptno) as emp;
+--------+------------+-------+---------+-------+
| DEPTNO | DNAME      | EMPNO | DEPTNO0 | ENAME |
+--------+------------+-------+---------+-------+
|     10 | ACCOUNTING |  7782 |      10 | CLARK |
|     20 | RESEARCH   |  7369 |      20 | SMITH |
+--------+------------+-------+---------+-------+
(2 rows)

!ok

# [CALCITE-6266] SqlValidatorException with LATERAL TABLE and JOIN
# The problem also occurred in ', LATERAL (SELECT ...) JOIN', because
# ',' should had lower precedence than 'JOIN', but should have had equal
# precedence.
with dept (deptno, dname)
    as (values (10, 'ACCOUNTING'), (20,'RESEARCH')),
  emp (empno, deptno, ename)
    as (values (7369, 20, 'SMITH'), (7782, 10, 'CLARK'), (7499, 30, 'ALLEN'))
select *
from dept,
  lateral (select * from emp where emp.deptno = dept.deptno) as emp
  cross join (values 'A', 'B') as v (v);
+--------+------------+-------+---------+-------+---+
| DEPTNO | DNAME      | EMPNO | DEPTNO0 | ENAME | V |
+--------+------------+-------+---------+-------+---+
|     10 | ACCOUNTING |  7782 |      10 | CLARK | A |
|     10 | ACCOUNTING |  7782 |      10 | CLARK | B |
|     20 | RESEARCH   |  7369 |      20 | SMITH | A |
|     20 | RESEARCH   |  7369 |      20 | SMITH | B |
+--------+------------+-------+---------+-------+---+
(4 rows)

!ok

# UNNEST applied to VALUES
with t (x, ys)
  as (values (1, array [2,3]), (4, array [5]))
select *
from t,
  unnest(t.ys) as y;
+---+--------+---+
| X | YS     | Y |
+---+--------+---+
| 1 | [2, 3] | 2 |
| 1 | [2, 3] | 3 |
| 4 | [5]    | 5 |
+---+--------+---+
(3 rows)

!ok

# LATERAL UNNEST means the same as UNNEST
# (Have checked Postgres)
with t (x, ys)
  as (values (1, array [2,3]), (4, array [5]))
select *
from t,
  lateral unnest(t.ys) as y;
+---+--------+---+
| X | YS     | Y |
+---+--------+---+
| 1 | [2, 3] | 2 |
| 1 | [2, 3] | 3 |
| 4 | [5]    | 5 |
+---+--------+---+
(3 rows)

!ok

# LEFT JOIN LATERAL
select ename, deptno, ename2
from emp as o
  left join lateral (select ename as ename2
                     from emp
                     where deptno = o.deptno + 10) on true
where job = 'MANAGER';
+-------+--------+--------+
| ENAME | DEPTNO | ENAME2 |
+-------+--------+--------+
| BLAKE |     30 |        |
| CLARK |     10 | ADAMS  |
| CLARK |     10 | FORD   |
| CLARK |     10 | JONES  |
| CLARK |     10 | SCOTT  |
| CLARK |     10 | SMITH  |
| JONES |     20 | ALLEN  |
| JONES |     20 | BLAKE  |
| JONES |     20 | JAMES  |
| JONES |     20 | MARTIN |
| JONES |     20 | TURNER |
| JONES |     20 | WARD   |
+-------+--------+--------+
(12 rows)

!ok

# [CALCITE-4693] Query with Lateral Join should converted to Left Join When sub-query is aggregate query

select * from "scott".dept, lateral (select count(cast(deptno as integer)) from "scott".emp where emp.deptno = dept.deptno);
+--------+------------+----------+--------+
| DEPTNO | DNAME      | LOC      | EXPR$0 |
+--------+------------+----------+--------+
|     10 | ACCOUNTING | NEW YORK |      3 |
|     20 | RESEARCH   | DALLAS   |      5 |
|     30 | SALES      | CHICAGO  |      6 |
|     40 | OPERATIONS | BOSTON   |      0 |
+--------+------------+----------+--------+
(4 rows)

!ok
EnumerableCalc(expr#0..4=[{inputs}], expr#5=[IS NULL($t4)], expr#6=[0:BIGINT], expr#7=[CASE($t5, $t6, $t4)], proj#0..2=[{exprs}], EXPR$0=[$t7])
  EnumerableHashJoin(condition=[=($0, $3)], joinType=[left])
    EnumerableTableScan(table=[[scott, DEPT]])
    EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t2)], expr#4=[0], expr#5=[CASE($t3, $t2, $t4)], DEPTNO=[$t0], EXPR$0=[$t5])
      EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($0, $1)], joinType=[left])
        EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
          EnumerableTableScan(table=[[scott, DEPT]])
        EnumerableAggregate(group=[{0}], EXPR$0=[COUNT($1)])
          EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER], expr#9=[IS NOT NULL($t7)], DEPTNO=[$t7], $f0=[$t8], $condition=[$t9])
            EnumerableTableScan(table=[[scott, EMP]])
!plan

select * from "scott".dept, lateral (select count(cast(deptno as integer)) from "scott".emp where emp.deptno = dept.deptno + 10);
+--------+------------+----------+--------+
| DEPTNO | DNAME      | LOC      | EXPR$0 |
+--------+------------+----------+--------+
|     10 | ACCOUNTING | NEW YORK |      5 |
|     20 | RESEARCH   | DALLAS   |      6 |
|     30 | SALES      | CHICAGO  |      0 |
|     40 | OPERATIONS | BOSTON   |      0 |
+--------+------------+----------+--------+
(4 rows)

!ok
EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NULL($t5)], expr#7=[0:BIGINT], expr#8=[CASE($t6, $t7, $t5)], proj#0..2=[{exprs}], EXPR$0=[$t8])
  EnumerableHashJoin(condition=[=($3, $4)], joinType=[left])
    EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10], expr#4=[+($t0, $t3)], proj#0..2=[{exprs}], $f3=[$t4])
      EnumerableTableScan(table=[[scott, DEPT]])
    EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t2)], expr#4=[0], expr#5=[CASE($t3, $t2, $t4)], DEPTNO0=[$t0], EXPR$0=[$t5])
      EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($0, $1)], joinType=[left])
        EnumerableAggregate(group=[{0}])
          EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10], expr#4=[+($t0, $t3)], $f3=[$t4])
            EnumerableTableScan(table=[[scott, DEPT]])
        EnumerableAggregate(group=[{0}], EXPR$0=[COUNT($1)])
          EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER], expr#9=[IS NOT NULL($t7)], DEPTNO0=[$t8], $f0=[$t8], $condition=[$t9])
            EnumerableTableScan(table=[[scott, EMP]])
!plan

select * from "scott".dept, lateral (select sum(cast(deptno as integer)) from "scott".emp where emp.deptno = dept.deptno);
+--------+------------+----------+--------+
| DEPTNO | DNAME      | LOC      | EXPR$0 |
+--------+------------+----------+--------+
|     10 | ACCOUNTING | NEW YORK |     30 |
|     20 | RESEARCH   | DALLAS   |    100 |
|     30 | SALES      | CHICAGO  |    180 |
|     40 | OPERATIONS | BOSTON   |        |
+--------+------------+----------+--------+
(4 rows)

!ok
EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}], EXPR$0=[$t4])
  EnumerableMergeJoin(condition=[=($0, $3)], joinType=[left])
    EnumerableTableScan(table=[[scott, DEPT]])
    EnumerableSort(sort0=[$0], dir0=[ASC])
      EnumerableAggregate(group=[{0}], EXPR$0=[SUM($1)])
        EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER], expr#9=[IS NOT NULL($t7)], DEPTNO=[$t7], $f0=[$t8], $condition=[$t9])
          EnumerableTableScan(table=[[scott, EMP]])
!plan

select * from "scott".dept, lateral (select sum(cast(deptno as integer)) from "scott".emp where emp.deptno = dept.deptno + 10);
+--------+------------+----------+--------+
| DEPTNO | DNAME      | LOC      | EXPR$0 |
+--------+------------+----------+--------+
|     10 | ACCOUNTING | NEW YORK |    100 |
|     20 | RESEARCH   | DALLAS   |    180 |
|     30 | SALES      | CHICAGO  |        |
|     40 | OPERATIONS | BOSTON   |        |
+--------+------------+----------+--------+
(4 rows)

!ok
EnumerableCalc(expr#0..5=[{inputs}], proj#0..2=[{exprs}], EXPR$0=[$t5])
  EnumerableHashJoin(condition=[=($3, $4)], joinType=[left])
    EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10], expr#4=[+($t0, $t3)], proj#0..2=[{exprs}], $f3=[$t4])
      EnumerableTableScan(table=[[scott, DEPT]])
    EnumerableAggregate(group=[{0}], EXPR$0=[SUM($1)])
      EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER], expr#9=[IS NOT NULL($t7)], DEPTNO0=[$t8], $f0=[$t8], $condition=[$t9])
        EnumerableTableScan(table=[[scott, EMP]])
!plan

# End lateral.iq