Materialize/test/sqllogictest/boolean.slt

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

mode cockroach

statement ok
CREATE TABLE bools (b bool)

statement ok
INSERT INTO bools VALUES (TRUE), (FALSE), (NULL);

statement ok
CREATE TABLE bools_text (b text)

statement ok
INSERT INTO bools_text VALUES
    ('t'), ('tr'), ('tR'), ('tRuE'), ('TRUE'), ('    tr    '),
    ('f'), ('fa'), ('faL'), ('fAlsE'), ('FALSE'), ('           fal '),
    ('on'), ('off'), ('On'), ('Off'), ('ON'), ('oFf'),
    ('1'), ('0'), ('               1'), ('0        ')

query TB rowsort
SELECT replace(b, ' ', 'x'), b::bool FROM bools_text
----
xxxxxxxxxxxxxxx1  true
xxxxxxxxxxxfalx   false
xxxxtrxxxx        true
0xxxxxxxx         false
0                 false
1                 true
f                 false
fa                false
faL               false
fAlsE             false
FALSE             false
off               false
oFf               false
Off               false
on                true
On                true
ON                true
t                 true
tr                true
tR                true
tRuE              true
TRUE              true

query error invalid input syntax for type boolean: "blah"
SELECT 'blah'::bool

query error NOT argument must have type boolean, not type integer
SELECT NOT 1

query error AND argument must have type boolean, not type integer
SELECT 1 AND 1

query error OR argument must have type boolean, not type integer
SELECT 1 OR 1

query error OR argument must have type boolean, not type integer
SELECT 1 OR FALSE

query error OR argument must have type boolean, not type integer
SELECT FALSE OR 1

query error AND argument must have type boolean, not type integer
SELECT 1 AND FALSE

query error AND argument must have type boolean, not type integer
SELECT FALSE AND 1

query B colnames
SELECT TRUE
----
bool
true

query B colnames
SELECT FALSE
----
bool
false

query B
SELECT NOT TRUE
----
false

query B
SELECT NOT FALSE
----
true

query BBB rowsort
SELECT a.b, b.b, a.b AND b.b FROM bools AS a CROSS JOIN bools AS b;
----
true  true  true
true  false false
true  NULL  NULL
false true  false
false false false
false NULL  false
NULL  true  NULL
NULL  false false
NULL  NULL  NULL

# Boolean AND error cases

query error division by zero
SELECT (1/0 > 0) AND TRUE;

# Postgres returns the error instead
query B
SELECT (1/0 > 0) AND FALSE;
----
false

query error division by zero
SELECT (1/0 > 0) AND NULL;

query error division by zero
SELECT TRUE AND (1/0 > 0);

query B
SELECT FALSE AND (1/0 > 0);
----
false

query error division by zero
SELECT NULL AND (1/0 > 0);

# Check we consistently pick the same error
# Postgres returns the first error instead

query error "32768" smallint out of range
SELECT (32768::int2 > 0) AND (1/0 > 0);

query error "32768" smallint out of range
SELECT (1/0 > 0) AND (32768::int2 > 0);

query BBB rowsort
SELECT a.b, b.b, a.b OR b.b FROM bools AS a CROSS JOIN bools AS b;
----
true  true  true
true  false true
true  NULL  true
false true  true
false false false
false NULL  NULL
NULL  true  true
NULL  false NULL
NULL  NULL  NULL

# Boolean OR error cases

# Postgres returns the error instead
query B
SELECT (1/0 > 0) OR TRUE;
----
true

query error division by zero
SELECT (1/0 > 0) OR FALSE;

query error division by zero
SELECT (1/0 > 0) OR NULL;

query B
SELECT TRUE OR (1/0 > 0);
----
true

query error division by zero
SELECT FALSE OR (1/0 > 0);

query error division by zero
SELECT NULL OR (1/0 > 0);

# Check we consistently pick the same error
# Postgres returns the first error instead

query error smallint out of range
SELECT (32768::int2 > 0) OR (1/0 > 0);

query error smallint out of range
SELECT (1/0 > 0) OR (32768::int2 > 0);

query B
SELECT TRUE AND NOT TRUE
----
false

query B
SELECT NOT FALSE OR FALSE
----
true

### integer to bool ###

query B
SELECT 99::int::bool
----
true

query B
SELECT 0::int::bool
----
false

query B
SELECT NOT 0::int::bool
----
true

### int8 to bool ###

query B
SELECT 99::bigint::bool
----
true

query B
SELECT 0::bigint::bool
----
false

