>>
>>-- add/enable support for syntax and functions
>>drop table if exists t032t1 cascade;

--- SQL operation complete.
>>
>>create table t032t1 (a int not null primary key, 
+>                     b byteint, 
+>                     c int not null enable,  
+>                     d date, e time, f timestamp,
+>                     g real);

--- SQL operation complete.
>>
>>invoke t032t1;

-- Definition of Trafodion table TRAFODION.SCH.T032T1
-- Definition current  Tue Jan 17 18:16:14 2017

  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                TINYINT DEFAULT NULL
  , C                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , D                                DATE DEFAULT NULL
  , E                                TIME(0) DEFAULT NULL
  , F                                TIMESTAMP(6) DEFAULT NULL
  , G                                REAL DEFAULT NULL
  )
  PRIMARY KEY (A ASC)

--- SQL operation complete.
>>
>>insert into t032t1 values (1, 2, 3, date '2016-08-15',  time '10:11:12',
+>                           timestamp '2016-08-15 10:11:12', 4e0);

--- 1 row(s) inserted.
>>insert into t032t1 values (2, 3, 3, date '2016-08-15',  time '10:11:12',
+>                           timestamp '2016-08-15 10:11:12', 4e0);

--- 1 row(s) inserted.
>>
>>select unique b from t032t1;

B   
----

   2
   3

--- 2 row(s) selected.
>>
>>select greatest(a, 10) from t032t1;

(EXPR)     
-----------

         10
         10

--- 2 row(s) selected.
>>select least(a, 10) from t032t1;

(EXPR)     
-----------

          1
          2

--- 2 row(s) selected.
>>
>>select ceil(g), ceiling(g) from t032t1;

(EXPR)                     (EXPR)                   
-------------------------  -------------------------

 4.00000000000000000E+000   4.00000000000000000E+000
 4.00000000000000000E+000   4.00000000000000000E+000

--- 2 row(s) selected.
>>
>>select months_between(d, date '2016-01-01') from t032t1;

(EXPR)               
---------------------

             7.322580
             7.322580

--- 2 row(s) selected.
>>select months_between(date '2016-01-01', d) from t032t1;

(EXPR)               
---------------------

            -7.322580
            -7.322580

--- 2 row(s) selected.
>>
>>select months_between(d, date '2016-01-15') from t032t1;

(EXPR)               
---------------------

             7.000000
             7.000000

--- 2 row(s) selected.
>>select months_between(date '2016-01-15', d) from t032t1;

(EXPR)               
---------------------

            -7.000000
            -7.000000

--- 2 row(s) selected.
>>
>>select last_day(d), last_day(f) from t032t1;

(EXPR)      (EXPR)                    
----------  --------------------------

2016-08-31  2016-08-31 10:11:12.000000
2016-08-31  2016-08-31 10:11:12.000000

--- 2 row(s) selected.
>>select next_day(d, 'sunday'), next_day(f, 'wednesday') from t032t1;

(EXPR)      (EXPR)                    
----------  --------------------------

2016-08-21  2016-08-17 10:11:12.000000
2016-08-21  2016-08-17 10:11:12.000000

--- 2 row(s) selected.
>>
>>select 'RANDOMVAL=' ||  trim(cast(rand() as varchar(20) not null)) from (values(1)) x(a);

(EXPR)                        
------------------------------

RANDOMVAL=1210881081          

--- 1 row(s) selected.
>>select random() from (values(1)) x(a);

(EXPR)    
----------

         1

--- 1 row(s) selected.
>>
>>select rand(100) from (values(1)) x(a);

(EXPR)    
----------

   1680700

--- 1 row(s) selected.
>>
>>select * from dual;

(EXPR)
------

     0

--- 1 row(s) selected.
>>
>>-- error 8413 enhancement
>>select cast('a' as int) from dual;

*** ERROR[8413] The string argument contains characters that cannot be converted. Source data(in hex): 61

--- 0 row(s) selected.
>>
>>-- group by, order by enhancements
>>select a aa from t032t1 group by aa;

AA         
-----------

          1
          2

--- 2 row(s) selected.
>>select a+1 from t032t1 group by a+1;

(EXPR)              
--------------------

                   2
                   3

--- 2 row(s) selected.
>>select a+1 from t032t1 order by a+1;

(EXPR)              
--------------------

                   2
                   3

