Materialize/test/sqllogictest/cockroach/subquery_correlated.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# 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.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/subquery_correlated
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

mode cockroach

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

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

# ------------------------------------------------------------------------------
# Create a simple schema that models customers and orders. Each customer has an
# id (c_id), and has zero or more orders that are related via a foreign key of
# the same name. A customer has a billing state and an order has a shipping
# state, either of which could be NULL. This schema, while simple, is rich
# enough to provide many interesting correlated subquery variations.
# ------------------------------------------------------------------------------
statement ok
CREATE TABLE c (c_id INT PRIMARY KEY, bill TEXT)

statement ok
CREATE TABLE o (o_id INT PRIMARY KEY, c_id INT, ship TEXT)

statement ok
INSERT INTO c VALUES
    (1, 'CA'),
    (2, 'TX'),
    (3, 'MA'),
    (4, 'TX'),
    (5, NULL),
    (6, 'FL')

statement ok
INSERT INTO o VALUES
    (10, 1, 'CA'), (20, 1, 'CA'), (30, 1, 'CA'),
    (40, 2, 'CA'), (50, 2, 'TX'), (60, 2, NULL),
    (70, 4, 'WY'), (80, 4, NULL),
    (90, 6, 'WA')

# ------------------------------------------------------------------------------
# Subqueries in select filters.
# ------------------------------------------------------------------------------

# Customers with orders.
query IT rowsort
SELECT * FROM c WHERE EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id);
----
1  CA
2  TX
4  TX
6  FL

# Customers with no orders.
query IT rowsort
SELECT * FROM c WHERE NOT EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id);
----
3  MA
5  NULL

# Customers with orders or with no orders (should return all customers).
query IT rowsort
SELECT *
FROM c
WHERE
    EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id)
    OR NOT EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id);
----
1  CA
2  TX
3  MA
4  TX
5  NULL
6  FL

# Customers with billing address in TX that have orders.
query IT rowsort
SELECT * FROM c WHERE EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id AND c.bill='TX');
----
2  TX
4  TX

# Customers that have at least one order shipped to WY.
query IT rowsort
SELECT * FROM c WHERE 'WY' IN (SELECT ship FROM o WHERE o.c_id=c.c_id);
----
4  TX

# Customers that have at least one order shipped to WY or to WA.
query IT rowsort
SELECT *
FROM c
WHERE
    'WY' IN (SELECT ship FROM o WHERE o.c_id=c.c_id)
    OR 'WA' IN (SELECT ship FROM o WHERE o.c_id=c.c_id);
----
4  TX
6  FL

# Customers that have at least one order shipped to CA, but none to TX.
query IT rowsort
SELECT *
FROM c
WHERE
    'CA' IN (SELECT ship FROM o WHERE o.c_id=c.c_id)
    AND 'TX' NOT IN (SELECT ship FROM o WHERE o.c_id=c.c_id);
----
1  CA

# Customers with at least one order with billing addr = shipping addr.
query IT rowsort
SELECT * FROM c WHERE bill IN (SELECT ship FROM o WHERE o.c_id=c.c_id);
----
1  CA
2  TX

# Customers with all orders with billing addr = shipping addr.
query IT rowsort
SELECT * FROM c WHERE bill = ALL(SELECT ship FROM o WHERE o.c_id=c.c_id);
----
1  CA
3  MA
5  NULL

# Customers with no order with billing addr = shipping addr (with NULL ship).
query IT rowsort
SELECT * FROM c WHERE bill NOT IN (SELECT ship FROM o WHERE o.c_id=c.c_id);
----
3  MA
5  NULL
6  FL

# Customers with no order with billing addr = shipping addr (no NULL ship).
query IT rowsort
SELECT * FROM c WHERE bill NOT IN (SELECT ship FROM o WHERE o.c_id=c.c_id AND ship IS NOT NULL);
----
3  MA
4  TX
5  NULL
6  FL

# Customers with no order with billing addr = shipping addr (only NULL ship).
query IT rowsort
SELECT * FROM c WHERE bill NOT IN (SELECT ship FROM o WHERE o.c_id=c.c_id AND ship IS NULL);
----
1  CA
3  MA
5  NULL
6  FL

