java/testing/org/apache/derbyTesting/functionTests/master/dml144.out

ij> AUTOCOMMIT OFF;
ij> -- MODULE  DML144  

-- SQL Test Suite, V6.0, Interactive SQL, dml144.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:0834 <length expression> (static)!

   CREATE TABLE GRUB (C1 VARCHAR (10));
0 rows inserted/updated/deleted
ij> -- PASS:0834 If table is created?

   COMMIT WORK;
ij> SELECT LENGTH (EMPNAME)
   FROM HU.STAFF WHERE GRADE = 10;
1          
-----------
20         
ij> -- PASS:0834 If 1 row selected and value is 20?

   SELECT LENGTH ('HI' || 'THERE')
   FROM HU.ECCO;
1          
-----------
7          
ij> -- PASS:0834 If 1 row selected and value is 7?

   INSERT INTO GRUB VALUES ('Hi  ');
1 row inserted/updated/deleted
ij> -- PASS:0834 If 1 row is inserted?

   SELECT LENGTH (C1)
   FROM GRUB;
1          
-----------
4          
ij> -- PASS:0834 If 1 row selected and value is 4?

-- following is not supported in derby
--   SELECT OCTET_LENGTH (C1)
--  FROM GRUB;
-- PASS:0834 If 1 row selected and value is > 2?

   UPDATE GRUB SET C1 = NULL;
1 row inserted/updated/deleted
ij> -- PASS:0834 If 1 row is updated?

   SELECT LENGTH (C1)
  FROM GRUB;
1          
-----------
NULL       
ij> -- PASS:0834 If 1 row selected and value is NULL?

-- following is not supported in derby
--   SELECT OCTET_LENGTH (C1)
--  FROM GRUB;
-- PASS:0834 If 1 row selected and value is NULL?

   ROLLBACK WORK;
ij> --O   DROP TABLE GRUB CASCADE;
   DROP TABLE GRUB ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0834 <<< END TEST

-- *********************************************

-- TEST:0835 <character substring function> (static)!

   CREATE TABLE MOREGRUB (C1 VARCHAR (10), ID INT);
0 rows inserted/updated/deleted
ij> -- PASS:0835 If table is created?

   COMMIT WORK;
ij> CREATE VIEW X4 (S1, S2, ID) AS
  SELECT SUBSTR (C1, 6),
         SUBSTR (C1, 2, 4), ID
  FROM MOREGRUB;
0 rows inserted/updated/deleted
ij> -- PASS:0835 If view is created?

   COMMIT WORK;
ij> SELECT SUBSTR (CITY, 4, 10)
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1         
----------
on        
ij> -- PASS:0835 If 1 row selected and value is 'on        '?

-- NOTE:0835 Right truncation subtest deleted.

   SELECT SUBSTR (CITY, 4, -1)
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1              
---------------
ERROR 22011: The second or third argument of the SUBSTR function is out of range.
ij> -- PASS:0835 If ERROR, substring error, 0 rows selected?

   SELECT SUBSTR (CITY, 0, 10)
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1         
----------
ERROR 22011: The second or third argument of the SUBSTR function is out of range.
ij> -- PASS:0835 If 1 row selected and value is 'Akron     '?

-- NOTE:0835 Host language variable subtest deleted.

   SELECT SUBSTR (CITY, 1, 1)
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1   
----
A   
ij> -- PASS:0835 If 1 row selected and value is 'A'?

   SELECT SUBSTR (CITY, 1, 0)
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1              
---------------
               
ij> -- PASS:0835 If 1 row selected and value is ''?

   SELECT SUBSTR (CITY, 12, 1)
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1   
----
    
ij> -- PASS:0835 If 1 row selected and value is ''?

   INSERT INTO MOREGRUB VALUES ('Pretzels', 1);
1 row inserted/updated/deleted
ij> -- PASS:0835 If 1 row is inserted?

   INSERT INTO MOREGRUB VALUES (NULL, 2);
1 row inserted/updated/deleted
ij> -- PASS:0835 If 1 row is inserted?

   INSERT INTO MOREGRUB VALUES ('Chips', 3);
1 row inserted/updated/deleted
ij> -- PASS:0835 If 1 row is inserted?

   SELECT S1 FROM X4 WHERE ID = 1;
S1        
----------
els       
ij> -- PASS:0835 If 1 row selected and S1 = 'els'?

   SELECT S1 FROM X4 WHERE ID = 3;
S1        
----------
          
ij> -- PASS:0835 If 1 row selected and S1 =  ''?

   SELECT S2 FROM X4 WHERE ID = 1;
S2  
----
retz
ij> -- PASS:0835 If 1 row selected and S2 = 'retz'?

   SELECT S2 FROM X4 WHERE ID = 3;
S2  
----
hips
ij> -- PASS:0835 If 1 row selected and S2 = 'hips'?

   SELECT SUBSTR (C1, ID)
  FROM MOREGRUB
  WHERE C1 LIKE 'Ch%';
1         
----------
ips       
ij> -- PASS:0835 If 1 row selected and value is 'ips'?

   SELECT SUBSTR (C1, 1, ID)
  FROM MOREGRUB
  WHERE C1 LIKE 'Ch%';
1         
----------
Chi       
ij> -- PASS:0835 If 1 row selected and value is 'Chi'?

-- NOTE:0835 Host language variable subtest deleted.

   SELECT S1 FROM X4 WHERE ID = 2;
S1        
----------
NULL      
ij> -- PASS:0835 If 1 row selected and S1 is NULL?

   DELETE FROM MOREGRUB;
3 rows inserted/updated/deleted
ij> INSERT INTO MOREGRUB VALUES ('Tacos', NULL);
1 row inserted/updated/deleted
ij> -- PASS:0835 If 1 row is inserted?

   SELECT SUBSTR (C1, 1, ID)
   FROM MOREGRUB;
1         
----------
NULL      
ij> -- PASS:0835 If 1 row selected and value is NULL?

   SELECT SUBSTR (C1, ID, 1)
   FROM MOREGRUB;
1   
----
NULL
ij> -- PASS:0835 If 1 row selected and value is NULL?

   UPDATE MOREGRUB SET C1 = NULL;
1 row inserted/updated/deleted
ij> SELECT SUBSTR (C1, ID, ID)
   FROM MOREGRUB;
1         
----------
NULL      
ij> -- PASS:0835 If 1 row selected and value is NULL?

   ROLLBACK WORK;
ij> --O   DROP TABLE MOREGRUB CASCADE;
   drop view x4;
0 rows inserted/updated/deleted
ij> DROP TABLE MOREGRUB ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0835 <<< END TEST

-- *********************************************

-- TEST:0839 Composed <length expression> and SUBSTR!

   SELECT LENGTH (SUBSTR
  (CITY, 4, 4))
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1          
-----------
4          
ij> -- PASS:0839 If 1 row selected and value is 4?

   SELECT LENGTH (SUBSTR
  (EMPNUM, 1))
  FROM HU.STAFF WHERE EMPNAME = 'Ed';
1          
-----------
3          
ij> -- PASS:0839 If 1 row selected and value is 3?

   COMMIT WORK;
ij> -- END TEST >>> 0839 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>