Materialize/test/testdrive/github-5502.td

# 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 from database-issues#5502, where earlier errors were erroneously
# discarded by TopK's error handling.

> CREATE TABLE t0(c0 FLOAT);
> CREATE TABLE t1(c0 CHAR(317) DEFAULT (CAST(0.37833587419206882174194106482900679111480712890625 AS VARCHAR(654))));
> CREATE TABLE t2(c0 DECIMAL, c1 integer);
> CREATE TABLE t3(c0 INT);
> CREATE TABLE t4(c0 boolean);
> INSERT INTO t4(c0) VALUES(TRUE);
> INSERT INTO t1(c0) VALUES(''), ((('')||(((((((1677746101)&(1677746101)))<<(((1402123780)&(836896798)))))|(((((-1221621620)|(1402123780)))&(((-1485737887)>>(-1221621620)))))))));
> INSERT INTO t2(c1, c0) VALUES(-251187149, 0.7499183096237433954200923835742287337779998779296875);
> CREATE VIEW v0(c0) AS (SELECT DISTINCT t2.c1 FROM t0, t3, t1, t2, t4 WHERE t4.c0);
> INSERT INTO t4(c0) VALUES(NOT ((NOT (((0.42697382035812925238360548974014818668365478515625)<=(0.78326640921410817242076518596149981021881103515625)))) IS UNKNOWN)), (TRUE), (FALSE);
> UPDATE t0 SET c0=0.11376621 WHERE (0.11376621) BETWEEN (0.4413229) AND (0.04469857);
> INSERT INTO t2(c0) VALUES(0.60333799444918667642667742256890051066875457763671875);
> INSERT INTO t2(c0) VALUES(0.53391115661476107323579753938247449696063995361328125), (0.96599097445518788962459666436188854277133941650390625), (0.50874802079664827036964425133191980421543121337890625);
> CREATE INDEX t2i0 ON t2(c1 DESC NULLS LAST);
> INSERT INTO t3(c0) VALUES(-1221621620);
> DELETE FROM t4;
> INSERT INTO t2(c0, c1) VALUES(0.798383958841370588999097890336997807025909423828125, 1402123780), (0.60310610645862705592179509039851836860179901123046875, -886808703), (0.96925146076085255675280905052204616367816925048828125, -1006231220);
> INSERT INTO t1(c0) VALUES('}~^KC0');
> INSERT INTO t2(c1) VALUES(-1221621620), (-2008997878), (409134752);
> INSERT INTO t0(c0) VALUES(0.11376621), (0.1963102), (-1.00623123E9), (0.3170005);
> INSERT INTO t0(c0) VALUES(0.84266704), (0.29847664), (0.28999355);
> INSERT INTO t2(c1) VALUES(NULL);
> INSERT INTO t2(c0) VALUES(0.3973591531507036922477027474087662994861602783203125);
> INSERT INTO t0(c0) VALUES(-1.22162163E9);
> INSERT INTO t0(c0) VALUES(0.36171648);
> INSERT INTO t4(c0) VALUES(TRUE);
> INSERT INTO t0(c0) VALUES(0.05187392);
> INSERT INTO t0(c0) VALUES(1677746101);

# Returns the same result in Postgres and Materialize (v0.48.0 and v0.49.0).
> SELECT
    SUM(count)
  FROM
    (
      SELECT
        (NOT (NOT (t4.c0)))::INT as count
      FROM
        t4
        RIGHT OUTER JOIN (SELECT - (t3.c0) FROM t1, t3) AS sub0 ON t4.c0
        RIGHT OUTER JOIN (SELECT (0.47583646) NOT IN (- (t3.c0)), CAST(t4.c0 AS VARCHAR(813))!~*t1.c0 FROM t3, t4, t2, v0, t1 WHERE NOT (((t4.c0)<(t4.c0)))) AS sub1 ON t4.c0
    ) as res;
936

# insert a bad value!
> INSERT INTO t1(c0) VALUES('Ja[LPB');

# Different results in Postgres and Materialize; returns an error in
# v0.48.0, swallows an error in v0.49.0 and erroneously returns 1248.
#
# This test was fixed by materialize#22560, indicating that the issue is not
# affecting the equijoin-based outer join lowering strategy.
# ! SELECT
#     SUM(count)
#   FROM
#     (
#       SELECT
#         (NOT (NOT (t4.c0)))::INT as count
#       FROM
#         t4
#         RIGHT OUTER JOIN (SELECT - (t3.c0) FROM t1, t3) AS sub0 ON t4.c0
#         RIGHT OUTER JOIN (SELECT (0.47583646) NOT IN (- (t3.c0)), CAST(t4.c0 AS VARCHAR(813))!~*t1.c0 FROM t3, t4, t2, v0, t1 WHERE NOT (((t4.c0)<(t4.c0))) LIMIT 5633455955921112428) AS sub1 ON t4.c0
#     ) as res;
# contains: invalid regular expression