Materialize/test/sqllogictest/cockroach/sqlsmith.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# 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.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/sqlsmith
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

mode cockroach

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

# This file contains regression tests discovered by sqlsmith.


# Regression: materialize#28836 (nulls in string_agg)

statement ok
SELECT subq_0.c3 AS c0, subq_0.c6 AS c1, subq_0.c4 AS c2, CASE WHEN (SELECT start_key FROM crdb_internal.ranges LIMIT 1 OFFSET 6) < CAST(NULLIF(pg_catalog.string_agg(CAST((SELECT start_key FROM crdb_internal.ranges LIMIT 1 OFFSET 7) AS BYTEA), CAST((SELECT pg_catalog.xor_agg(tgargs) FROM pg_catalog.pg_trigger) AS BYTEA)) OVER (PARTITION BY subq_0.c0 ORDER BY subq_0.c0, subq_0.c5, subq_0.c2), CAST(NULL AS BYTEA)) AS BYTEA) THEN subq_0.c6 ELSE subq_0.c6 END AS c3, subq_0.c2 AS c4, subq_0.c7 AS c5, CAST(COALESCE(subq_0.c7, subq_0.c7) AS INT8) AS c6 FROM (SELECT ref_0.table_name AS c0, ref_0.table_catalog AS c1, ref_0.table_type AS c2, (SELECT rolcreatedb FROM pg_catalog.pg_roles LIMIT 1 OFFSET 79) AS c3, ref_0.table_name AS c4, ref_0.version AS c5, ref_0.version AS c6, ref_0.version AS c7 FROM information_schema.tables AS ref_0 WHERE (ref_0.version IS NOT NULL) OR (pg_catalog.set_masklen(CAST(CAST(NULL AS INET) AS INET), CAST(ref_0.version AS INT8)) != (SELECT pg_catalog.max(client_addr) FROM pg_catalog.pg_stat_activity)) LIMIT 101) AS subq_0 WHERE subq_0.c7 IS NOT NULL

# Regression: make sure lookup join planNode propagates its close signal. This
# query could panic otherwise with a failure to empty all memory accounts.

statement ok
CREATE TABLE a (a INT PRIMARY KEY);

statement ok
SELECT true FROM (SELECT ref_1.a AS c0 FROM crdb_internal.cluster_queries AS ref_0 JOIN a AS ref_1 ON (ref_0.node_id = ref_1.a) WHERE (SELECT a from a limit 1 offset 1) is null);

# Regression: cockroach#34437 (union all could produce panic in distsql planning)

statement ok
CREATE TABLE table8 (col1 TIME, col2 BYTEA, col4 OID, col6 NAME, col9 TIMESTAMP, PRIMARY KEY (col1));

statement ok
CREATE TABLE table5 (col0 TIME NULL, col1 OID, col3 INET, PRIMARY KEY (col1 ASC));

statement ok
INSERT INTO table8 (col1, col2, col4, col6)
VALUES ('19:06:18.321589', NULL, NULL, NULL)
UNION ALL (SELECT NULL, NULL, NULL, NULL FROM table5 AS tab_8);

# Regression: cockroach#36441 (raw indexed var can't be type checked)
query TO
WITH
    with_20394 (col_162526)
        AS (
            SELECT
                *
            FROM
                (
                    VALUES
                        (
                            'd2d225e2-e9be-4420-a645-d1b8f577511c':::UUID
                        )
                )
                    AS tab_25520 (col_162526)
            UNION ALL
                SELECT
                    *
                FROM
                    (
                        VALUES
                            (
                                '1d6eaf81-8a2c-43c5-a495-a3b102917ab1':::UUID
                            )
                    )
                        AS tab_25521 (col_162527)
        )
SELECT
    max(with_20394.col_162526::UUID)::UUID AS col_162534,
    3697877132:::OID AS col_162541
FROM
    with_20394
GROUP BY
    with_20394.col_162526
ORDER BY
    with_20394.col_162526 ASC
----
1d6eaf81-8a2c-43c5-a495-a3b102917ab1  3697877132
d2d225e2-e9be-4420-a645-d1b8f577511c  3697877132

