Materialize/test/sqllogictest/subquery.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

mode cockroach

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_enable_table_keys = true
----
COMPLETE 0

statement ok
CREATE TABLE nullary ()

statement ok
CREATE TABLE peeps (
  peep text
)

statement ok
INSERT INTO peeps VALUES ('alice'), ('bob'), ('eve')

statement ok
CREATE TABLE likes (
  liker text,
  likee text
)

statement ok
INSERT INTO likes VALUES ('alice', 'bob'), ('bob', 'eve'), ('alice', 'eve')

query TB rowsort
SELECT peep, EXISTS(
  SELECT * FROM likes WHERE peep = liker
) FROM peeps
----
alice true
bob true
eve false

query TTB rowsort
SELECT *, EXISTS(
  SELECT * FROM likes as likes2 WHERE likes.likee = likes2.liker
) FROM likes
----
alice bob true
alice eve false
bob eve false

# inner scope shadows outer scope, so `likee` without qualification refers to `likes2.likee`
query TTB rowsort
SELECT *, EXISTS(
  SELECT * FROM likes as likes2 WHERE likee = likes2.liker
) FROM likes
----
alice bob false
alice eve false
bob eve false

# similarly, without `as likes2`, `likes.liker` refers to the inner scope
query TTB rowsort
SELECT *, EXISTS(
  SELECT * FROM likes WHERE likee = likes.liker
) FROM likes
----
alice bob false
alice eve false
bob eve false

statement error more than one record produced in subquery
SELECT peep, (
  SELECT likee FROM likes WHERE liker = peep
) FROM peeps

statement ok
CREATE TABLE favorites (peep text, other text)

statement ok
INSERT INTO favorites VALUES ('alice', 'eve'), ('alice', 'alice'), ('bob', 'bob')

query TTB rowsort
SELECT
    liker,
    likee,
    likee IN (SELECT other FROM favorites WHERE peep = liker) AS is_favorite
FROM likes
----
alice  bob  false
bob    eve  false
alice  eve  true

query TTB rowsort
SELECT
    liker,
    likee,
    likee NOT IN (SELECT other FROM favorites WHERE peep = liker) AS is_favorite
FROM likes
----
alice  bob  true
bob    eve  true
alice  eve  false

statement ok
CREATE TABLE age (peep text, age int)

statement ok
INSERT INTO age VALUES ('alice', 103), ('bob', 100), ('eve', 104)

statement error more than one record produced in subquery
SELECT peeps.peep, (
  SELECT age FROM likes, age WHERE peeps.peep = liker AND likee = age.peep
) FROM peeps

query TB rowsort
SELECT peeps.peep, age < ANY (
  SELECT age FROM likes, age WHERE peeps.peep = liker AND likee = age.peep
) FROM peeps, age
WHERE peeps.peep = age.peep
----
alice true
bob true
eve false

query TB rowsort
SELECT peeps.peep, age < ALL (
  SELECT age FROM likes, age WHERE peeps.peep = liker AND likee = age.peep
) FROM peeps, age
WHERE peeps.peep = age.peep
----
alice false
bob true
eve true

# ANY/ALL semantics

query BBBBBBBBBBBBBBB
(VALUES (
  1 < ANY(SELECT * FROM (VALUES (1)) WHERE false),
  1 < ANY(VALUES (0)),
  1 < ANY(VALUES (1)),
  1 < ANY(VALUES (2)),
  1 < ANY(VALUES (0), (NULL)),
  1 < ANY(VALUES (1), (NULL)),
  1 < ANY(VALUES (2), (NULL)),
  NULL < ANY(SELECT * FROM (VALUES (1)) WHERE false),
  NULL < ANY(VALUES (0)),
  NULL < ANY(VALUES (1)),
  NULL < ANY(VALUES (2)),
  NULL < ANY(VALUES (NULL)),
  NULL < ANY(VALUES (0), (NULL)),
  NULL < ANY(VALUES (1), (NULL)),
  NULL < ANY(VALUES (2), (NULL))
))
----
false  false  false  true  NULL  NULL  true  false  NULL  NULL  NULL  NULL  NULL  NULL  NULL

