Google Git
Sign in
apache / trafodion / refs/tags/2.3.0rc1 / . / core / sql / regress / core / EXPECTED032
blob: 59885a702ed3e746011baf07e099fab7a38ae65a [file] [log] [blame]
>>
>>?section BIN16S
>>-- Conversions to BIN16S. =========================================================
>>
>> -- Create tables.
>>
>> -- Create and load t032t01.
>> create table t032t01 (a numeric(2) signed not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t01 values (-99),(-1),(0),(1),(99);
--- 5 row(s) inserted.
>>
>> -- Result should be 5 rows: -99, -1, 0, 1, 99.
>> select * from t032t01;
A
----
-99
-1
0
1
99
--- 5 row(s) selected.
>>
>> -- Create and load t032t02.
>> create table t032t02 (a smallint signed not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t02 values (-32768),(-1),(0),(1),(32767);
--- 5 row(s) inserted.
>>
>> -- Result should be 5 rows: -32768, -1, 0, 1, 32767.
>> select * from t032t02;
A
------
-32768
-1
0
1
32767
--- 5 row(s) selected.
>>
>> -- Case: CONV_BIN16S_BIN16S.
>>
>> -- Result should be 0 rows and no errors.
>> select a from t032t01 where a <= cast(-500 as smallint signed);
--- 0 row(s) selected.
>>
>> -- Result should be 0 rows and no errors.
>> select a from t032t01 where a = cast(4 as smallint signed);
--- 0 row(s) selected.
>>
>> -- Result should be 1 row: 1.
>> select a from t032t01 where a = cast(1 as smallint signed);
A
----
1
--- 1 row(s) selected.
>>
>> -- Result should be 1 row: 32767.
>> select a from t032t02 where a >= cast(32767 as smallint signed);
A
------
32767
--- 1 row(s) selected.
>>
>> -- Result should be 3 rows: -32768, -1, 0.
>> select a from t032t02 where a < cast(1 as smallint signed);
A
------
-32768
-1
0
--- 3 row(s) selected.
>>
>> -- Case: CONV_BIN16U_BIN16S.
>>
>> -- Result should be 5 rows: -32768, -1, 0, 1, 32767.
>> select a from t032t02 where a <= cast(65535 as smallint unsigned);
A
------
-32768
-1
0
1
32767
--- 5 row(s) selected.
>>
>> -- Result should be 2 rows: 0, 1.
>> select a from t032t02 where a >= cast(0 as smallint unsigned)
+> and a < cast(32767 as smallint unsigned);
A
------
0
1
--- 2 row(s) selected.
>>
>> -- Result should be 5 rows: -99, -1, 0, 1, 99.
>> select a from t032t01 where a < cast(100 as smallint unsigned);
A
----
-99
-1
0
1
99
--- 5 row(s) selected.
>>
>> -- Case: CONV_BIN32S_BIN16S.
>>
>> -- Result should be 4 rows: -1, 0, 1, 32767.
>> select a from t032t02 where a > cast(-2 as integer signed)
+> and a <= cast(2147483647 as integer signed);
A
------
-1
0
1
32767
--- 4 row(s) selected.
>>
>> -- Result should be 3 rows: -32768, -1, 0.
>> select a from t032t02 where a > cast(-2147483648 as integer signed)
+> and a <= cast(0 as integer signed);
A
------
-32768
-1
0
--- 3 row(s) selected.
>>
>> -- Result should be 5 rows: -99, -1, 0, 1, 99.
>> select a from t032t01 where a > cast(-500 as integer signed);
A
----
-99
-1
0
1
99
--- 5 row(s) selected.
>>
>> -- Case: CONV_BIN32U_BIN16S.
>>
>> -- Result should be 3 rows: 0, 1, 32767.
>> select a from t032t02 where a >= cast(0 as integer unsigned)
+> and a < cast(4294967295 as integer unsigned);
A
------
0
1
32767
--- 3 row(s) selected.
>>
>> -- Result should be 5 rows: -99, -1, 0, 1, 99.
>> select a from t032t01 where a <= cast(500 as integer unsigned);
A
----
-99
-1
0
1
99
--- 5 row(s) selected.
>>
>> -- Case: CONV_BIN64S_BIN16S.
>>
>> -- Result should be 4 rows: -1, 0, 1, 32767.
>> select a from t032t02 where a <= cast(9.22e18 as largeint signed)
+> and a > cast(-50 as largeint signed);
A
------
-1
0
1
32767
--- 4 row(s) selected.
>>
>> -- Result should be 4 rows: -32768, -1, 0, 1.
>> select a from t032t02 where a > cast(-9.22e18 as largeint signed)
+> and a <= cast(1 as largeint signed);
A
------
-32768
-1
0
1
--- 4 row(s) selected.
>>
>> -- Result should be 5 rows: -99, -1, 0, 1, 99.
>> select a from t032t01 where a <= cast(10000 as largeint signed);
A
----
-99
-1
0
1
99
--- 5 row(s) selected.
>>
>> -- Case: CONV_DECS_BIN16S. (Handled by CONV_DECS_BIN32S.)
>>
>> -- Result should be 4 rows: -32768, -1, 0, 1.
>> select a from t032t02 where a > cast(-99999 as decimal(5) signed)
+> and a <= cast(28 as decimal(5) signed);
A
------
-32768
-1
0
1
--- 4 row(s) selected.
>>
>> -- Result should be 1 row: 32767.
>> select a from t032t02 where a = cast(32767 as decimal(5) signed);
A
------
32767
--- 1 row(s) selected.
>>
>> -- Result should be 4 rows: -1, 0, 1, 99.
>> select a from t032t01 where a >= cast(-50 as decimal(3) signed)
+> and a < cast(100 as decimal(3) signed);
A
----
-1
0
1
99
--- 4 row(s) selected.
>>
>> -- Case: CONV_DECU_BIN16S. (Handled by CONV_DECS_BIN32S.)
>>
>> -- Result should be 2 rows: 1, 32767.
>> select a from t032t02 where a >= cast(1 as decimal(9) unsigned)
+> and a < cast(99999999 as decimal(8) unsigned);
A
------
1
32767
--- 2 row(s) selected.
>>
>> -- Result should be 1 row: 1.
>> select a from t032t02 where a = cast(1 as decimal(1) unsigned);
A
------
1
--- 1 row(s) selected.
>>
>> -- Result should be 2 rows: 1, 99.
>> select a from t032t01 where a >= cast(1 as decimal(7) unsigned)
+> and a < cast(100 as decimal(3) unsigned);
A
----
1
99
--- 2 row(s) selected.
>>
>> -- Case: CONV_FLOAT32_BIN16S.
>>
>> -- Result should be 4 rows: -1, 0, 1, 32767.
>> select a from t032t02 where a <= cast(3e38 as real)
+> and a > cast(-50 as real);
A
------
-1
0
1
32767
--- 4 row(s) selected.
>>
>> -- Result should be 4 rows: -32768, -1, 0, 1.
>> select a from t032t02 where a > cast(-3e38 as real)
+> and a <= cast(1 as real);
A
------
-32768
-1
0
1
--- 4 row(s) selected.
>>
>> -- Result should be 5 rows: -99, -1, 0, 1, 99.
>> select a from t032t01 where a <= cast(3e38 as real);
A
----
-99
-1
0
1
99
--- 5 row(s) selected.
>>
>> -- Result should be 0 rows. Case #10-981208-4368.
>> select a from t032t01 where a = cast(1.1e0 as real);
--- 0 row(s) selected.
>>
>> -- Result should be 4 rows: -32768, -1, 0, 1. Case #10-981208-4368.
>> select a from t032t02 where a < cast(1.1e0 as real);
A
------
-32768
-1
0
1
--- 4 row(s) selected.
>>
>> -- Result should be 4 rows: -1, 0, 1, 32767. Case #10-981208-4368.
>> select a from t032t02 where a > cast(-1.1e0 as real);
A
------
-1
0
1
32767
--- 4 row(s) selected.
>>
>> -- Result should be 1 rows: 1. Case #10-981208-4368.
>> select a from t032t02 where a = cast(1e0 as real);
A
------
1
--- 1 row(s) selected.
>>
>> -- Case: CONV_FLOAT64_BIN16S.
>>
>> -- Result should be 4 rows: -1, 0, 1, 32767.
>> select a from t032t02 where a <= cast(4e70 as double precision)
+> and a > cast(-50 as double precision);
A
------
-1
0
1
32767
--- 4 row(s) selected.
>>
>> -- Result should be 4 rows: -32768, -1, 0, 1.
>> select a from t032t02 where a > cast(-4e70 as double precision)
+> and a <= cast(1 as double precision);
A
------
-32768
-1
0
1
--- 4 row(s) selected.
>>
>> -- Result should be 5 rows: -99, -1, 0, 1, 99.
>> select a from t032t01 where a <= cast(1e50 as double precision);
A
----
-99
-1
0
1
99
--- 5 row(s) selected.
>>
>> -- Result should be 4 rows: -99, -1, 0, 1. Case #10-981208-4368.
>> select a from t032t01 where a < cast(1.1e0 as double precision);
A
----
-99
-1
0
1
--- 4 row(s) selected.
>>
>> -- Case: CONV_LARGEDEC_BIN16S.
>>
>> -- Result should be 4 rows: -1, 0, 1, 32767.
>> select a from t032t02 where a <= 33000.0000001 * 1.000000000001
+> and a > -(15000.0000001 * 1.000000000001);
A
------
-1
0
1
32767
--- 4 row(s) selected.
>>
>> -- Result should be 4 rows: -32768, -1, 0, 1.
>> select a from t032t02 where a > -33000.0000001 * 1.000000000001
+> and a < 1.000000000001 * 1.000000000001;
A
------
-32768
-1
0
1
--- 4 row(s) selected.
>>
>>
>> -- Drop tables.
>> drop table t032t01;
--- SQL operation complete.
>> drop table t032t02;
--- SQL operation complete.
>>
>>?section BIN16U
>>-- Conversions to BIN16U. =========================================================
>>
>> -- Create tables.
>>
>> -- Create and load t032t03.
>> create table t032t03 (a numeric(3) unsigned not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t03 values (0),(1),(50),(999);
--- 4 row(s) inserted.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999.
>> select * from t032t03;
A
-----
0
1
50
999
--- 4 row(s) selected.
>>
>> -- Create and load t032t04.
>> create table t032t04 (a smallint unsigned not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t04 values (0),(1),(50),(32767),(65535);
--- 5 row(s) inserted.
>>
>> -- Result should be 5 rows: 0, 1, 50, 32767, 65535
>> select * from t032t04;
A
-----
0
1
50
32767
65535
--- 5 row(s) selected.
>>
>> -- Case: CONV_BIN16S_BIN16U.
>>
>> -- Result should be 3 rows: 0, 1, 50.
>> select a from t032t03 where a < (-cast(-100 as smallint signed));
A
-----
0
1
50
--- 3 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999.
>> select a from t032t03 where a >= cast(0 as smallint signed);
A
-----
0
1
50
999
--- 4 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999.
>> select a from t032t03 where a >= cast(-32768 as smallint signed);
A
-----
0
1
50
999
--- 4 row(s) selected.
>>
>> -- Result should be 3 rows: 0, 1, 50.
>> select a from t032t03 where a >= cast(-1 as smallint signed)
+> and a < cast(75 as smallint signed);
A
-----
0
1
50
--- 3 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999.
>> select a from t032t03 where a <= cast(32767 as smallint signed);
A
-----
0
1
50
999
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 1, 50.
>> select a from t032t04 where a >= cast(1 as smallint signed)
+> and a <= cast(100 as smallint signed);
A
-----
1
50
--- 2 row(s) selected.
>>
>> -- Case: CONV_BIN16U_BIN16U.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999.
>> select a from t032t03 where a <= cast(65535 as smallint unsigned);
A
-----
0
1
50
999
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 1, 50.
>> select a from t032t04 where a >= cast(1 as smallint unsigned)
+> and a < cast(32767 as smallint unsigned);
A
-----
1
50
--- 2 row(s) selected.
>>
>> -- Case: CONV_BIN32S_BIN16U.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999.
>> select a from t032t03 where a > cast(-2 as integer signed)
+> and a <= cast(2147483647 as integer signed);
A
-----
0
1
50
999
--- 4 row(s) selected.
>>
>> -- Result should be 1 row: 0.
>> select a from t032t04 where a > cast(-2147483648 as integer signed)
+> and a <= cast(0 as integer signed);
A
-----
0
--- 1 row(s) selected.
>>
>> -- Result should be 5 rows: 0, 1, 50, 32767, 65535
>> select a from t032t04 where a > cast(-500 as integer signed);
A
-----
0
1
50
32767
65535
--- 5 row(s) selected.
>>
>> -- Case: CONV_BIN32U_BIN16U.
>>
>> -- Result should be 3 rows: 1, 50, 999.
>> select a from t032t03 where a >= cast(1 as integer unsigned)
+> and a < cast(4294967295 as integer unsigned);
A
-----
1
50
999
--- 3 row(s) selected.
>>
>> -- Result should be 2 rows: 50, 32767
>> select a from t032t04 where a > cast(1 as integer unsigned)
+> and a < cast(32800 as integer unsigned);
A
-----
50
32767
--- 2 row(s) selected.
>>
>> -- Case: CONV_BIN64S_BIN16U.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999.
>> select a from t032t03 where a <= cast(9.22e18 as largeint signed)
+> and a > cast(-50 as largeint signed);
A
-----
0
1
50
999
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 0, 1.
>> select a from t032t04 where a > cast(-9.22e18 as largeint signed)
+> and a <= cast(1 as largeint signed);
A
-----
0
1
--- 2 row(s) selected.
>>
>> -- Result should be 3 rows: 0, 1, 50.
>> select a from t032t03 where a <= cast(50 as largeint signed);
A
-----
0
1
50
--- 3 row(s) selected.
>>
>> -- Result should be 3 rows: 50, 32767, 65535.
>> select a from t032t04 where a > cast(10 as largeint signed);
A
-----
50
32767
65535
--- 3 row(s) selected.
>>
>> -- Case: CONV_DECS_BIN16U. (Handled by CONV_DECS_BIN32U.)
>>
>> -- Result should be 5 rows: 0, 1, 50, 32767, 65535.
>> select a from t032t04 where a > cast(-9 as decimal(1) signed)
+> and a <= cast(999999 as decimal(6) signed);
A
-----
0
1
50
32767
65535
--- 5 row(s) selected.
>>
>> -- Result should be 2 rows: 50, 32767.
>> select a from t032t04 where a >= cast(12 as decimal(2) signed)
+> and a < cast(65535 as decimal(6) signed);
A
-----
50
32767
--- 2 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999.
>> select a from t032t03 where a <= cast(12345 as decimal(5) signed);
A
-----
0
1
50
999
--- 4 row(s) selected.
>>
>> -- Case: CONV_DECU_BIN16U. (Handled by CONV_DECS_BIN32U.)
>>
>> -- Result should be 4 rows: 0, 1, 50, 999.
>> select a from t032t03 where a >= cast(0 as decimal(9) unsigned)
+> and a < cast(9999999 as decimal(7) unsigned);
A
-----
0
1
50
999
--- 4 row(s) selected.
>>
>> -- Result should be 1 row: 50.
>> select a from t032t04 where a = cast(50 as decimal(3) unsigned);
A
-----
50
--- 1 row(s) selected.
>>
>> -- Case: CONV_FLOAT32_BIN16U.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999.
>> select a from t032t03 where a <= cast(3e38 as real)
+> and a > cast(-50 as real);
A
-----
0
1
50
999
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 0, 1.
>> select a from t032t04 where a > cast(-3e38 as real)
+> and a <= cast(1 as real);
A
-----
0
1
--- 2 row(s) selected.
>>
>> -- Result should be 5 rows: 0, 1, 50, 32767, 65535.
>> select a from t032t04 where a < cast(3e38 as real);
A
-----
0
1
50
32767
65535
--- 5 row(s) selected.
>>
>> -- Result should be 2 rows: 32767, 65535. Case #10-981208-4368.
>> select a from t032t04 where a >= cast(50.9e0 as real);
A
-----
32767
65535
--- 2 row(s) selected.
>>
>> -- Case: CONV_FLOAT64_BIN16U.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999.
>> select a from t032t03 where a <= cast(4e70 as double precision)
+> and a > cast(-50 as double precision);
A
-----
0
1
50
999
--- 4 row(s) selected.
>>
>> -- Result should be 3 rows: 0, 1, 50.
>> select a from t032t04 where a > cast(-4e70 as double precision)
+> and a <= cast(51 as double precision);
A
-----
0
1
50
--- 3 row(s) selected.
>>
>> -- Result should be 3 rows: 0, 1, 50. Case #10-981208-4368.
>> select a from t032t04 where a >= cast(-1.7e0 as double precision)
+> and a < cast(51.2e0 as double precision);
A
-----
0
1
50
--- 3 row(s) selected.
>>
>> -- Case: CONV_LARGEDEC_BIN16U.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999.
>> select a from t032t03 where a <= 33000.0000001 * 1.000000000001
+> and a > -(15000.0000001 * 1.000000000001);
A
-----
0
1
50
999
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 0, 1.
>> select a from t032t04 where a >= -33000.0000001 * 1.000000000001
+> and a < 1.000000000001 * 1.000000000001;
A
-----
0
1
--- 2 row(s) selected.
>>
>> -- Drop tables.
>> drop table t032t03;
--- SQL operation complete.
>> drop table t032t04;
--- SQL operation complete.
>>
>>?section BIN32S
>>-- Conversions to BIN32S. =========================================================
>>
>> -- Create tables.
>>
>> -- Create and load t032t05.
>> create table t032t05 (a integer signed not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t05 values (-2147483648),(-1),(0),(1),(6),(2147483647);
--- 6 row(s) inserted.
>>
>> -- Result should be 6 rows: -2147483648, -1, 0, 1, 6, 2147483647.
>> select * from t032t05;
A
-----------
-2147483648
-1
0
1
6
2147483647
--- 6 row(s) selected.
>>
>> -- Create and load t032t06.
>> create table t032t06 (a numeric(7) signed not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t06 values (-9999999),(-1),(0),(1),(6),(9999999);
--- 6 row(s) inserted.
>>
>> -- Result should be 6 rows: -9999999, -1, 0, 1, 6, 9999999.
>> select * from t032t06;
A
-----------
-9999999
-1
0
1
6
9999999
--- 6 row(s) selected.
>>
>> -- Case: CONV_BIN32S_BIN32S.
>>
>> -- Result should be 5 rows: -1, 0, 1, 6, 2147483647.
>> select a from t032t05 where a >= cast(-2 as integer signed)
+> and a <= cast(2147483647 as integer signed);
A
-----------
-1
0
1
6
2147483647
--- 5 row(s) selected.
>>
>> -- Result should be 3 rows: -9999999, -1, 0.
>> select a from t032t06 where a > cast(-2147483648 as integer signed)
+> and a <= cast(0 as integer signed);
A
-----------
-9999999
-1
0
--- 3 row(s) selected.
>>
>> -- Result should be 5 rows: -1, 0, 1, 6, 9999999.
>> select a from t032t06 where a < cast(10000000 as integer signed)
+> and a > cast(-9999999 as integer signed);
A
-----------
-1
0
1
6
9999999
--- 5 row(s) selected.
>>
>> -- Case: CONV_BIN32U_BIN32S.
>>
>> -- Result should be 3 rows: 1, 6, 2147483647.
>> select a from t032t05 where a >= cast(1 as integer unsigned)
+> and a < cast(4294967295 as integer unsigned);
A
-----------
1
6
2147483647
--- 3 row(s) selected.
>>
>> -- Result should be 3 rows: 1, 6, 9999999.
>> select a from t032t06 where a >= cast(1 as integer unsigned)
+> and a < cast(10000000 as integer unsigned);
A
-----------
1
6
9999999
--- 3 row(s) selected.
>>
>> -- Case: CONV_BIN64S_BIN32S.
>>
>> -- Result should be 5 rows: -1, 0, 1, 6, 2147483647.
>> select a from t032t05 where a <= cast(9.22e18 as largeint signed)
+> and a > cast(-50 as largeint signed);
A
-----------
-1
0
1
6
2147483647
--- 5 row(s) selected.
>>
>> -- Result should be 4 rows: -2147483648, -1, 0, 1.
>> select a from t032t05 where a > cast(-9.22e18 as largeint signed)
+> and a <= cast(1 as largeint signed);
A
-----------
-2147483648
-1
0
1
--- 4 row(s) selected.
>>
>> -- Result should be 1 row: 6.
>> select a from t032t06 where a = cast(6 as largeint signed);
A
-----------
6
--- 1 row(s) selected.
>>
>> -- Case: CONV_DECS_BIN32S.
>>
>> -- Result should be 5 rows: -2147483648, -1, 0, 1, 6.
>> select a from t032t05 where a > cast(-999999999999 as decimal(12) signed)
+> and a <= cast(100 as decimal(4) signed);
A
-----------
-2147483648
-1
0
1
6
--- 5 row(s) selected.
>>
>> -- Result should be 2 rows: 6, 9999999.
>> select a from t032t06 where a >= cast(4 as decimal(2) signed)
+> and a <= cast(9999999 as decimal(8) signed);
A
-----------
6
9999999
--- 2 row(s) selected.
>>
>> -- Result should be 1 row: -1.
>> select a from t032t06 where a = cast(-1 as decimal(5) signed);
A
-----------
-1
--- 1 row(s) selected.
>>
>> -- Case: CONV_DECU_BIN32S. (Handled by CONV_DECS_BIN32S.)
>>
>> -- Result should be 2 rows: 1, 6.
>> select a from t032t05 where a > cast(0 as decimal(9) unsigned)
+> and a <= cast(99999999 as decimal(8) unsigned);
A
-----------
1
6
--- 2 row(s) selected.
>>
>> -- Result should be 5 rows: -2147483648, -1, 0, 1, 6.
>> select a from t032t05 where a < cast(7 as decimal(9) unsigned);
A
-----------
-2147483648
-1
0
1
6
--- 5 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 6, 9999999.
>> select a from t032t06 where a >= cast(0 as decimal(1) unsigned)
+> and a < cast(10000000 as decimal(8) unsigned);
A
-----------
0
1
6
9999999
--- 4 row(s) selected.
>>
>> -- Case: CONV_FLOAT32_BIN32S.
>>
>> -- Result should be 5 rows: -1, 0, 1, 6, 2147483647.
>> select a from t032t05 where a <= cast(3e38 as real)
+> and a > cast(-50 as real);
A
-----------
-1
0
1
6
2147483647
--- 5 row(s) selected.
>>
>> -- Result should be 4 rows: -2147483648, -1, 0, 1.
>> select a from t032t05 where a > cast(-3e38 as real)
+> and a <= cast(1 as real);
A
-----------
-2147483648
-1
0
1
--- 4 row(s) selected.
>>
>> -- Result should be 1 row: -9999999.
>> select a from t032t06 where a = cast(-9999999 as real);
A
-----------
-9999999
--- 1 row(s) selected.
>>
>> -- Result should be 2 rows: 6, 2147483647. Case #10-981208-4368.
>> select a from t032t05 where a > cast(4.00001e0 as real);
A
-----------
6
2147483647
--- 2 row(s) selected.
>>
>> -- Case: CONV_FLOAT64_BIN32S.
>>
>> -- Result should be 5 rows: -1, 0, 1, 6, 2147483647.
>> select a from t032t05 where a <= cast(4e70 as double precision)
+> and a > cast(-50 as double precision);
A
-----------
-1
0
1
6
2147483647
--- 5 row(s) selected.
>>
>> -- Result should be 4 rows: -2147483648, -1, 0, 1.
>> select a from t032t05 where a > cast(-4e70 as double precision)
+> and a <= cast(1 as double precision);
A
-----------
-2147483648
-1
0
1
--- 4 row(s) selected.
>>
>> -- Result should be 6 rows: -9999999, -1, 0, 1, 6, 9999999.
>> select a from t032t06 where a <= cast(1e50 as double precision);
A
-----------
-9999999
-1
0
1
6
9999999
--- 6 row(s) selected.
>>
>> -- Result should be 5 rows: -1, 0, 1, 6, 9999999. Case #10-981208-4368.
>> select a from t032t06 where a > cast(-1.0002e0 as double precision);
A
-----------
-1
0
1
6
9999999
--- 5 row(s) selected.
>>
>> -- Case: CONV_LARGEDEC_BIN32S.
>>
>> -- Result should be 4 rows: -1, 0, 1, 6.
>> select a from t032t05 where a <= 33000.0000001 * 1.000000000001
+> and a > -(15000.0000001 * 1.000000000001);
A
-----------
-1
0
1
6
--- 4 row(s) selected.
>>
>> -- Result should be 3 rows: -1, 0, 1.
>> select a from t032t05 where a > -33000.0000001 * 1.000000000001
+> and a < 1.000000000001 * 1.000000000001;
A
-----------
-1
0
1
--- 3 row(s) selected.
>>
>> -- Result should be 4 rows: -9999999, -1, 0, 1.
>> select a from t032t06 where a <= 1.000000000001 * 1.000000000001;
A
-----------
-9999999
-1
0
1
--- 4 row(s) selected.
>>
>> -- Drop tables.
>> drop table t032t05;
--- SQL operation complete.
>> drop table t032t06;
--- SQL operation complete.
>>
>>
>>?section BIN32U
>>-- Conversions to BIN32U. =========================================================
>>
>> -- Create tables.
>>
>> -- Create and load t032t07.
>> create table t032t07 (a integer unsigned not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t07 values (0),(1),(50),(4294967295);
--- 4 row(s) inserted.
>>
>> -- Result should be 4 rows: 0, 1, 50, 4294967295.
>> select * from t032t07;
A
----------
0
1
50
4294967295
--- 4 row(s) selected.
>>
>> -- Create and load t032t08.
>> create table t032t08 (a numeric(7) unsigned not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t08 values (0),(1),(50),(9999999);
--- 4 row(s) inserted.
>>
>> -- Result should be 4 rows: 0, 1, 50, 9999999.
>> select * from t032t08;
A
----------
0
1
50
9999999
--- 4 row(s) selected.
>>
>> -- Case: CONV_BIN16S_BIN32U.
>>
>> -- Result should be 4 rows: 0, 1, 50, 4294967295.
>> select a from t032t07 where a > cast(-32768 as smallint signed);
A
----------
0
1
50
4294967295
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 1, 50.
>> select a from t032t07 where a >= cast(1 as smallint signed)
+> and a <= cast(75 as smallint signed);
A
----------
1
50
--- 2 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 50, 9999999.
>> select a from t032t08 where a > -(cast(1 as smallint signed));
A
----------
0
1
50
9999999
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 50, 9999999.
>> select a from t032t08 where a > cast(1 as smallint signed);
A
----------
50
9999999
--- 2 row(s) selected.
>>
>> -- Case: CONV_BIN32S_BIN32U.
>>
>> -- Result should be 3 rows: 0, 1, 50.
>> select a from t032t07 where a > cast(-2 as integer signed)
+> and a < cast(2147483647 as integer signed);
A
----------
0
1
50
--- 3 row(s) selected.
>>
>> -- Result should be 1 rows: 0.
>> select a from t032t07 where a > cast(-2147483648 as integer signed)
+> and a <= cast(0 as integer signed);
A
----------
0
--- 1 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 50, 9999999.
>> select a from t032t08 where a > cast(-500 as integer signed);
A
----------
0
1
50
9999999
--- 4 row(s) selected.
>>
>> -- Case: CONV_BIN32U_BIN32U.
>>
>> -- Result should be 2 rows: 1, 50.
>> select a from t032t07 where a >= cast(1 as integer unsigned)
+> and a < cast(4294967295 as integer unsigned);
A
----------
1
50
--- 2 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 50, 9999999.
>> select a from t032t08 where a <= cast(999999999 as integer unsigned);
A
----------
0
1
50
9999999
--- 4 row(s) selected.
>>
>> -- Case: CONV_BIN64S_BIN32U.
>>
>> -- Result should be 4 rows: 0, 1, 50, 4294967295.
>> select a from t032t07 where a <= cast(9.22e18 as largeint signed)
+> and a > cast(-50 as largeint signed);
A
----------
0
1
50
4294967295
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 0, 1.
>> select a from t032t07 where a > cast(-9.22e18 as largeint signed)
+> and a <= cast(1 as largeint signed);
A
----------
0
1
--- 2 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 50, 9999999.
>> select a from t032t08 where a < cast(10000000 as largeint signed);
A
----------
0
1
50
9999999
--- 4 row(s) selected.
>>
>> -- Case: CONV_DECS_BIN32U.
>>
>> -- Result should be 3 rows: 0, 1, 50.
>> select a from t032t07 where a > cast(-999999 as decimal(6) signed)
+> and a <= cast(100 as decimal(3) signed);
A
----------
0
1
50
--- 3 row(s) selected.
>>
>> -- Result should be 2 rows: 50, 4294967295.
>> select a from t032t07 where a <= cast(4294967295 as decimal(10) signed)
+> and a > cast(5 as decimal(10) signed);
A
----------
50
4294967295
--- 2 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 50, 9999999.
>> select a from t032t08 where a >= cast(-50 as decimal(3) signed)
+> and a < cast(10000000 as decimal(8) signed);
A
----------
0
1
50
9999999
--- 4 row(s) selected.
>>
>> -- Case: CONV_DECU_BIN32U. (Handled by CONV_DECS_BIN32U)
>>
>> -- Result should be 2 rows: 1, 50.
>> select a from t032t07 where a >= cast(1 as decimal(1) unsigned)
+> and a < cast(999999999 as decimal(9) unsigned);
A
----------
1
50
--- 2 row(s) selected.
>>
>> -- Result should be 1 rows: 1.
>> select a from t032t07 where a = cast(1 as decimal(1) unsigned);
A
----------
1
--- 1 row(s) selected.
>>
>> -- Result should be 2 rows: 1, 50.
>> select a from t032t08 where a >= cast(1 as decimal(7) unsigned)
+> and a < cast(100 as decimal(3) unsigned);
A
----------
1
50
--- 2 row(s) selected.
>>
>> -- Case: CONV_FLOAT32_BIN32U.
>>
>> -- Result should be 4 rows: 0, 1, 50, 4294967295.
>> select a from t032t07 where a <= cast(3e38 as real)
+> and a > cast(-50 as real);
A
----------
0
1
50
4294967295
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 0, 1.
>> select a from t032t07 where a > cast(-3e38 as real)
+> and a <= cast(1 as real);
A
----------
0
1
--- 2 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 50, 9999999.
>> select a from t032t08 where a <= cast(3e38 as real);
A
----------
0
1
50
9999999
--- 4 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 50, 9999999. Case #10-981208-4368.
>> select a from t032t08 where a <= cast(9999999.99 as real);
A
----------
0
1
50
9999999
--- 4 row(s) selected.
>>
>> -- Result should be 0 rows. Case #10-981208-4368.
>> select a from t032t08 where a = cast(50.0001 as real);
--- 0 row(s) selected.
>>
>> -- Case: CONV_FLOAT64_BIN32U.
>>
>> -- Result should be 4 rows: 0, 1, 50, 4294967295.
>> select a from t032t07 where a <= cast(4e70 as double precision)
+> and a > cast(-50 as double precision);
A
----------
0
1
50
4294967295
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 0, 1.
>> select a from t032t07 where a > cast(-4e70 as double precision)
+> and a <= cast(1 as double precision);
A
----------
0
1
--- 2 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 50, 9999999.
>> select a from t032t08 where a <= cast(1e50 as double precision);
A
----------
0
1
50
9999999
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 0, 1,. Case #10-981208-4368.
>> select a from t032t07 where a < cast(1.01 as double precision);
A
----------
0
1
--- 2 row(s) selected.
>>
>> -- Case: CONV_LARGEDEC_BIN32U.
>>
>> -- Result should be 4 rows: 0, 1, 50, 4294967295.
>> select a from t032t07 where a <= 4294967295.0000001 * 1.000000000001
+> and a > -(4294967295.0000001 * 1.000000000001);
A
----------
0
1
50
4294967295
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 0, 1.
>> select a from t032t07 where a > -33000.0000001 * 1.000000000001
+> and a < 1.000000000001 * 1.000000000001;
A
----------
0
1
--- 2 row(s) selected.
>>
>> -- Result should be 2 rows: 50, 9999999.
>> select a from t032t08 where a <= 9999999.0000000001 * 1.000000000001
+> and a > 49.999999999999999 / 1.00000000000001;
A
----------
50
9999999
--- 2 row(s) selected.
>>
>> -- Drop tables.
>> drop table t032t07;
--- SQL operation complete.
>> drop table t032t08;
--- SQL operation complete.
>>
>>
>>?section BIN64S
>>-- Conversions to BIN64S. =========================================================
>>
>> -- Create tables.
>>
>> -- Create and load t032t09.
>> create table t032t09 (a largeint signed not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t09 values (-9223372036854775808),(-1),(0),(1),(6),(9223372036854775807);
--- 6 row(s) inserted.
>>
>> -- Result should be 6 rows: -9223372036854775808, -1, 0, 1, 6, 9223372036854775807.
>> select * from t032t09;
A
--------------------
-9223372036854775808
-1
0
1
6
9223372036854775807
--- 6 row(s) selected.
>>
>> -- Create and load t032t10.
>> create table t032t10 (a numeric(15) signed not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t10 values (-999999999999999),(-1),(0),(1),(6),(999999999999999);
--- 6 row(s) inserted.
>>
>> -- Result should be 6 rows: -999999999999999, -1, 0, 1, 6, 999999999999999.
>> select * from t032t10;
A
--------------------
-999999999999999
-1
0
1
6
999999999999999
--- 6 row(s) selected.
>>
>> -- Case: CONV_BIN64S_BIN64S.
>>
>> -- Result should be 5 rows: -1, 0, 1, 6, 9223372036854775807.
>> select a from t032t09 where a <= cast(9223372036854775807 as largeint signed)
+> and a > cast(-50 as largeint signed);
A
--------------------
-1
0
1
6
9223372036854775807
--- 5 row(s) selected.
>>
>> -- Result should be 3 rows: -1, 0, 1.
>> select a from t032t09 where a > cast(-9223372036854775808 as largeint signed)
+> and a <= cast(1 as largeint signed);
A
--------------------
-1
0
1
--- 3 row(s) selected.
>>
>> -- Result should be 6 rows: -999999999999999, -1, 0, 1, 6, 999999999999999.
>> select a from t032t10 where a < cast((999999999999999+1) as largeint signed)
+> and a > cast((-999999999999999-1) as largeint signed);
A
--------------------
-999999999999999
-1
0
1
6
999999999999999
--- 6 row(s) selected.
>>
>> -- Case: CONV_DECS_BIN64S.
>>
>> -- Result should be 4 rows: -1, 0, 1, 6.
>> select a from t032t09 where a > cast(-999999999999 as decimal(12) signed)
+> and a <= cast(100 as decimal(4) signed);
A
--------------------
-1
0
1
6
--- 4 row(s) selected.
>>
>> -- Result should be 1 row: 6.
>> select a from t032t09 where a >= cast(4 as decimal(2) signed)
+> and a <= cast(9999999 as decimal(8) signed);
A
--------------------
6
--- 1 row(s) selected.
>>
>> -- Result should be 1 rows: -1.
>> select a from t032t10 where a = cast(-1 as decimal(5) signed);
A
--------------------
-1
--- 1 row(s) selected.
>>
>> -- Case: CONV_DECU_BIN64S.
>>
>> -- Result should be 2 rows: 1, 6.
>> select a from t032t09 where a > cast(0 as decimal(9) unsigned)
+> and a <= cast(99999999 as decimal(8) unsigned);
A
--------------------
1
6
--- 2 row(s) selected.
>>
>> -- Result should be 5 rows: -9223372036854775808, -1, 0, 1, 6.
>> select a from t032t09 where a < cast(7 as decimal(9) unsigned);
A
--------------------
-9223372036854775808
-1
0
1
6
--- 5 row(s) selected.
>>
>> -- Result should be 3 rows: 0, 1, 6.
>> select a from t032t10 where a >= cast(0 as decimal(1) unsigned)
+> and a < cast(10000000 as decimal(8) unsigned);
A
--------------------
0
1
6
--- 3 row(s) selected.
>>
>> -- Case: CONV_FLOAT32_BIN64S.
>>
>> -- Result should be 5 rows: -1, 0, 1, 6, 9223372036854775807.
>> select a from t032t09 where a <= cast(3e38 as real)
+> and a > cast(-50 as real);
A
--------------------
-1
0
1
6
9223372036854775807
--- 5 row(s) selected.
>>
>> -- Result should be 4 rows: -9223372036854775808, -1, 0, 1.
>> select a from t032t09 where a > cast(-3e38 as real)
+> and a <= cast(1 as real);
A
--------------------
-9223372036854775808
-1
0
1
--- 4 row(s) selected.
>>
>> -- Result should be 3 rows: 0, 1, 6. Case #10-981208-4368.
>> select a from t032t09 where a >= cast(-.999999 as real)
+> and a < cast(6.1 as real);
A
--------------------
0
1
6
--- 3 row(s) selected.
>>
>> -- Case: CONV_FLOAT64_BIN64S.
>>
>> -- Result should be 5 rows: -1, 0, 1, 6, 9223372036854775807.
>> select a from t032t09 where a <= cast(4e70 as double precision)
+> and a > cast(-50 as double precision);
A
--------------------
-1
0
1
6
9223372036854775807
--- 5 row(s) selected.
>>
>> -- Result should be 4 rows: -9223372036854775808, -1, 0, 1.
>> select a from t032t09 where a > cast(-4e70 as double precision)
+> and a <= cast(1 as double precision);
A
--------------------
-9223372036854775808
-1
0
1
--- 4 row(s) selected.
>>
>> -- Result should be 6 rows: -9999999, -1, 0, 1, 6, 9999999.
>> select a from t032t10 where a <= cast(1e50 as double precision);
A
--------------------
-999999999999999
-1
0
1
6
999999999999999
--- 6 row(s) selected.
>>
>> -- Result should be 3 rows: -999999999999999, -1, 0. Case #10-981208-4368.
>> select a from t032t10 where a <= cast(.00001 as double precision);
A
--------------------
-999999999999999
-1
0
--- 3 row(s) selected.
>>
>> -- Case: CONV_LARGEDEC_BIN64S.
>>
>> -- Result should be 4 rows: -1, 0, 1, 6.
>> select a from t032t09 where a <= 33000.0000001 * 1.000000000001
+> and a > -(15000.0000001 * 1.000000000001);
A
--------------------
-1
0
1
6
--- 4 row(s) selected.
>>
>> -- Result should be 4 rows: -999999999999999, -1, 0, 1.
>> select a from t032t10 where a <= 1.000000000001 * 1.000000000001;
A
--------------------
-999999999999999
-1
0
1
--- 4 row(s) selected.
>>
>>
>> -- Drop tables.
>> drop table t032t09;
--- SQL operation complete.
>> drop table t032t10;
--- SQL operation complete.
>>
>>
>>?section DECU
>>-- Conversions to DECU. =========================================================
>>
>> -- Create tables.
>>
>> -- Create and load t032t11.
>> create table t032t11 (a decimal(9) unsigned not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t11 values (0),(1),(50),(999999999);
--- 4 row(s) inserted.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999999999.
>> select * from t032t11;
A
---------
0
1
50
999999999
--- 4 row(s) selected.
>>
>> -- Create and load t032t12.
>> create table t032t12 (a decimal(1) unsigned not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t12 values (0),(1),(5),(9);
--- 4 row(s) inserted.
>>
>> -- Result should be 4 rows: 0, 1, 5, 9.
>> select * from t032t12;
A
-
0
1
5
9
--- 4 row(s) selected.
>>
>> -- Case: CONV_BIN16S_DECU.
>>
>> -- Result should be 2 rows: 50, 999999999.
>> select a from t032t11 where a >= cast(50 as smallint signed);
A
---------
50
999999999
--- 2 row(s) selected.
>>
>> -- Result should be 0 rows.
>> select a from t032t11 where a < cast(0 as smallint signed);
--- 0 row(s) selected.
>>
>> -- Result should be 3 rows: 0, 1, 50.
>> select a from t032t11 where a >= cast(-1 as smallint signed)
+> and a < cast(75 as smallint signed);
A
---------
0
1
50
--- 3 row(s) selected.
>>
>> -- Result should be 3 rows: 0, 1, 5.
>> select a from t032t12 where a > cast(-10 as smallint signed)
+> and a <= cast(7 as smallint signed);
A
-
0
1
5
--- 3 row(s) selected.
>>
>> -- Result should be 0 rows.
>> select a from t032t12 where a > cast(11 as smallint signed);
--- 0 row(s) selected.
>>
>> -- Case: CONV_BIN16U_DECU. (Handled by CONV_BIN16U_DECS.)
>>
>> -- Result should be 3 rows: 0, 1, 50.
>> select a from t032t11 where a <= cast(65535 as smallint unsigned);
A
---------
0
1
50
--- 3 row(s) selected.
>>
>> -- Result should be 2 rows: 1, 50.
>> select a from t032t11 where a >= cast(1 as smallint unsigned)
+> and a < cast(100 as smallint unsigned);
A
---------
1
50
--- 2 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 5, 9.
>> select a from t032t12 where a < cast(10 as smallint unsigned);
A
-
0
1
5
9
--- 4 row(s) selected.
>>
>> -- Case: CONV_BIN32S_DECU.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999999999.
>> select a from t032t11 where a > cast(-2 as integer signed)
+> and a <= cast(2147483647 as integer signed);
A
---------
0
1
50
999999999
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 0, 1.
>> select a from t032t11 where a > cast(-2147483648 as integer signed)
+> and a <= cast(1 as integer signed);
A
---------
0
1
--- 2 row(s) selected.
>>
>> -- Result should be 3 rows: 0, 1, 5.
>> select a from t032t12 where a > cast(-500 as integer signed)
+> and a <= cast(5 as integer signed);
A
-
0
1
5
--- 3 row(s) selected.
>>
>> -- Case: CONV_BIN32U_DECU. (Handled by CONV_BIN32U_DECS.)
>>
>> -- Result should be 4 rows: 0, 1, 50, 999999999.
>> select a from t032t11 where a >= cast(0 as integer unsigned)
+> and a < cast(4294967295 as integer unsigned);
A
---------
0
1
50
999999999
--- 4 row(s) selected.
>>
>> -- Result should be 3 rows: 1, 50, 999999999.
>> select a from t032t11 where a <= cast(999999999 as integer unsigned)
+> and a > cast(0 as integer unsigned);
A
---------
1
50
999999999
--- 3 row(s) selected.
>>
>> -- Case: CONV_BIN64S_DECU.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999999999.
>> select a from t032t11 where a <= cast(9.22e18 as largeint signed)
+> and a > cast(-50 as largeint signed);
A
---------
0
1
50
999999999
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 0, 1.
>> select a from t032t11 where a > cast(-9.22e18 as largeint signed)
+> and a <= cast(1 as largeint signed);
A
---------
0
1
--- 2 row(s) selected.
>>
>> -- Result should be 1 row: 50.
>> select a from t032t11 where a <= cast(50 as largeint signed)
+> and a > cast(1 as largeint signed);
A
---------
50
--- 1 row(s) selected.
>>
>> -- Case: CONV_DECS_DECU.
>>
>> -- Result should be 2 rows: 0, 1.
>> select a from t032t11 where a > cast(-99999 as decimal(5) signed)
+> and a <= cast(28 as decimal(5) signed);
A
---------
0
1
--- 2 row(s) selected.
>>
>> -- Result should be 1 row: 50.
>> select a from t032t11 where a = cast(50 as decimal(5) signed);
A
---------
50
--- 1 row(s) selected.
>>
>> -- Result should be 3 rows: 0, 1, 5.
>> select a from t032t12 where a >= cast(-50 as decimal(3) signed)
+> and a < cast(6 as decimal(3) signed);
A
-
0
1
5
--- 3 row(s) selected.
>>
>> -- Case: CONV_DECU_DECU. (Handled by CONV_DECS_DECU.)
>>
>> -- Result should be 2 rows: 1, 50.
>> select a from t032t11 where a >= cast(1 as decimal(9) unsigned)
+> and a < cast(99999999 as decimal(8) unsigned);
A
---------
1
50
--- 2 row(s) selected.
>>
>> -- Result should be 1 rows: 1.
>> select a from t032t11 where a = cast(1 as decimal(1) unsigned);
A
---------
1
--- 1 row(s) selected.
>>
>> -- Result should be 3 rows: 1, 5, 9.
>> select a from t032t12 where a >= cast(1 as decimal(7) unsigned)
+> and a < cast(100 as decimal(3) unsigned);
A
-
1
5
9
--- 3 row(s) selected.
>>
>> -- Case: CONV_FLOAT32_DECU.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999999999.
>> select a from t032t11 where a <= cast(3e38 as real)
+> and a > cast(-50 as real);
A
---------
0
1
50
999999999
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 0, 1.
>> select a from t032t11 where a > cast(-3e38 as real)
+> and a <= cast(1 as real);
A
---------
0
1
--- 2 row(s) selected.
>>
>> -- Result should be 2 rows: 5, 9.
>> select a from t032t12 where a <= cast(3e38 as real)
+> and a > cast(1 as real);
A
-
5
9
--- 2 row(s) selected.
>>
>> -- Result should be 3 rows: 0, 1, 50. Case #10-981208-4368.
>> select a from t032t11 where a < cast(50.1e0 as real);
A
---------
0
1
50
--- 3 row(s) selected.
>>
>> -- Case: CONV_FLOAT64_DECU.
>>
>> -- Result should be 4 rows: 0, 1, 50, 999999999.
>> select a from t032t11 where a <= cast(4e70 as double precision)
+> and a > cast(-50 as double precision);
A
---------
0
1
50
999999999
--- 4 row(s) selected.
>>
>> -- Result should be 2 rows: 0, 1.
>> select a from t032t11 where a > cast(-4e70 as double precision)
+> and a <= cast(1 as double precision);
A
---------
0
1
--- 2 row(s) selected.
>>
>> -- Result should be 2 rows: 5, 9.
>> select a from t032t12 where a <= cast(1e50 as double precision)
+> and a > cast(1 as double precision);
A
-
5
9
--- 2 row(s) selected.
>>
>> -- Result should be 3 rows: 1, 50, 999999999.
>> select a from t032t11 where a > cast(.5e0 as double precision);
A
---------
1
50
999999999
--- 3 row(s) selected.
>>
>> -- Case: CONV_LARGEDEC_DECU.
>>
>> -- Result should be 3 rows: 0, 1, 50.
>> select a from t032t11 where a <= 33000.0000001 * 1.000000000001
+> and a > -(15000.0000001 * 1.000000000001);
A
---------
0
1
50
--- 3 row(s) selected.
>>
>> -- Result should be 2 rows: 0, 1.
>> select a from t032t11 where a > -33000.0000001 * 1.000000000001
+> and a < 1.000000000001 * 1.000000000001;
A
---------
0
1
--- 2 row(s) selected.
>>
>> -- Drop tables.
>> drop table t032t11;
--- SQL operation complete.
>> drop table t032t12;
--- SQL operation complete.
>>
>>
>>?section DECS
>>-- Conversions to DECS. =========================================================
>>
>> -- Create tables.
>>
>> -- Create and load t032t13.
>> create table t032t13 (a decimal(18) signed not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t13 values (-999999999999999999),(-100),(-1),(0),(1),(50),(999999999999999999);
--- 7 row(s) inserted.
>>
>> -- Result should be 7 rows: -999999999999999999, -100, -1, 0, 1, 50, 999999999999999999.
>> select * from t032t13;
A
-------------------
-999999999999999999
-100
-1
0
1
50
999999999999999999
--- 7 row(s) selected.
>>
>> -- Create and load t032t14.
>> create table t032t14 (a decimal(1) signed not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t14 values (-9),(-1),(0),(1),(5),(9);
--- 6 row(s) inserted.
>>
>> -- Result should be 6 rows: -9, -1, 0, 1, 5, 9.
>> select * from t032t14;
A
--
-9
-1
0
1
5
9
--- 6 row(s) selected.
>>
>>
>> -- Case: CONV_BIN16S_DECS.
>>
>> -- Result should be 4 rows: -1, 0, 1, 50.
>> select a from t032t13 where a > cast(-4 as smallint signed)
+> and a <= cast(50 as smallint signed);
A
-------------------
-1
0
1
50
--- 4 row(s) selected.
>>
>> -- Result should be 5 rows: -100, -1, 0, 1, 50.
>> select a from t032t13 where a >= cast(-32768 as smallint signed)
+> and a < cast(32767 as smallint signed);
A
-------------------
-100
-1
0
1
50
--- 5 row(s) selected.
>>
>> -- Result should be 1 row: 50.
>> select a from t032t13 where a = cast(50 as smallint signed);
A
-------------------
50
--- 1 row(s) selected.
>>
>> -- Case: CONV_BIN16U_DECS.
>>
>> -- Result should be 6 rows: -999999999999999999, -100, -1, 0, 1, 50.
>> select a from t032t13 where a <= cast(65535 as smallint unsigned);
A
-------------------
-999999999999999999
-100
-1
0
1
50
--- 6 row(s) selected.
>>
>> -- Result should be 3 rows: 0, 1, 50.
>> select a from t032t13 where a >= cast(0 as smallint unsigned)
+> and a < cast(32767 as smallint unsigned);
A
-------------------
0
1
50
--- 3 row(s) selected.
>>
>> -- Result should be 3 rows: 1, 5, 9.
>> select a from t032t14 where a < cast(10 as smallint unsigned)
+> and a > cast(0 as smallint unsigned);
A
--
1
5
9
--- 3 row(s) selected.
>>
>> -- Case: CONV_BIN32S_DECS.
>>
>> -- Result should be 4 rows: -1, 0, 1, 50.
>> select a from t032t13 where a > cast(-2 as integer signed)
+> and a <= cast(2147483647 as integer signed);
A
-------------------
-1
0
1
50
--- 4 row(s) selected.
>>
>> -- Result should be 3 rows: -100, -1, 0.
>> select a from t032t13 where a > cast(-2147483648 as integer signed)
+> and a <= cast(0 as integer signed);
A
-------------------
-100
-1
0
--- 3 row(s) selected.
>>
>> -- Result should be 4 rows: -1, 0, 1, 5.
>> select a from t032t14 where a > cast(-9 as integer signed)
+> and a <= cast(5 as integer signed);
A
--
-1
0
1
5
--- 4 row(s) selected.
>>
>> -- Case: CONV_BIN32U_DECS.
>>
>> -- Result should be 3 rows: 0, 1, 50.
>> select a from t032t13 where a >= cast(0 as integer unsigned)
+> and a < cast(4294967295 as integer unsigned);
A
-------------------
0
1
50
--- 3 row(s) selected.
>>
>> -- Result should be 6 rows: -999999999999999999, -100, -1, 0, 1, 50.
>> select a from t032t13 where a <= cast(500 as integer unsigned);
A
-------------------
-999999999999999999
-100
-1
0
1
50
--- 6 row(s) selected.
>>
>> -- Result should be 3 rows: 1, 5, 9.
>> select a from t032t14 where a <= cast(500 as integer unsigned)
+> and a > cast(0 as integer unsigned);
A
--
1
5
9
--- 3 row(s) selected.
>>
>> -- Case: CONV_BIN64S_DECS.
>>
>> -- Result should be 5 rows: -1, 0, 1, 50, 999999999999999999.
>> select a from t032t13 where a <= cast(9.22e18 as largeint signed)
+> and a > cast(-50 as largeint signed);
A
-------------------
-1
0
1
50
999999999999999999
--- 5 row(s) selected.
>>
>> -- Result should be 5 rows: -999999999999999999, -100, -1, 0, 1.
>> select a from t032t13 where a > cast(-9.22e18 as largeint signed)
+> and a <= cast(1 as largeint signed);
A
-------------------
-999999999999999999
-100
-1
0
1
--- 5 row(s) selected.
>>
>> -- Result should be 5 rows: -1, 0, 1, 5, 9.
>> select a from t032t14 where a < cast(10 as largeint signed)
+> and a >= cast(-1 as largeint signed);
A
--
-1
0
1
5
9
--- 5 row(s) selected.
>>
>> -- Case: CONV_DECS_DECS.
>>
>> -- Result should be 4 rows: -100, -1, 0, 1.
>> select a from t032t13 where a > cast(-99999 as decimal(5) signed)
+> and a <= cast(28 as decimal(5) signed);
A
-------------------
-100
-1
0
1
--- 4 row(s) selected.
>>
>> -- Result should be 1 rows: -100.
>> select a from t032t13 where a = cast(-100 as decimal(5) signed);
A
-------------------
-100
--- 1 row(s) selected.
>>
>> -- Result should be 6 rows: -9, -1, 0, 1, 5, 9.
>> select a from t032t14 where a >= cast(-50 as decimal(3) signed)
+> and a < cast(100 as decimal(3) signed);
A
--
-9
-1
0
1
5
9
--- 6 row(s) selected.
>>
>> -- Case: CONV_DECU_DECS. (Handled by ??.)
>>
>> -- Result should be 3 rows: 1, 50, 999999999999999999.
>> select a from t032t13 where a >= cast(1 as decimal(9) unsigned);
A
-------------------
1
50
999999999999999999
--- 3 row(s) selected.
>>
>> -- Result should be 6 rows: -999999999999999999, -100, -1, 0, 1, 50.
>> select a from t032t13 where a <= cast(75 as decimal(3) unsigned);
A
-------------------
-999999999999999999
-100
-1
0
1
50
--- 6 row(s) selected.
>>
>> -- Result should be 3 rows: 1, 5, 9.
>> select a from t032t14 where a >= cast(1 as decimal(7) unsigned)
+> and a < cast(10 as decimal(3) unsigned);
A
--
1
5
9
--- 3 row(s) selected.
>>
>> -- Case: CONV_FLOAT32_DECS.
>>
>> -- Result should be 5 rows: -1, 0, 1, 50, 999999999999999999.
>> select a from t032t13 where a <= cast(3e38 as real)
+> and a > cast(-50 as real);
A
-------------------
-1
0
1
50
999999999999999999
--- 5 row(s) selected.
>>
>> -- Result should be 5 rows: -999999999999999999, -100, -1, 0, 1.
>> select a from t032t13 where a > cast(-3e38 as real)
+> and a <= cast(1 as real);
A
-------------------
-999999999999999999
-100
-1
0
1
--- 5 row(s) selected.
>>
>> -- Result should be 5 rows: -9, -1, 0, 1, 5.
>> select a from t032t14 where a <= cast(6 as real);
A
--
-9
-1
0
1
5
--- 5 row(s) selected.
>>
>> -- Result should be 5 rows: -1, 0, 1, 5, 9. Case #10-981208-4368.
>> select a from t032t14 where a > cast(-1.002 as real);
A
--
-1
0
1
5
9
--- 5 row(s) selected.
>>
>> -- Case: CONV_FLOAT64_DECS.
>>
>> -- Result should be 5 rows: -1, 0, 1, 50, 999999999999999999.
>> select a from t032t13 where a <= cast(4e70 as double precision)
+> and a > cast(-50 as double precision);
A
-------------------
-1
0
1
50
999999999999999999
--- 5 row(s) selected.
>>
>> -- Result should be 5 rows: -999999999999999999, -100, -1, 0, 1.
>> select a from t032t13 where a > cast(-4e70 as double precision)
+> and a <= cast(25 as double precision);
A
-------------------
-999999999999999999
-100
-1
0
1
--- 5 row(s) selected.
>>
>> -- Result should be 6 rows: -100, -1, 0, 1, 50, 999999999999999999.
>> -- Case #10-981208-4368.
>> select a from t032t13 where a > cast(-100.01 as double precision);
A
-------------------
-100
-1
0
1
50
999999999999999999
--- 6 row(s) selected.
>>
>> -- Case: CONV_LARGEDEC_DECS.
>>
>> -- Result should be 5 rows: -100, -1, 0, 1, 50.
>> select a from t032t13 where a <= 33000.0000001 * 1.000000000001
+> and a > -(15000.0000001 * 1.000000000001);
A
-------------------
-100
-1
0
1
50
--- 5 row(s) selected.
>>
>> -- Result should be 4 rows: -100, -1, 0, 1.
>> select a from t032t13 where a > -33000.0000001 * 1.000000000001
+> and a < 1.000000000001 * 1.000000000001;
A
-------------------
-100
-1
0
1
--- 4 row(s) selected.
>>
>> -- Result should be 5 rows: -9, -1, 0, 1, 5.
>> select a from t032t14 where a > -33000.0000001 * 1.000000000001
+> and a < 5.000000000001 * 1.000000000001;
A
--
-9
-1
0
1
5
--- 5 row(s) selected.
>>
>> -- Drop tables.
>> drop table t032t13;
--- SQL operation complete.
>> drop table t032t14;
--- SQL operation complete.
>>
>>?section FLOAT32
>>-- Conversions to FLOAT32. =========================================================
>>
>> -- Create tables.
>>
>> -- Create and load t032t15.
>> create table t032t15 (a real not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t15 values (-3e38),(-100),(-1),(0),(1),(50),(3e38);
--- 7 row(s) inserted.
>>
>> -- Result should be 7 rows: -3e38, -100, -1, 0, 1, 50, 3e38.
>> select * from t032t15;
A
---------------
-3.0000000E+038
-1.0000000E+002
-1.0000000E+000
0.0000000E+000
1.0000000E+000
5.0000000E+001
3.0000000E+038
--- 7 row(s) selected.
>>
>> -- Case: CONV_FLOAT64_FLOAT32.
>>
>> -- Result should be 5 rows: -1, 0, 1, 50, 3e38.
>> select a from t032t15 where a <= cast(4e70 as double precision)
+> and a > cast(-50 as double precision);
A
---------------
-1.0000000E+000
0.0000000E+000
1.0000000E+000
5.0000000E+001
3.0000000E+038
--- 5 row(s) selected.
>>
>> -- Result should be 5 rows: -3e38, -100, -1, 0, 1.
>> select a from t032t15 where a > cast(-4e70 as double precision)
+> and a <= cast(25 as double precision);
A
---------------
-3.0000000E+038
-1.0000000E+002
-1.0000000E+000
0.0000000E+000
1.0000000E+000
--- 5 row(s) selected.
>>
>> -- Case: CONV_LARGEDEC_FLOAT32.
>>
>> -- Result should be 5 rows: -100, -1, 0, 1, 50.
>> -- Test commented out because of runtime problem. 9/7/97 -Fred TBD
>> -- select a from t032t15 where a <= 33000.0000001 * 1.000000000001
>> -- and a > -(15000.0000001 * 1.000000000001);
>>
>> -- Result should be 4 rows: -100, -1, 0, 1.
>> -- Test commented out because of possible runtime problem. 9/7/97 -Fred TBD
>> -- select a from t032t15 where a > -33000.0000001 * 1.000000000001
>> -- and a < 1.000000000001 * 1.000000000001;
>>
>> -- Case #10-990106-2044
>>
>> set envvar MDAM ON;
--- SQL operation complete.
>>
>>
>> -- When IEEE floating support is added in R2, you may want to replace
>> -- the next two select statements with these (RSM)
>> -- select * from t032t15 where a <= cast(1e-300 as double precision);
>> -- select * from t032t15 where a >= cast(-1e-300 as double precision);
>>
>> -- Result should be 4 rows: -3e38, -100, -1, 0.
>> select * from t032t15 where a <= cast(1e-76 as double precision);
A
---------------
-3.0000000E+038
-1.0000000E+002
-1.0000000E+000
0.0000000E+000
--- 4 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 50, 3e38.
>> select * from t032t15 where a >= cast(-1e-76 as double precision);
A
---------------
0.0000000E+000
1.0000000E+000
5.0000000E+001
3.0000000E+038
--- 4 row(s) selected.
>>
>> reset envvar MDAM;
--- SQL operation complete.
>>
>> -- Drop tables.
>> drop table t032t15;
--- SQL operation complete.
>>
>>?section FLOAT64
>>-- Conversions to FLOAT64. =========================================================
>>
>> -- Create tables.
>>
>> -- Create and load t032t16.
>> create table t032t16 (a double precision not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t16 values (-4e70),(-100),(-1),(0),(1),(50),(4e70);
--- 7 row(s) inserted.
>>
>> -- Result should be 7 rows: -4e70, -100, -1, 0, 1, 50, 4e70.
>> select * from t032t16;
A
-------------------------
-4.00000000000000000E+070
-1.00000000000000000E+002
-1.00000000000000000E+000
0.00000000000000000E+000
1.00000000000000000E+000
5.00000000000000000E+001
4.00000000000000000E+070
--- 7 row(s) selected.
>>
>> -- Case: CONV_LARGEDEC_FLOAT64.
>>
>> -- Result should be 5 rows: -100, -1, 0, 1, 50.
>> -- Test commented out because of possible runtime problem. 9/7/97 -Fred TBD
>> -- select a from t032t16 where a <= 33000.0000001 * 1.000000000001
>> -- and a > -(15000.0000001 * 1.000000000001);
>>
>> -- Result should be 4 rows: -100, -1, 0, 1.
>> -- Test commented out because of possible runtime problem. 9/7/97 -Fred TBD
>> -- select a from t032t16 where a > -33000.0000001 * 1.000000000001
>> -- and a < 1.000000000001 * 1.000000000001;
>>
>> -- Drop tables.
>> drop table t032t16;
--- SQL operation complete.
>>
>>?section BPINTU
>>-- Conversions to BPINTU. =========================================================
>>#ifMX
>> -- Create tables.
>>
>> -- Create and load t032t17.
>> create table t032t17 (a bit precision integer (3) unsigned not null ,
+> primary key (a) );
--- SQL operation complete.
>> insert into t032t17 values (0),(1),(2),(3),(7);
--- 5 row(s) inserted.
>>
>> -- Result should be 5 rows: 0, 1, 2, 3, 7.
>> select * from t032t17;
A
-----
0
1
2
3
7
--- 5 row(s) selected.
>>
>> -- Case: CONV_FLOAT32_BPINTU.
>>
>> -- Result should be 3 rows: 0, 1, 2. Case #10-981208-4368.
>> select a from t032t17 where a < cast(2.1e0 as real);
A
-----
0
1
2
--- 3 row(s) selected.
>>
>> -- Case: CONV_FLOAT64_BPINTU.
>>
>> -- Result should be 0 rows. Case #10-981208-4368.
>> select a from t032t17 where a = cast(2.1e0 as double precision);
--- 0 row(s) selected.
>>
>> -- Result should be 4 rows: 0, 1, 2, 3. Case #10-981208-4368.
>> select a from t032t17 where a < cast(6.99999e0 as double precision);
A
-----
0
1
2
3
--- 4 row(s) selected.
>>
>> -- Drop tables.
>> drop table t032t17;
--- SQL operation complete.
>>#ifMX
>>
>>?section bugfix1
>>-- Case #10-981217-9077.
>>
>> -- Create tables.
>>
>> -- Create and load t032t18.
>> create table t032t18 (a numeric(9,3) not null , primary key (a) );
--- SQL operation complete.
>> insert into t032t18 values (-1),(0.1),(1),(2.001),(3);
--- 5 row(s) inserted.
>>
>> -- Result should be 5 rows: -1, .1, 1, 2.001, 3.
>> select * from t032t18;
A
------------
-1.000
.100
1.000
2.001
3.000
--- 5 row(s) selected.
>>
>> -- Result should be 3 rows: -1, .1, 1.
>> select a from t032t18 where a < cast(1.1e0 as real);
A
------------
-1.000
.100
1.000
--- 3 row(s) selected.
>>
>> -- Result should be 4 rows: -1, .1, 1, 2.001.
>> select a from t032t18 where a < cast(2.5e0 as double precision);
A
------------
-1.000
.100
1.000
2.001
--- 4 row(s) selected.
>>
>> -- Drop tables.
>> drop table t032t18;
--- SQL operation complete.
>>
>>?section terminate
>>
>>reset envvar NO_KEY_KLUDGE;
--- SQL operation complete.
>>exit;
End of MXCI Session