Materialize/test/sqllogictest/github-6464.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.

# Regression test for database-issues#6464.
# Note that this test employs the old syntax with the EXPECTED GROUP SIZE because the behavior
# tested is exactly the backwards-compatible one of assigning the hinted value to all operators
# in the same query block.

statement ok
CREATE TABLE teachers (id INT, name TEXT);

statement ok
CREATE TABLE sections (id INT, teacher_id INT, course_id INT, schedule TEXT);

statement ok
CREATE MATERIALIZED VIEW distinct_on_group_by_limit AS
  SELECT DISTINCT ON(teacher_id) id, teacher_id, MAX(course_id)
  FROM sections
  GROUP BY id, teacher_id
  OPTIONS (EXPECTED GROUP SIZE = 1000)
  ORDER BY teacher_id, id
  LIMIT 2;

query T multiline
EXPLAIN RAW PLAN FOR
  SELECT DISTINCT ON(teacher_id) id, teacher_id, MAX(course_id)
  FROM sections
  GROUP BY id, teacher_id
  OPTIONS (EXPECTED GROUP SIZE = 1000)
  ORDER BY teacher_id, id
  LIMIT 2;
----
Finish order_by=[#1 asc nulls_last, #0 asc nulls_last] limit=2 output=[#0..=#2]
  TopK group_by=[#1] order_by=[#0 asc nulls_last] limit=1 exp_group_size=1000
    Reduce group_by=[#4, #5] aggregates=[max(#2{course_id})] exp_group_size=1000
      Map (#0{id}, #1{teacher_id})
        Get materialize.public.sections

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH (humanized expressions) AS VERBOSE TEXT FOR MATERIALIZED VIEW distinct_on_group_by_limit;
----
materialize.public.distinct_on_group_by_limit:
  TopK order_by=[#1{teacher_id} asc nulls_last, #0{id} asc nulls_last] limit=2 exp_group_size=1000
    TopK group_by=[#1{teacher_id}] order_by=[#0{id} asc nulls_last] limit=1 exp_group_size=1000
      Reduce group_by=[#0{id}, #1{teacher_id}] aggregates=[max(#2{course_id})] exp_group_size=1000
        Project (#0{id}..=#2{course_id})
          ReadStorage materialize.public.sections

Source materialize.public.sections

Target cluster: quickstart

EOF