# 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#7688.

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

simple
SET ENABLE_SESSION_CARDINALITY_ESTIMATES TO TRUE
----
COMPLETE 0

statement ok
CREATE TABLE tab0_raw(pk INTEGER, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT);

statement ok
CREATE MATERIALIZED VIEW tab0 AS SELECT DISTINCT ON(pk) * FROM tab0_raw;

statement ok
INSERT INTO tab0_raw VALUES(0,6,72.32,'diidw',65,65.1,'uudvn'), (1,57,90.1,'jvnyz',84,48.99,'raktj'), (2,68,91.83,'wefta',37,71.86,'zddoc'), (3,10,78.14,'zwjtc',7,9.96,'epmyn'), (4,63,24.41,'rwaus',66,53.7,'gbgmw'), (5,87,70.88,'rwpww',46,26.5,'bvbew'), (6,76,46.18,'lfvrf',99,92.47,'hqpgb'), (7,25,81.99,'khylz',54,73.22,'qaonp'), (8,93,17.58,'clxlk',88,59.16,'ziwhr'), (9,64,18.54,'fgkop',82,18.73,'lztum');

statement ok
CREATE TABLE tab1_raw(pk INTEGER, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT);

statement ok
CREATE MATERIALIZED VIEW tab1 AS SELECT DISTINCT ON(pk) * FROM tab1_raw;

statement ok
CREATE INDEX idx_tab1_0 on tab1 (col0);

statement ok
CREATE INDEX idx_tab1_1 on tab1 (col1);

statement ok
CREATE INDEX idx_tab1_3 on tab1 (col3);

statement ok
CREATE INDEX idx_tab1_4 on tab1 (col4);

statement ok
INSERT INTO tab1_raw SELECT * FROM tab0;

statement ok
CREATE TABLE tab2_raw(pk INTEGER, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT);

statement ok
CREATE MATERIALIZED VIEW tab2 AS SELECT DISTINCT ON(pk) * FROM tab2_raw;

statement ok
CREATE INDEX idx_tab2_0 ON tab2 (col4);

statement ok
CREATE INDEX idx_tab2_2 ON tab2 (col1);

statement ok
CREATE INDEX idx_tab2_4 ON tab2 (col0,col3 DESC);

statement ok
CREATE INDEX idx_tab2_5 ON tab2 (col3);

statement ok
INSERT INTO tab2_raw SELECT * FROM tab0;

statement ok
CREATE TABLE tab3_raw(pk INTEGER, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT);

statement ok
CREATE MATERIALIZED VIEW tab3 AS SELECT DISTINCT ON(pk) * FROM tab3_raw;

statement ok
CREATE INDEX idx_tab3_0 ON tab3 (col0,col1,col3);

statement ok
CREATE INDEX idx_tab3_1 ON tab3 (col4);

statement ok
INSERT INTO tab3_raw SELECT * FROM tab0;

statement ok
CREATE TABLE tab4_raw(pk INTEGER, col0 INTEGER, col1 FLOAT, col2 TEXT, col3 INTEGER, col4 FLOAT, col5 TEXT);

statement ok
CREATE MATERIALIZED VIEW tab4 AS SELECT DISTINCT ON(pk) * FROM tab4_raw;

statement ok
CREATE INDEX idx_tab4_0 ON tab4 (col4 DESC,col3 DESC);

statement ok
CREATE INDEX idx_tab4_2 ON tab4 (col1);

statement ok
INSERT INTO tab4_raw SELECT * FROM tab0;

statement ok
EXPLAIN OPTIMIZED PLAN WITH(cardinality, humanized expressions) AS VERBOSE TEXT FOR SELECT pk FROM tab0 WHERE col0 <= 88 AND (((col4 <= 97.11 AND col0 IN (11,85,87,63,88) OR (col0 <= 45 AND ((((((((col0 > 79)) OR col1 <= 30.14 OR col3 >= 12))) OR col0 >= 89 OR col1 < 20.99 OR col1 >= 74.51 AND col3 > 77) AND (col0 IN (67,97,94,86,81))) AND ((col1 <= 10.70 AND col1 IS NULL AND col3 > 49 AND col3 > 66 AND (((col4 > 42.2) AND ((((col4 < 86.27) AND col3 >= 77 AND col3 < 48))) AND col3 >= 49)) AND col0 IN (SELECT col3 FROM tab0 WHERE col4 BETWEEN 20.3 AND 97.63))) AND col0 >= 25) OR ((col0 <= 35)) AND col0 < 68 OR ((col0 = 98))) OR (col1 <= 17.96) AND ((((col0 IS NULL))) OR col4 <= 2.63 AND (col0 > 2) AND col3 > 8) OR col3 <= 88 AND (((col0 IS NULL))) OR col0 >= 30)) AND col0 > 5) OR col0 > 3

query I
SELECT pk FROM tab0 WHERE col0 <= 88 AND (((col4 <= 97.11 AND col0 IN (11,85,87,63,88) OR (col0 <= 45 AND ((((((((col0 > 79)) OR col1 <= 30.14 OR col3 >= 12))) OR col0 >= 89 OR col1 < 20.99 OR col1 >= 74.51 AND col3 > 77) AND (col0 IN (67,97,94,86,81))) AND ((col1 <= 10.70 AND col1 IS NULL AND col3 > 49 AND col3 > 66 AND (((col4 > 42.2) AND ((((col4 < 86.27) AND col3 >= 77 AND col3 < 48))) AND col3 >= 49)) AND col0 IN (SELECT col3 FROM tab0 WHERE col4 BETWEEN 20.3 AND 97.63))) AND col0 >= 25) OR ((col0 <= 35)) AND col0 < 68 OR ((col0 = 98))) OR (col1 <= 17.96) AND ((((col0 IS NULL))) OR col4 <= 2.63 AND (col0 > 2) AND col3 > 8) OR col3 <= 88 AND (((col0 IS NULL))) OR col0 >= 30)) AND col0 > 5) OR col0 > 3
----
0
1
2
3
4
5
6
7
8
9