| >> |
| >>-- 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 Wed Jun 13 23:15:19 2018 |
| |
| ( |
| 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=791360135 |
| |
| --- 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 |
| >>drop schema if exists t032sch cascade; |
| |
| --- SQL operation complete. |
| >> |
| >>-- 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 Wed Jun 13 23:15:37 2018 |
| |
| ( |
| SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE |
| , A INT DEFAULT NULL |
| ) |
| |
| --- SQL operation complete. |
| >>insert into t032t2 values (1); |
| |
| --- 1 row(s) inserted. |
| >> |
| >>-- auto NOT NULL attr for pkey columns |
| >>create schema if not exists t032sch; |
| |
| --- SQL operation complete. |
| >>set schema t032sch; |
| |
| --- SQL operation complete. |
| >>drop table if exists t032t1; |
| |
| --- SQL operation complete. |
| >>drop table if exists t032t2; |
| |
| --- SQL operation complete. |
| >>drop table if exists t032t3; |
| |
| --- SQL operation complete. |
| >>drop table if exists t032t4 cascade; |
| |
| --- SQL operation complete. |
| >>drop table if exists t032t5 cascade; |
| |
| --- SQL operation complete. |
| >> |
| >>-- NOT NULL attr for pkey columns |
| >>create table t032t1 (a int primary key); |
| |
| --- SQL operation complete. |
| >>showddl t032t1; |
| |
| CREATE TABLE TRAFODION.T032SCH.T032T1 |
| ( |
| A INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PRIMARY KEY (A ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| --- SQL operation complete. |
| >>drop table t032t1; |
| |
| --- SQL operation complete. |
| >>create table t032t1 (a int, primary key(a)); |
| |
| --- SQL operation complete. |
| >>showddl t032t1; |
| |
| CREATE TABLE TRAFODION.T032SCH.T032T1 |
| ( |
| A INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PRIMARY KEY (A ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| --- SQL operation complete. |
| >>drop table t032t1; |
| |
| --- SQL operation complete. |
| >>create table t032t1 (a int) primary key(a); |
| |
| --- SQL operation complete. |
| >>showddl t032t1; |
| |
| CREATE TABLE TRAFODION.T032SCH.T032T1 |
| ( |
| A INT NO DEFAULT NOT NULL NOT DROPPABLE |
| , PRIMARY KEY (A ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| --- SQL operation complete. |
| >>drop table t032t1; |
| |
| --- SQL operation complete. |
| >>create table t032t1 primary key(a) as select cast(1 as nullable) a from dual; |
| |
| --- 1 row(s) inserted. |
| >>showddl t032t1; |
| |
| CREATE TABLE TRAFODION.T032SCH.T032T1 |
| ( |
| A NUMERIC(1, 0) NO DEFAULT NOT NULL NOT |
| DROPPABLE |
| , PRIMARY KEY (A ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| --- SQL operation complete. |
| >> |
| >>-- no NOT NULL attr if nullable pkey is specified |
| >>create table t032t2 (a int constraint t2pk primary key nullable); |
| |
| --- SQL operation complete. |
| >>showddl t032t2; |
| |
| CREATE TABLE TRAFODION.T032SCH.T032T2 |
| ( |
| A INT DEFAULT NULL |
| , CONSTRAINT TRAFODION.T032SCH.T2PK PRIMARY KEY (A ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| --- SQL operation complete. |
| >>insert into t032t2 values (1), (null); |
| |
| --- 2 row(s) inserted. |
| >>select * from t032t2 order by a; |
| |
| A |
| ----------- |
| |
| 1 |
| ? |
| |
| --- 2 row(s) selected. |
| >>drop table t032t2; |
| |
| --- SQL operation complete. |
| >>create table t032t2 (a int, b int); |
| |
| --- SQL operation complete. |
| >>alter table t032t2 add constraint pkt2 primary key nullable (a); |
| |
| --- SQL operation complete. |
| >>showddl t032t2; |
| |
| CREATE TABLE TRAFODION.T032SCH.T032T2 |
| ( |
| A INT DEFAULT NULL |
| , B INT DEFAULT NULL |
| , CONSTRAINT TRAFODION.T032SCH.PKT2 PRIMARY KEY (A ASC) |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| --- SQL operation complete. |
| >>-- should return unique violation |
| >>insert into t032t2 values (null, 1), (null, 2); |
| |
| *** ERROR[8102] The operation is prevented by a unique constraint. |
| |
| --- 0 row(s) inserted. |
| >> |
| >>-- alter/add pkey on empty table |
| >>create table t032t3 (a int, b int not null); |
| |
| --- SQL operation complete. |
| >>alter table t032t3 add constraint pkt3 primary key(a); |
| |
| --- SQL operation complete. |
| >>invoke t032t3; |
| |
| -- Definition of Trafodion table TRAFODION.T032SCH.T032T3 |
| -- Definition current Wed Jun 13 23:17:19 2018 |
| |
| ( |
| A INT DEFAULT NULL NOT NULL NOT DROPPABLE |
| , B INT NO DEFAULT NOT NULL NOT DROPPABLE |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>-- alter/add pkey on non-empty table |
| >>create table t032t4 (a int, b int not null); |
| |
| --- SQL operation complete. |
| >>insert into t032t4 values (1,1); |
| |
| --- 1 row(s) inserted. |
| >>alter table t032t4 add constraint pkt4 primary key(a); |
| |
| --- SQL operation complete. |
| >>invoke t032t4; |
| |
| -- Definition of Trafodion table TRAFODION.T032SCH.T032T4 |
| -- Definition current Wed Jun 13 23:18:03 2018 |
| |
| ( |
| A INT DEFAULT NULL NOT NULL NOT DROPPABLE |
| , B INT NO DEFAULT NOT NULL NOT DROPPABLE |
| ) |
| PRIMARY KEY (A ASC) |
| |
| --- SQL operation complete. |
| >> |
| >>-- pkey not allowed with dependent objects |
| >>create table t032t5 (a int, b int); |
| |
| --- SQL operation complete. |
| >>create view v032t5 as select * from t032t5; |
| |
| --- SQL operation complete. |
| >>alter table t032t5 add constraint pkt5 primary key(a); |
| |
| *** ERROR[3242] This statement is not supported. Reason: Cannot alter/add primary key constraint on a table with dependencies. Drop all dependent objects (views, indexes, unique and referential constraints) on the specified table and recreate them after adding the primary key. |
| |
| --- SQL operation failed with errors. |
| >> |
| >>-- pkey created as unique constraint if cqd is set |
| >>cqd traf_alter_add_pkey_as_unique_constraint 'ON'; |
| |
| --- SQL operation complete. |
| >>alter table t032t5 add constraint pkt5 primary key(a); |
| |
| --- SQL operation complete. |
| >>showddl t032t5; |
| |
| CREATE TABLE TRAFODION.T032SCH.T032T5 |
| ( |
| A INT DEFAULT NULL |
| , B INT DEFAULT NULL |
| ) |
| ATTRIBUTES ALIGNED FORMAT |
| ; |
| |
| -- The following index is a system created index -- |
| CREATE UNIQUE INDEX PKT5 ON TRAFODION.T032SCH.T032T5 |
| ( |
| A ASC |
| ) |
| ; |
| |
| ALTER TABLE TRAFODION.T032SCH.T032T5 ADD CONSTRAINT TRAFODION.T032SCH.PKT5 |
| UNIQUE |
| ( |
| A |
| ) |
| ; |
| |
| --- SQL operation complete. |
| >> |
| >>drop schema t032sch cascade; |
| |
| --- SQL operation complete. |
| >> |
| >>log; |