# Customers with bill state < any ship state.
query IT rowsort
SELECT * FROM c WHERE bill < ANY(SELECT ship FROM o WHERE o.c_id=c.c_id);
----
4  TX
6  FL

# Customers where bill state < any ship state is null result. This prevents
# normalizing ANY into EXISTS.
query IT rowsort
SELECT * FROM c WHERE (bill < ANY(SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NULL;
----
2  TX

# Customers where bill state < any ship state is not null result. This prevents
# normalizing ANY into EXISTS.
query IT rowsort
SELECT * FROM c WHERE (bill < ANY(SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NOT NULL;
----
1  CA
3  MA
4  TX
5  NULL
6  FL

# Customers with bill state > any ship state.
query IT rowsort
SELECT * FROM c WHERE bill > ANY(SELECT ship FROM o WHERE o.c_id=c.c_id);
----
2  TX

# Customers where bill state > any ship state is null result. This prevents
# normalizing ANY into EXISTS.
query IT rowsort
SELECT * FROM c WHERE (bill > ANY(SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NULL;
----
4  TX

# Customers where bill state > any ship state is not null result. This prevents
# normalizing ANY into EXISTS.
query IT rowsort
SELECT * FROM c WHERE (bill > ANY(SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NOT NULL;
----
1  CA
2  TX
3  MA
5  NULL
6  FL

# Customers where bill state matches any ship state.
query IT rowsort
SELECT * FROM c WHERE bill = ANY(SELECT ship FROM o);
----
1  CA
2  TX
4  TX

# Customers where bill state matches any ship state or is null.
query IT rowsort
SELECT * FROM c WHERE bill = ANY(SELECT ship FROM o) OR bill IS NULL;
----
1  CA
2  TX
4  TX
5  NULL

# Test NULL IN case. Use IS NOT NULL to prevent normalize ANY into EXISTS.
query IT rowsort
SELECT * FROM c WHERE (NULL::text IN (SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NOT NULL;
----
3  MA
5  NULL

# Test NULL NOT IN case. Use IS NOT NULL to prevent normalize ANY into EXISTS.
query IT rowsort
SELECT * FROM c WHERE (NULL::text NOT IN (SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NOT NULL;
----
3  MA
5  NULL

# Customers where it is unknown whether a replaced bill state is one of the ship
# states. This tests a more complex scalar expression as argument to IN.
query IT rowsort
SELECT * FROM c WHERE (replace(bill, 'TX', 'WY') IN (SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NULL;
----
2  TX

# Customers with all orders with billing addr = shipping addr, or with at least
# one order shipped to WY.
query IT rowsort
SELECT *
FROM c
WHERE
    bill = ALL(SELECT ship FROM o WHERE o.c_id=c.c_id)
    OR EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id AND ship='WY');
----
1  CA
3  MA
4  TX
5  NULL

# Customers with all orders with billing addr = shipping addr, but with at least
# one order.
query IT rowsort
SELECT *
FROM c
WHERE
    bill = ALL(SELECT ship FROM o WHERE o.c_id=c.c_id)
    AND EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id);
----
1  CA

# Customers with more than one order.
query IT rowsort
SELECT * FROM c WHERE (SELECT count(*) FROM o WHERE o.c_id=c.c_id) > 1;
----
1  CA
2  TX
4  TX

# Customers with more than one order shipped to a known state (i.e. NOT NULL).
query IT rowsort
SELECT * FROM c WHERE (SELECT count(ship) FROM o WHERE o.c_id=c.c_id) > 1;
----
1  CA
2  TX

# For each customer, orders shipped to lowest state (alphabetically).
query IIT rowsort
SELECT c.c_id, o.o_id, o.ship
FROM c
INNER JOIN o
ON c.c_id=o.c_id AND o.ship = (SELECT min(o.ship) FROM o WHERE o.c_id=c.c_id);
----
1  20  CA
2  40  CA
4  70  WY
1  10  CA
6  90  WA
1  30  CA

# Customers who have shipped more orders to a particular state than all other
# customers have shipped to that state, combined.
query ITI rowsort
SELECT c.c_id, o.ship, count(*)
FROM c
INNER JOIN o
ON c.c_id=o.c_id
WHERE
    (SELECT count(*) FROM o AS o2 WHERE o2.ship = o.ship AND o2.c_id = o.c_id) >
    (SELECT count(*) FROM o AS o2 WHERE o2.ship = o.ship AND o2.c_id <> o.c_id)
GROUP BY c.c_id, o.ship;
----
1  CA  3
2  TX  1
4  WY  1
6  WA  1

# Customers with more than one order and with the highest state = 'CA'.
query IT rowsort
SELECT *
FROM c
WHERE
    (SELECT count(*) FROM o WHERE o.c_id=c.c_id) > 1
    AND (SELECT max(ship) FROM o WHERE o.c_id=c.c_id) = 'CA';
----
1  CA

# Customers with more than one order or with an unknown ship state.
query IT rowsort
SELECT *
FROM c
WHERE
    (SELECT count(*) FROM o WHERE o.c_id=c.c_id) > 1
    OR EXISTS(SELECT ship FROM o WHERE o.c_id=c.c_id AND ship IS NULL);
----
1  CA
2  TX
4  TX

# Customers that have a bill state equal to the max ship state of all their
# orders (alphabetically).
query IT rowsort
SELECT c_id, bill
FROM c AS c2
WHERE EXISTS
(
    SELECT * FROM c WHERE bill=(SELECT max(ship) FROM o WHERE c_id=c2.c_id AND c_id=c.c_id)
)
----
1  CA
2  TX

# Customers that have at least one order shipped to their billing state (or if
# the ship state is null).
query IT rowsort
SELECT c_id, bill
FROM c AS c2
WHERE EXISTS
(
    SELECT *
    FROM (SELECT c_id, coalesce(ship, bill) AS state FROM o WHERE c_id=c2.c_id) AS o
    WHERE state=bill
)
----
1  CA
2  TX
4  TX

query II rowsort
SELECT c_id, generate_series(1, (SELECT count(*) FROM o WHERE o.c_id=c.c_id)) FROM c
----
1  1
1  2
1  3
2  1
2  2
2  3
4  1
4  2
6  1


# Customers that have no orders with a NULL ship state.
# Note: Result differs from Cockroach but matches Postgres.
query IT rowsort
SELECT *
FROM c
WHERE (SELECT ship FROM o WHERE o.c_id=c.c_id ORDER BY ship LIMIT 1) IS NOT NULL
----
1  CA
2  TX
4  TX
6  FL

# Customers that have first order shipping to 'CA' or 'WY' (no NULL ship).
query IT
SELECT *
FROM c
WHERE
    (SELECT ship FROM o WHERE o.c_id=c.c_id AND ship IS NOT NULL ORDER BY ship LIMIT 1)='CA'
    OR (SELECT ship FROM o WHERE o.c_id=c.c_id AND ship IS NOT NULL ORDER BY ship LIMIT 1)='WY'
ORDER BY c_id
----
1  CA
2  TX
4  TX

# Apply
query IT rowsort
SELECT *
FROM c
WHERE (SELECT o_id FROM o WHERE o.c_id=c.c_id AND ship='WY')=4;
----

# ------------------------------------------------------------------------------
# Subqueries in projection lists.
#   Although the queries are similar to those above, they are often compiled
#   differently in the context of a projection list, due to different null
#   result handling rules.
# ------------------------------------------------------------------------------

# Customers with orders.
query IB
SELECT c_id, EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id) FROM c ORDER BY c_id;
----
1  true
2  true
3  false
4  true
5  false
6  true

# Customers with no orders.
query IB
SELECT c_id, NOT EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id) FROM c ORDER BY c_id;
----
1  false
2  false
3  true
4  false
5  true
6  false

# Customers with orders or with no orders (should be all customers).
query IB
SELECT
    c_id,
    EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id)
    OR NOT EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id)
FROM c
ORDER BY c_id;
----
1  true
2  true
3  true
4  true
5  true
6  true

# Customers with billing address in TX that have orders.
query IB
SELECT c_id, EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id AND c.bill='TX') FROM c ORDER BY c_id;
----
1  false
2  true
3  false
4  true
5  false
6  false

# Customers that have at least one order shipped to WY.
query IB
SELECT c_id, 'WY' IN (SELECT ship FROM o WHERE o.c_id=c.c_id) FROM c ORDER BY c_id;
----
1  false
2  NULL
3  false
4  true
5  false
6  false

# Customers that have at least one order shipped to WY or to WA.
query IB
SELECT
    c_id,
    'WY' IN (SELECT ship FROM o WHERE o.c_id=c.c_id)
    OR 'WA' IN (SELECT ship FROM o WHERE o.c_id=c.c_id)
FROM c
ORDER BY c_id;
----
1  false
2  NULL
3  false
4  true
5  false
6  true

# Customers that have at least one order shipped to CA, but none to TX.
query IB
SELECT
    c_id,
    'CA' IN (SELECT ship FROM o WHERE o.c_id=c.c_id)
    AND 'TX' NOT IN (SELECT ship FROM o WHERE o.c_id=c.c_id)
FROM c
ORDER BY c_id;
----
1  true
2  false
3  false
4  NULL
5  false
6  false

# Customers with at least one order with billing addr = shipping addr.
query IB
SELECT c_id, bill IN (SELECT ship FROM o WHERE o.c_id=c.c_id) FROM c ORDER BY c_id;
----
1  true
2  true
3  false
4  NULL
5  false
6  false

# Customers with all orders with billing addr = shipping addr.
query IB
SELECT c_id, bill = ALL(SELECT ship FROM o WHERE o.c_id=c.c_id) FROM c ORDER BY c_id;
----
1  true
2  false
3  true
4  false
5  true
6  false

# Customers with no order with billing addr = shipping addr (with NULL ship).
query IB
SELECT c_id, bill NOT IN (SELECT ship FROM o WHERE o.c_id=c.c_id) FROM c ORDER BY c_id;
----
1  false
2  false
3  true
4  NULL
5  true
6  true

# Customers with no order with billing addr = shipping addr (no NULL ship).
query IB
SELECT c_id, bill NOT IN (SELECT ship FROM o WHERE o.c_id=c.c_id AND ship IS NOT NULL)
FROM c
ORDER BY c_id;
----
1  false
2  false
3  true
4  true
5  true
6  true

# Customers with no order with billing addr = shipping addr (only NULL ship).
query IB
SELECT c_id, bill NOT IN (SELECT ship FROM o WHERE o.c_id=c.c_id AND ship IS NULL)
FROM c
ORDER BY c_id;
----
1  true
2  NULL
3  true
4  NULL
5  true
6  true

# Customers with bill state < any ship state.
query IB
SELECT c_id, bill < ANY(SELECT ship FROM o WHERE o.c_id=c.c_id) FROM c ORDER BY c_id;
----
1  false
2  NULL
3  false
4  true
5  false
6  true

# Customers where bill state < any ship state is null result.
query IB
SELECT c_id, (bill < ANY(SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NULL FROM c ORDER BY c_id;
----
1  false
2  true
3  false
4  false
5  false
6  false

# Customers where bill state < any ship state is not null result.
query IB
SELECT c_id, (bill < ANY(SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NOT NULL FROM c ORDER BY c_id;
----
1  true
2  false
3  true
4  true
5  true
6  true

# Customers with bill state > any ship state.
query IB
SELECT c_id, bill > ANY(SELECT ship FROM o WHERE o.c_id=c.c_id) FROM c ORDER BY c_id;
----
1  false
2  true
3  false
4  NULL
5  false
6  false

# Customers where bill state > any ship state is null result.
query IB
SELECT c_id, (bill > ANY(SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NULL FROM c ORDER BY c_id;
----
1  false
2  false
3  false
4  true
5  false
6  false

# Customers where bill state > any ship state is not null result.
query IB
SELECT c_id, (bill > ANY(SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NOT NULL FROM c ORDER BY c_id;
----
1  true
2  true
3  true
4  false
5  true
6  true

# Customers where bill state matches any non-null ship state.
query IB rowsort
SELECT c_id, bill = ANY(SELECT ship FROM o WHERE ship IS NOT NULL) FROM c;
----
1  true
2  true
3  false
4  true
5  NULL
6  false

# Customers where bill state matches any non-null ship state or is null.
query IB rowsort
SELECT c_id, bill = ANY(SELECT ship FROM o WHERE ship IS NOT NULL) OR bill IS NULL FROM c;
----
1  true
2  true
3  false
4  true
5  true
6  false

# Test NULL IN case.
query IB
SELECT c_id, (NULL::text IN (SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NOT NULL
FROM c
ORDER BY c_id;
----
1  false
2  false
3  true
4  false
5  true
6  false

# Test NULL NOT IN case. Use IS NOT NULL to prevent normalize ANY into EXISTS.
query IB
SELECT c_id, (NULL::text NOT IN (SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NOT NULL
FROM c
ORDER BY c_id;
----
1  false
2  false
3  true
4  false
5  true
6  false

# Customers where it is unknown whether a replaced bill state is one of the ship
# states. This tests a more complex scalar expression as argument to IN.
query IB
SELECT c_id, (replace(bill, 'TX', 'WY') IN (SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NULL
FROM c
ORDER BY c_id;
----
1  false
2  true
3  false
4  false
5  false
6  false

# Customers with all orders with billing addr = shipping addr, or with at least
# one order shipped to WY.
query IB
SELECT
    c_id,
    bill = ALL(SELECT ship FROM o WHERE o.c_id=c.c_id)
    OR EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id AND ship='WY')
FROM c
ORDER BY c_id;
----
1  true
2  false
3  true
4  true
5  true
6  false

# Customers with all orders with billing addr = shipping addr, but with at least
# one order.
query IB
SELECT
    c_id,
    bill = ALL(SELECT ship FROM o WHERE o.c_id=c.c_id)
    AND EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id)
FROM c
ORDER BY c_id;
----
1  true
2  false
3  false
4  false
5  false
6  false

# Apply.
query IT rowsort
SELECT *
FROM c
WHERE (SELECT min(ship) FROM o WHERE o.c_id=c.c_id) IN (SELECT ship FROM o WHERE o.c_id=c.c_id);
----
1  CA
2  TX
4  TX
6  FL

# Customers with at least one shipping address = minimum shipping address.
query IB
SELECT
    c_id,
    (SELECT min(ship) FROM o WHERE o.c_id=c.c_id) IN (SELECT ship FROM o WHERE o.c_id=c.c_id)
FROM c
ORDER BY c_id;
----
1  true
2  true
3  false
4  true
5  false
6  true

# Maximum number of orders for a customer. Use subquery in aggregate function.
query I
SELECT max((SELECT count(*) FROM o WHERE o.c_id=c.c_id)) FROM c;
----
3

# Order count by customer.
query II
SELECT
    c_id,
    (SELECT count(*) FROM o WHERE o.c_id=c.c_id)
FROM c
ORDER BY c_id;
----
1  3
2  3
3  0
4  2
5  0
6  1

# Count bill/ship addresses in each state.
# Note: Result differs from Cockroach but matches Postgres.
query TI
SELECT
    s.st,
    (SELECT count(*) FROM c WHERE c.bill=s.st) + (SELECT count(*) FROM o WHERE o.ship=s.st)
FROM (SELECT c.bill AS st FROM c UNION SELECT o.ship AS st FROM o) s
ORDER BY s.st;
----
CA    5
FL    1
MA    1
TX    3
WA    1
WY    1
NULL  0

# Customer orders grouped by ship state, compared with count of all orders
# shipped to that state by all other customers combined.
query ITII rowsort
SELECT c.c_id, o.ship, count(*) AS cust,
    (SELECT count(*) FROM o AS o2 WHERE o2.ship = o.ship AND o2.c_id <> c.c_id) AS other
FROM c
INNER JOIN o
ON c.c_id=o.c_id
GROUP BY c.c_id, o.ship;
----
2  TX    1  0
4  NULL  1  0
6  WA    1  0
2  NULL  1  0
4  WY    1  0
1  CA    3  1
2  CA    1  3

# Customers with their orders (even if no orders), plus max of bill and ship
# states for that customer (alphabetically).
query IIT
SELECT
    c.c_id,
    o.o_id,
    (
        SELECT max(CASE WHEN c2.bill > o2.ship THEN c2.bill ELSE o2.ship END)
        FROM c AS c2, o AS o2
        WHERE c2.c_id=o2.c_id AND c2.c_id=c.c_id
    )
FROM c
LEFT JOIN o
ON c.c_id=o.c_id
ORDER BY c.c_id, o.o_id
----
1  10    CA
1  20    CA
1  30    CA
2  40    TX
2  50    TX
2  60    TX
3  NULL  NULL
4  70    WY
4  80    WY
5  NULL  NULL
6  90    WA

# Customers, with boolean indicating whether they have at least one order with a
# NULL ship state.
# Note: Result differs from Cockroach but matches Postgres.
query IB
SELECT
    c.c_id,
    (SELECT ship FROM o WHERE o.c_id=c.c_id ORDER BY ship LIMIT 1) IS NOT NULL
FROM c
ORDER BY c.c_id
----
1  true
2  true
3  false
4  true
5  false
6  true

# Customers, with boolean indicating whether their first order shipped to 'CA'
# or 'WY' (no NULL ship).
query IB
SELECT
    c.c_id,
    (SELECT ship FROM o WHERE o.c_id=c.c_id AND ship IS NOT NULL ORDER BY ship LIMIT 1)='CA'
    OR (SELECT ship FROM o WHERE o.c_id=c.c_id AND ship IS NOT NULL ORDER BY ship LIMIT 1)='WY'
FROM c
ORDER BY c_id
----
1  true
2  true
3  NULL
4  true
5  NULL
6  false

query T
SELECT (SELECT string_agg(ship, ', ')
  FROM
  (SELECT c_id AS o_c_id, ship FROM o ORDER BY ship)
  WHERE o_c_id=c.c_id)
FROM c ORDER BY c_id
----
CA, CA, CA
CA, TX
NULL
WY
NULL
WA

query T
SELECT (SELECT string_agg(DISTINCT ship, ', ')
  FROM
  (SELECT c_id AS o_c_id, ship FROM o ORDER BY ship)
  WHERE o_c_id=c.c_id)
FROM c ORDER BY c_id
----
CA
CA, TX
NULL
WY
NULL
WA

query ITI
SELECT
    *
FROM
    (SELECT c_id AS c_c_id, bill FROM c) s1,
    LATERAL (SELECT o_id FROM o WHERE c_id = c_c_id) s2
ORDER BY c_c_id, bill, o_id
----
1 CA 10
1 CA 20
1 CA 30
2 TX 40
2 TX 50
2 TX 60
4 TX 70
4 TX 80
6 FL 90

query TI
SELECT
    *
FROM
    (SELECT bill FROM c) s1,
    LATERAL (SELECT o_id FROM o WHERE ship = bill) s2
ORDER BY bill, o_id
----
CA 10
CA 20
CA 30
CA 40
TX 50
TX 50

# ------------------------------------------------------------------------------
# Subqueries in other interesting locations.
# ------------------------------------------------------------------------------

# Group by order count by customer, and order by that order count as well.
query II
SELECT
    (SELECT count(*) FROM o WHERE o.c_id=c.c_id) AS order_cnt,
    count(*) AS cust_cnt
FROM c
GROUP BY (SELECT count(*) FROM o WHERE o.c_id=c.c_id)
ORDER BY (SELECT count(*) FROM o WHERE o.c_id=c.c_id) DESC
----
3  2
2  1
1  1
0  2

# Subquery in VALUES clause.
query III
SELECT c_cnt, o_cnt, c_cnt + o_cnt AS total
FROM (VALUES ((SELECT count(*) FROM c), (SELECT count(*) FROM o))) AS v(c_cnt, o_cnt)
WHERE c_cnt > 0 AND o_cnt > 0;
----
6  9  15

# Subquery in JOIN condition.
query II rowsort
SELECT c.c_id, o.o_id
FROM c
INNER JOIN o
ON c.c_id=o.c_id AND EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id AND ship IS NULL);
----
2  40
2  50
2  60
4  70
4  80

# TODO(justin): Not supported by Materialize.
# query error more than one row returned by a subquery
# SELECT c.c_id, o.o_id
# FROM c
# INNER JOIN o
# ON c.c_id=o.c_id AND o.ship = (SELECT o.ship FROM o WHERE o.c_id=c.c_id);

# TODO(justin): Not supported by Materialize.
# # Subquery in ARRAY(...)
# query ITT
# SELECT
#   c_id,
#   ARRAY(SELECT o_id FROM o WHERE o.c_id = c.c_id ORDER BY o_id),
#   ARRAY(SELECT o_id FROM o WHERE o.ship = c.bill ORDER BY o_id)
# FROM c ORDER BY c_id
# ----
# 1  {10,20,30}  {10,20,30,40}
# 2  {40,50,60}  {50}
# 3  {}          {}
# 4  {70,80}     {50}
# 5  {}          {}
# 6  {90}        {}
#
# query IT
# SELECT
#   c_id,
#   ARRAY(SELECT o_id FROM o WHERE o.c_id = c.c_id ORDER BY o_id)
# FROM c ORDER BY c_id
# ----
# 1  {10,20,30}
# 2  {40,50,60}
# 3  {}
# 4  {70,80}
# 5  {}
# 6  {90}

# Regression for issue database-issues#7343: missing support for correlated subquery in JSON
# operator.
statement ok
CREATE TABLE groups(
  id INT PRIMARY KEY,
  data JSONB
)

statement ok
INSERT INTO groups(id, data) VALUES(1, '{"name": "Group 1", "members": [{"name": "admin", "type": "USER"}, {"name": "user", "type": "USER"}]}')

statement ok
INSERT INTO groups(id, data) VALUES(2, '{"name": "Group 2", "members": [{"name": "admin2", "type": "USER"}]}')

# database-issues#544
# query TT
# SELECT
#   g.data->>'name' AS group_name,
#   jsonb_array_elements( (SELECT gg.data->'members' FROM groups gg WHERE gg.data->>'name' = g.data->>'name') )
# FROM
#   groups g
# ----
# Group 1	{"name": "admin", "type": "USER"}
# Group 1	{"name": "user", "type": "USER"}
# Group 2	{"name": "admin2", "type": "USER"}

# query TT
# SELECT
#     data->>'name',
#     members
# FROM
#     groups AS g,
#     jsonb_array_elements(
#         (
#             SELECT
#                 gg.data->'members' AS members
#             FROM
#                 groups AS gg
#             WHERE
#                 gg.data->>'name' = g.data->>'name'
#         )
#     ) AS members
# ----
# Group 1  {"name": "admin", "type": "USER"}
# Group 1  {"name": "user", "type": "USER"}
# Group 2  {"name": "admin2", "type": "USER"}

# ------------------------------------------------------------------------------
# Regression test cases.
# ------------------------------------------------------------------------------

# Regression for issue 35437.

statement ok
CREATE TABLE users (
    id INT NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY (id)
)

statement ok
INSERT INTO users(id, name) VALUES (1, 'user1')

statement ok
INSERT INTO users(id, name) VALUES (2, 'user2')

statement ok
INSERT INTO users(id, name) VALUES (3, 'user3')

statement ok
CREATE TABLE stuff (
    id INT NOT NULL,
    date DATE,
    user_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users (id)
)

statement ok
INSERT INTO stuff(id, date, user_id) VALUES (1, '2007-10-15'::DATE, 1)

statement ok
INSERT INTO stuff(id, date, user_id) VALUES (2, '2007-12-15'::DATE, 1)

statement ok
INSERT INTO stuff(id, date, user_id) VALUES (3, '2007-11-15'::DATE, 1)

statement ok
INSERT INTO stuff(id, date, user_id) VALUES (4, '2008-01-15'::DATE, 2)

statement ok
INSERT INTO stuff(id, date, user_id) VALUES (5, '2007-06-15'::DATE, 3)

statement ok
INSERT INTO stuff(id, date, user_id) VALUES (6, '2007-03-15'::DATE, 3)

# database-issues#949
# query ITITI
# SELECT
#     users.id AS users_id,
#     users.name AS users_name,
#     stuff_1.id AS stuff_1_id,
#     stuff_1.date AS stuff_1_date,
#     stuff_1.user_id AS stuff_1_user_id
# FROM
#     users
#     LEFT JOIN stuff AS stuff_1
#     ON
#         users.id = stuff_1.user_id
#         AND stuff_1.id
#             = (
#                     SELECT
#                         stuff_2.id
#                     FROM
#                         stuff AS stuff_2
#                     WHERE
#                         stuff_2.user_id = users.id
#                     ORDER BY
#                         stuff_2.date DESC
#                     LIMIT
#                         1
#                 )
# ORDER BY
#     users.name;
# ----
# 1  user1  2  2007-12-15 00:00:00 +0000 +0000  1
# 2  user2  4  2008-01-15 00:00:00 +0000 +0000  2
# 3  user3  5  2007-06-15 00:00:00 +0000 +0000  3

statement ok
DROP TABLE stuff;

statement ok
DROP TABLE users;