query BBBBBBBBBBBBBBB
(VALUES (
  1 < ALL(SELECT * FROM (VALUES (1)) WHERE false),
  1 < ALL(VALUES (0)),
  1 < ALL(VALUES (1)),
  1 < ALL(VALUES (2)),
  1 < ALL(VALUES (0), (NULL)),
  1 < ALL(VALUES (1), (NULL)),
  1 < ALL(VALUES (2), (NULL)),
  NULL < ALL(SELECT * FROM (VALUES (1)) WHERE false),
  NULL < ALL(VALUES (0)),
  NULL < ALL(VALUES (1)),
  NULL < ALL(VALUES (2)),
  NULL < ALL(VALUES (NULL)),
  NULL < ALL(VALUES (0), (NULL)),
  NULL < ALL(VALUES (1), (NULL)),
  NULL < ALL(VALUES (2), (NULL))
))
----
true  false  false  true  false  false  NULL  true  NULL  NULL  NULL  NULL  NULL  NULL  NULL

query error db error: ERROR: subquery1 has 0 columns available but 1 columns specified
SELECT 1 < ALL(SELECT * FROM nullary)

query error Expected subselect to return 1 column, got 0 columns
SELECT (SELECT);
----

query
SELECT * FROM (SELECT);
----


query error db error: ERROR: subquery1 has 2 columns available but 1 columns specified
SELECT 1 < ALL(SELECT 1, 2)

statement ok
CREATE TABLE s1 (a int NOT NULL)

statement ok
CREATE TABLE s2 (a int NOT NULL)

statement ok
CREATE TABLE s3 (b int NOT NULL)

statement ok
INSERT INTO s1 VALUES (0)

statement ok
INSERT INTO s2 VALUES (1)

statement ok
INSERT INTO s3 VALUES (1)

# Verify that scope resolution prefers the closer scope when a name occurs in
# multiple outer levels.
query B
SELECT true FROM s1 WHERE EXISTS (SELECT true FROM s2 WHERE EXISTS (SELECT true FROM s3 WHERE a = s3.b))
----
true

statement ok
CREATE TABLE t1 (a int NOT NULL)

statement ok
CREATE TABLE t2 (b int NOT NULL)

statement ok
CREATE TABLE t3 (a int NOT NULL, b int NOT NULL)

statement ok
INSERT INTO t1 VALUES (1), (2), (3)

statement ok
INSERT INTO t2 VALUES (1), (2), (3)

statement ok
INSERT INTO t3 VALUES (2, 3), (1, 4)

# Test a constraint that spans multiple levels of nesting.
query I
SELECT a FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE EXISTS (SELECT 1 FROM t3 WHERE t1.a = t3.a AND t2.b = t3.b))
----
2

mode standard

# Verify that the plans for some simple non-correlated subqueries are sane.

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2)
----
Explained Query:
  CrossJoin type=differential // { arity: 1 }
    implementation
      %1[×]UA » %0:t1[×]
    ArrangeBy keys=[[]] // { arity: 1 }
      ReadStorage materialize.public.t1 // { arity: 1 }
    ArrangeBy keys=[[]] // { arity: 0 }
      Distinct project=[] // { arity: 0 }
        Project () // { arity: 0 }
          ReadStorage materialize.public.t2 // { arity: 1 }

