where.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/where
#
# 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

statement ok
CREATE TABLE kv (
  k INT PRIMARY KEY,
  v INT
)

statement ok
INSERT INTO kv VALUES (1, 2), (3, 4), (5, 6), (7, 8)

statement ok
CREATE TABLE kvString (
  k TEXT PRIMARY KEY,
  v TEXT
)

statement ok
INSERT INTO kvString VALUES ('like1', 'hell%'), ('like2', 'worl%')

query II rowsort
SELECT * FROM kv WHERE True
----
1 2
3 4
5 6
7 8

query II
SELECT * FROM kv WHERE False
----

query II rowsort
SELECT * FROM kv WHERE k IN (1, 3)
----
1 2
3 4

query II
SELECT * FROM kv WHERE v IN (6)
----
5 6

query II rowsort
SELECT * FROM kv WHERE k IN (SELECT k FROM kv)
----
1 2
3 4
5 6
7 8

query II rowsort
SELECT * FROM kv WHERE (k,v) IN (SELECT * FROM kv)
----
1 2
3 4
5 6
7 8

query error column "nonexistent" does not exist
SELECT * FROM kv WHERE nonexistent = 1

query B
SELECT 'hello' LIKE v FROM kvString WHERE k LIKE 'like%' ORDER BY k
----
true
false

query B
SELECT 'hello' SIMILAR TO v FROM kvString WHERE k SIMILAR TO 'like[1-2]' ORDER BY k
----
true
false

query B
SELECT 'hello' ~ replace(v, '%', '.*') FROM kvString WHERE k ~ 'like[1-2]' ORDER BY k
----
true
false

# Test mixed type tuple comparison.

query II rowsort
SELECT * FROM kv WHERE k IN (1, 5.0, 9)
----
1 2
5 6

# Regression tests for #22670.
statement ok
CREATE TABLE ab (a INT, b INT)

statement ok
INSERT INTO ab VALUES (1, 10), (2, 20), (3, 30), (4, NULL), (NULL, 50), (NULL, NULL)

query II rowsort
SELECT * FROM ab WHERE a IN (1, 3, 4)
----
1  10
3  30
4  NULL

query II rowsort
SELECT * FROM ab WHERE a IN (1, 3, 4, NULL)
----
1  10
3  30
4  NULL

query II rowsort
SELECT * FROM ab WHERE (a, b) IN ((1, 10), (3, 30), (4, 40))
----
1  10
3  30

query II rowsort
SELECT * FROM ab WHERE (a, b) IN ((1, 10), (4, NULL), (NULL, 50))
----
1  10