# Regression: cockroach#36830 (can't run wrapped window node)
statement ok
CREATE TABLE table9 (a INT8);

statement ok
INSERT INTO table9 SELECT lag(a) OVER (PARTITION BY a) FROM table9;

# Regression: cockroach#36607 (can't serialize or type-check arrays of NULL properly)
query TTTT
WITH
    with_194015 (col_1548014)
        AS (
            SELECT
                *
            FROM
                (
                    VALUES
                        (('-28 years -2 mons -677 days -11:53:30.528699':::INTERVAL::INTERVAL + '11:55:41.419498':::TIME::TIME)::TIME + '1973-01-24':::DATE::DATE),
                        ('1970-01-11 01:38:09.000155+00:00':::TIMESTAMP),
                        ('1970-01-09 07:04:13.000247+00:00':::TIMESTAMP),
                        ('1970-01-07 14:19:52.000951+00:00':::TIMESTAMP),
                        (NULL)
                )
                    AS tab_240443 (col_1548014)
        ),
    with_194016 (col_1548015, col_1548016, col_1548017)
        AS (
            SELECT
                *
            FROM
                (
                    VALUES
                        (
                            '160.182.25.199/22':::INET::INET << 'c2af:30cb:5db8:bb79:4d11:2d0:1de8:bcea/59':::INET::INET,
                            '09:14:05.761109':::TIME::TIME + '4 years 7 mons 345 days 23:43:13.325036':::INTERVAL::INTERVAL,
                            B'0101010110101011101001111010100011001111001110001000101100011001101'
                        ),
                        (false, '14:36:41.282187':::TIME, B'011111111011001100000001101101011111110110010011110100110111100')
                )
                    AS tab_240444 (col_1548015, col_1548016, col_1548017)
        ),
    with_194017 (col_1548018)
        AS (SELECT * FROM (VALUES ('43a30bc5-e412-426d-b99a-65783a7ed445':::UUID), (NULL), (crdb_internal.cluster_id()::UUID)) AS tab_240445 (col_1548018))
SELECT
    CASE
    WHEN false THEN age('1970-01-09 08:48:24.000568+00:00':::TIMESTAMPTZ::TIMESTAMPTZ, '1970-01-07 08:40:45.000483+00:00':::TIMESTAMPTZ::TIMESTAMPTZ)::INTERVAL
    ELSE (
        (
            (-0.02805450661234963150):::DECIMAL::DECIMAL
            * array_position(
                    (gen_random_uuid()::UUID::UUID || (NULL::UUID || NULL::UUID[])::UUID[])::UUID[],
                    '5f29920d-7db1-4efc-b1cc-d1a7d0bcf145':::UUID::UUID
                )::INT8::INT8
        )::DECIMAL
        * age('1970-01-04 07:17:45.000268+00:00':::TIMESTAMPTZ::TIMESTAMPTZ, NULL::TIMESTAMPTZ)::INTERVAL::INTERVAL
    )
    END::INTERVAL
    + '-21 years -10 mons -289 days -13:27:05.205069':::INTERVAL::INTERVAL
        AS col_1548019,
    '1984-01-07':::DATE AS col_1548020,
    NULL AS col_1548021,
    'f96fd19a-d2a9-4d98-81dd-97e3fc2a45d2':::UUID AS col_1548022
FROM
    with_194015
ORDER BY
    with_194015.col_1548014 DESC
LIMIT
    4:::INT8;
----
NULL  1984-01-07 00:00:00 +0000 +0000  NULL  f96fd19a-d2a9-4d98-81dd-97e3fc2a45d2
NULL  1984-01-07 00:00:00 +0000 +0000  NULL  f96fd19a-d2a9-4d98-81dd-97e3fc2a45d2
NULL  1984-01-07 00:00:00 +0000 +0000  NULL  f96fd19a-d2a9-4d98-81dd-97e3fc2a45d2
NULL  1984-01-07 00:00:00 +0000 +0000  NULL  f96fd19a-d2a9-4d98-81dd-97e3fc2a45d2