# 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