materialize
materialize copied to clipboard
Published
20 hours ago •
MaterializeInc
MaterializeInc
stack exhaustion in mz_transform::Optimizer::optimize with nested unions
What version of Materialize are you using?
environmentd v0.26.1-dev (4604a289e) listening...
How did you install Materialize?
Docker image
What is the issue?
Running the query below results in stack exhaustion. The stack is 56K frames deep. The query is compicated, but is it really that complicated?
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
CREATE TABLE t1 (f1 DOUBLE PRECISION, f2 DOUBLE PRECISION NOT NULL);
CREATE INDEX t1i1 ON t1(f1);
CREATE INDEX t1i2 ON t1(f2, f1);
CREATE TABLE t2 (f1 DOUBLE PRECISION, f2 DOUBLE PRECISION NOT NULL);
CREATE INDEX t2i1 ON t2(f1);
CREATE INDEX i2i2 ON t2(f2, f1);
CREATE TABLE t3 (f1 DOUBLE PRECISION, f2 DOUBLE PRECISION NOT NULL);
SELECT DISTINCT f1 FROM t2 WHERE f1 > 6 UNION ALL ( ( ( SELECT DISTINCT f1 FROM t2 WHERE f1 IS NOT NULL INTERSECT ( SELECT f1 FROM t1 INTERSECT ALL ( ( ( SELECT DISTINCT f1 FROM t1 WHERE f1 < 8 UNION SELECT f1 FROM t1 ) UNION SELECT DISTINCT MIN ( f1 ) FROM t2 WHERE f1 IS NULL ) UNION ALL SELECT MIN ( f2 ) FROM t1 WHERE f1 < 3 UNION ALL ( SELECT DISTINCT f1 FROM t1 WHERE f1 IS NOT NULL INTERSECT ALL SELECT f2 FROM t1 WHERE f1 > 0 UNION SELECT COUNT ( DISTINCT f1 ) FROM t2 WHERE f1 > 7 ) UNION ALL SELECT MAX ( f2 ) FROM t1 UNION ALL ( SELECT f1 FROM t2 UNION SELECT DISTINCT COUNT ( f1 ) FROM t2 EXCEPT ALL SELECT f2 FROM t2 EXCEPT ALL SELECT SUM ( f1 ) FROM t1 WHERE f1 > 6 INTERSECT ALL SELECT f1 FROM t1 WHERE f1 IS NULL ) EXCEPT ALL SELECT DISTINCT f2 FROM t2 INTERSECT ALL ( SELECT DISTINCT MAX ( f1 ) FROM t2 WHERE f1 IS NULL INTERSECT SELECT f2 FROM t2 WHERE f1 IS NOT NULL ) EXCEPT ALL SELECT f2 FROM t2 WHERE f1 = 7 INTERSECT SELECT f2 FROM t2 WHERE f1 > 1 ) ) EXCEPT ALL SELECT f1 FROM t1 WHERE f1 IS NULL EXCEPT ALL SELECT DISTINCT f1 FROM t1 WHERE f1 < 7 INTERSECT SELECT MIN ( f1 ) FROM t1 ) EXCEPT ALL ( ( SELECT f1 FROM t2 WHERE f1 > 7 UNION ALL ( SELECT COUNT ( DISTINCT f1 ) FROM t2 WHERE f1 IS NOT NULL EXCEPT ALL SELECT DISTINCT MAX ( DISTINCT f2 ) FROM t2 WHERE f1 < 5 ) UNION ( ( SELECT MIN ( f2 ) FROM t2 WHERE f1 IS NOT NULL UNION ALL SELECT f1 FROM t2 WHERE f1 IS NOT NULL UNION ALL ( ( SELECT COUNT ( f2 ) FROM t2 WHERE f1 IS NOT NULL INTERSECT ALL SELECT DISTINCT MAX ( DISTINCT f2 ) FROM t1 WHERE f1 IS NULL UNION SELECT DISTINCT f2 FROM t2 WHERE f1 = 1 ) EXCEPT ALL SELECT DISTINCT MIN ( f2 ) FROM t1 ) ) EXCEPT ALL ( SELECT DISTINCT f1 FROM t1 INTERSECT ALL SELECT DISTINCT MAX ( DISTINCT f1 ) FROM t1 WHERE f1 IS NULL EXCEPT ALL SELECT DISTINCT f2 FROM t1 WHERE f1 < 8 INTERSECT ALL SELECT f2 FROM t1 INTERSECT ALL ( SELECT DISTINCT COUNT ( f2 ) FROM t1 EXCEPT SELECT DISTINCT MIN ( f1 ) FROM t1 WHERE f1 IS NULL ) EXCEPT SELECT f2 FROM t1 WHERE f1 < 5 UNION SELECT DISTINCT f2 FROM t1 WHERE f1 < 2 UNION SELECT MAX ( f2 ) FROM t2 WHERE f1 IS NULL UNION ALL SELECT DISTINCT COUNT ( f1 ) FROM t2 EXCEPT ALL SELECT f1 FROM t1 WHERE f1 IS NOT NULL ) UNION ( SELECT f2 FROM t1 WHERE f1 IS NULL INTERSECT ( SELECT SUM ( DISTINCT f1 ) FROM t2 WHERE f1 < 4 UNION ALL ( SELECT DISTINCT f2 FROM t2 WHERE f1 > 3 INTERSECT ALL SELECT DISTINCT COUNT ( f1 ) FROM t1 EXCEPT SELECT DISTINCT MAX ( DISTINCT f2 ) FROM t2 WHERE f1 IS NOT NULL ) ) UNION SELECT DISTINCT f1 FROM t2 WHERE f1 IS NOT NULL ) ) EXCEPT ALL ( ( SELECT DISTINCT f1 FROM t1 WHERE f1 > 5 UNION ALL ( ( SELECT DISTINCT SUM ( f2 ) FROM t2 WHERE f1 IS NOT NULL INTERSECT SELECT COUNT ( DISTINCT f1 ) FROM t1 WHERE f1 IS NOT NULL UNION SELECT SUM ( DISTINCT f2 ) FROM t1 WHERE f1 = 2 ) EXCEPT ALL SELECT DISTINCT f2 FROM t1 WHERE f1 IS NULL UNION ALL ( SELECT DISTINCT f1 FROM t2 INTERSECT SELECT DISTINCT f1 FROM t2 WHERE f1 = 9 ) INTERSECT ALL SELECT COUNT ( f2 ) FROM t2 WHERE f1 IS NOT NULL ) ) INTERSECT ( ( ( SELECT DISTINCT f2 FROM t2 WHERE f1 IS NULL INTERSECT SELECT DISTINCT f1 FROM t1 WHERE f1 IS NOT NULL ) UNION SELECT f2 FROM t2 WHERE f1 = 2 ) INTERSECT ALL ( ( ( ( ( SELECT DISTINCT f2 FROM t2 WHERE f1 IS NULL INTERSECT ALL ( SELECT DISTINCT f1 FROM t2 WHERE f1 < 2 EXCEPT ( SELECT f2 FROM t1 INTERSECT SELECT DISTINCT COUNT ( DISTINCT f1 ) FROM t2 WHERE f1 = 5 ) ) INTERSECT ALL SELECT MAX ( DISTINCT f2 ) FROM t2 WHERE f1 IS NULL ) EXCEPT ( ( SELECT DISTINCT MAX ( DISTINCT f2 ) FROM t1 WHERE f1 < 3 EXCEPT ALL SELECT DISTINCT f1 FROM t1 WHERE f1 IS NULL ) EXCEPT SELECT SUM ( f1 ) FROM t1 EXCEPT ( SELECT DISTINCT f1 FROM t2 WHERE f1 < 5 UNION ALL SELECT SUM ( DISTINCT f1 ) FROM t1 WHERE f1 IS NULL EXCEPT SELECT SUM ( f2 ) FROM t1 WHERE f1 IS NULL ) INTERSECT SELECT DISTINCT MAX ( DISTINCT f2 ) FROM t1 WHERE f1 > 6 INTERSECT ALL ( SELECT MAX ( f1 ) FROM t2 WHERE f1 IS NULL INTERSECT ALL SELECT f1 FROM t1 EXCEPT ALL ( ( SELECT DISTINCT f1 FROM t2 WHERE f1 > 3 EXCEPT ALL ( SELECT DISTINCT MAX ( DISTINCT f2 ) FROM t2 WHERE f1 IS NULL INTERSECT SELECT MAX ( f1 ) FROM t2 WHERE f1 = 8 INTERSECT SELECT DISTINCT MAX ( DISTINCT f2 ) FROM t2 WHERE f1 IS NULL UNION SELECT MIN ( DISTINCT f1 ) FROM t1 ) ) INTERSECT ALL SELECT f2 FROM t1 WHERE f1 = 3 INTERSECT ALL SELECT DISTINCT MAX ( f1 ) FROM t1 WHERE f1 = 5 UNION ALL SELECT DISTINCT MAX ( f2 ) FROM t1 WHERE f1 IS NULL INTERSECT ALL ( SELECT COUNT ( DISTINCT f2 ) FROM t1 WHERE f1 IS NULL INTERSECT ALL SELECT f2 FROM t2 WHERE f1 > 0 UNION ALL SELECT DISTINCT MAX ( DISTINCT f1 ) FROM t2 WHERE f1 > 4 EXCEPT SELECT DISTINCT f2 FROM t2 WHERE f1 IS NOT NULL ) UNION ALL ( ( ( SELECT DISTINCT f2 FROM t2 WHERE f1 > 6 EXCEPT SELECT SUM ( DISTINCT f2 ) FROM t2 WHERE f1 IS NOT NULL ) UNION SELECT f1 FROM t2 WHERE f1 IS NULL INTERSECT SELECT DISTINCT f2 FROM t2 WHERE f1 > 3 EXCEPT ALL SELECT f2 FROM t1 WHERE f1 IS NULL EXCEPT ALL ( ( ( SELECT COUNT ( f2 ) FROM t2 EXCEPT ALL ( SELECT DISTINCT f2 FROM t1 WHERE f1 = 1 EXCEPT ALL SELECT DISTINCT MIN ( f1 ) FROM t1 INTERSECT ALL SELECT DISTINCT SUM ( DISTINCT f2 ) FROM t2 WHERE f1 = 1 INTERSECT SELECT f1 FROM t1 WHERE f1 > 5 EXCEPT ALL SELECT SUM ( f1 ) FROM t1 EXCEPT ALL SELECT MAX ( DISTINCT f1 ) FROM t2 WHERE f1 < 7 ) INTERSECT ALL SELECT DISTINCT COUNT ( DISTINCT f2 ) FROM t2 WHERE f1 IS NULL EXCEPT ALL SELECT f1 FROM t2 WHERE f1 IS NOT NULL INTERSECT ALL SELECT MAX ( DISTINCT f1 ) FROM t1 WHERE f1 IS NOT NULL ) INTERSECT ( ( SELECT DISTINCT f1 FROM t1 INTERSECT SELECT DISTINCT MIN ( DISTINCT f1 ) FROM t1 WHERE f1 = 4 ) EXCEPT SELECT f2 FROM t1 WHERE f1 = 0 ) ) INTERSECT ALL SELECT DISTINCT f1 FROM t1 WHERE f1 IS NOT NULL ) ) EXCEPT ALL SELECT DISTINCT f1 FROM t1 WHERE f1 < 8 EXCEPT ALL SELECT MAX ( f1 ) FROM t2 WHERE f1 = 2 UNION ( ( SELECT DISTINCT SUM ( f2 ) FROM t1 WHERE f1 > 0 INTERSECT SELECT DISTINCT COUNT ( f1 ) FROM t1 INTERSECT SELECT MAX ( f1 ) FROM t2 WHERE f1 IS NOT NULL INTERSECT ALL ( SELECT DISTINCT f2 FROM t1 UNION ALL SELECT DISTINCT f1 FROM t2 WHERE f1 = 8 UNION ALL ( SELECT DISTINCT f2 FROM t2 WHERE f1 IS NOT NULL EXCEPT SELECT DISTINCT MIN ( DISTINCT f1 ) FROM t2 WHERE f1 IS NOT NULL ) EXCEPT SELECT f1 FROM t2 WHERE f1 IS NOT NULL ) ) EXCEPT SELECT DISTINCT f2 FROM t1 WHERE f1 > 4 EXCEPT ALL ( SELECT DISTINCT f2 FROM t2 EXCEPT ( SELECT MAX ( f1 ) FROM t1 WHERE f1 < 7 EXCEPT ALL SELECT MIN ( DISTINCT f1 ) FROM t1 WHERE f1 < 9 UNION ALL SELECT DISTINCT MAX ( f2 ) FROM t2 WHERE f1 = 7 INTERSECT SELECT COUNT ( f1 ) FROM t1 ) INTERSECT ALL SELECT DISTINCT SUM ( DISTINCT f1 ) FROM t1 ) UNION ALL ( SELECT f2 FROM t2 INTERSECT ALL SELECT DISTINCT f2 FROM t2 ) INTERSECT ALL SELECT DISTINCT MAX ( f1 ) FROM t2 UNION SELECT f1 FROM t2 WHERE f1 > 5 ) UNION ( SELECT MIN ( f2 ) FROM t1 INTERSECT ALL ( SELECT f1 FROM t1 EXCEPT ALL SELECT DISTINCT SUM ( DISTINCT f2 ) FROM t1 EXCEPT ( ( SELECT DISTINCT f2 FROM t1 INTERSECT ( SELECT f1 FROM t2 EXCEPT SELECT DISTINCT f1 FROM t2 WHERE f1 IS NOT NULL EXCEPT SELECT DISTINCT MIN ( f2 ) FROM t1 WHERE f1 = 7 UNION ALL ( SELECT SUM ( f2 ) FROM t1 INTERSECT ALL ( SELECT MIN ( DISTINCT f1 ) FROM t1 WHERE f1 = 4 EXCEPT ALL SELECT f1 FROM t1 ) ) EXCEPT ( SELECT DISTINCT f1 FROM t2 WHERE f1 IS NOT NULL UNION ALL ( SELECT DISTINCT f1 FROM t1 WHERE f1 = 9 EXCEPT ( ( SELECT DISTINCT f1 FROM t1 WHERE f1 > 6 INTERSECT ( SELECT DISTINCT f2 FROM t1 WHERE f1 IS NULL UNION ( SELECT f1 FROM t1 WHERE f1 < 4 EXCEPT ALL ( SELECT DISTINCT f1 FROM t2 EXCEPT SELECT MIN ( DISTINCT f2 ) FROM t1 WHERE f1 < 9 UNION SELECT DISTINCT MAX ( f1 ) FROM t1 WHERE f1 IS NOT NULL ) EXCEPT ( SELECT DISTINCT MAX ( DISTINCT f2 ) FROM t2 INTERSECT ALL SELECT DISTINCT COUNT ( DISTINCT f1 ) FROM t2 WHERE f1 IS NOT NULL ) ) EXCEPT ( ( SELECT MIN ( DISTINCT f2 ) FROM t2 WHERE f1 IS NOT NULL UNION ALL SELECT DISTINCT f2 FROM t2 WHERE f1 IS NULL INTERSECT ALL SELECT DISTINCT COUNT ( f1 ) FROM t2 INTERSECT SELECT f1 FROM t2 WHERE f1 IS NULL UNION SELECT DISTINCT MAX ( f1 ) FROM t2 WHERE f1 < 0 UNION ALL SELECT MAX ( f1 ) FROM t1 WHERE f1 > 1 UNION SELECT DISTINCT f1 FROM t2 WHERE f1 < 0 ) UNION SELECT MIN ( f2 ) FROM t1 WHERE f1 < 8 ) EXCEPT ALL ( SELECT DISTINCT f1 FROM t2 EXCEPT SELECT DISTINCT SUM ( f2 ) FROM t2 WHERE f1 IS NULL UNION ALL SELECT DISTINCT SUM ( DISTINCT f1 ) FROM t1 WHERE f1 > 8 INTERSECT ALL SELECT DISTINCT MAX ( DISTINCT f1 ) FROM t1 ) EXCEPT ( SELECT DISTINCT f2 FROM t1 WHERE f1 > 7 INTERSECT SELECT f2 FROM t2 ) EXCEPT SELECT DISTINCT MAX ( DISTINCT f2 ) FROM t2 WHERE f1 > 0 ) EXCEPT SELECT DISTINCT f2 FROM t1 WHERE f1 = 9 EXCEPT ALL SELECT MAX ( DISTINCT f2 ) FROM t1 WHERE f1 > 6 INTERSECT SELECT DISTINCT f1 FROM t1 WHERE f1 = 6 ) INTERSECT SELECT f1 FROM t1 WHERE f1 > 9 ) INTERSECT ALL SELECT SUM ( f2 ) FROM t2 ) INTERSECT ( SELECT DISTINCT f2 FROM t1 EXCEPT ALL SELECT DISTINCT MAX ( DISTINCT f2 ) FROM t1 WHERE f1 IS NULL ) EXCEPT ALL SELECT COUNT ( f1 ) FROM t2 WHERE f1 > 9 ) ) EXCEPT ALL SELECT COUNT ( DISTINCT f2 ) FROM t1 ) INTERSECT ALL SELECT DISTINCT f1 FROM t2 WHERE f1 = 4 ) UNION ALL SELECT f1 FROM t1 WHERE f1 IS NOT NULL ) INTERSECT SELECT DISTINCT COUNT ( f2 ) FROM t1 WHERE f1 IS NULL ) ) UNION ALL SELECT f2 FROM t1 ) INTERSECT ALL SELECT DISTINCT f2 FROM t2 INTERSECT ALL SELECT DISTINCT f1 FROM t1 UNION SELECT DISTINCT SUM ( f1 ) FROM t2 WHERE f1 IS NOT NULL INTERSECT ALL SELECT f1 FROM t1 WHERE f1 < 5 UNION ALL SELECT DISTINCT MAX ( f2 ) FROM t1 WHERE f1 > 2 EXCEPT SELECT COUNT ( f1 ) FROM t1 WHERE f1 IS NULL ) INTERSECT ALL ( SELECT DISTINCT SUM ( f2 ) FROM t2 WHERE f1 > 5 UNION ALL SELECT f1 FROM t1 WHERE f1 IS NULL EXCEPT ALL SELECT DISTINCT f1 FROM t1 WHERE f1 IS NULL ) INTERSECT SELECT DISTINCT SUM ( DISTINCT f1 ) FROM t2 WHERE f1 IS NOT NULL UNION SELECT f1 FROM t1 WHERE f1 IS NOT NULL ) EXCEPT ALL SELECT SUM ( f1 ) FROM t2 EXCEPT ALL ( SELECT DISTINCT MAX ( f2 ) FROM t1 WHERE f1 = 5 EXCEPT ( SELECT COUNT ( f2 ) FROM t2 WHERE f1 = 7 INTERSECT ( ( SELECT COUNT ( DISTINCT f1 ) FROM t2 EXCEPT ALL ( ( SELECT DISTINCT MIN ( f1 ) FROM t2 WHERE f1 < 8 UNION ALL SELECT f1 FROM t1 ) EXCEPT SELECT DISTINCT MAX ( DISTINCT f2 ) FROM t1 WHERE f1 IS NOT NULL INTERSECT ALL SELECT DISTINCT f2 FROM t2 ) ) INTERSECT SELECT DISTINCT SUM ( DISTINCT f2 ) FROM t1 ) EXCEPT ALL ( SELECT f1 FROM t2 WHERE f1 IS NULL INTERSECT ( SELECT SUM ( f1 ) FROM t1 WHERE f1 IS NOT NULL EXCEPT ALL SELECT f1 FROM t2 WHERE f1 IS NOT NULL ) UNION ALL ( ( ( ( SELECT f2 FROM t2 WHERE f1 = 4 UNION ALL ( ( SELECT DISTINCT SUM ( DISTINCT f1 ) FROM t1 EXCEPT SELECT DISTINCT f2 FROM t2 WHERE f1 IS NULL ) EXCEPT SELECT f2 FROM t1 WHERE f1 > 5 UNION SELECT DISTINCT COUNT ( DISTINCT f2 ) FROM t1 INTERSECT SELECT DISTINCT COUNT ( DISTINCT f1 ) FROM t1 WHERE f1 IS NULL UNION ( SELECT MAX ( DISTINCT f2 ) FROM t2 WHERE f1 = 8 INTERSECT ALL ( SELECT DISTINCT f2 FROM t1 WHERE f1 IS NULL EXCEPT SELECT DISTINCT MIN ( f2 ) FROM t2 WHERE f1 = 5 INTERSECT ALL SELECT DISTINCT f1 FROM t2 WHERE f1 IS NOT NULL EXCEPT ALL SELECT DISTINCT f2 FROM t2 WHERE f1 IS NOT NULL UNION ALL SELECT MAX ( DISTINCT f1 ) FROM t1 INTERSECT SELECT MAX ( f2 ) FROM t1 EXCEPT SELECT DISTINCT COUNT ( f2 ) FROM t1 WHERE f1 IS NULL UNION ( ( ( ( SELECT MAX ( DISTINCT f1 ) FROM t1 UNION ALL SELECT SUM ( DISTINCT f2 ) FROM t2 WHERE f1 IS NOT NULL ) EXCEPT SELECT MAX ( f2 ) FROM t2 EXCEPT ALL SELECT MAX ( DISTINCT f2 ) FROM t1 WHERE f1 IS NULL UNION ALL SELECT DISTINCT COUNT ( DISTINCT f1 ) FROM t1 WHERE f1 < 0 ) UNION ALL SELECT f2 FROM t2 UNION SELECT DISTINCT MAX ( DISTINCT f1 ) FROM t1 WHERE f1 IS NULL ) INTERSECT ALL ( SELECT f1 FROM t2 WHERE f1 > 6 INTERSECT ALL SELECT DISTINCT f1 FROM t1 ) ) ) EXCEPT ALL SELECT f1 FROM t1 ) ) ) INTERSECT SELECT DISTINCT MAX ( f1 ) FROM t1 INTERSECT SELECT DISTINCT f1 FROM t1 ) EXCEPT ALL ( SELECT SUM ( f1 ) FROM t2 WHERE f1 < 2 EXCEPT SELECT DISTINCT f1 FROM t2 ) ) UNION ALL SELECT DISTINCT SUM ( DISTINCT f2 ) FROM t1 WHERE f1 IS NOT NULL UNION SELECT DISTINCT f1 FROM t1 UNION ALL ( SELECT DISTINCT MIN ( f1 ) FROM t2 WHERE f1 IS NOT NULL INTERSECT ALL SELECT DISTINCT f2 FROM t1 WHERE f1 < 7 ) INTERSECT ALL SELECT f1 FROM t1 WHERE f1 = 3 INTERSECT SELECT DISTINCT MAX ( f2 ) FROM t1 WHERE f1 > 0 EXCEPT ALL ( SELECT DISTINCT f2 FROM t1 WHERE f1 = 3 UNION SELECT COUNT ( DISTINCT f2 ) FROM t2 WHERE f1 IS NOT NULL UNION ALL SELECT DISTINCT f1 FROM t1 WHERE f1 IS NULL INTERSECT ( SELECT DISTINCT f2 FROM t1 WHERE f1 IS NULL INTERSECT ALL SELECT MAX ( DISTINCT f2 ) FROM t2 WHERE f1 IS NULL ) UNION ALL SELECT MIN ( f1 ) FROM t1 UNION SELECT f2 FROM t1 WHERE f1 < 9 ) UNION ALL SELECT SUM ( DISTINCT f1 ) FROM t2 WHERE f1 IS NULL INTERSECT ALL SELECT DISTINCT f2 FROM t2 WHERE f1 = 4 EXCEPT SELECT DISTINCT SUM ( DISTINCT f1 ) FROM t2 WHERE f1 IS NOT NULL ) INTERSECT SELECT f1 FROM t1 WHERE f1 = 0 ) EXCEPT SELECT COUNT ( f1 ) FROM t2 UNION SELECT DISTINCT MAX ( DISTINCT f2 ) FROM t2 WHERE f1 = 5 ) UNION ALL SELECT DISTINCT f2 FROM t1 UNION SELECT f2 FROM t1 WHERE f1 IS NOT NULL ) EXCEPT SELECT SUM ( DISTINCT f1 ) FROM t2 UNION ALL ( ( ( SELECT DISTINCT SUM ( DISTINCT f1 ) FROM t1 UNION ALL SELECT f1 FROM t2 ) UNION ALL SELECT DISTINCT MIN ( DISTINCT f2 ) FROM t1 ) EXCEPT SELECT DISTINCT COUNT ( f1 ) FROM t1 INTERSECT ALL SELECT DISTINCT f2 FROM t2 ) INTERSECT ALL ( SELECT DISTINCT COUNT ( DISTINCT f1 ) FROM t1 WHERE f1 IS NULL EXCEPT ALL SELECT MAX ( DISTINCT f2 ) FROM t2 WHERE f1 < 7 ) INTERSECT ALL SELECT DISTINCT f2 FROM t1 UNION ALL ( SELECT DISTINCT SUM ( DISTINCT f2 ) FROM t1 INTERSECT ALL SELECT DISTINCT f1 FROM t2 EXCEPT SELECT DISTINCT MAX ( DISTINCT f1 ) FROM t1 WHERE f1 > 7 ) UNION ALL SELECT DISTINCT f1 FROM t2 WHERE f1 IS NOT NULL EXCEPT ( ( SELECT DISTINCT f1 FROM t2 UNION SELECT f1 FROM t2 WHERE f1 > 3 ) UNION ALL SELECT f2 FROM t1 UNION SELECT MAX ( DISTINCT f1 ) FROM t1 ) ) EXCEPT ALL ( ( ( SELECT DISTINCT f2 FROM t2 EXCEPT ALL ( SELECT SUM ( DISTINCT f2 ) FROM t1 WHERE f1 IS NOT NULL INTERSECT ALL ( SELECT DISTINCT f1 FROM t2 WHERE f1 IS NOT NULL UNION ALL SELECT f1 FROM t1 WHERE f1 IS NULL ) INTERSECT ALL SELECT DISTINCT MIN ( f2 ) FROM t1 WHERE f1 > 9 UNION ( SELECT DISTINCT f1 FROM t1 EXCEPT ALL SELECT COUNT ( DISTINCT f2 ) FROM t2 WHERE f1 > 0 ) ) ) EXCEPT SELECT DISTINCT f2 FROM t2 EXCEPT ALL SELECT f2 FROM t2 WHERE f1 < 5 UNION ( SELECT DISTINCT SUM ( f2 ) FROM t2 EXCEPT ALL SELECT COUNT ( f2 ) FROM t2 WHERE f1 IS NULL EXCEPT SELECT DISTINCT f2 FROM t2 WHERE f1 IS NULL ) UNION SELECT MAX ( f2 ) FROM t1 WHERE f1 IS NOT NULL UNION SELECT MAX ( DISTINCT f1 ) FROM t1 WHERE f1 IS NOT NULL ) INTERSECT ( ( SELECT DISTINCT f2 FROM t1 WHERE f1 IS NULL INTERSECT ALL SELECT DISTINCT f1 FROM t1 WHERE f1 IS NOT NULL INTERSECT ( ( SELECT MIN ( DISTINCT f2 ) FROM t2 UNION ( ( SELECT SUM ( f2 ) FROM t2 WHERE f1 IS NULL EXCEPT ALL ( ( SELECT f1 FROM t1 UNION SELECT f1 FROM t2 WHERE f1 IS NULL ) EXCEPT ALL SELECT DISTINCT MIN ( DISTINCT f1 ) FROM t1 WHERE f1 IS NOT NULL ) ) UNION SELECT COUNT ( DISTINCT f2 ) FROM t2 ) UNION ALL ( ( SELECT DISTINCT f2 FROM t1 WHERE f1 > 5 EXCEPT ( SELECT DISTINCT f2 FROM t1 INTERSECT SELECT f1 FROM t1 WHERE f1 < 2 UNION ALL SELECT DISTINCT SUM ( DISTINCT f2 ) FROM t1 WHERE f1 IS NULL UNION ALL ( ( SELECT f2 FROM t1 WHERE f1 = 3 INTERSECT SELECT DISTINCT SUM ( DISTINCT f2 ) FROM t2 WHERE f1 IS NOT NULL ) EXCEPT ALL ( ( SELECT f2 FROM t2 WHERE f1 < 5 EXCEPT SELECT DISTINCT f2 FROM t2 WHERE f1 < 7 ) INTERSECT SELECT f2 FROM t1 UNION ALL ( ( ( SELECT f1 FROM t1 UNION ALL SELECT DISTINCT f1 FROM t1 ) UNION SELECT f2 FROM t2 ) UNION ALL SELECT DISTINCT f2 FROM t1 UNION ALL SELECT DISTINCT COUNT ( DISTINCT f2 ) FROM t2 EXCEPT ALL SELECT SUM ( f2 ) FROM t1 WHERE f1 > 9 INTERSECT SELECT DISTINCT f1 FROM t2 WHERE f1 < 5 INTERSECT SELECT SUM ( f1 ) FROM t2 WHERE f1 < 0 INTERSECT ALL SELECT DISTINCT COUNT ( f1 ) FROM t1 WHERE f1 IS NULL ) ) INTERSECT ALL ( SELECT DISTINCT MAX ( DISTINCT f2 ) FROM t2 EXCEPT ALL ( SELECT DISTINCT f2 FROM t2 WHERE f1 IS NOT NULL UNION SELECT f2 FROM t1 ) ) ) EXCEPT ALL ( SELECT DISTINCT f2 FROM t2 UNION SELECT f2 FROM t1 WHERE f1 > 0 ) INTERSECT ALL SELECT f2 FROM t1 ) INTERSECT ALL SELECT DISTINCT SUM ( f2 ) FROM t1 WHERE f1 = 8 INTERSECT ALL ( ( ( SELECT f2 FROM t2 UNION SELECT DISTINCT SUM ( DISTINCT f1 ) FROM t1 WHERE f1 IS NULL EXCEPT ( ( SELECT f2 FROM t1 EXCEPT SELECT f2 FROM t2 ) EXCEPT ( SELECT DISTINCT f2 FROM t2 INTERSECT ALL SELECT f1 FROM t1 WHERE f1 IS NULL ) ) INTERSECT SELECT COUNT ( f2 ) FROM t1 ) INTERSECT SELECT DISTINCT f2 FROM t2 WHERE f1 > 7 ) UNION ALL SELECT SUM ( f2 ) FROM t2 WHERE f1 = 3 ) ) INTERSECT ALL ( SELECT f2 FROM t2 WHERE f1 IS NOT NULL UNION ALL SELECT DISTINCT f2 FROM t2 WHERE f1 IS NOT NULL UNION ALL SELECT f2 FROM t1 WHERE f1 < 8 ) ) ) UNION ALL SELECT f1 FROM t1 WHERE f1 IS NOT NULL ) ) EXCEPT ( ( SELECT f2 FROM t2 EXCEPT ( ( SELECT MIN ( DISTINCT f1 ) FROM t2 WHERE f1 IS NOT NULL UNION ALL ( ( SELECT DISTINCT f1 FROM t2 WHERE f1 > 2 INTERSECT ALL SELECT DISTINCT f1 FROM t1 EXCEPT ALL SELECT DISTINCT f2 FROM t1 UNION SELECT DISTINCT MIN ( f2 ) FROM t1 WHERE f1 > 2 EXCEPT SELECT MAX ( f1 ) FROM t1 INTERSECT SELECT f2 FROM t1 ) INTERSECT ALL ( SELECT DISTINCT SUM ( DISTINCT f2 ) FROM t2 WHERE f1 = 2 EXCEPT ALL ( ( SELECT DISTINCT COUNT ( DISTINCT f2 ) FROM t2 UNION SELECT DISTINCT SUM ( f2 ) FROM t2 WHERE f1 IS NOT NULL EXCEPT ALL SELECT DISTINCT f1 FROM t1 WHERE f1 IS NULL EXCEPT ALL SELECT MIN ( f1 ) FROM t1 WHERE f1 IS NOT NULL ) UNION SELECT DISTINCT SUM ( f2 ) FROM t2 ) UNION SELECT f1 FROM t1 WHERE f1 IS NULL ) INTERSECT ALL SELECT f2 FROM t2 WHERE f1 IS NOT NULL EXCEPT ( SELECT DISTINCT COUNT ( DISTINCT f1 ) FROM t1 INTERSECT ALL SELECT DISTINCT f1 FROM t1 WHERE f1 = 7 ) INTERSECT ALL SELECT f2 FROM t2 WHERE f1 < 7 ) ) UNION ALL SELECT DISTINCT SUM ( DISTINCT f2 ) FROM t1 WHERE f1 = 3 UNION ALL SELECT f2 FROM t2 WHERE f1 IS NULL ) ) INTERSECT ALL ( ( SELECT MAX ( f1 ) FROM t1 UNION ALL SELECT DISTINCT f2 FROM t2 UNION ALL SELECT DISTINCT f1 FROM t1 WHERE f1 < 9 ) EXCEPT ( SELECT MAX ( DISTINCT f2 ) FROM t2 WHERE f1 < 9 EXCEPT ALL SELECT f2 FROM t2 WHERE f1 IS NOT NULL UNION ALL ( SELECT DISTINCT COUNT ( DISTINCT f1 ) FROM t1 WHERE f1 IS NULL INTERSECT SELECT f2 FROM t2 WHERE f1 > 1 ) ) ) UNION SELECT SUM ( DISTINCT f2 ) FROM t1 WHERE f1 < 1 ) UNION ALL SELECT DISTINCT MAX ( f1 ) FROM t2 ) ) EXCEPT SELECT f1 FROM t2 WHERE f1 < 9 ) EXCEPT SELECT f1 FROM t1 INTERSECT SELECT f1 FROM t2 EXCEPT ( SELECT DISTINCT f1 FROM t2 INTERSECT ALL ( SELECT DISTINCT MAX ( f2 ) FROM t2 WHERE f1 IS NULL UNION ALL SELECT f2 FROM t2 WHERE f1 IS NOT NULL INTERSECT SELECT f1 FROM t1 WHERE f1 IS NOT NULL UNION ALL SELECT MIN ( DISTINCT f1 ) FROM t1 ) UNION ALL SELECT f1 FROM t2 WHERE f1 IS NOT NULL ) INTERSECT SELECT DISTINCT MIN ( f2 ) FROM t2 ) EXCEPT ALL SELECT f1 FROM t1 WHERE f1 IS NOT NULL INTERSECT SELECT DISTINCT f2 FROM t2 WHERE f1 = 6 ) INTERSECT SELECT SUM ( DISTINCT f2 ) FROM t1 WHERE f1 > 5 EXCEPT SELECT f1 FROM t1 WHERE f1 < 3 INTERSECT ( ( ( SELECT MAX ( DISTINCT f2 ) FROM t1 INTERSECT SELECT DISTINCT MAX ( DISTINCT f1 ) FROM t2 WHERE f1 = 4 ) EXCEPT SELECT SUM ( f1 ) FROM t2 WHERE f1 IS NULL ) INTERSECT SELECT f1 FROM t2 ) ) ) ) INTERSECT ( SELECT DISTINCT SUM ( f1 ) FROM t1 WHERE f1 < 0 EXCEPT SELECT MIN ( f1 ) FROM t2 ) UNION ALL ( SELECT SUM ( DISTINCT f2 ) FROM t2 WHERE f1 IS NOT NULL INTERSECT ALL SELECT MIN ( f1 ) FROM t1 WHERE f1 > 9 ) EXCEPT ALL SELECT DISTINCT COUNT ( f2 ) FROM t2 WHERE f1 < 2 ) INTERSECT ( SELECT MIN ( DISTINCT f2 ) FROM t1 WHERE f1 IS NULL UNION SELECT DISTINCT COUNT ( f2 ) FROM t1 WHERE f1 IS NULL ) INTERSECT SELECT DISTINCT MAX ( f1 ) FROM t2 WHERE f1 IS NULL ) EXCEPT SELECT f2 FROM t2 WHERE f1 > 8 ) ;
Relevant log output
(gdb) bt
#0 0x00007f0c59f01246 in __GI___pthread_mutex_trylock (mutex=0x7f0c42e08610) at ../nptl/pthread_mutex_trylock.c:38
#1 0x00005597902d6410 in _rjem_je_tcache_bin_flush_small ()
#2 0x0000559790259c76 in _rjem_je_sdallocx_default ()
#3 0x00005597904e7958 in core::ptr::drop_in_place<mz_expr::relation::MirRelationExpr> ()
...
#56511 0x00005597904e77eb in core::ptr::drop_in_place<mz_expr::relation::MirRelationExpr> ()
#56512 0x00005597904e77eb in core::ptr::drop_in_place<mz_expr::relation::MirRelationExpr> ()
#56513 0x00005597904f89a8 in mz_transform::Optimizer::optimize ()
#56514 0x000055978f94d802 in mz_adapter::coord::sequencer::<impl mz_adapter::coord::Coordinator<S>>::sequence_peek::{{closure}}::{{closure}} ()
#56515 0x000055978fd3ede2 in <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll ()
#56516 0x000055978f95edb8 in mz_adapter::coord::sequencer::<impl mz_adapter::coord::Coordinator<S>>::sequence_plan::{{closure}}::{{closure}} ()
#56517 0x000055978fcc4de2 in <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll ()
#56518 0x000055978fda3868 in <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll ()
#56519 0x000055978fdaefdd in <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll ()
#56520 0x000055978fd828bf in <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll ()
#56521 0x000055978fdb69c2 in <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll ()
#56522 0x000055978fed0e3e in <tracing::instrument::Instrumented<T> as core::future::future::Future>::poll ()
#56523 0x000055978f941dd7 in mz_adapter::coord::message_handler::<impl mz_adapter::coord::Coordinator<S>>::message_command::{{closure}}::{{closure}} ()
#56524 0x000055978f93cf63 in mz_adapter::coord::message_handler::<impl mz_adapter::coord::Coordinator<S>>::handle_message::{{closure}} ()
#56525 0x000055978fc52a21 in <core::future::from_generator::GenFuture<T> as core::future::future::Future>::poll ()
#56526 0x000055978f668abd in std::thread::local::LocalKey<T>::with ()
#56527 0x000055978f545e0f in tokio::park::thread::CachedParkThread::block_on ()
#56528 0x000055978fbd0e84 in tokio::runtime::handle::Handle::block_on ()
#56529 0x000055978f4c51fd in std::sys_common::backtrace::__rust_begin_short_backtrace ()
#56530 0x000055978f5eb2bd in core::ops::function::FnOnce::call_once{{vtable-shim}} ()
#56531 0x0000559792283b63 in std::sys::unix::thread::Thread::new::thread_start ()
#56532 0x00007f0c59efe609 in start_thread (arg=<optimized out>) at pthread_create.c:477
#56533 0x00007f0c59ad1133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
Merged https://github.com/MaterializeInc/materialize/pull/14441, after which this query doesn't cause a panic anymore, but instead prints a proper error msg to the user about hitting the recursion limit.
A proper fix that would allow the query to actually run, would probably involve making a variadic Let. @aalexandrov, if we don't have an issue for that already, then I'll create one.
I'm keeping this issue open, because it's still in our long-term plans to fix this. Very big queries should be supported, because:
- @antiguru says that users will use BI tools to create monster queries;
- @wangandi says that there was already one issue from a customer with a stack overflow, where the customer generated a big test query to test the limits of the optimizer.
I'll close this issue because it relates to a specific incident that came from a bug triaging process. I'll create a seprate issue if we don't have one for making the plans more robust to stack overflows.