--- 2 row(s) selected.
>>select a+1 aa from t032t1 order by a+1;

AA                  
--------------------

                   2
                   3

--- 2 row(s) selected.
>>select a+1 aa from t032t1 order by aa;

AA                  
--------------------

                   2
                   3

--- 2 row(s) selected.
>>select a+1 aa from t032t1 order by 1;

AA                  
--------------------

                   2
                   3

--- 2 row(s) selected.
>>select * from (select b from t032t1 order by b desc) x(z);

Z   
----

   2
   3

--- 2 row(s) selected.
>>select * from (select [first 1] a from t032t1) x(z);

Z          
-----------

          1

--- 1 row(s) selected.
>>select count(*) from t032t1 order by count(*);

(EXPR)              
--------------------

                   2

--- 1 row(s) selected.
>>select count(*) from t032t1 order by count(*) desc;

(EXPR)              
--------------------

                   2

--- 1 row(s) selected.
>>select count(*) cc from t032t1 order by count(*) desc;

CC                  
--------------------

                   2

--- 1 row(s) selected.
>>select count(distinct a) from t032t1 order by count(distinct a);

(EXPR)              
--------------------

                   2

--- 1 row(s) selected.
>>
>>-- error cases
>>select count(a) from t032t1 order by count(distinct a);

*** ERROR[4197] This expression cannot be used in the ORDER BY clause.

*** ERROR[8822] The statement was not prepared.

>>select sum(a), b from t032t1 group by sum(a), b;

*** ERROR[4197] This expression cannot be used in the GROUP BY clause.

*** ERROR[8822] The statement was not prepared.

>>select sum(a) from t032t1 group by 1;

*** ERROR[4185] Select list index is not allowed to be specified in the GROUP BY clause for this query.

*** ERROR[8822] The statement was not prepared.

>>
>>-- incompatible operations
>>select a from t032t1 where a = '2';

A          
-----------

          2

--- 1 row(s) selected.
>>select b from t032t1 where b = '2';

B   
----

   2

--- 1 row(s) selected.
>>select a + '1' from t032t1;

(EXPR)              
--------------------

                   2
                   3

--- 2 row(s) selected.
>>select a || '1' from t032t1;

(EXPR)      
------------

          11
          21

--- 2 row(s) selected.
>>insert into t032t1 values ('3', 3, 3, date '2016-08-15',  time '10:11:12',
+>                           timestamp '2016-08-15 10:11:12', 4e0);

--- 1 row(s) inserted.
>>select * from t032t1;

A            B     C            D           E         F                           G
-----------  ----  -----------  ----------  --------  --------------------------  ---------------

          1     2            3  2016-08-15  10:11:12  2016-08-15 10:11:12.000000   4.0000000E+000
          2     3            3  2016-08-15  10:11:12  2016-08-15 10:11:12.000000   4.0000000E+000
          3     3            3  2016-08-15  10:11:12  2016-08-15 10:11:12.000000   4.0000000E+000

--- 3 row(s) selected.
>>
>>select * from (values(1)) x(a) where current_date = cast(current_timestamp as date);

A   
----

   1

--- 1 row(s) selected.
>>select * from (values(1)) x(a) where current_timestamp(0) = cast(current_timestamp(6) as timestamp(0));

A   
----

   1

--- 1 row(s) selected.
>>
>>select cast(1e0 as interval year) from dual;

(EXPR)
------

     1

--- 1 row(s) selected.
>>
>>
>>-- auto create schema
>>
>>-- should fail, schema doesnt exist
>>create table t032sch.t032t2 (a int);

*** ERROR[1003] Schema TRAFODION.T032SCH does not exist.

--- SQL operation failed with errors.
>>
>>-- should succeed, schema will be automatically created
>>cqd traf_auto_create_schema 'ON';

--- SQL operation complete.
>>create table t032sch.t032t2 (a int);

--- SQL operation complete.
>>set schema t032sch;

--- SQL operation complete.
>>invoke t032t2;

-- Definition of Trafodion table TRAFODION.T032SCH.T032T2
-- Definition current  Tue Jan 17 18:16:25 2017

  (
    SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , A                                INT DEFAULT NULL
  )

--- SQL operation complete.
>>insert into t032t2 values (1);

--- 1 row(s) inserted.
>>
>>drop schema t032sch cascade;

--- SQL operation complete.
>>
>>log;