Source materialize.public.t1
Source materialize.public.t2

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT *  FROM t1, t3 WHERE t1.a = t3.a AND EXISTS (SELECT * FROM t2)
----
Explained Query:
  Project (#0{a}, #0{a}, #2{b}) // { arity: 3 }
    Join on=(#0{a} = #1{a}) type=delta // { arity: 3 }
      implementation
        %0:t1 » %2[×]UA » %1:t3[#0{a}]K
        %1:t3 » %2[×]UA » %0:t1[#0{a}]K
        %2 » %0:t1[×] » %1:t3[#0{a}]K
      ArrangeBy keys=[[], [#0{a}]] // { arity: 1 }
        ReadStorage materialize.public.t1 // { arity: 1 }
      ArrangeBy keys=[[#0{a}]] // { arity: 2 }
        ReadStorage materialize.public.t3 // { arity: 2 }
      ArrangeBy keys=[[]] // { arity: 0 }
        Distinct project=[] // { arity: 0 }
          Project () // { arity: 0 }
            ReadStorage materialize.public.t2 // { arity: 1 }

Source materialize.public.t1
Source materialize.public.t2
Source materialize.public.t3

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT *  FROM t1, t3 WHERE t1.a = t3.a AND EXISTS (SELECT * FROM t2 WHERE t3.b = t2.b)
----
Explained Query:
  Project (#0{a}, #0{a}, #2{b}) // { arity: 3 }
    Join on=(#0{a} = #1{a} AND #2{b} = #3{b}) type=delta // { arity: 4 }
      implementation
        %0:t1 » %1:t3[#0{a}]K » %2[#0]UKA
        %1:t3 » %2[#0]UKA » %0:t1[#0{a}]K
        %2 » %1:t3[#1]K » %0:t1[#0{a}]K
      ArrangeBy keys=[[#0{a}]] // { arity: 1 }
        ReadStorage materialize.public.t1 // { arity: 1 }
      ArrangeBy keys=[[#0{a}], [#1{b}]] // { arity: 2 }
        ReadStorage materialize.public.t3 // { arity: 2 }
      ArrangeBy keys=[[#0{b}]] // { arity: 1 }
        Distinct project=[#0{b}] // { arity: 1 }
          ReadStorage materialize.public.t2 // { arity: 1 }

Source materialize.public.t1
Source materialize.public.t2
Source materialize.public.t3

Target cluster: quickstart

EOF

# Regression test for database-issues#396
# The following subquery currently generates a plan with a map with
# 4 scalars that refer to other scalars in the map. If query planning optimizes away
# this particular case, replace with another query that generates such a plan
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR
SELECT age, ascii_num * 2 as result FROM (
  SELECT age, ascii(letter) AS ascii_num FROM (
    SELECT age, substr(replaced, 2, 1) AS letter FROM (
      SELECT age, replace(likee, 'o', 'i') AS replaced FROM (
        SELECT likee, age FROM likes, age WHERE liker=peep
      )
    )
  )
)
----
Explained Query:
  Project (#3{age}, #4) // { arity: 2 }
    Map ((ascii(substr(replace(#1{likee}, "o", "i"), 2, 1)) * 2)) // { arity: 5 }
      Join on=(#0{liker} = #2{peep}) type=differential // { arity: 4 }
        implementation
          %0:likes[#0{liker}]K » %1:age[#0{peep}]K
        ArrangeBy keys=[[#0{liker}]] // { arity: 2 }
          Filter (#0{liker}) IS NOT NULL // { arity: 2 }
            ReadStorage materialize.public.likes // { arity: 2 }
        ArrangeBy keys=[[#0{peep}]] // { arity: 2 }
          Filter (#0{peep}) IS NOT NULL // { arity: 2 }
            ReadStorage materialize.public.age // { arity: 2 }

Source materialize.public.likes
  filter=((#0{liker}) IS NOT NULL)
Source materialize.public.age
  filter=((#0{peep}) IS NOT NULL)

Target cluster: quickstart

EOF

mode cockroach

query II rowsort
SELECT age, ascii_num * 2 as result FROM (
  SELECT age, ascii(letter) AS ascii_num FROM (
    SELECT age, substr(replaced, 2, 1) AS letter FROM (
      SELECT age, replace(likee, 'o', 'i') AS replaced FROM (
        SELECT likee, age FROM likes, age WHERE liker=peep
      )
    )
  )
)
----
100  236
103  210
103  236

# regression test for https://github.com/MaterializeInc/database-issues/issues/621

statement ok
CREATE TABLE tab0(pk INTEGER PRIMARY KEY, col0 INTEGER, col1 FLOAT, col3 INTEGER)

statement ok
INSERT INTO tab0 VALUES(0,1,2,63)

statement ok
INSERT INTO tab0 VALUES(87,63,3,4)

query I rowsort
SELECT pk FROM tab0 WHERE
(
  col3 IN (
    SELECT col0 FROM tab0
  )
)
----
0

query I rowsort
SELECT pk FROM tab0 WHERE
(
  (
    col0 IN (
      SELECT col3 FROM tab0 WHERE col3 IN (
        SELECT col0 FROM tab0
      )
    )
  )
)
----
87

query I rowsort
SELECT pk FROM tab0 WHERE
(
  col3 IN (
    SELECT col0 FROM tab0
  )
  OR
  (
    col0 IN (
      SELECT col3 FROM tab0 WHERE col3 IN (
        SELECT col0 FROM tab0
      )
    )
  )
)
----
0
87

query I
SELECT col FROM (SELECT 1 AS col) t WHERE col = ANY(VALUES (1))
----
1

query I
SELECT col FROM (SELECT 1 AS col) t WHERE col = ANY((VALUES (1)))
----
1

query I
SELECT col FROM (SELECT 1 AS col) t WHERE col IN(VALUES(1))
----
1

query I
SELECT col FROM (SELECT 1 AS col) t WHERE col IN(((VALUES(1))))
----
1

statement ok
CREATE TABLE x (a int not null)

statement ok
CREATE TABLE y (b int not null)

statement ok
INSERT INTO x VALUES (1), (2), (3);

statement ok
INSERT INTO y VALUES (2), (3), (4);

query B
SELECT b != ALL(SELECT a FROM x) FROM y
----
false
false
true

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT b IN (SELECT a FROM x) FROM y
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{b}] // { arity: 1 }
        ReadStorage materialize.public.y // { arity: 1 }
    cte l1 =
      Project (#0{b}) // { arity: 1 }
        Join on=(#0{b} = #1{a}) type=differential // { arity: 2 }
          implementation
            %0:l0[#0]UKA » %1[#0]UKA
          ArrangeBy keys=[[#0{b}]] // { arity: 1 }
            Get l0 // { arity: 1 }
          ArrangeBy keys=[[#0{a}]] // { arity: 1 }
            Distinct project=[#0{a}] // { arity: 1 }
              ReadStorage materialize.public.x // { arity: 1 }
  Return // { arity: 1 }
    Project (#2) // { arity: 1 }
      Join on=(#0{b} = #1{b}) type=differential // { arity: 3 }
        implementation
          %0:y[#0]K » %1[#0]K
        ArrangeBy keys=[[#0{b}]] // { arity: 1 }
          ReadStorage materialize.public.y // { arity: 1 }
        ArrangeBy keys=[[#0{b}]] // { arity: 2 }
          Union // { arity: 2 }
            Map (true) // { arity: 2 }
              Get l1 // { arity: 1 }
            Map (false) // { arity: 2 }
              Union // { arity: 1 }
                Negate // { arity: 1 }
                  Get l1 // { arity: 1 }
                Get l0 // { arity: 1 }

Source materialize.public.x
Source materialize.public.y

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT b != ALL(SELECT a FROM x) FROM y
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{b}] // { arity: 1 }
        ReadStorage materialize.public.y // { arity: 1 }
    cte l1 =
      Project (#0{b}) // { arity: 1 }
        Join on=(#0{b} = #1{a}) type=differential // { arity: 2 }
          implementation
            %0:l0[#0]UKA » %1[#0]UKA
          ArrangeBy keys=[[#0{b}]] // { arity: 1 }
            Get l0 // { arity: 1 }
          ArrangeBy keys=[[#0{a}]] // { arity: 1 }
            Distinct project=[#0{a}] // { arity: 1 }
              ReadStorage materialize.public.x // { arity: 1 }
  Return // { arity: 1 }
    Project (#3) // { arity: 1 }
      Map (NOT(#2)) // { arity: 4 }
        Join on=(#0{b} = #1{b}) type=differential // { arity: 3 }
          implementation
            %0:y[#0]K » %1[#0]K
          ArrangeBy keys=[[#0{b}]] // { arity: 1 }
            ReadStorage materialize.public.y // { arity: 1 }
          ArrangeBy keys=[[#0{b}]] // { arity: 2 }
            Union // { arity: 2 }
              Map (true) // { arity: 2 }
                Get l1 // { arity: 1 }
              Map (false) // { arity: 2 }
                Union // { arity: 1 }
                  Negate // { arity: 1 }
                    Get l1 // { arity: 1 }
                  Get l0 // { arity: 1 }

Source materialize.public.x
Source materialize.public.y

Target cluster: quickstart

EOF

# Check that we correctly invert the filter here via De Morgan.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT b > ALL(SELECT a FROM x) FROM y
----
Explained Query:
  With
    cte l0 =
      Distinct project=[#0{b}] // { arity: 1 }
        ReadStorage materialize.public.y // { arity: 1 }
    cte l1 =
      Distinct project=[#0{b}] // { arity: 1 }
        Project (#0{b}) // { arity: 1 }
          Filter (#0{b} <= #1{a}) // { arity: 2 }
            CrossJoin type=differential // { arity: 2 }
              implementation
                %0:l0[×] » %1:x[×]
              ArrangeBy keys=[[]] // { arity: 1 }
                Get l0 // { arity: 1 }
              ArrangeBy keys=[[]] // { arity: 1 }
                ReadStorage materialize.public.x // { arity: 1 }
  Return // { arity: 1 }
    Project (#3) // { arity: 1 }
      Map (NOT(#2)) // { arity: 4 }
        Join on=(#0{b} = #1{b}) type=differential // { arity: 3 }
          implementation
            %0:y[#0]K » %1[#0]K
          ArrangeBy keys=[[#0{b}]] // { arity: 1 }
            ReadStorage materialize.public.y // { arity: 1 }
          ArrangeBy keys=[[#0{b}]] // { arity: 2 }
            Union // { arity: 2 }
              Map (true) // { arity: 2 }
                Get l1 // { arity: 1 }
              Map (false) // { arity: 2 }
                Union // { arity: 1 }
                  Negate // { arity: 1 }
                    Get l1 // { arity: 1 }
                  Get l0 // { arity: 1 }

Source materialize.public.x
Source materialize.public.y

Target cluster: quickstart

EOF

statement ok
CREATE TABLE xs (x int not null)

statement ok
CREATE TABLE ys (y int not null)

statement ok
INSERT INTO xs VALUES (1), (1), (2);

statement ok
INSERT INTO ys VALUES (0), (1), (2);

# Check that we correctly perform subqueries that do not preserve
# multiplicity of the outer relation.
query II rowsort
select x, (select count(*) from ys where y < x) from xs
----
1  1
1  1
2  2

# Tests that conditional subqueries should not error.
query RR rowsort
select y, (
    case when (select count(*) from xs where x < y) != 0
         then (select 1.0 / count(*) from xs where x < y)
         else (select 1.0 / (count(*) - 1) from xs where x < y)
         end
  )
from ys
----
0  -1
1  -1
2  0.5

# Regression test for database-issues#1221, in which quantified comparision simplification was
# not correctly handling LATERAL joins.
query II
SELECT * FROM (VALUES (1)), LATERAL (SELECT * FROM (SELECT column1) WHERE true)
----
1  1

# Regression test for database-issues#1222, in which the query planner mishandled the outer
# scope of a nested LATERAL join.
query IIII
SELECT * FROM
    (SELECT 1, 1 AS col2),
    LATERAL (SELECT * FROM (SELECT col2) LEFT JOIN LATERAL (SELECT col2) ON true)
----
1  1  1  1

# Regression test for database-issues#1287, in which quantified expression simplification
# failed to handle map expressions which depended upon a column introduced by
# an earlier expression in the same map node.
query I
SELECT (SELECT 1 FROM ((SELECT col1) UNION (SELECT 1)))
FROM (SELECT 1 col1)
----
1

query error aggregate functions that refer exclusively to outer columns not yet supported
SELECT (SELECT count(likes.likee)) FROM likes

# Regression test for database-issues#2209, where the multiplicity of the outer query was not
# preserved when an EXISTS subquery only involves constants/mpf/flatmaps.
query I
SELECT x FROM xs WHERE EXISTS (SELECT y FROM (SELECT 1 as y) WHERE x = y)
----
1
1

# Regression test for database-issues#2994, in which the presence of a SELECT subquery inside
# of another subexpression produced a degenerate join that was not elided before
# join implementation planning.
query I
SELECT 1 FROM x RIGHT JOIN (SELECT 2 FROM y) ON  NULL
WHERE a IN (9, 0)
  OR 0  <  ALL (
      SELECT agg1  NULL
      FROM (SELECT MAX(3) agg1  FROM (SELECT  FROM x)
      GROUP BY TRUE)
  )
  GROUP BY TRUE;
----
1

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT 1 FROM x RIGHT JOIN (SELECT 2 FROM y) ON  NULL
WHERE a IN (9, 0)
  OR 0  <  ALL (
      SELECT agg1  NULL
      FROM (SELECT MAX(3) agg1  FROM (SELECT  FROM x)
      GROUP BY TRUE)
  )
  GROUP BY TRUE;
----
Explained Query:
  Map (1) // { arity: 1 }
    Distinct project=[] // { arity: 0 }
      Project () // { arity: 0 }
        ReadStorage materialize.public.y // { arity: 1 }

Source materialize.public.x
Source materialize.public.y

Target cluster: quickstart

EOF