topk.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

statement ok
CREATE TABLE cities (
    name text NOT NULL,
    state text NOT NULL,
    pop int NOT NULL
)

statement ok
INSERT INTO cities VALUES
    ('Los_Angeles', 'CA', 3979576),
    ('Phoenix', 'AZ', 1680992),
    ('Houston', 'TX', 2320268),
    ('San_Diego', 'CA', 1423851),
    ('San_Francisco', 'CA', 881549),
    ('New_York', 'NY', 8336817),
    ('Dallas', 'TX', 1343573),
    ('San_Antonio', 'TX', 1547253),
    ('San_Jose', 'CA', 1021795),
    ('Chicago', 'IL', 2695598),
    ('Austin', 'TX', 978908)

query TT rowsort
SELECT state, name FROM
    (SELECT DISTINCT state FROM cities) grp,
    LATERAL (SELECT name FROM cities WHERE state = grp.state ORDER BY pop DESC LIMIT 3)
----
AZ  Phoenix
CA  Los_Angeles
CA  San_Diego
CA  San_Jose
IL  Chicago
NY  New_York
TX  Houston
TX  San_Antonio
TX  Dallas

mode standard

query T multiline
EXPLAIN PLAN FOR SELECT state, name FROM
    (SELECT DISTINCT state FROM cities) grp,
    LATERAL (SELECT name, pop FROM cities WHERE state = grp.state ORDER BY pop DESC LIMIT 3)
----
%0 =
| Get materialize.public.cities (u1)
| TopK group=(#1) order=(#2 desc) limit=3 offset=0
| Project (#1, #0)

EOF

query T multiline
EXPLAIN PLAN FOR SELECT state, name FROM
    (SELECT DISTINCT state FROM cities) grp
    LEFT JOIN LATERAL (SELECT name, pop FROM cities  where cities.state = grp.state ORDER BY pop DESC LIMIT 3) ON true
----
%0 = Let l0 =
| Get materialize.public.cities (u1)
| TopK group=(#1) order=(#2 desc) limit=3 offset=0
| Project (#0, #1)

%1 =
| Get %0 (l0)
| Project (#1, #0)

%2 =
| Get %0 (l0)
| Project (#1)
| Distinct group=(#0)
| Negate

%3 =
| Get materialize.public.cities (u1)
| Project (#1)
| Distinct group=(#0)

%4 =
| Union %2 %3
| Map null

%5 =
| Union %1 %4

EOF

# Test that LIMIT 0 is optimized out
query T multiline
EXPLAIN PLAN FOR SELECT state, COUNT(*) FROM (
    SELECT state, name FROM
        (SELECT DISTINCT state FROM cities) grp,
        LATERAL (SELECT name, pop FROM cities WHERE state = grp.state ORDER BY pop DESC LIMIT 0)
    )
    GROUP BY state
----
%0 =
| Constant

EOF


# Test that LIMIT 1 results in a unique key
query T multiline
EXPLAIN PLAN FOR SELECT state, COUNT(*) FROM (
    SELECT state, name FROM
        (SELECT DISTINCT state FROM cities) grp,
        LATERAL (SELECT name, pop FROM cities WHERE state = grp.state ORDER BY pop DESC LIMIT 1)
    )
    GROUP BY state
----
%0 =
| Get materialize.public.cities (u1)
| Project (#1, #2)
| TopK group=(#0) order=(#1 desc) limit=1 offset=0
| Map 1
| Project (#0, #2)

EOF