# 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 # TODO: The types supported by mod are SMALLINT, BIGINT, INTEGER, DECIMAL, and NUMERIC. # Tests all types? ### mod ### # positive dividend and divisor query I SELECT mod(34, 7) ---- 6 query I SELECT mod(35, 7) ---- 0 query I SELECT mod(4, 4) ---- 0 query I SELECT mod(7, 11) ---- 7 query R SELECT mod(4.3, 2.1) ---- 0.1 query R SELECT mod(4, 2.1) ---- 1.9 query R SELECT mod(0.34, 1.1) ---- 0.34 query R SELECT mod(4.2, 2.1) ---- 0 # negative dividend query I SELECT mod(-3234, 73) ---- -22 query I SELECT mod(-100, 10) ---- 0 query I SELECT mod(-5, 5) ---- 0 query I SELECT mod(-237, 1100) ---- -237 query R SELECT mod(-2.254, 2.2) ---- -0.054 query R SELECT mod(-4, 1.75) ---- -0.5 query R SELECT mod(-0.3234, 200) ---- -0.3234 query R SELECT mod(-7.5, 1.5) ---- 0 # negative divisor query I SELECT mod(23, -5) ---- 3 query I SELECT mod(20, -2) ---- 0 query I SELECT mod(29, -29) ---- 0 query I SELECT mod(7, -11) ---- 7 query R SELECT mod(8.5, -4.6) ---- 3.9 query R SELECT mod(30, -11.9) ---- 6.2 query R SELECT mod(0.0019, -0.013) ---- 0.0019 query R SELECT mod(14.4, -0.002) ---- 0 # negative dividend and negative divisor query I SELECT mod(-121, -17) ---- -2 query I SELECT mod(-64, -8) ---- 0 query I SELECT mod(-344, -344) ---- 0 query I SELECT mod(-13, -123) ---- -13 query R SELECT mod(-5.6, -2.3) ---- -1 query R SELECT mod(-10.4, -2) ---- -0.4 query R SELECT mod(-2.45, -45.6) ---- -2.45 query R SELECT mod(-12.15, -4.05) ---- 0 # special values: 0/NULL dividend and/or divisor query I SELECT mod(0, 4) ---- 0 query R SELECT mod(0.000, 2) ---- 0 query R SELECT mod(0, 1.23) ---- 0 query error division by zero SELECT mod(4, 0.0) query error division by zero SELECT mod(0, 0) query I SELECT mod(0, NULL) ---- NULL query I SELECT mod(NULL, 0) ---- NULL query I SELECT mod(-5, NULL) ---- NULL query I SELECT mod(NULL, 0.45) ---- NULL query error division by zero SELECT 1 % 0 query error division by zero SELECT 1 % 0.0 query error division by zero SELECT 1.0 % 0 query error division by zero SELECT 1.0 % 0.0 query error division by zero SELECT 1 % CAST (0.0 AS float) query error division by zero SELECT 1 / 0 query error division by zero SELECT 1 / 0.0 query error division by zero SELECT 1.0 / 0 query error division by zero SELECT 1.0 / 0.0 query error division by zero SELECT 1 / CAST (0.0 AS float) query I SELECT 1 + CAST ('5' AS double precision) ---- 6 query TT SELECT CAST ('+Inf' AS double precision), CAST ('inf' AS double precision) ---- inf inf query T SELECT CAST (CAST (1.1 AS double precision) AS text) ---- 1.1 query T SELECT CAST (CAST (1 as int) AS text) ---- 1 query TT SELECT FALSE::text, TRUE::text ---- false true query TTTTT SELECT 1::smallint::text, 1::bigint::text, 1.0::text, 1.0::float::text, 1.0::double::text ---- 1 1 1 1 1 query TTTTT SELECT (-1)::smallint::text, (-1)::bigint::text, (-1.0)::text, (-1.0)::float::text, (-1.0)::double::text ---- -1 -1 -1 -1 -1 query TTT SELECT 1.1::text, 1.1::float::text, 1.1::double::text ---- 1.1 1.1 1.1 query R SELECT round(CAST (1.5678 AS float)) ---- 2 query R SELECT round(CAST (-1.4678 AS float)) ---- -1 query R SELECT round(CAST (1.5678 AS double precision)) ---- 2 query R SELECT round(CAST (-1.4678 AS double precision)) ---- -1 statement ok CREATE TABLE nums ( n integer ) statement ok INSERT INTO nums VALUES (4) query R SELECT round(1.5678, CAST ((SELECT n FROM nums) AS integer)) ---- 1.5678 statement ok DELETE FROM nums query R SELECT floor(1.5678) ---- 1 query R SELECT round(1.5678, 3) ---- 1.568 query R SELECT round(1.5678, 2) ---- 1.57 query R SELECT round(1.5678, 1) ---- 1.6 query R SELECT round(1.5678, 0) ---- 2 query R SELECT round(1.5678, 7) ---- 1.5678 query R SELECT round(515, 0) ---- 515 query R SELECT round(515, 1) ---- 515 query R SELECT round(515, -1) ---- 520 query R SELECT round(515, -2) ---- 500 query RRR SELECT round(CAST (515 AS decimal), -3), round(515, -3), round(CAST (515 AS decimal(4, 0)), -3) ---- 1000 1000 1000 query R SELECT round(748.58, -4) ---- 0 query RR SELECT round(123.9994, 3), round(123.9995, 3) ---- 123.999 124 query RR SELECT round(123.4545, 2), round(123.45, -2) ---- 123.45 100 query R SELECT round(150.75, 0) ---- 151 query R SELECT round(NULL) ---- NULL # This needs an explicit cast while we develop the new decimal type in parallel. query R SELECT round(NULL::decimal, 2) ---- NULL query R SELECT round(1.567, NULL) ---- NULL # This needs an explicit cast while we develop the new decimal type in parallel. query R SELECT round(NULL::decimal, NULL) ---- NULL statement ok INSERT INTO nums VALUES (NULL) query R SELECT round((SELECT * FROM nums)) ---- NULL query R SELECT round((SELECT * FROM nums), 2) ---- NULL query R SELECT round(2, (SELECT * FROM nums)) ---- NULL query R SELECT round((SELECT * FROM nums), (SELECT * FROM nums)) ---- NULL statement ok DROP TABLE nums statement ok CREATE TABLE nums ( n float ) statement ok INSERT INTO nums VALUES (NULL) query R SELECT round((SELECT * FROM nums)); ---- NULL query error db error: ERROR: function round\(double precision, integer\) does not exist SELECT round((SELECT * FROM nums), 2) query error db error: ERROR: function round\(double precision, double precision\) does not exist SELECT round((SELECT * FROM nums), (SELECT * FROM nums)) query error db error: ERROR: function round\(numeric, double precision\) does not exist SELECT round(5.0, (SELECT * FROM nums)) query R SELECT round(5.0, CAST ((SELECT * FROM nums) AS integer)) ---- NULL query error db error: ERROR: function round\(double precision, integer\) does not exist SELECT round(CAST (5.0 AS double precision), 3) query error db error: ERROR: function round\(double precision, integer\) does not exist SELECT round(CAST (5.0 AS float), 3) query error db error: ERROR: function round\(boolean, integer\) does not exist SELECT round(true, 3) query error SELECT round(true) query error db error: ERROR: function round\(double precision, numeric\) does not exist SELECT round(CAST (5.0 AS float), 3.0) query error db error: ERROR: function round\(double precision, double precision\) does not exist SELECT round(CAST (5.0 AS float), CAST (3.0 AS float)) query R SELECT trunc(CAST (1.5678 AS float)) ---- 1 query R SELECT trunc(CAST (-1.4678 AS float)) ---- -1 query R SELECT trunc(CAST (1.5678 AS double precision)) ---- 1 query R SELECT trunc(CAST (-1.4678 AS double precision)) ---- -1 query R SELECT trunc(1.5678) ---- 1 query R SELECT trunc(-1.5678) ---- -1 query RRRRR SELECT trunc(1.1), trunc(1.111), trunc(100.1), trunc(100.11), trunc(-4.1) ---- 1 1 100 100 -4 # postgres converts ints to floats on trunc query R SELECT trunc(1) ---- 1 query R SELECT trunc(NULL) ---- NULL query R SELECT trunc(NULL::decimal) ---- NULL query R SELECT trunc((SELECT * FROM nums)) ---- NULL query RRRR SELECT trunc('NaN'::decimal), trunc('Inf'::float), trunc(0), trunc(-0) ---- NaN inf 0 0 statement ok DELETE FROM nums statement ok INSERT INTO nums VALUES (1.5678) query R SELECT trunc(1.5678) ---- 1 statement ok DROP TABLE nums query error db error: ERROR: function trunc\(boolean\) does not exist SELECT trunc(true) query I SELECT 2147483646+1 ---- 2147483647 query error numeric field overflow SELECT 2147483647+1 statement error numeric field overflow SELECT 1073741824*2 query I SELECT 9223372036854775806::bigint+1::bigint ---- 9223372036854775807 query error numeric field overflow SELECT 9223372036854775807::bigint+1::bigint query error numeric field overflow SELECT 9223372036854775807::bigint-(-1)::bigint query error numeric field overflow SELECT (-6734743351254754)::bigint * (-99783359317598)::bigint query error numeric field overflow SELECT (CAST(-6734743351254754 AS bigint) * CAST(-99783359317598 AS bigint)) ### overflow and underflow checking ### statement ok SELECT '1e300'::float query error value out of range: overflow SELECT '1e300'::float * '1e200'::float query R SELECT 'Inf'::float * '1e200'::float ---- inf query R SELECT '1e200'::float * '-Inf'::float ---- -inf query error value out of range: underflow SELECT '1e-300'::float * '1e-200'::float query R SELECT '0.0'::float * '1e-200'::float ---- 0 query R SELECT '1e-300'::float * '0.0'::float ---- 0 query error value out of range: overflow SELECT '1e308'::float + '1e308'::float query R SELECT 'Inf'::float + '1e308'::float ---- inf query R SELECT '1e308'::float + '-Inf'::float ---- -inf query error value out of range: overflow SELECT '-1e308'::float - '1e308'::float query R SELECT 'Inf'::float - '1e308'::float ---- inf query R SELECT '1e308'::float - '-Inf'::float ---- inf query error division by zero SELECT '1e-300'::float / '0.0'::float query error value out of range: overflow SELECT '1e300'::float / '1e-30'::float query R SELECT 'Inf'::float / '1e-30'::float ---- inf query error value out of range: underflow SELECT '1e-300'::float / '1e30'::float query R SELECT '1e-300'::float / 'Inf'::float ---- 0 query R SELECT '0.0'::float / 'Inf'::float ---- 0 statement ok SELECT '3e38'::float4 query error value out of range: overflow SELECT '1e30'::float4 * '1e10'::float4 query R SELECT 'Inf'::float4 * '1e10'::float4 ---- inf query R SELECT '1e30'::float4 * '-Inf'::float4 ---- -inf query error value out of range: underflow SELECT '1e-30'::float4 * '1e-20'::float4 query R SELECT '0.0'::float4 * '1e-20'::float4 ---- 0 query R SELECT '1e-30'::float4 * '0.0'::float4 ---- 0 query error value out of range: overflow SELECT '3e38'::float4 + '3e38'::float4 query R SELECT 'Inf'::float4 + '3e38'::float4 ---- inf query R SELECT '3e38'::float4 + '-Inf'::float4 ---- -inf query error value out of range: overflow SELECT '-3e38'::float4 - '3e38'::float4 query R SELECT 'Inf'::float4 - '3e38'::float4 ---- inf query R SELECT '3e38'::float4 - '-Inf'::float4 ---- inf query error division by zero SELECT '1e-30'::float4 / '0.0'::float4 query error value out of range: overflow SELECT '1e30'::float4 / '1e-20'::float4 query R SELECT 'Inf'::float4 / '1e-20'::float4 ---- inf query error value out of range: underflow SELECT '1e-30'::float4 / '1e20'::float4 query R SELECT '1e-30'::float4 / 'Inf'::float4 ---- 0 query R SELECT '0.0'::float4 / 'Inf'::float4 ---- 0 ### bitwise operators ### # dummy table and data needed for tests statement ok DROP TABLE IF EXISTS nums statement ok CREATE TABLE nums ( x1 int2, x2 int2, x3 int2, y1 int4, y2 int4, y3 int4, z1 int8, z2 int8, z3 int8 ) statement ok INSERT INTO nums VALUES (11, 12, 13, 21, 22, 23, 31, 32, 33) # negation query IIIII SELECT ~0, ~1, ~65535, ~2147483647, ~9223372036854775807 ---- -1 -2 -65536 -2147483648 -9223372036854775808 # bitwise and query IIIII SELECT 3 & 4, 7 & 4, 7 & 14, 1342579021 & 1035032143, 2309456347094635094 & 4321056356463245221 ---- 0 4 6 268435533 2307057198754714116 # bitwise or query IIIII SELECT 3 | 4, 7 | 4, 7 | 14, 1342579021 | 1035032143, 2309456347094635094 | 4321056356463245221 ---- 7 7 15 2109175631 4323455504803166199 # bitwise xor query IIIII SELECT 3 # 4, 7 # 4, 7 # 14, 1342579021 # 1035032143, 2309456347094635094 # 4321056356463245221 ---- 7 3 9 1840740098 2016398306048452083 # bitwise left shift query IIII SELECT no_bits, 1::int2 << no_bits as r2, 1::int4 << no_bits as r4, 1::int8 << no_bits as r8 FROM (VALUES (-65), (-64), (-63), (-49), (-48), (-47), (-33), (-32), (-31), (-17), (-16), (-15), (-1), ( 0 ), ( +1), (+15), (+16), (+17), (+31), (+32), (+33), (+47), (+48), (+49), (+63), (+64), (+65) ) as params(no_bits) ORDER by no_bits ---- -65 0 -2147483648 -9223372036854775808 -64 1 1 1 -63 2 2 2 -49 -32768 32768 32768 -48 0 65536 65536 -47 0 131072 131072 -33 0 -2147483648 2147483648 -32 1 1 4294967296 -31 2 2 8589934592 -17 -32768 32768 140737488355328 -16 0 65536 281474976710656 -15 0 131072 562949953421312 -1 0 -2147483648 -9223372036854775808 0 1 1 1 1 2 2 2 15 -32768 32768 32768 16 0 65536 65536 17 0 131072 131072 31 0 -2147483648 2147483648 32 1 1 4294967296 33 2 2 8589934592 47 -32768 32768 140737488355328 48 0 65536 281474976710656 49 0 131072 562949953421312 63 0 -2147483648 -9223372036854775808 64 1 1 1 65 2 2 2 # bitwise right shift query IIII SELECT no_bits, -32767::int2 >> no_bits as r2, -2147483647::int4 >> no_bits as r4, -9223372036854775807::int8 >> no_bits as r8 FROM (VALUES (-65), (-64), (-63), (-49), (-48), (-47), (-33), (-32), (-31), (-17), (-16), (-15), (-1), ( 0 ), ( +1), (+15), (+16), (+17), (+31), (+32), (+33), (+47), (+48), (+49), (+63), (+64), (+65) ) as params(no_bits) ORDER by no_bits ---- -65 -1 -1 -1 -64 -32767 -2147483647 -9223372036854775807 -63 -16384 -1073741824 -4611686018427387904 -49 -1 -65536 -281474976710656 -48 -1 -32768 -140737488355328 -47 -1 -16384 -70368744177664 -33 -1 -1 -4294967296 -32 -32767 -2147483647 -2147483648 -31 -16384 -1073741824 -1073741824 -17 -1 -65536 -65536 -16 -1 -32768 -32768 -15 -1 -16384 -16384 -1 -1 -1 -1 0 -32767 -2147483647 -9223372036854775807 1 -16384 -1073741824 -4611686018427387904 15 -1 -65536 -281474976710656 16 -1 -32768 -140737488355328 17 -1 -16384 -70368744177664 31 -1 -1 -4294967296 32 -32767 -2147483647 -2147483648 33 -16384 -1073741824 -1073741824 47 -1 -65536 -65536 48 -1 -32768 -32768 49 -1 -16384 -16384 63 -1 -1 -1 64 -32767 -2147483647 -9223372036854775807 65 -16384 -1073741824 -4611686018427387904 # precedence between bitwise operators query III SELECT 5 >> 1 << 3 as implicit_l, (5 >> 1) << 3 as explicit_l, 5 >> (1 << 3) as explicit_r ---- 16 16 0 query III SELECT 5 << 1 >> 3 as implicit_l, (5 << 1) >> 3 as explicit_l, 5 << (1 >> 3) as explicit_r ---- 1 1 5 query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT x1 >> x2 << x3 as r1, y1 << y2 >> y3 as r2 FROM nums ---- Explained Query: Project (#9, #10) // { arity: 2 } Map (((#0{x1} >> smallint_to_integer(#1{x2})) << smallint_to_integer(#2{x3})), ((#3{y1} << #4{y2}) >> #5{y3})) // { arity: 11 } ReadStorage materialize.public.nums // { arity: 9 } Source materialize.public.nums Target cluster: quickstart EOF # precedence between bitwise operators and '&' query III SELECT 5 >> 1 & 12 as implicit_l, (5 >> 1) & 12 as explicit_l, 5 >> (1 & 12) as explicit_r ---- 0 0 5 query III SELECT 5 << 1 & 12 as implicit_l, (5 << 1) & 12 as explicit_l, 5 << (1 & 12) as explicit_r ---- 8 8 5 query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT x1 >> x2 & x3 as r1, y1 << y2 & y3 as r2 FROM nums ---- Explained Query: Project (#9, #10) // { arity: 2 } Map (((#0{x1} >> smallint_to_integer(#1{x2})) & #2{x3}), ((#3{y1} << #4{y2}) & #5{y3})) // { arity: 11 } ReadStorage materialize.public.nums // { arity: 9 } Source materialize.public.nums Target cluster: quickstart EOF # precedence between '&' and '|' query III SELECT 0 & 0 | 1 as implicit_l, (0 & 0) | 1 as explicit_l, 0 & (0 | 1) as explicit_r ---- 1 1 0 query III SELECT 1 | 0 & 0 as implicit_l, (1 | 0) & 0 as explicit_l, 1 | (0 & 0) as explicit_r ---- 0 0 1 query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT x1 & x2 | x3 as r1, y1 & y2 | y3 as r2, z1 & z2 | z3 as r3, x1 & y2 | z3 as r4 FROM nums ---- Explained Query: Project (#9..=#12) // { arity: 4 } Map (((#0{x1} & #1{x2}) | #2{x3}), ((#3{y1} & #4{y2}) | #5{y3}), ((#6{z1} & #7{z2}) | #8{z3}), (integer_to_bigint((smallint_to_integer(#0{x1}) & #4{y2})) | #8{z3})) // { arity: 13 } ReadStorage materialize.public.nums // { arity: 9 } Source materialize.public.nums Target cluster: quickstart EOF # precedence between '&' and '#' query III SELECT 0 & 0 # 1 as implicit_l, (0 & 0) # 1 as explicit_l, 0 & (0 # 1) as explicit_r ---- 1 1 0 query III SELECT 1 # 0 & 0 as implicit_l, (1 # 0) & 0 as explicit_l, 1 # (0 & 0) as explicit_r ---- 0 0 1 query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT x1 # x2 & x3 as r1, y1 # y2 & y3 as r2, z1 # z2 & z3 as r3, x1 # y2 & z3 as r4 FROM nums ---- Explained Query: Project (#9..=#12) // { arity: 4 } Map (((#0{x1} # #1{x2}) & #2{x3}), ((#3{y1} # #4{y2}) & #5{y3}), ((#6{z1} # #7{z2}) & #8{z3}), (integer_to_bigint((smallint_to_integer(#0{x1}) # #4{y2})) & #8{z3})) // { arity: 13 } ReadStorage materialize.public.nums // { arity: 9 } Source materialize.public.nums Target cluster: quickstart EOF # precedence between '|' and '#' query III SELECT 1 | 0 # 1 as implicit_l, (1 | 0) # 1 as explicit_l, 1 | (0 # 1) as explicit_r ---- 0 0 1 query III SELECT 1 # 0 | 1 as implicit_l, (1 # 0) | 1 as explicit_l, 1 # (0 | 1) as explicit_r ---- 1 1 0 query T multiline EXPLAIN OPTIMIZED PLAN WITH(humanized expressions, arity, join implementations) AS VERBOSE TEXT FOR SELECT x1 # x2 | x3 as r1, y1 # y2 | y3 as r2, z1 # z2 | z3 as r3, x1 # y2 | z3 as r4 FROM nums ---- Explained Query: Project (#9..=#12) // { arity: 4 } Map (((#0{x1} # #1{x2}) | #2{x3}), ((#3{y1} # #4{y2}) | #5{y3}), ((#6{z1} # #7{z2}) | #8{z3}), (integer_to_bigint((smallint_to_integer(#0{x1}) # #4{y2})) | #8{z3})) // { arity: 13 } ReadStorage materialize.public.nums // { arity: 9 } Source materialize.public.nums Target cluster: quickstart EOF # precedence between '~' and ("|", "&") query IIIIII SELECT ~1 & 0 as def_and, ~(1 & 0) as l_prec_and, (~1) & 0 as h_prec_and, ~0 | 1 as def_or , ~(0 | 1) as l_prec_or , (~0) | 1 as h_prec_or ---- 0 -1 0 -1 -2 -1 # precedence between '~' and ('+', '-') query IIIIII SELECT ~1 + 1 as def_add, ~(1 + 1) as l_prec_add, (~1) + 1 as h_prec_add, ~1 - 2 as def_sub, ~(1 - 2) as l_prec_sub, (~1) - 2 as h_prec_sub ---- -3 -3 -1 0 0 -4 # overflow for negating minimum integers query error "-32768" smallint out of range SELECT - '-32768'::int2 query error "-32768" smallint out of range SELECT ABS('-32768'::int2) query error "-2147483648" integer out of range SELECT - '-2147483648'::int4 query error "-2147483648" integer out of range SELECT ABS('-2147483648'::int4) query error "-9223372036854775808" bigint out of range SELECT - '-9223372036854775808'::int8 query error "-9223372036854775808" bigint out of range SELECT ABS('-9223372036854775808'::int8) query error "-32768 / -1" smallint out of range SELECT '-32768'::int2 / '-1'::int2 query error "-2147483648 / -1" integer out of range SELECT '-2147483648'::int4 / '-1'::int4 query error "-9223372036854775808 / -1" bigint out of range SELECT '-9223372036854775808'::int8 / '-1'::int8 query I SELECT '-32768'::int2 % '-1'::int2 ---- 0 query I SELECT '-2147483648'::int4 % '-1'::int4 ---- 0 query I SELECT '-9223372036854775808'::int8 % '-1'::int8 ---- 0