This is an automated email from the ASF dual-hosted git repository.
xiong pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new acf4209e0d Add cast.iq, a Quidem test for CAST
acf4209e0d is described below
commit acf4209e0d53f17818c17735235d8762a2d7adb4
Author: Xiong Duan <[email protected]>
AuthorDate: Fri Sep 6 06:43:54 2024 +0800
Add cast.iq, a Quidem test for CAST
---
core/src/test/resources/sql/cast.iq | 1237 +++++++++++++++++++++++++++++++++++
core/src/test/resources/sql/misc.iq | 1094 -------------------------------
2 files changed, 1237 insertions(+), 1094 deletions(-)
diff --git a/core/src/test/resources/sql/cast.iq
b/core/src/test/resources/sql/cast.iq
new file mode 100644
index 0000000000..b615bc7aff
--- /dev/null
+++ b/core/src/test/resources/sql/cast.iq
@@ -0,0 +1,1237 @@
+# cast.iq - Tests for CAST
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to you under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+!use scott
+!set outputformat mysql
+
+# Cast a character literal to a timestamp; note: the plan does not contain CAST
+values cast('1969-07-21 12:34:56' as timestamp);
++---------------------+
+| EXPR$0 |
++---------------------+
+| 1969-07-21 12:34:56 |
++---------------------+
+(1 row)
+
+!ok
+EnumerableValues(tuples=[[{ 1969-07-21 12:34:56 }]])
+!plan
+
+# Cast a character literal without time to a timestamp; note: the plan does
not contain CAST
+values cast('1969-07-21' as timestamp);
++---------------------+
+| EXPR$0 |
++---------------------+
+| 1969-07-21 00:00:00 |
++---------------------+
+(1 row)
+
+!ok
+EnumerableValues(tuples=[[{ 1969-07-21 00:00:00 }]])
+!plan
+
+# Cast a character literal to a date; note: the plan does not contain CAST
+values cast('1969-07-21' as date);
++------------+
+| EXPR$0 |
++------------+
+| 1969-07-21 |
++------------+
+(1 row)
+
+!ok
+EnumerableValues(tuples=[[{ 1969-07-21 }]])
+!plan
+
+# Slightly different format
+# (Incidentally, this format is not allowed in date literals, per the standard)
+values cast('1989-7-4' as date);
++------------+
+| EXPR$0 |
++------------+
+| 1989-07-04 |
++------------+
+(1 row)
+
+!ok
+
+# Cast a character literal to an integer; note: the plan does not contain CAST
+values cast('196907' as integer);
++--------+
+| EXPR$0 |
++--------+
+| 196907 |
++--------+
+(1 row)
+
+!ok
+EnumerableValues(tuples=[[{ 196907 }]])
+!plan
+
+# Cast an integer literal to a bigint; note: the plan does not contain CAST
+values cast(123 as bigint);
++--------+
+| EXPR$0 |
++--------+
+| 123 |
++--------+
+(1 row)
+
+!ok
+EnumerableValues(tuples=[[{ 123 }]])
+!plan
+
+# Cast an integer literal to a decimal; note: the plan does not contain CAST
+values cast('123.45' as decimal(5, 2));
++--------+
+| EXPR$0 |
++--------+
+| 123.45 |
++--------+
+(1 row)
+
+!ok
+EnumerableValues(tuples=[[{ 123.45 }]])
+!plan
+
+# Cast a character literal to a decimal; note: the plan does not contain CAST
+values cast('123.45' as decimal(5, 2));
++--------+
+| EXPR$0 |
++--------+
+| 123.45 |
++--------+
+(1 row)
+
+!ok
+EnumerableValues(tuples=[[{ 123.45 }]])
+!plan
+
+# Cast a character literal to a double; note: the plan does not contain CAST
+values cast('-123.45' as double);
++---------+
+| EXPR$0 |
++---------+
+| -123.45 |
++---------+
+(1 row)
+
+!ok
+EnumerableValues(tuples=[[{ -1.2345E2 }]])
+!plan
+
+values cast('false' as boolean);
++--------+
+| EXPR$0 |
++--------+
+| false |
++--------+
+(1 row)
+
+!ok
+EnumerableValues(tuples=[[{ false }]])
+!plan
+
+values cast('TRUE' as boolean);
++--------+
+| EXPR$0 |
++--------+
+| true |
++--------+
+(1 row)
+
+!ok
+EnumerableValues(tuples=[[{ true }]])
+!plan
+
+values cast('TR' || 'UE' as boolean);
++--------+
+| EXPR$0 |
++--------+
+| true |
++--------+
+(1 row)
+
+!ok
+EnumerableCalc(expr#0=[{inputs}], expr#1=['TR'], expr#2=['UE'],
expr#3=[||($t1, $t2)], expr#4=[CAST($t3):BOOLEAN NOT NULL], EXPR$0=[$t4])
+ EnumerableValues(tuples=[[{ 0 }]])
+!plan
+
+!if (fixed.calcite2539) {
+
+# In the following, that we get an error at run time,
+# and that the plan shows that the expression has not been reduced.
+values cast('null' as boolean);
+Invalid character for cast
+!error
+EnumerableCalc(expr#0=[{inputs}], expr#1=['null'], expr#2=[CAST($t1):BOOLEAN
NOT NULL], EXPR$0=[$t2])
+ EnumerableValues(tuples=[[{ 0 }]])
+!plan
+
+# The following throw give an error (good!)
+# but throw java.lang.ExceptionInInitializerError (not great).
+values cast('' as date);
+Caused by: java.lang.NumberFormatException: For input string: ""
+!error
+
+values cast('' as timestamp);
+Caused by: java.lang.NumberFormatException: For input string: ""
+!error
+
+values cast('' as integer);
+Caused by: java.lang.NumberFormatException: For input string: ""
+!error
+
+values cast('' as boolean);
+Caused by: java.lang.RuntimeException: Invalid character for cast
+!error
+
+values cast('' as double);
+Caused by: java.lang.NumberFormatException: empty String
+!error
+
+# Postgres fails:
+# ERROR: invalid input syntax for integer: "1.56"
+values cast('15.6' as integer);
+Caused by: java.lang.NumberFormatException: For input string: "15.6"
+!error
+
+# Postgres fails:
+# ERROR: invalid input syntax for integer: " - 5 "
+values cast(' - 5 ' as double);
+Caused by: java.lang.NumberFormatException: For input string: "- 5"
+!error
+
+# Out of TINYINT range (max 127)
+values cast('200' as tinyint);
+Caused by: java.lang.NumberFormatException: Value out of range. Value:"200"
Radix:10
+!error
+
+# Out of SMALLINT range (max 32767)
+values cast('50000' as smallint);
+Caused by: java.lang.NumberFormatException: Value out of range. Value:"50000"
Radix:10
+!error
+
+# Out of INTEGER range (max 2.1e9)
+values cast('4567891234' as integer);
+Caused by: java.lang.NumberFormatException: For input string: "4567891234"
+!error
+
+# Out of BIGINT range (max 9.2e18)
+values cast('12345678901234567890' as bigint);
+Caused by: java.lang.NumberFormatException: For input string:
"12345678901234567890"
+!error
+!}
+
+# Out of REAL range
+# (Should give an error, not infinity.)
+values cast('12.34e56' as real);
++----------+
+| EXPR$0 |
++----------+
+| Infinity |
++----------+
+(1 row)
+
+!ok
+
+# Out of FLOAT range
+# (Should give an error, not infinity.)
+values cast('12.34e5678' as float);
++----------+
+| EXPR$0 |
++----------+
+| Infinity |
++----------+
+(1 row)
+
+!ok
+
+# Out of DOUBLE range
+# (Should give an error, not infinity.)
+values cast('12.34e5678' as double);
++----------+
+| EXPR$0 |
++----------+
+| Infinity |
++----------+
+(1 row)
+
+!ok
+
+# Postgres succeeds
+values cast(' -5 ' as double);
++--------+
+| EXPR$0 |
++--------+
+| -5.0 |
++--------+
+(1 row)
+
+!ok
+
+# [CALCITE-4838] Add RoundingMode in RelDataTypeSystem to specify the rounding
behavior
+!use scott-rounding-half-up
+
+# Test cast approximate numeric to int when rounding mode is HALF-UP
+select cast(5.5 as int),
+ cast(3.5 as int),
+ cast(1.6 as int),
+ cast(1.1 as int),
+ cast(1.0 as int),
+ cast(-1.0 as int),
+ cast(-1.1 as int),
+ cast(-1.6 as int),
+ cast(-2.5 as int),
+ cast(15.5 as int)
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 6 | 4 | 2 | 1 | 1 | -1 | -1 | -2 |
-3 | 16 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast approximate numeric to tinyint when rounding mode is HALF-UP
+select cast(5.5 as tinyint),
+ cast(3.5 as tinyint),
+ cast(1.6 as tinyint),
+ cast(1.1 as tinyint),
+ cast(1.0 as tinyint),
+ cast(-1.0 as tinyint),
+ cast(-1.1 as tinyint),
+ cast(-1.6 as tinyint),
+ cast(-2.5 as tinyint),
+ cast(15.5 as tinyint)
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 6 | 4 | 2 | 1 | 1 | -1 | -1 | -2 |
-3 | 16 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast approximate numeric to smallint when rounding mode is HALF-UP
+select cast(5.5 as smallint),
+ cast(3.5 as smallint),
+ cast(1.6 as smallint),
+ cast(1.1 as smallint),
+ cast(1.0 as smallint),
+ cast(-1.0 as smallint),
+ cast(-1.1 as smallint),
+ cast(-1.6 as smallint),
+ cast(-2.5 as smallint),
+ cast(15.5 as smallint)
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 6 | 4 | 2 | 1 | 1 | -1 | -1 | -2 |
-3 | 16 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast approximate numeric to bigint when rounding mode is HALF-UP
+select cast(5.5 as bigint),
+ cast(3.5 as bigint),
+ cast(1.6 as bigint),
+ cast(1.1 as bigint),
+ cast(1.0 as bigint),
+ cast(-1.0 as bigint),
+ cast(-1.1 as bigint),
+ cast(-1.6 as bigint),
+ cast(-2.5 as bigint),
+ cast(15.5 as bigint)
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 6 | 4 | 2 | 1 | 1 | -1 | -1 | -2 |
-3 | 16 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast decimal to decimal when rounding mode is HALF-UP
+select cast(cast(5.55 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(3.55 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(1.66 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(1.11 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(1.00 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(-1.00 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(-1.11 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(-1.66 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(-2.55 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(15.55 as decimal(4,2)) as decimal(3,1))
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 5.6 | 3.6 | 1.7 | 1.1 | 1.0 | -1.0 | -1.1 | -1.7 |
-2.6 | 15.6 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast varchar to decimal when rounding mode is HALF-UP
+select cast('5.5' as decimal(2, 0)),
+ cast('3.5' as decimal(2, 0)),
+ cast('1.6' as decimal(2, 0)),
+ cast('1.1' as decimal(2, 0)),
+ cast('1.0' as decimal(2, 0)),
+ cast('-1.0' as decimal(2, 0)),
+ cast('-1.1' as decimal(2, 0)),
+ cast('-1.6' as decimal(2, 0)),
+ cast('-2.5' as decimal(2, 0)),
+ cast('15.5' as decimal(2, 0))
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 6 | 4 | 2 | 1 | 1 | -1 | -1 | -2 |
-3 | 16 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast float to decimal when rounding mode is HALF-UP
+select cast(cast(5.5 as float) as decimal(2,0)),
+ cast(cast(3.5 as float) as decimal(2,0)),
+ cast(cast(1.6 as float) as decimal(2,0)),
+ cast(cast(1.1 as float) as decimal(2,0)),
+ cast(cast(1.0 as float) as decimal(2,0)),
+ cast(cast(-1.0 as float) as decimal(2,0)),
+ cast(cast(-1.1 as float) as decimal(2,0)),
+ cast(cast(-1.6 as float) as decimal(2,0)),
+ cast(cast(-2.5 as float) as decimal(2,0)),
+ cast(cast(15.5 as float) as decimal(2,0))
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 6 | 4 | 2 | 1 | 1 | -1 | -1 | -2 |
-3 | 16 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast float to decimal but rounding behavior happens in scala when
rounding mode is HALF-UP
+select cast(cast(5.55 as float) as decimal(2,1)),
+ cast(cast(3.55 as float) as decimal(2,1)),
+ cast(cast(1.66 as float) as decimal(2,1)),
+ cast(cast(1.11 as float) as decimal(2,1)),
+ cast(cast(1.00 as float) as decimal(2,1)),
+ cast(cast(-1.00 as float) as decimal(2,1)),
+ cast(cast(-1.11 as float) as decimal(2,1)),
+ cast(cast(-1.66 as float) as decimal(2,1)),
+ cast(cast(-2.55 as float) as decimal(2,1)),
+ cast(cast(15.55 as float) as decimal(3,1))
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 5.6 | 3.6 | 1.7 | 1.1 | 1.0 | -1.0 | -1.1 | -1.7 |
-2.6 | 15.6 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast double to decimal when rounding mode is HALF-UP
+select cast(cast(5.5 as double) as decimal(2,0)),
+ cast(cast(3.5 as double) as decimal(2,0)),
+ cast(cast(1.6 as double) as decimal(2,0)),
+ cast(cast(1.1 as double) as decimal(2,0)),
+ cast(cast(1.0 as double) as decimal(2,0)),
+ cast(cast(-1.0 as double) as decimal(2,0)),
+ cast(cast(-1.1 as double) as decimal(2,0)),
+ cast(cast(-1.6 as double) as decimal(2,0)),
+ cast(cast(-2.5 as double) as decimal(2,0)),
+ cast(cast(15.5 as double) as decimal(2,0))
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 6 | 4 | 2 | 1 | 1 | -1 | -1 | -2 |
-3 | 16 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast double to decimal but rounding behavior happens in scala when
rounding mode is HALF-UP
+select cast(cast(5.55 as double) as decimal(2,1)),
+ cast(cast(3.55 as double) as decimal(2,1)),
+ cast(cast(1.66 as double) as decimal(2,1)),
+ cast(cast(1.11 as double) as decimal(2,1)),
+ cast(cast(1.00 as double) as decimal(2,1)),
+ cast(cast(-1.00 as double) as decimal(2,1)),
+ cast(cast(-1.11 as double) as decimal(2,1)),
+ cast(cast(-1.66 as double) as decimal(2,1)),
+ cast(cast(-2.55 as double) as decimal(2,1)),
+ cast(cast(15.55 as double) as decimal(3,1))
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 5.6 | 3.6 | 1.7 | 1.1 | 1.0 | -1.0 | -1.1 | -1.7 |
-2.6 | 15.6 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+
+!use scott
+
+# Test cast approximate numeric to int when rounding mode is DOWN
+select cast(5.5 as int),
+ cast(3.5 as int),
+ cast(1.6 as int),
+ cast(1.1 as int),
+ cast(1.0 as int),
+ cast(-1.0 as int),
+ cast(-1.1 as int),
+ cast(-1.6 as int),
+ cast(-2.5 as int),
+ cast(15.5 as int)
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 5 | 3 | 1 | 1 | 1 | -1 | -1 | -1 |
-2 | 15 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast approximate numeric to tinyint when rounding mode is DOWN
+select cast(5.5 as tinyint),
+ cast(3.5 as tinyint),
+ cast(1.6 as tinyint),
+ cast(1.1 as tinyint),
+ cast(1.0 as tinyint),
+ cast(-1.0 as tinyint),
+ cast(-1.1 as tinyint),
+ cast(-1.6 as tinyint),
+ cast(-2.5 as tinyint),
+ cast(15.5 as tinyint)
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 5 | 3 | 1 | 1 | 1 | -1 | -1 | -1 |
-2 | 15 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast approximate numeric to smallint when rounding mode is DOWN
+select cast(5.5 as smallint),
+ cast(3.5 as smallint),
+ cast(1.6 as smallint),
+ cast(1.1 as smallint),
+ cast(1.0 as smallint),
+ cast(-1.0 as smallint),
+ cast(-1.1 as smallint),
+ cast(-1.6 as smallint),
+ cast(-2.5 as smallint),
+ cast(15.5 as smallint)
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 5 | 3 | 1 | 1 | 1 | -1 | -1 | -1 |
-2 | 15 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast approximate numeric to bigint when rounding mode is DOWN
+select cast(5.5 as bigint),
+ cast(3.5 as bigint),
+ cast(1.6 as bigint),
+ cast(1.1 as bigint),
+ cast(1.0 as bigint),
+ cast(-1.0 as bigint),
+ cast(-1.1 as bigint),
+ cast(-1.6 as bigint),
+ cast(-2.5 as bigint),
+ cast(15.5 as bigint)
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 5 | 3 | 1 | 1 | 1 | -1 | -1 | -1 |
-2 | 15 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast decimal to decimal when rounding mode is DOWN
+select cast(cast(5.55 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(3.55 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(1.66 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(1.11 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(1.00 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(-1.00 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(-1.11 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(-1.66 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(-2.55 as decimal(3,2)) as decimal(2,1)),
+ cast(cast(15.55 as decimal(4,2)) as decimal(3,1))
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 5.5 | 3.5 | 1.6 | 1.1 | 1.0 | -1.0 | -1.1 | -1.6 |
-2.5 | 15.5 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast varchar to decimal when rounding mode is DOWN
+select cast('5.5' as decimal(2, 0)),
+ cast('3.5' as decimal(2, 0)),
+ cast('1.6' as decimal(2, 0)),
+ cast('1.1' as decimal(2, 0)),
+ cast('1.0' as decimal(2, 0)),
+ cast('-1.0' as decimal(2, 0)),
+ cast('-1.1' as decimal(2, 0)),
+ cast('-1.6' as decimal(2, 0)),
+ cast('-2.5' as decimal(2, 0)),
+ cast('15.5' as decimal(2, 0))
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 5 | 3 | 1 | 1 | 1 | -1 | -1 | -1 |
-2 | 15 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast float to decimal when rounding mode is DOWN
+select cast(cast(5.5 as float) as decimal(2,0)),
+ cast(cast(3.5 as float) as decimal(2,0)),
+ cast(cast(1.6 as float) as decimal(2,0)),
+ cast(cast(1.1 as float) as decimal(2,0)),
+ cast(cast(1.0 as float) as decimal(2,0)),
+ cast(cast(-1.0 as float) as decimal(2,0)),
+ cast(cast(-1.1 as float) as decimal(2,0)),
+ cast(cast(-1.6 as float) as decimal(2,0)),
+ cast(cast(-2.5 as float) as decimal(2,0)),
+ cast(cast(15.5 as float) as decimal(2,0))
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 5 | 3 | 1 | 1 | 1 | -1 | -1 | -1 |
-2 | 15 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast float to decimal but rounding behavior happens in scala when
rounding mode is DOWN
+select cast(cast(5.55 as float) as decimal(2,1)),
+ cast(cast(3.55 as float) as decimal(2,1)),
+ cast(cast(1.66 as float) as decimal(2,1)),
+ cast(cast(1.11 as float) as decimal(2,1)),
+ cast(cast(1.00 as float) as decimal(2,1)),
+ cast(cast(-1.00 as float) as decimal(2,1)),
+ cast(cast(-1.11 as float) as decimal(2,1)),
+ cast(cast(-1.66 as float) as decimal(2,1)),
+ cast(cast(-2.55 as float) as decimal(2,1)),
+ cast(cast(15.55 as float) as decimal(3,1))
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 5.5 | 3.5 | 1.6 | 1.1 | 1.0 | -1.0 | -1.1 | -1.6 |
-2.5 | 15.5 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast double to decimal when rounding mode is DOWN
+select cast(cast(5.5 as double) as decimal(2,0)),
+ cast(cast(3.5 as double) as decimal(2,0)),
+ cast(cast(1.6 as double) as decimal(2,0)),
+ cast(cast(1.1 as double) as decimal(2,0)),
+ cast(cast(1.0 as double) as decimal(2,0)),
+ cast(cast(-1.0 as double) as decimal(2,0)),
+ cast(cast(-1.1 as double) as decimal(2,0)),
+ cast(cast(-1.6 as double) as decimal(2,0)),
+ cast(cast(-2.5 as double) as decimal(2,0)),
+ cast(cast(15.5 as double) as decimal(2,0))
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 5 | 3 | 1 | 1 | 1 | -1 | -1 | -1 |
-2 | 15 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# Test cast double to decimal but rounding behavior happens in scala when
rounding mode is DOWN
+select cast(cast(5.55 as double) as decimal(2,1)),
+ cast(cast(3.55 as double) as decimal(2,1)),
+ cast(cast(1.66 as double) as decimal(2,1)),
+ cast(cast(1.11 as double) as decimal(2,1)),
+ cast(cast(1.00 as double) as decimal(2,1)),
+ cast(cast(-1.00 as double) as decimal(2,1)),
+ cast(cast(-1.11 as double) as decimal(2,1)),
+ cast(cast(-1.66 as double) as decimal(2,1)),
+ cast(cast(-2.55 as double) as decimal(2,1)),
+ cast(cast(15.55 as double) as decimal(3,1))
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+| 5.5 | 3.5 | 1.6 | 1.1 | 1.0 | -1.0 | -1.1 | -1.6 |
-2.5 | 15.5 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# [CALCITE-4806] Lossy CAST is incorrectly simplified
+
+!use scott-rounding-half-up
+
+# rounding mode is HALF-UP
+select cast(5.5 as int) = 5 as value1,
+ cast(5.5 as int) = 6 as value2,
+ cast(3.5 as int) = 3 as value3,
+ cast(3.5 as int) = 4 as value4,
+ cast(1.6 as int) = 1 as value5,
+ cast(1.6 as int) = 2 as value6,
+ cast(1.1 as int) = 1 as value7,
+ cast(1.1 as int) = 2 as value8,
+ cast(1.0 as int) = 1 as value9,
+ cast(1.0 as int) = 2 as value10
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
+| VALUE1 | VALUE2 | VALUE3 | VALUE4 | VALUE5 | VALUE6 | VALUE7 | VALUE8 |
VALUE9 | VALUE10 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
+| false | true | false | true | false | true | true | false | true
| false |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
+(1 row)
+
+!ok
+
+EnumerableValues(tuples=[[{ false, true, false, true, false, true, true,
false, true, false }]])
+!plan
+
+# same as before, but is negative number
+select cast(-1.0 as int) = -1 as value1,
+ cast(-1.0 as int) = -2 as value2,
+ cast(-1.1 as int) = -1 as value3,
+ cast(-1.1 as int) = -2 as value4,
+ cast(-1.6 as int) = -1 as value5,
+ cast(-1.6 as int) = -2 as value6,
+ cast(-2.5 as int) = -2 as value7,
+ cast(-2.5 as int) = -3 as value8
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+
+| VALUE1 | VALUE2 | VALUE3 | VALUE4 | VALUE5 | VALUE6 | VALUE7 | VALUE8 |
++--------+--------+--------+--------+--------+--------+--------+--------+
+| true | false | true | false | false | true | false | true |
++--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+EnumerableValues(tuples=[[{ true, false, true, false, false, true, false, true
}]])
+!plan
+
+!use scott
+
+# rounding mode is DOWN
+select cast(5.5 as int) = 5 as value1,
+ cast(5.5 as int) = 6 as value2,
+ cast(3.5 as int) = 3 as value3,
+ cast(3.5 as int) = 4 as value4,
+ cast(1.6 as int) = 1 as value5,
+ cast(1.6 as int) = 2 as value6,
+ cast(1.1 as int) = 1 as value7,
+ cast(1.1 as int) = 2 as value8,
+ cast(1.0 as int) = 1 as value9,
+ cast(1.0 as int) = 2 as value10
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
+| VALUE1 | VALUE2 | VALUE3 | VALUE4 | VALUE5 | VALUE6 | VALUE7 | VALUE8 |
VALUE9 | VALUE10 |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
+| true | false | true | false | true | false | true | false | true
| false |
++--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
+(1 row)
+
+!ok
+
+EnumerableValues(tuples=[[{ true, false, true, false, true, false, true,
false, true, false }]])
+!plan
+
+# same as before, but is negative number
+select cast(-1.0 as int) = -1 as value1,
+ cast(-1.0 as int) = -2 as value2,
+ cast(-1.1 as int) = -1 as value3,
+ cast(-1.1 as int) = -2 as value4,
+ cast(-1.6 as int) = -1 as value5,
+ cast(-1.6 as int) = -2 as value6,
+ cast(-2.5 as int) = -2 as value7,
+ cast(-2.5 as int) = -3 as value8
+from (values 0) as t(x);
++--------+--------+--------+--------+--------+--------+--------+--------+
+| VALUE1 | VALUE2 | VALUE3 | VALUE4 | VALUE5 | VALUE6 | VALUE7 | VALUE8 |
++--------+--------+--------+--------+--------+--------+--------+--------+
+| true | false | true | false | true | false | true | false |
++--------+--------+--------+--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+EnumerableValues(tuples=[[{ true, false, true, false, true, false, true, false
}]])
+!plan
+
+# [CALCITE-4871] CAST a literal to DECIMAL type return wrong result
+
+# when precision greater than the max precision, use the max precision replace
the precision
+values cast(15.3 as decimal(1000, 4));
++---------+
+| EXPR$0 |
++---------+
+| 15.3000 |
++---------+
+(1 row)
+
+!ok
+
+# scale and precision is equal
+values cast(0 as decimal(3,3));
++--------+
+| EXPR$0 |
++--------+
+| 0.000 |
++--------+
+(1 row)
+
+!ok
+
+# Cast a decimal literal to a decimal
+
+values cast(15.3 as decimal(3,0));
++--------+
+| EXPR$0 |
++--------+
+| 15 |
++--------+
+(1 row)
+
+!ok
+
+values cast(15.3 as decimal(3,1));
++--------+
+| EXPR$0 |
++--------+
+| 15.3 |
++--------+
+(1 row)
+
+!ok
+
+values cast(15.3 as decimal(3,2));
+Value 15.3 cannot be represented as a DECIMAL(3, 2)
+!error
+
+values cast(15.6 as decimal(3,0));
++--------+
+| EXPR$0 |
++--------+
+| 15 |
++--------+
+(1 row)
+
+!ok
+
+values cast(15.6 as decimal(3,1));
++--------+
+| EXPR$0 |
++--------+
+| 15.6 |
++--------+
+(1 row)
+
+!ok
+
+values cast(15.6 as decimal(3,2));
+Value 15.6 cannot be represented as a DECIMAL(3, 2)
+!error
+
+# Cast a character literal to a decimal
+
+values cast(' 15.3 ' as decimal(3,0));
++--------+
+| EXPR$0 |
++--------+
+| 15 |
++--------+
+(1 row)
+
+!ok
+
+values cast(' 15.3 ' as decimal(3,1));
++--------+
+| EXPR$0 |
++--------+
+| 15.3 |
++--------+
+(1 row)
+
+!ok
+
+values cast(' 15.3 ' as decimal(3,2));
+Value 15.3 cannot be represented as a DECIMAL(3, 2)
+!error
+
+values cast(' 15.66 ' as decimal(3,0));
++--------+
+| EXPR$0 |
++--------+
+| 15 |
++--------+
+(1 row)
+
+!ok
+
+values cast(' 15.66 ' as decimal(3,1));
++--------+
+| EXPR$0 |
++--------+
+| 15.6 |
++--------+
+(1 row)
+
+!ok
+
+values cast(' 15.66 ' as decimal(3,2));
+Value 15.66 cannot be represented as a DECIMAL(3, 2)
+!error
+
+values cast(' 15.66 ' as decimal(4,1));
++--------+
+| EXPR$0 |
++--------+
+| 15.6 |
++--------+
+(1 row)
+
+!ok
+
+# Cast a int literal to a decimal
+values cast(15 as decimal(3,1));
++--------+
+| EXPR$0 |
++--------+
+| 15.0 |
++--------+
+(1 row)
+
+!ok
+
+values cast(15 as decimal(3,2));
+Value 15 cannot be represented as a DECIMAL(3, 2)
+!error
+
+values cast(15 as decimal(3,0));
++--------+
+| EXPR$0 |
++--------+
+| 15 |
++--------+
+(1 row)
+
+!ok
+
+# Cast a double literal to a decimal
+
+values cast(cast(15.3 as double) as decimal(3,0));
++--------+
+| EXPR$0 |
++--------+
+| 15 |
++--------+
+(1 row)
+
+!ok
+
+values cast(cast(15.3 as double) as decimal(3,1));
++--------+
+| EXPR$0 |
++--------+
+| 15.3 |
++--------+
+(1 row)
+
+!ok
+
+values cast(cast(15.3 as double) as decimal(3,2));
+Value 15.3 cannot be represented as a DECIMAL(3, 2)
+!error
+
+values cast(cast(15.6 as double) as decimal(3,0));
++--------+
+| EXPR$0 |
++--------+
+| 15 |
++--------+
+(1 row)
+
+!ok
+
+values cast(cast(15.6 as double) as decimal(3,1));
++--------+
+| EXPR$0 |
++--------+
+| 15.6 |
++--------+
+(1 row)
+
+!ok
+
+values cast(cast(15.6 as double) as decimal(3,2));
+Value 15.6 cannot be represented as a DECIMAL(3, 2)
+!error
+
+# Cast a float literal to a decimal
+
+values cast(cast(15.3 as float) as decimal(3,0));
++--------+
+| EXPR$0 |
++--------+
+| 15 |
++--------+
+(1 row)
+
+!ok
+
+values cast(cast(15.3 as float) as decimal(3,1));
++--------+
+| EXPR$0 |
++--------+
+| 15.3 |
++--------+
+(1 row)
+
+!ok
+
+values cast(cast(15.3 as float) as decimal(3,2));
+Value 15.3 cannot be represented as a DECIMAL(3, 2)
+!error
+
+values cast(cast(15.6 as float) as decimal(3,0));
++--------+
+| EXPR$0 |
++--------+
+| 15 |
++--------+
+(1 row)
+
+!ok
+
+values cast(cast(15.6 as float) as decimal(3,1));
++--------+
+| EXPR$0 |
++--------+
+| 15.6 |
++--------+
+(1 row)
+
+!ok
+
+values cast(cast(15.6 as float) as decimal(3,2));
+Value 15.6 cannot be represented as a DECIMAL(3, 2)
+!error
+
+# Cast a real literal to a decimal
+
+values cast(cast(15.3 as real) as decimal(3,0));
++--------+
+| EXPR$0 |
++--------+
+| 15 |
++--------+
+(1 row)
+
+!ok
+
+values cast(cast(15.3 as real) as decimal(3,1));
++--------+
+| EXPR$0 |
++--------+
+| 15.3 |
++--------+
+(1 row)
+
+!ok
+
+values cast(cast(15.3 as real) as decimal(3,2));
+Value 15.300000190734863 cannot be represented as a DECIMAL(3, 2)
+!error
+
+values cast(cast(15.6 as real) as decimal(3,0));
++--------+
+| EXPR$0 |
++--------+
+| 15 |
++--------+
+(1 row)
+
+!ok
+
+values cast(cast(15.6 as real) as decimal(3,1));
++--------+
+| EXPR$0 |
++--------+
+| 15.6 |
++--------+
+(1 row)
+
+!ok
+
+values cast(cast(15.6 as real) as decimal(3,2));
+Value 15.600000381469727 cannot be represented as a DECIMAL(3, 2)
+!error
+
+# [CALCITE-5769] Optimizing 'CAST(e AS t) IS NOT NULL' to 'e IS NOT NULL'
+
+# Test CAST(e as t) IS NOT NULL when CAST is lossless And e IS NOT NULL
+select cast(deptno as integer) IS NOT NULL from "scott".dept;
++--------+
+| EXPR$0 |
++--------+
+| true |
+| true |
+| true |
+| true |
++--------+
+(4 rows)
+
+!ok
+EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], EXPR$0=[$t3])
+ EnumerableTableScan(table=[[scott, DEPT]])
+!plan
+
+# Test CAST(e as t) IS NULL when CAST is lossless And e IS NOT NULL
+select cast(deptno as integer) IS NULL from "scott".dept;
++--------+
+| EXPR$0 |
++--------+
+| false |
+| false |
+| false |
+| false |
++--------+
+(4 rows)
+
+!ok
+EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], EXPR$0=[$t3])
+ EnumerableTableScan(table=[[scott, DEPT]])
+!plan
+
+# Test CAST(e as t) IS NOT NULL when CAST is lossless And e is nullable
+select cast(mgr as integer) IS NOT NULL from "scott".emp;
++--------+
+| EXPR$0 |
++--------+
+| false |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
+| true |
++--------+
+(14 rows)
+
+!ok
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t3)], EXPR$0=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# Test CAST(e as t) IS NULL when CAST is lossless And e is nullable
+select cast(mgr as integer) IS NULL from "scott".emp;
++--------+
+| EXPR$0 |
++--------+
+| false |
+| true |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
++--------+
+(14 rows)
+
+!ok
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NULL($t3)], EXPR$0=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# Test CAST(e as t) IS NOT NULL when CAST is not lossless And e is nullable
+select cast(comm as integer) IS NOT NULL from "scott".emp;
++--------+
+| EXPR$0 |
++--------+
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| false |
+| true |
+| true |
+| true |
+| true |
++--------+
+(14 rows)
+
+!ok
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):INTEGER], expr#9=[IS
NOT NULL($t8)], EXPR$0=[$t9])
+ EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# End cast.iq
diff --git a/core/src/test/resources/sql/misc.iq
b/core/src/test/resources/sql/misc.iq
index 4e814df1b3..2d61e27950 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -1734,272 +1734,6 @@ select (case when (true) then 1 end) from (values(1));
EXPR$0 INTEGER(10)
!type
-# Cast a character literal to a timestamp; note: the plan does not contain CAST
-values cast('1969-07-21 12:34:56' as timestamp);
-+---------------------+
-| EXPR$0 |
-+---------------------+
-| 1969-07-21 12:34:56 |
-+---------------------+
-(1 row)
-
-!ok
-EnumerableValues(tuples=[[{ 1969-07-21 12:34:56 }]])
-!plan
-
-# Cast a character literal without time to a timestamp; note: the plan does
not contain CAST
-values cast('1969-07-21' as timestamp);
-+---------------------+
-| EXPR$0 |
-+---------------------+
-| 1969-07-21 00:00:00 |
-+---------------------+
-(1 row)
-
-!ok
-EnumerableValues(tuples=[[{ 1969-07-21 00:00:00 }]])
-!plan
-
-# Cast a character literal to a date; note: the plan does not contain CAST
-values cast('1969-07-21' as date);
-+------------+
-| EXPR$0 |
-+------------+
-| 1969-07-21 |
-+------------+
-(1 row)
-
-!ok
-EnumerableValues(tuples=[[{ 1969-07-21 }]])
-!plan
-
-# Slightly different format
-# (Incidentally, this format is not allowed in date literals, per the standard)
-values cast('1989-7-4' as date);
-+------------+
-| EXPR$0 |
-+------------+
-| 1989-07-04 |
-+------------+
-(1 row)
-
-!ok
-
-# Cast a character literal to an integer; note: the plan does not contain CAST
-values cast('196907' as integer);
-+--------+
-| EXPR$0 |
-+--------+
-| 196907 |
-+--------+
-(1 row)
-
-!ok
-EnumerableValues(tuples=[[{ 196907 }]])
-!plan
-
-# Cast an integer literal to a bigint; note: the plan does not contain CAST
-values cast(123 as bigint);
-+--------+
-| EXPR$0 |
-+--------+
-| 123 |
-+--------+
-(1 row)
-
-!ok
-EnumerableValues(tuples=[[{ 123 }]])
-!plan
-
-# Cast an integer literal to a decimal; note: the plan does not contain CAST
-values cast('123.45' as decimal(5, 2));
-+--------+
-| EXPR$0 |
-+--------+
-| 123.45 |
-+--------+
-(1 row)
-
-!ok
-EnumerableValues(tuples=[[{ 123.45 }]])
-!plan
-
-# Cast a character literal to a decimal; note: the plan does not contain CAST
-values cast('123.45' as decimal(5, 2));
-+--------+
-| EXPR$0 |
-+--------+
-| 123.45 |
-+--------+
-(1 row)
-
-!ok
-EnumerableValues(tuples=[[{ 123.45 }]])
-!plan
-
-# Cast a character literal to a double; note: the plan does not contain CAST
-values cast('-123.45' as double);
-+---------+
-| EXPR$0 |
-+---------+
-| -123.45 |
-+---------+
-(1 row)
-
-!ok
-EnumerableValues(tuples=[[{ -1.2345E2 }]])
-!plan
-
-values cast('false' as boolean);
-+--------+
-| EXPR$0 |
-+--------+
-| false |
-+--------+
-(1 row)
-
-!ok
-EnumerableValues(tuples=[[{ false }]])
-!plan
-
-values cast('TRUE' as boolean);
-+--------+
-| EXPR$0 |
-+--------+
-| true |
-+--------+
-(1 row)
-
-!ok
-EnumerableValues(tuples=[[{ true }]])
-!plan
-
-values cast('TR' || 'UE' as boolean);
-+--------+
-| EXPR$0 |
-+--------+
-| true |
-+--------+
-(1 row)
-
-!ok
-EnumerableCalc(expr#0=[{inputs}], expr#1=['TR'], expr#2=['UE'],
expr#3=[||($t1, $t2)], expr#4=[CAST($t3):BOOLEAN NOT NULL], EXPR$0=[$t4])
- EnumerableValues(tuples=[[{ 0 }]])
-!plan
-
-!if (fixed.calcite2539) {
-
-# In the following, that we get an error at run time,
-# and that the plan shows that the expression has not been reduced.
-values cast('null' as boolean);
-Invalid character for cast
-!error
-EnumerableCalc(expr#0=[{inputs}], expr#1=['null'], expr#2=[CAST($t1):BOOLEAN
NOT NULL], EXPR$0=[$t2])
- EnumerableValues(tuples=[[{ 0 }]])
-!plan
-
-# The following throw give an error (good!)
-# but throw java.lang.ExceptionInInitializerError (not great).
-values cast('' as date);
-Caused by: java.lang.NumberFormatException: For input string: ""
-!error
-
-values cast('' as timestamp);
-Caused by: java.lang.NumberFormatException: For input string: ""
-!error
-
-values cast('' as integer);
-Caused by: java.lang.NumberFormatException: For input string: ""
-!error
-
-values cast('' as boolean);
-Caused by: java.lang.RuntimeException: Invalid character for cast
-!error
-
-values cast('' as double);
-Caused by: java.lang.NumberFormatException: empty String
-!error
-
-# Postgres fails:
-# ERROR: invalid input syntax for integer: "1.56"
-values cast('15.6' as integer);
-Caused by: java.lang.NumberFormatException: For input string: "15.6"
-!error
-
-# Postgres fails:
-# ERROR: invalid input syntax for integer: " - 5 "
-values cast(' - 5 ' as double);
-Caused by: java.lang.NumberFormatException: For input string: "- 5"
-!error
-
-# Out of TINYINT range (max 127)
-values cast('200' as tinyint);
-Caused by: java.lang.NumberFormatException: Value out of range. Value:"200"
Radix:10
-!error
-
-# Out of SMALLINT range (max 32767)
-values cast('50000' as smallint);
-Caused by: java.lang.NumberFormatException: Value out of range. Value:"50000"
Radix:10
-!error
-
-# Out of INTEGER range (max 2.1e9)
-values cast('4567891234' as integer);
-Caused by: java.lang.NumberFormatException: For input string: "4567891234"
-!error
-
-# Out of BIGINT range (max 9.2e18)
-values cast('12345678901234567890' as bigint);
-Caused by: java.lang.NumberFormatException: For input string:
"12345678901234567890"
-!error
-!}
-
-# Out of REAL range
-# (Should give an error, not infinity.)
-values cast('12.34e56' as real);
-+----------+
-| EXPR$0 |
-+----------+
-| Infinity |
-+----------+
-(1 row)
-
-!ok
-
-# Out of FLOAT range
-# (Should give an error, not infinity.)
-values cast('12.34e5678' as float);
-+----------+
-| EXPR$0 |
-+----------+
-| Infinity |
-+----------+
-(1 row)
-
-!ok
-
-# Out of DOUBLE range
-# (Should give an error, not infinity.)
-values cast('12.34e5678' as double);
-+----------+
-| EXPR$0 |
-+----------+
-| Infinity |
-+----------+
-(1 row)
-
-!ok
-
-# Postgres succeeds
-values cast(' -5 ' as double);
-+--------+
-| EXPR$0 |
-+--------+
-| -5.0 |
-+--------+
-(1 row)
-
-!ok
-
# RAND_INTEGER with seed
select i, rand_integer(1, 5) as r
from (values 1, 2, 3, 4, 5) as t(i);
@@ -2462,126 +2196,8 @@ TS
1969-07-21 00:00:00
!ok
-!use scott
!set outputformat mysql
-# [CALCITE-5769] Optimizing 'CAST(e AS t) IS NOT NULL' to 'e IS NOT NULL'
-
-# Test CAST(e as t) IS NOT NULL when CAST is lossless And e IS NOT NULL
-select cast(deptno as integer) IS NOT NULL from "scott".dept;
-+--------+
-| EXPR$0 |
-+--------+
-| true |
-| true |
-| true |
-| true |
-+--------+
-(4 rows)
-
-!ok
-EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], EXPR$0=[$t3])
- EnumerableTableScan(table=[[scott, DEPT]])
-!plan
-
-# Test CAST(e as t) IS NULL when CAST is lossless And e IS NOT NULL
-select cast(deptno as integer) IS NULL from "scott".dept;
-+--------+
-| EXPR$0 |
-+--------+
-| false |
-| false |
-| false |
-| false |
-+--------+
-(4 rows)
-
-!ok
-EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], EXPR$0=[$t3])
- EnumerableTableScan(table=[[scott, DEPT]])
-!plan
-
-# Test CAST(e as t) IS NOT NULL when CAST is lossless And e is nullable
-select cast(mgr as integer) IS NOT NULL from "scott".emp;
-+--------+
-| EXPR$0 |
-+--------+
-| false |
-| true |
-| true |
-| true |
-| true |
-| true |
-| true |
-| true |
-| true |
-| true |
-| true |
-| true |
-| true |
-| true |
-+--------+
-(14 rows)
-
-!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t3)], EXPR$0=[$t8])
- EnumerableTableScan(table=[[scott, EMP]])
-!plan
-
-# Test CAST(e as t) IS NULL when CAST is lossless And e is nullable
-select cast(mgr as integer) IS NULL from "scott".emp;
-+--------+
-| EXPR$0 |
-+--------+
-| false |
-| true |
-| false |
-| false |
-| false |
-| false |
-| false |
-| false |
-| false |
-| false |
-| false |
-| false |
-| false |
-| false |
-+--------+
-(14 rows)
-
-!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NULL($t3)], EXPR$0=[$t8])
- EnumerableTableScan(table=[[scott, EMP]])
-!plan
-
-# Test CAST(e as t) IS NOT NULL when CAST is not lossless And e is nullable
-select cast(comm as integer) IS NOT NULL from "scott".emp;
-+--------+
-| EXPR$0 |
-+--------+
-| false |
-| false |
-| false |
-| false |
-| false |
-| false |
-| false |
-| false |
-| false |
-| false |
-| true |
-| true |
-| true |
-| true |
-+--------+
-(14 rows)
-
-!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):INTEGER], expr#9=[IS
NOT NULL($t8)], EXPR$0=[$t9])
- EnumerableTableScan(table=[[scott, EMP]])
-!plan
-
# [CALCITE-5870] Allow literals like DECIMAL '12.3' (consistent with Postgres)
# Test a decimal value between decimal logic for range checking.
select 12.3 between decimal '5.6' and decimal '17.8';
@@ -2658,714 +2274,4 @@ FROM "hr"."emps";
!ok
-# [CALCITE-4838] Add RoundingMode in RelDataTypeSystem to specify the rounding
behavior
-!use scott-rounding-half-up
-
-# Test cast approximate numeric to int when rounding mode is HALF-UP
-select cast(5.5 as int),
- cast(3.5 as int),
- cast(1.6 as int),
- cast(1.1 as int),
- cast(1.0 as int),
- cast(-1.0 as int),
- cast(-1.1 as int),
- cast(-1.6 as int),
- cast(-2.5 as int),
- cast(15.5 as int)
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 6 | 4 | 2 | 1 | 1 | -1 | -1 | -2 |
-3 | 16 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast approximate numeric to tinyint when rounding mode is HALF-UP
-select cast(5.5 as tinyint),
- cast(3.5 as tinyint),
- cast(1.6 as tinyint),
- cast(1.1 as tinyint),
- cast(1.0 as tinyint),
- cast(-1.0 as tinyint),
- cast(-1.1 as tinyint),
- cast(-1.6 as tinyint),
- cast(-2.5 as tinyint),
- cast(15.5 as tinyint)
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 6 | 4 | 2 | 1 | 1 | -1 | -1 | -2 |
-3 | 16 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast approximate numeric to smallint when rounding mode is HALF-UP
-select cast(5.5 as smallint),
- cast(3.5 as smallint),
- cast(1.6 as smallint),
- cast(1.1 as smallint),
- cast(1.0 as smallint),
- cast(-1.0 as smallint),
- cast(-1.1 as smallint),
- cast(-1.6 as smallint),
- cast(-2.5 as smallint),
- cast(15.5 as smallint)
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 6 | 4 | 2 | 1 | 1 | -1 | -1 | -2 |
-3 | 16 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast approximate numeric to bigint when rounding mode is HALF-UP
-select cast(5.5 as bigint),
- cast(3.5 as bigint),
- cast(1.6 as bigint),
- cast(1.1 as bigint),
- cast(1.0 as bigint),
- cast(-1.0 as bigint),
- cast(-1.1 as bigint),
- cast(-1.6 as bigint),
- cast(-2.5 as bigint),
- cast(15.5 as bigint)
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 6 | 4 | 2 | 1 | 1 | -1 | -1 | -2 |
-3 | 16 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast decimal to decimal when rounding mode is HALF-UP
-select cast(cast(5.55 as decimal(3,2)) as decimal(2,1)),
- cast(cast(3.55 as decimal(3,2)) as decimal(2,1)),
- cast(cast(1.66 as decimal(3,2)) as decimal(2,1)),
- cast(cast(1.11 as decimal(3,2)) as decimal(2,1)),
- cast(cast(1.00 as decimal(3,2)) as decimal(2,1)),
- cast(cast(-1.00 as decimal(3,2)) as decimal(2,1)),
- cast(cast(-1.11 as decimal(3,2)) as decimal(2,1)),
- cast(cast(-1.66 as decimal(3,2)) as decimal(2,1)),
- cast(cast(-2.55 as decimal(3,2)) as decimal(2,1)),
- cast(cast(15.55 as decimal(4,2)) as decimal(3,1))
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 5.6 | 3.6 | 1.7 | 1.1 | 1.0 | -1.0 | -1.1 | -1.7 |
-2.6 | 15.6 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast varchar to decimal when rounding mode is HALF-UP
-select cast('5.5' as decimal(2, 0)),
- cast('3.5' as decimal(2, 0)),
- cast('1.6' as decimal(2, 0)),
- cast('1.1' as decimal(2, 0)),
- cast('1.0' as decimal(2, 0)),
- cast('-1.0' as decimal(2, 0)),
- cast('-1.1' as decimal(2, 0)),
- cast('-1.6' as decimal(2, 0)),
- cast('-2.5' as decimal(2, 0)),
- cast('15.5' as decimal(2, 0))
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 6 | 4 | 2 | 1 | 1 | -1 | -1 | -2 |
-3 | 16 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast float to decimal when rounding mode is HALF-UP
-select cast(cast(5.5 as float) as decimal(2,0)),
- cast(cast(3.5 as float) as decimal(2,0)),
- cast(cast(1.6 as float) as decimal(2,0)),
- cast(cast(1.1 as float) as decimal(2,0)),
- cast(cast(1.0 as float) as decimal(2,0)),
- cast(cast(-1.0 as float) as decimal(2,0)),
- cast(cast(-1.1 as float) as decimal(2,0)),
- cast(cast(-1.6 as float) as decimal(2,0)),
- cast(cast(-2.5 as float) as decimal(2,0)),
- cast(cast(15.5 as float) as decimal(2,0))
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 6 | 4 | 2 | 1 | 1 | -1 | -1 | -2 |
-3 | 16 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast float to decimal but rounding behavior happens in scala when
rounding mode is HALF-UP
-select cast(cast(5.55 as float) as decimal(2,1)),
- cast(cast(3.55 as float) as decimal(2,1)),
- cast(cast(1.66 as float) as decimal(2,1)),
- cast(cast(1.11 as float) as decimal(2,1)),
- cast(cast(1.00 as float) as decimal(2,1)),
- cast(cast(-1.00 as float) as decimal(2,1)),
- cast(cast(-1.11 as float) as decimal(2,1)),
- cast(cast(-1.66 as float) as decimal(2,1)),
- cast(cast(-2.55 as float) as decimal(2,1)),
- cast(cast(15.55 as float) as decimal(3,1))
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 5.6 | 3.6 | 1.7 | 1.1 | 1.0 | -1.0 | -1.1 | -1.7 |
-2.6 | 15.6 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast double to decimal when rounding mode is HALF-UP
-select cast(cast(5.5 as double) as decimal(2,0)),
- cast(cast(3.5 as double) as decimal(2,0)),
- cast(cast(1.6 as double) as decimal(2,0)),
- cast(cast(1.1 as double) as decimal(2,0)),
- cast(cast(1.0 as double) as decimal(2,0)),
- cast(cast(-1.0 as double) as decimal(2,0)),
- cast(cast(-1.1 as double) as decimal(2,0)),
- cast(cast(-1.6 as double) as decimal(2,0)),
- cast(cast(-2.5 as double) as decimal(2,0)),
- cast(cast(15.5 as double) as decimal(2,0))
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 6 | 4 | 2 | 1 | 1 | -1 | -1 | -2 |
-3 | 16 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast double to decimal but rounding behavior happens in scala when
rounding mode is HALF-UP
-select cast(cast(5.55 as double) as decimal(2,1)),
- cast(cast(3.55 as double) as decimal(2,1)),
- cast(cast(1.66 as double) as decimal(2,1)),
- cast(cast(1.11 as double) as decimal(2,1)),
- cast(cast(1.00 as double) as decimal(2,1)),
- cast(cast(-1.00 as double) as decimal(2,1)),
- cast(cast(-1.11 as double) as decimal(2,1)),
- cast(cast(-1.66 as double) as decimal(2,1)),
- cast(cast(-2.55 as double) as decimal(2,1)),
- cast(cast(15.55 as double) as decimal(3,1))
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 5.6 | 3.6 | 1.7 | 1.1 | 1.0 | -1.0 | -1.1 | -1.7 |
-2.6 | 15.6 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-
-!use scott
-
-# Test cast approximate numeric to int when rounding mode is DOWN
-select cast(5.5 as int),
- cast(3.5 as int),
- cast(1.6 as int),
- cast(1.1 as int),
- cast(1.0 as int),
- cast(-1.0 as int),
- cast(-1.1 as int),
- cast(-1.6 as int),
- cast(-2.5 as int),
- cast(15.5 as int)
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 5 | 3 | 1 | 1 | 1 | -1 | -1 | -1 |
-2 | 15 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast approximate numeric to tinyint when rounding mode is DOWN
-select cast(5.5 as tinyint),
- cast(3.5 as tinyint),
- cast(1.6 as tinyint),
- cast(1.1 as tinyint),
- cast(1.0 as tinyint),
- cast(-1.0 as tinyint),
- cast(-1.1 as tinyint),
- cast(-1.6 as tinyint),
- cast(-2.5 as tinyint),
- cast(15.5 as tinyint)
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 5 | 3 | 1 | 1 | 1 | -1 | -1 | -1 |
-2 | 15 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast approximate numeric to smallint when rounding mode is DOWN
-select cast(5.5 as smallint),
- cast(3.5 as smallint),
- cast(1.6 as smallint),
- cast(1.1 as smallint),
- cast(1.0 as smallint),
- cast(-1.0 as smallint),
- cast(-1.1 as smallint),
- cast(-1.6 as smallint),
- cast(-2.5 as smallint),
- cast(15.5 as smallint)
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 5 | 3 | 1 | 1 | 1 | -1 | -1 | -1 |
-2 | 15 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast approximate numeric to bigint when rounding mode is DOWN
-select cast(5.5 as bigint),
- cast(3.5 as bigint),
- cast(1.6 as bigint),
- cast(1.1 as bigint),
- cast(1.0 as bigint),
- cast(-1.0 as bigint),
- cast(-1.1 as bigint),
- cast(-1.6 as bigint),
- cast(-2.5 as bigint),
- cast(15.5 as bigint)
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 5 | 3 | 1 | 1 | 1 | -1 | -1 | -1 |
-2 | 15 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast decimal to decimal when rounding mode is DOWN
-select cast(cast(5.55 as decimal(3,2)) as decimal(2,1)),
- cast(cast(3.55 as decimal(3,2)) as decimal(2,1)),
- cast(cast(1.66 as decimal(3,2)) as decimal(2,1)),
- cast(cast(1.11 as decimal(3,2)) as decimal(2,1)),
- cast(cast(1.00 as decimal(3,2)) as decimal(2,1)),
- cast(cast(-1.00 as decimal(3,2)) as decimal(2,1)),
- cast(cast(-1.11 as decimal(3,2)) as decimal(2,1)),
- cast(cast(-1.66 as decimal(3,2)) as decimal(2,1)),
- cast(cast(-2.55 as decimal(3,2)) as decimal(2,1)),
- cast(cast(15.55 as decimal(4,2)) as decimal(3,1))
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 5.5 | 3.5 | 1.6 | 1.1 | 1.0 | -1.0 | -1.1 | -1.6 |
-2.5 | 15.5 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast varchar to decimal when rounding mode is DOWN
-select cast('5.5' as decimal(2, 0)),
- cast('3.5' as decimal(2, 0)),
- cast('1.6' as decimal(2, 0)),
- cast('1.1' as decimal(2, 0)),
- cast('1.0' as decimal(2, 0)),
- cast('-1.0' as decimal(2, 0)),
- cast('-1.1' as decimal(2, 0)),
- cast('-1.6' as decimal(2, 0)),
- cast('-2.5' as decimal(2, 0)),
- cast('15.5' as decimal(2, 0))
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 5 | 3 | 1 | 1 | 1 | -1 | -1 | -1 |
-2 | 15 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast float to decimal when rounding mode is DOWN
-select cast(cast(5.5 as float) as decimal(2,0)),
- cast(cast(3.5 as float) as decimal(2,0)),
- cast(cast(1.6 as float) as decimal(2,0)),
- cast(cast(1.1 as float) as decimal(2,0)),
- cast(cast(1.0 as float) as decimal(2,0)),
- cast(cast(-1.0 as float) as decimal(2,0)),
- cast(cast(-1.1 as float) as decimal(2,0)),
- cast(cast(-1.6 as float) as decimal(2,0)),
- cast(cast(-2.5 as float) as decimal(2,0)),
- cast(cast(15.5 as float) as decimal(2,0))
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 5 | 3 | 1 | 1 | 1 | -1 | -1 | -1 |
-2 | 15 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast float to decimal but rounding behavior happens in scala when
rounding mode is DOWN
-select cast(cast(5.55 as float) as decimal(2,1)),
- cast(cast(3.55 as float) as decimal(2,1)),
- cast(cast(1.66 as float) as decimal(2,1)),
- cast(cast(1.11 as float) as decimal(2,1)),
- cast(cast(1.00 as float) as decimal(2,1)),
- cast(cast(-1.00 as float) as decimal(2,1)),
- cast(cast(-1.11 as float) as decimal(2,1)),
- cast(cast(-1.66 as float) as decimal(2,1)),
- cast(cast(-2.55 as float) as decimal(2,1)),
- cast(cast(15.55 as float) as decimal(3,1))
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 5.5 | 3.5 | 1.6 | 1.1 | 1.0 | -1.0 | -1.1 | -1.6 |
-2.5 | 15.5 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast double to decimal when rounding mode is DOWN
-select cast(cast(5.5 as double) as decimal(2,0)),
- cast(cast(3.5 as double) as decimal(2,0)),
- cast(cast(1.6 as double) as decimal(2,0)),
- cast(cast(1.1 as double) as decimal(2,0)),
- cast(cast(1.0 as double) as decimal(2,0)),
- cast(cast(-1.0 as double) as decimal(2,0)),
- cast(cast(-1.1 as double) as decimal(2,0)),
- cast(cast(-1.6 as double) as decimal(2,0)),
- cast(cast(-2.5 as double) as decimal(2,0)),
- cast(cast(15.5 as double) as decimal(2,0))
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 5 | 3 | 1 | 1 | 1 | -1 | -1 | -1 |
-2 | 15 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# Test cast double to decimal but rounding behavior happens in scala when
rounding mode is DOWN
-select cast(cast(5.55 as double) as decimal(2,1)),
- cast(cast(3.55 as double) as decimal(2,1)),
- cast(cast(1.66 as double) as decimal(2,1)),
- cast(cast(1.11 as double) as decimal(2,1)),
- cast(cast(1.00 as double) as decimal(2,1)),
- cast(cast(-1.00 as double) as decimal(2,1)),
- cast(cast(-1.11 as double) as decimal(2,1)),
- cast(cast(-1.66 as double) as decimal(2,1)),
- cast(cast(-2.55 as double) as decimal(2,1)),
- cast(cast(15.55 as double) as decimal(3,1))
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 | EXPR$5 | EXPR$6 | EXPR$7 |
EXPR$8 | EXPR$9 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-| 5.5 | 3.5 | 1.6 | 1.1 | 1.0 | -1.0 | -1.1 | -1.6 |
-2.5 | 15.5 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-# [CALCITE-4806] Lossy CAST is incorrectly simplified
-
-!use scott-rounding-half-up
-
-# rounding mode is HALF-UP
-select cast(5.5 as int) = 5 as value1,
- cast(5.5 as int) = 6 as value2,
- cast(3.5 as int) = 3 as value3,
- cast(3.5 as int) = 4 as value4,
- cast(1.6 as int) = 1 as value5,
- cast(1.6 as int) = 2 as value6,
- cast(1.1 as int) = 1 as value7,
- cast(1.1 as int) = 2 as value8,
- cast(1.0 as int) = 1 as value9,
- cast(1.0 as int) = 2 as value10
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
-| VALUE1 | VALUE2 | VALUE3 | VALUE4 | VALUE5 | VALUE6 | VALUE7 | VALUE8 |
VALUE9 | VALUE10 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
-| false | true | false | true | false | true | true | false | true
| false |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
-(1 row)
-
-!ok
-
-EnumerableValues(tuples=[[{ false, true, false, true, false, true, true,
false, true, false }]])
-!plan
-
-# same as before, but is negative number
-select cast(-1.0 as int) = -1 as value1,
- cast(-1.0 as int) = -2 as value2,
- cast(-1.1 as int) = -1 as value3,
- cast(-1.1 as int) = -2 as value4,
- cast(-1.6 as int) = -1 as value5,
- cast(-1.6 as int) = -2 as value6,
- cast(-2.5 as int) = -2 as value7,
- cast(-2.5 as int) = -3 as value8
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+
-| VALUE1 | VALUE2 | VALUE3 | VALUE4 | VALUE5 | VALUE6 | VALUE7 | VALUE8 |
-+--------+--------+--------+--------+--------+--------+--------+--------+
-| true | false | true | false | false | true | false | true |
-+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-EnumerableValues(tuples=[[{ true, false, true, false, false, true, false, true
}]])
-!plan
-
-!use scott
-
-# rounding mode is DOWN
-select cast(5.5 as int) = 5 as value1,
- cast(5.5 as int) = 6 as value2,
- cast(3.5 as int) = 3 as value3,
- cast(3.5 as int) = 4 as value4,
- cast(1.6 as int) = 1 as value5,
- cast(1.6 as int) = 2 as value6,
- cast(1.1 as int) = 1 as value7,
- cast(1.1 as int) = 2 as value8,
- cast(1.0 as int) = 1 as value9,
- cast(1.0 as int) = 2 as value10
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
-| VALUE1 | VALUE2 | VALUE3 | VALUE4 | VALUE5 | VALUE6 | VALUE7 | VALUE8 |
VALUE9 | VALUE10 |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
-| true | false | true | false | true | false | true | false | true
| false |
-+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
-(1 row)
-
-!ok
-
-EnumerableValues(tuples=[[{ true, false, true, false, true, false, true,
false, true, false }]])
-!plan
-
-# same as before, but is negative number
-select cast(-1.0 as int) = -1 as value1,
- cast(-1.0 as int) = -2 as value2,
- cast(-1.1 as int) = -1 as value3,
- cast(-1.1 as int) = -2 as value4,
- cast(-1.6 as int) = -1 as value5,
- cast(-1.6 as int) = -2 as value6,
- cast(-2.5 as int) = -2 as value7,
- cast(-2.5 as int) = -3 as value8
-from (values 0) as t(x);
-+--------+--------+--------+--------+--------+--------+--------+--------+
-| VALUE1 | VALUE2 | VALUE3 | VALUE4 | VALUE5 | VALUE6 | VALUE7 | VALUE8 |
-+--------+--------+--------+--------+--------+--------+--------+--------+
-| true | false | true | false | true | false | true | false |
-+--------+--------+--------+--------+--------+--------+--------+--------+
-(1 row)
-
-!ok
-
-EnumerableValues(tuples=[[{ true, false, true, false, true, false, true, false
}]])
-!plan
-
-# [CALCITE-4871] CAST a literal to DECIMAL type return wrong result
-
-!set outputformat csv
-
-# when precision greater than the max precision, use the max precision replace
the precision
-values cast(15.3 as decimal(1000, 4));
-EXPR$0
-15.3000
-!ok
-
-# scale and precision is equal
-values cast(0 as decimal(3,3));
-EXPR$0
-0.000
-!ok
-
-# Cast a decimal literal to a decimal
-
-values cast(15.3 as decimal(3,0));
-EXPR$0
-15
-!ok
-
-values cast(15.3 as decimal(3,1));
-EXPR$0
-15.3
-!ok
-
-values cast(15.3 as decimal(3,2));
-Value 15.3 cannot be represented as a DECIMAL(3, 2)
-!error
-
-values cast(15.6 as decimal(3,0));
-EXPR$0
-15
-!ok
-
-values cast(15.6 as decimal(3,1));
-EXPR$0
-15.6
-!ok
-
-values cast(15.6 as decimal(3,2));
-Value 15.6 cannot be represented as a DECIMAL(3, 2)
-!error
-
-# Cast a character literal to a decimal
-
-values cast(' 15.3 ' as decimal(3,0));
-EXPR$0
-15
-!ok
-
-values cast(' 15.3 ' as decimal(3,1));
-EXPR$0
-15.3
-!ok
-
-values cast(' 15.3 ' as decimal(3,2));
-Value 15.3 cannot be represented as a DECIMAL(3, 2)
-!error
-
-values cast(' 15.66 ' as decimal(3,0));
-EXPR$0
-15
-!ok
-
-values cast(' 15.66 ' as decimal(3,1));
-EXPR$0
-15.6
-!ok
-
-values cast(' 15.66 ' as decimal(3,2));
-Value 15.66 cannot be represented as a DECIMAL(3, 2)
-!error
-
-values cast(' 15.66 ' as decimal(4,1));
-EXPR$0
-15.6
-!ok
-
-# Cast a int literal to a decimal
-values cast(15 as decimal(3,1));
-EXPR$0
-15.0
-!ok
-
-values cast(15 as decimal(3,2));
-Value 15 cannot be represented as a DECIMAL(3, 2)
-!error
-
-values cast(15 as decimal(3,0));
-EXPR$0
-15
-!ok
-
-# Cast a double literal to a decimal
-
-values cast(cast(15.3 as double) as decimal(3,0));
-EXPR$0
-15
-!ok
-
-values cast(cast(15.3 as double) as decimal(3,1));
-EXPR$0
-15.3
-!ok
-
-values cast(cast(15.3 as double) as decimal(3,2));
-Value 15.3 cannot be represented as a DECIMAL(3, 2)
-!error
-
-values cast(cast(15.6 as double) as decimal(3,0));
-EXPR$0
-15
-!ok
-
-values cast(cast(15.6 as double) as decimal(3,1));
-EXPR$0
-15.6
-!ok
-
-values cast(cast(15.6 as double) as decimal(3,2));
-Value 15.6 cannot be represented as a DECIMAL(3, 2)
-!error
-
-# Cast a float literal to a decimal
-
-values cast(cast(15.3 as float) as decimal(3,0));
-EXPR$0
-15
-!ok
-
-values cast(cast(15.3 as float) as decimal(3,1));
-EXPR$0
-15.3
-!ok
-
-values cast(cast(15.3 as float) as decimal(3,2));
-Value 15.3 cannot be represented as a DECIMAL(3, 2)
-!error
-
-values cast(cast(15.6 as float) as decimal(3,0));
-EXPR$0
-15
-!ok
-
-values cast(cast(15.6 as float) as decimal(3,1));
-EXPR$0
-15.6
-!ok
-
-values cast(cast(15.6 as float) as decimal(3,2));
-Value 15.6 cannot be represented as a DECIMAL(3, 2)
-!error
-
-# Cast a real literal to a decimal
-
-values cast(cast(15.3 as real) as decimal(3,0));
-EXPR$0
-15
-!ok
-
-values cast(cast(15.3 as real) as decimal(3,1));
-EXPR$0
-15.3
-!ok
-
-values cast(cast(15.3 as real) as decimal(3,2));
-Value 15.300000190734863 cannot be represented as a DECIMAL(3, 2)
-!error
-
-values cast(cast(15.6 as real) as decimal(3,0));
-EXPR$0
-15
-!ok
-
-values cast(cast(15.6 as real) as decimal(3,1));
-EXPR$0
-15.6
-!ok
-
-values cast(cast(15.6 as real) as decimal(3,2));
-Value 15.600000381469727 cannot be represented as a DECIMAL(3, 2)
-!error
-
# End misc.iq