query B
SELECT NOT 0::bigint::bool
----
true

### bool to integer ###

query I
SELECT true::int
----
1

query I
SELECT false::int
----
0

statement ok
CREATE TABLE x (a int, u int, j jsonb, b bool)

# Ensure the NOT gets pushed into the binary operation.
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT
  NOT(a = u),
  NOT(a != u),
  NOT(a < u),
  NOT(a > u),
  NOT(a >= u),
  NOT(a <= u),
  NOT(NOT(b)),
  -- Doesn't have a negation.
  NOT(j @> '{}'::JSONB)
FROM x
----
Explained Query:
  Project (#4..=#9, #3{b}, #10) // { arity: 8 }
    Map ((#0{a} != #1{u}), (#0{a} = #1{u}), (#0{a} >= #1{u}), (#0{a} <= #1{u}), (#0{a} < #1{u}), (#0{a} > #1{u}), NOT((#2{j} @> {}))) // { arity: 11 }
      ReadStorage materialize.public.x // { arity: 4 }

Source materialize.public.x

Target cluster: quickstart

EOF

statement ok
CREATE TABLE y (a boolean, b bool)

# Bypass if statements with identical branches
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT
  CASE WHEN b THEN a ELSE a END
FROM y
----
Explained Query:
  Project (#0{a}) // { arity: 1 }
    ReadStorage materialize.public.y // { arity: 2 }

Source materialize.public.y

Target cluster: quickstart

EOF

# Bypass if statements with identical branches
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT
  CASE WHEN b THEN NULL ELSE true END
FROM y
----
Explained Query:
  Project (#2) // { arity: 1 }
    Map ((null OR NOT(#1{b}) OR (#1{b}) IS NULL)) // { arity: 3 }
      ReadStorage materialize.public.y // { arity: 2 }

Source materialize.public.y

Target cluster: quickstart

EOF


# Bypass if statements with identical branches
query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT
  CASE WHEN b THEN NULL ELSE false END
FROM y
----
Explained Query:
  Project (#2) // { arity: 1 }
    Map ((#1{b} AND null AND (#1{b}) IS NOT NULL)) // { arity: 3 }
      ReadStorage materialize.public.y // { arity: 2 }

Source materialize.public.y

Target cluster: quickstart

EOF


query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT
  CASE WHEN b THEN true ELSE NULL END
FROM y
----
Explained Query:
  Project (#2) // { arity: 1 }
    Map ((null OR (#1{b} AND (#1{b}) IS NOT NULL))) // { arity: 3 }
      ReadStorage materialize.public.y // { arity: 2 }

Source materialize.public.y

Target cluster: quickstart

EOF


query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT
  CASE WHEN b THEN false ELSE NULL END
FROM y
----
Explained Query:
  Project (#2) // { arity: 1 }
    Map ((null AND (NOT(#1{b}) OR (#1{b}) IS NULL))) // { arity: 3 }
      ReadStorage materialize.public.y // { arity: 2 }

Source materialize.public.y

Target cluster: quickstart

EOF

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT
  CASE WHEN b THEN false ELSE TRUE END
FROM y
----
Explained Query:
  Project (#2) // { arity: 1 }
    Map ((NOT(#1{b}) OR (#1{b}) IS NULL)) // { arity: 3 }
      ReadStorage materialize.public.y // { arity: 2 }

Source materialize.public.y

Target cluster: quickstart

EOF

statement ok
CREATE TABLE z (a int, b int)

statement ok
insert into z values (null, null), (1, null), (null, 2), (1, 2), (2, 1)

query T multiline
EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT *
FROM z
WHERE CASE WHEN a > b THEN FALSE ELSE TRUE END
----
Explained Query:
  Filter ((#0{a}) IS NULL OR (#1{b}) IS NULL OR (#0{a} <= #1{b})) // { arity: 2 }
    ReadStorage materialize.public.z // { arity: 2 }

Source materialize.public.z
  filter=(((#0{a}) IS NULL OR (#1{b}) IS NULL OR (#0{a} <= #1{b})))

Target cluster: quickstart

EOF

query II rowsort
SELECT *
FROM z
WHERE CASE WHEN a > b THEN FALSE ELSE TRUE END
----
NULL  NULL
NULL  2
1  NULL
1  2

# TODO: Reenable when database-issues#8807 is fixed
# query T
# SELECT CASE WHEN FALSE THEN 'short_string'::char(20) ELSE 'long_string_long_string'::char(30) END
# ----
# long_string_long_string