| ij> AUTOCOMMIT OFF; |
| ij> -- MODULE DML149 |
| |
| -- SQL Test Suite, V6.0, Interactive SQL, dml149.sql |
| -- 59-byte ID |
| -- TEd Version # |
| |
| -- AUTHORIZATION FLATER |
| set schema FLATER; |
| 0 rows inserted/updated/deleted |
| ij> --O SELECT USER FROM HU.ECCO; |
| VALUES USER; |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| FLATER |
| ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment |
| --O ROLLBACK WORK; |
| |
| -- date_time print |
| |
| -- TEST:0561 Double SET TRANSACTION! |
| |
| ROLLBACK WORK; |
| ij> -- PASS:0561 If successful completion? |
| |
| --O SET TRANSACTION READ ONLY; |
| -- PASS:0561 If successful completion? |
| |
| --O SET TRANSACTION READ WRITE; |
| -- PASS:0561 If successful completion? |
| |
| --O INSERT INTO USIG VALUES (10, 20); |
| -- PASS:0561 If 1 row is inserted? |
| |
| --O ROLLBACK WORK; |
| -- PASS:0561 If successful completion? |
| |
| --O SET TRANSACTION READ WRITE; |
| -- PASS:0561 If successful completion? |
| |
| --O SET TRANSACTION READ ONLY; |
| -- PASS:0561 If successful completion? |
| |
| --O INSERT INTO USIG VALUES (10, 20); |
| -- PASS:0561 If ERROR, invalid transaction state, 0 rows inserted? |
| |
| --O ROLLBACK WORK; |
| -- PASS:0561 If successful completion? |
| |
| -- END TEST >>> 0561 <<< END TEST |
| |
| -- ********************************************* |
| |
| -- TEST:0846 Feature 20, CAST functions (static) nits! |
| |
| CREATE TABLE NO_DUCK ( |
| GOOSE NUMERIC (4, 2), |
| ALBATROSS FLOAT, |
| SEAGULL INT, |
| OSPREY CHAR (10)); |
| 0 rows inserted/updated/deleted |
| ij> -- PASS:0846 If table is created? |
| |
| COMMIT WORK; |
| ij> -- NOTE:0846 CAST (100 AS NUMERIC (2)) loses the leading significant digit |
| SELECT CAST (100 AS NUMERIC (2)) |
| FROM HU.ECCO; |
| 1 |
| --- |
| ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(2,0). |
| ij> -- PASS:0846 If ERROR, numeric value out of range, 0 rows selected? |
| |
| SELECT CAST (100.5 AS DECIMAL (3)) |
| FROM HU.ECCO; |
| 1 |
| ---- |
| 100 |
| ij> -- PASS:0846 If 1 row selected and value is 100 or 101? |
| |
| INSERT INTO NO_DUCK VALUES ( |
| CAST (' 23.23 ' AS NUMERIC (4, 2)), 1.57E-1, -9, 'QUACK'); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0846 If 1 row is inserted? |
| |
| SELECT COUNT(*) |
| FROM NO_DUCK WHERE GOOSE = 23.23; |
| 1 |
| ----------- |
| 1 |
| ij> -- PASS:0846 If count = 1? |
| |
| -- No cast from CHAR to FLOAT allowed |
| -- DELETE FROM NO_DUCK |
| -- WHERE ALBATROSS - CAST (' 15.5E0 ' AS FLOAT) < 3E-1; |
| -- PASS:0846 If 1 row is deleted? |
| -- Instead to keep the rest of the test in sync we will: |
| DELETE FROM NO_DUCK |
| WHERE ALBATROSS - CAST (15.5E0 AS FLOAT) < 3E-1; |
| 1 row inserted/updated/deleted |
| ij> SELECT COUNT(*) FROM NO_DUCK; |
| 1 |
| ----------- |
| 0 |
| ij> -- PASS:0846 If count = 0? |
| |
| INSERT INTO NO_DUCK |
| --O SELECT 22.22, CAST (C1 AS FLOAT), 0, C1 FROM HU.ECCO; |
| SELECT 22.22, CAST (C1 AS FLOAT), 0, C1 FROM ECCO; |
| ERROR 42X05: Table/View 'ECCO' does not exist. |
| ij> -- PASS:0846 If ERROR, invalid character value for cast, 0 rows inserted? |
| |
| INSERT INTO NO_DUCK |
| SELECT 22.22, 2.222E1, CAST (C1 AS INT), 'QUACK!' FROM HU.ECCO; |
| ERROR 22018: Invalid character string format for type INTEGER. |
| ij> -- PASS:0846 If ERROR, invalid character value for cast, 0 rows inserted? |
| |
| SELECT CAST (CAST (3 AS DEC (5, 3)) AS CHAR (5)) |
| FROM HU.ECCO; |
| 1 |
| ----- |
| 3.000 |
| ij> -- PASS:0846 If 1 row selected and value is '3.000'? |
| |
| INSERT INTO NO_DUCK VALUES ( |
| 12.00, -10.5E0, 12, 'QUACK!'); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0846 If 1 row is inserted? |
| |
| UPDATE NO_DUCK |
| SET OSPREY = CAST (GOOSE AS CHAR (10)) |
| WHERE SEAGULL = CAST (GOOSE AS DEC); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0846 If 1 row is updated? |
| |
| SELECT OSPREY |
| FROM NO_DUCK; |
| OSPREY |
| ---------- |
| 12.00 |
| ij> -- PASS:0846 If 1 row selected and OSPREY = '12.00 '? |
| |
| SELECT OSPREY |
| FROM NO_DUCK |
| WHERE OSPREY < CAST (SEAGULL + 1 AS CHAR (10)) |
| AND OSPREY = CAST (GOOSE * 1 AS CHAR (10)); |
| OSPREY |
| ---------- |
| 12.00 |
| ij> -- PASS:0846 If 1 row selected and OSPREY = '12.00 '? |
| |
| UPDATE NO_DUCK |
| SET OSPREY = CAST (-SEAGULL AS CHAR (10)); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0846 If 1 row is updated? |
| |
| SELECT OSPREY |
| FROM NO_DUCK; |
| OSPREY |
| ---------- |
| -12 |
| ij> -- PASS:0846 If 1 row selected and OSPREY = '-12 '? |
| |
| -- NOTE:0846 Expected value -12.00 is too long for CHAR (5) cast |
| SELECT CAST (-GOOSE AS CHAR (5)) |
| FROM NO_DUCK; |
| 1 |
| ----- |
| -12.0 |
| WARNING 01004: Data truncation |
| ij> -- PASS:0846 If ERROR, string data, right truncation, 0 rows selected? |
| |
| UPDATE NO_DUCK |
| SET ALBATROSS = 0.0; |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0846 If 1 row is updated? |
| |
| SELECT CAST (-ALBATROSS AS CHAR (5)) |
| FROM NO_DUCK; |
| ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'. |
| ij> -- PASS:0846 If 1 row selected and value is '0E0 '? |
| |
| SELECT CAST (0230E-1 AS CHAR (10)) |
| FROM HU.ECCO; |
| ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'. |
| ij> -- PASS:0846 If 1 row selected and value is '2.3E1 '? |
| |
| SELECT CAST (0230E+1 AS CHAR (10)) |
| FROM HU.ECCO; |
| ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'. |
| ij> -- PASS:0846 If 1 row selected and value is '2.3E3 '? |
| |
| DELETE FROM NO_DUCK; |
| 1 row inserted/updated/deleted |
| ij> INSERT INTO NO_DUCK VALUES ( |
| 0.00, -10.5E0, -0, 'QUACK!'); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0846 If 1 row is inserted? |
| |
| UPDATE NO_DUCK |
| SET OSPREY = CAST (ALBATROSS AS CHAR (10)) |
| WHERE GOOSE = CAST (SEAGULL AS NUMERIC (2)); |
| ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'. |
| ij> -- PASS:0846 If 1 row is updated? |
| |
| SELECT OSPREY |
| FROM NO_DUCK; |
| OSPREY |
| ---------- |
| QUACK! |
| ij> -- PASS:0846 If 1 row selected and OSPREY = '-1.05E1 '? |
| |
| UPDATE NO_DUCK SET ALBATROSS = -0.5; |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0846 If 1 row is updated? |
| |
| UPDATE NO_DUCK |
| SET OSPREY = CAST (ALBATROSS AS CHAR (10)); |
| ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'. |
| ij> -- PASS:0846 If 1 row is updated? |
| |
| SELECT OSPREY |
| FROM NO_DUCK; |
| OSPREY |
| ---------- |
| QUACK! |
| ij> -- PASS:0846 If 1 row selected and OSPREY = '-5E-1 '? |
| |
| UPDATE NO_DUCK |
| SET OSPREY = CAST (-ALBATROSS AS CHAR (10)); |
| ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'. |
| ij> -- PASS:0846 If 1 row is updated? |
| |
| SELECT OSPREY |
| FROM NO_DUCK; |
| OSPREY |
| ---------- |
| QUACK! |
| ij> -- PASS:0846 If 1 row selected and OSPREY = '5E-1 '? |
| |
| -- NOTE:0846 Expected value -5E-1 is too long for CHAR (4) cast |
| SELECT CAST (ALBATROSS AS CHAR (4)) |
| FROM NO_DUCK; |
| ERROR 42846: Cannot convert types 'DOUBLE' to 'CHAR'. |
| ij> -- PASS:0846 If ERROR, string data, right truncation, 0 rows selected? |
| |
| SELECT CAST (NULL AS CHAR (10)), GOOSE FROM NO_DUCK |
| WHERE SEAGULL = 0 |
| UNION |
| SELECT OSPREY, CAST (SEAGULL AS NUMERIC (4, 2)) FROM NO_DUCK |
| WHERE GOOSE > 10000; |
| 1 |2 |
| ------------------ |
| NULL |0.00 |
| ij> -- PASS:0846 If 1 row selected and first value is NULL? |
| |
| UPDATE NO_DUCK SET GOOSE = |
| CAST (NULL AS NUMERIC (2, 2)); |
| 1 row inserted/updated/deleted |
| ij> -- PASS:0846 If 1 row is updated? |
| |
| SELECT COUNT(*) |
| FROM NO_DUCK WHERE GOOSE IS NULL; |
| 1 |
| ----------- |
| 1 |
| ij> -- PASS:0846 If count = 1? |
| |
| SELECT CAST (GOOSE AS INT) |
| FROM NO_DUCK; |
| 1 |
| ----------- |
| NULL |
| ij> -- PASS:0846 If 1 row selected and value is NULL? |
| |
| ROLLBACK WORK; |
| ij> --O DROP TABLE NO_DUCK CASCADE; |
| DROP TABLE NO_DUCK ; |
| 0 rows inserted/updated/deleted |
| ij> COMMIT WORK; |
| ij> -- END TEST >>> 0846 <<< END TEST |
| |
| -- *************************************************////END-OF-MODULE |
| ; |
| ij> |