Weishiun Tsai created TRAFODION-2165:
----------------------------------------
Summary: Select min() returns wrong result
Key: TRAFODION-2165
URL: https://issues.apache.org/jira/browse/TRAFODION-2165
Project: Apache Trafodion
Issue Type: Bug
Components: sql-exe
Affects Versions: 2.1-incubating
Reporter: Weishiun Tsai
As shown below, the following sequence of statements ends with a select min()
query. It should have returned 10, as the query returns 13 rows with the same
u1 values of 10 without min(). But it returns 0 right now.
>>create schema mytest2;
--- SQL operation complete.
>>set schema mytest2;
--- SQL operation complete.
>>
>>create table OPTABLE
+>( p1 largeint not null
+>, u1 smallint unsigned
+>, zi1 smallint not null
+>, f1 double precision
+>, n1 numeric (4,2) unsigned
+>, d1 decimal (4,2)
+>, t1 date
+>, c1 char
+>, p2 integer not null
+>, u2 integer unsigned
+>, zi2 integer not null
+>, f2 real
+>, n2 numeric (6,3) unsigned
+>, d2 decimal (6,3)
+>, t2 time
+>, c2 char(2)
+>, p3 smallint not null
+>, u3 largeint
+>, zi3 largeint not null
+>, f3 float
+>, n3 numeric (12,4)
+>, d3 decimal (12,4)
+>, t3 interval hour to second
+>, c3 char(3)
+>, z char (10)
+>, primary key (p1, p2, p3) )
+>;
--- SQL operation complete.
>>
>>insert into OPTABLE values (
+>10, 10, 10, 10, 10, 10, date '1959-12-31', 'a' ,
+>9, 9, 9, 9, 9, 9, time '23:59:59', 'aa',
+>9,null, -1,null,null,null, null, null, null
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
+>10, 10, 10, 10, 10, 10, time '00:00:00', 'aa' ,
+>10, 10, 10, 10, 10, 10, interval '00:00:00' hour to second, 'aaa', 'Row01'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
+>10, 10, 10, 10, 10, 10, time '00:00:15', 'aa' ,
+>20, 20, 20, 20, 20, 20, interval '00:00:15' hour to second, 'aab', 'Row02'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
+>10, 10, 10, 10, 10, 10, time '00:00:30', 'aa' ,
+>30, 30, 30, 30, 30, 30, interval '00:00:30' hour to second, 'aac', 'Row03'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
+>20, 20, 20, 20, 20, 20, time '00:00:45', 'ab' ,
+>10, 10, 10, 10, 10, 10, interval '00:00:45' hour to second, 'aba', 'Row04'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
+>20, 20, 20, 20, 20, 20, time '00:01:00', 'ab' ,
+>20, 20, 20, 20, 20, 20, interval '00:01:00' hour to second, 'abb', 'Row05'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
+>20, 20, 20, 20, 20, 20, time '00:01:15', 'ab' ,
+>30, 30, 30, 30, 30, 30, interval '00:01:15' hour to second, 'abc', 'Row06'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
+>30, 30, 30, 30, 30, 30, time '00:01:30', 'ac' ,
+>10, 10, 10, 10, 10, 10, interval '00:01:30' hour to second, 'aca', 'Row07'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
+>30, 30, 30, 30, 30, 30, time '00:01:45', 'ac' ,
+>20, 20, 20, 20, 20, 20, interval '00:01:45' hour to second, 'acb', 'Row08'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
+>30, 30, 30, 30, 30, 30, time '00:02:00', 'ac' ,
+>30, 30, 30, 30, 30, 30, null, 'acc', 'Row09'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
+>10, 10, 10, 10, 10, 10, null, 'ba' ,
+>10, 10, 10, 10, 10, 10, interval '00:02:00' hour to second, 'baa', 'Row10'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
+>10, 10, 10, 10, 10, 10, time '00:59:00', 'ba' ,
+>20, 20, 20, 20, 20, 20, interval '00:59:00' hour to second, 'bab', 'Row11'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
+>10, 10, 10, 10, 10, 10, time '00:59:15', 'ba' ,
+>30, 30, 30, 30, 30, 30, interval '00:59:15' hour to second, 'bac', 'Row12'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
+>20, 20, 20, 20, 20, 20, time '00:59:30', 'bb' ,
+>10, 10, 10, 10, 10, 10, interval '00:59:30' hour to second, 'bba', 'Row13'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
+>20, 20, 20, 20, 20, 20, time '00:59:45', 'bb' ,
+>20, 20, 20, 20, 20, 20, interval '00:59:45' hour to second, 'bbb', 'Row14'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
+>20, 20, 20, 20, 20, 20, time '01:00:00', 'bb' ,
+>30, 30, 30, 30, 30, 30, interval '01:00:00' hour to second, 'bbc', 'Row15'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
+>30, 30, 30, 30, 30, 30, time '01:00:00', 'bc' ,
+>10, 10, 10, 10, 10, 10, null, 'bca', 'Row16'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
+>30, 30, 30, 30, 30, 30, time '01:00:15', 'bc' ,
+>20, 20, 20, 20, 20, 20, interval '01:00:15' hour to second, 'bcb', 'Row17'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
+>30, 30, 30, 30, 30, 30, time '01:00:30', 'bc' ,
+>30, 30, 30, 30, 30, 30, interval '01:00:30' hour to second, 'bcc', 'Row18'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
+>10, 10, 10, 10, 10, 10, time '01:00:45', 'ca' ,
+>10, 10, 10, 10, 10, 10, interval '01:00:45' hour to second, 'caa', 'Row19'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
+>10, 10, 10, 10, 10, 10, null, 'ca' ,
+>20, 20, 20, 20, 20, 20, interval '01:01:00' hour to second, 'cab', 'Row20'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
+>10, 10, 10, 10, 10, 10, time '01:59:00', 'ca' ,
+>30, 30, 30, 30, 30, 30, interval '01:59:00' hour to second, 'cac', 'Row21'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
+>20, 20, 20, 20, 20, 20, time '01:59:15', 'cb' ,
+>10, 10, 10, 10, 10, 10, interval '01:59:15' hour to second, 'cba', 'Row22'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
+>20, 20, 20, 20, 20, 20, time '01:59:30', 'cb' ,
+>20, 20, 20, 20, 20, 20, interval '01:59:30' hour to second, 'cbb', 'Row23'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
+>20, 20, 20, 20, 20, 20, time '01:59:45', 'cb' ,
+>30, 30, 30, 30, 30, 30, interval '01:59:45' hour to second, 'cbc', 'Row24'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
+>30, 30, 30, 30, 30, 30, time '02:00:00', 'cc' ,
+>10, 10, 10, 10, 10, 10, interval '02:00:00' hour to second, 'cca', 'Row25'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
+>30, 30, 30, 30, 30, 30, time '11:59:00', 'cc' ,
+>20, 20, 20, 20, 20, 20, interval '11:59:00' hour to second, 'ccb', 'Row26'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
+>30, 30, 30, 30, 30, 30, time '11:59:15', 'cc' ,
+>30, 30, 30, 30, 30, 30, interval '11:59:15' hour to second, 'ccc', 'Row27'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>30, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
+>30, 10, 10, 10, 10, 10, time '11:59:30', 'ac' ,
+>40,null, -1,null,null,null, interval '11:59:30' hour to second, 'aca',
'Row28'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>30, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
+>40,null, -1,null,null,null, time '11:59:45', 'aa' ,
+>30, 10, 10, 10, 10, 10, interval '11:59:45' hour to second, null, 'Row29'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>30, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
+>40,null, -1,null,null,null, time '12:00:00', null ,
+>40,null, -1,null,null,null, interval '12:00:00' hour to second, 'aaa',
'Row30'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>40,null, -1,null,null,null, date '1960-01-01', null ,
+>30, 10, 10, 10, 10, 10, time '12:00:00', 'aa' ,
+>30, 10, 10, 10, 10, 10, interval '12:00:00' hour to second, 'aaa', 'Row31'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>40,null, -2,null,null,null, date '1960-01-01', 'b' ,
+>30, 10, 10, 10, 10, 10, time '23:59:15', 'bb' ,
+>40, 10, 10, 10, 10, 10, interval '23:59:15' hour to second, null , 'Row32'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>40,null, -1,null,null,null, date '1960-01-01', 'b' ,
+>40, 10, 10, 10, 10, 10, time '23:59:30', null ,
+>30,null, -1,null,null,null, interval '23:59:30' hour to second, 'bbb',
'Row33'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>40,null, -1,null,null,null, date '1960-01-01', null ,
+>40,null, -1,null,null,null, time '23:59:45', 'bb' ,
+>40, 10, 10, 10, 10, 10, interval '23:59:45' hour to second, 'bbb', 'Row34'
+>);
--- 1 row(s) inserted.
>>
>>insert into OPTABLE values (
+>40,null, -1,null,null,null, date '1960-01-02', 'c' ,
+>40,null, -1,null,null,null, time '00:00:00', 'cc' ,
+>50,null, -1,null,null,null, interval '24:00:00' hour to second, null ,
'Row35'
+>);
--- 1 row(s) inserted.
>>
>>
>>update statistics for table OPTABLE on every column;
--- SQL operation complete.
>>create index iuaaa on OPTABLE(U1 asc, u2 asc, u3 asc);
--- SQL operation complete.
>>
>>select count(*) from OPTABLE where u1=10;
(EXPR)
--------------------
13
--- 1 row(s) selected.
>>select u1 from OPTABLE where u1=10;
U1
-----
10
10
10
10
10
10
10
10
10
10
10
10
10
--- 13 row(s) selected.
>>
>>set parserflags 1;
--- SQL operation complete.
>>select * from table(index_table iuaaa) order by 1;
U1@ U2@ U3@ P1 P2 P3
----- ---------- -------------------- -------------------- ----------- ------
10 9 ? 10 9 9
10 10 10 10 10 10
10 10 20 10 10 20
10 10 30 10 10 30
10 10 ? 30 30 40
10 20 10 10 20 10
10 20 20 10 20 20
10 20 30 10 20 30
10 30 10 10 30 10
10 30 20 10 30 20
10 30 30 10 30 30
10 ? 10 30 40 30
10 ? ? 30 40 40
20 10 10 20 10 10
20 10 20 20 10 20
20 10 30 20 10 30
20 20 10 20 20 10
20 20 20 20 20 20
20 20 30 20 20 30
20 30 10 20 30 10
20 30 20 20 30 20
20 30 30 20 30 30
30 10 10 30 10 10
30 10 20 30 10 20
30 10 30 30 10 30
30 20 10 30 20 10
30 20 20 30 20 20
30 20 30 30 20 30
30 30 10 30 30 10
30 30 20 30 30 20
30 30 30 30 30 30
? 10 10 40 30 30
? 10 10 40 30 40
? 10 ? 40 40 30
? ? 10 40 40 40
? ? ? 40 40 50
--- 36 row(s) selected.
>>
>>prepare x from select min(u1) from OPTABLE where u1=10;
--- SQL command prepared.
>>explain options 'f' x;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
3 . 4 root 1.00E+000
2 . 3 shortcut_scalar_aggr 1.00E+000
1 . 2 firstn 1.00E+000
. . 1 trafodion_index_scan IUAAA 1.00E+001
--- SQL operation complete.
>>execute x;
(EXPR)
------
0
--- 1 row(s) selected.
>>
>>drop schema mytest2 cascade;
--- SQL operation complete.
==================================================
Steps To Reproduce:
create schema mytest2;
set schema mytest2;
create table OPTABLE
( p1 largeint not null
, u1 smallint unsigned
, zi1 smallint not null
, f1 double precision
, n1 numeric (4,2) unsigned
, d1 decimal (4,2)
, t1 date
, c1 char
, p2 integer not null
, u2 integer unsigned
, zi2 integer not null
, f2 real
, n2 numeric (6,3) unsigned
, d2 decimal (6,3)
, t2 time
, c2 char(2)
, p3 smallint not null
, u3 largeint
, zi3 largeint not null
, f3 float
, n3 numeric (12,4)
, d3 decimal (12,4)
, t3 interval hour to second
, c3 char(3)
, z char (10)
, primary key (p1, p2, p3) )
;
insert into OPTABLE values (
10, 10, 10, 10, 10, 10, date '1959-12-31', 'a' ,
9, 9, 9, 9, 9, 9, time '23:59:59', 'aa',
9,null, -1,null,null,null, null, null, null
);
insert into OPTABLE values (
10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
10, 10, 10, 10, 10, 10, time '00:00:00', 'aa' ,
10, 10, 10, 10, 10, 10, interval '00:00:00' hour to second, 'aaa', 'Row01'
);
insert into OPTABLE values (
10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
10, 10, 10, 10, 10, 10, time '00:00:15', 'aa' ,
20, 20, 20, 20, 20, 20, interval '00:00:15' hour to second, 'aab', 'Row02'
);
insert into OPTABLE values (
10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
10, 10, 10, 10, 10, 10, time '00:00:30', 'aa' ,
30, 30, 30, 30, 30, 30, interval '00:00:30' hour to second, 'aac', 'Row03'
);
insert into OPTABLE values (
10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
20, 20, 20, 20, 20, 20, time '00:00:45', 'ab' ,
10, 10, 10, 10, 10, 10, interval '00:00:45' hour to second, 'aba', 'Row04'
);
insert into OPTABLE values (
10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
20, 20, 20, 20, 20, 20, time '00:01:00', 'ab' ,
20, 20, 20, 20, 20, 20, interval '00:01:00' hour to second, 'abb', 'Row05'
);
insert into OPTABLE values (
10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
20, 20, 20, 20, 20, 20, time '00:01:15', 'ab' ,
30, 30, 30, 30, 30, 30, interval '00:01:15' hour to second, 'abc', 'Row06'
);
insert into OPTABLE values (
10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
30, 30, 30, 30, 30, 30, time '00:01:30', 'ac' ,
10, 10, 10, 10, 10, 10, interval '00:01:30' hour to second, 'aca', 'Row07'
);
insert into OPTABLE values (
10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
30, 30, 30, 30, 30, 30, time '00:01:45', 'ac' ,
20, 20, 20, 20, 20, 20, interval '00:01:45' hour to second, 'acb', 'Row08'
);
insert into OPTABLE values (
10, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
30, 30, 30, 30, 30, 30, time '00:02:00', 'ac' ,
30, 30, 30, 30, 30, 30, null, 'acc', 'Row09'
);
insert into OPTABLE values (
20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
10, 10, 10, 10, 10, 10, null, 'ba' ,
10, 10, 10, 10, 10, 10, interval '00:02:00' hour to second, 'baa', 'Row10'
);
insert into OPTABLE values (
20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
10, 10, 10, 10, 10, 10, time '00:59:00', 'ba' ,
20, 20, 20, 20, 20, 20, interval '00:59:00' hour to second, 'bab', 'Row11'
);
insert into OPTABLE values (
20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
10, 10, 10, 10, 10, 10, time '00:59:15', 'ba' ,
30, 30, 30, 30, 30, 30, interval '00:59:15' hour to second, 'bac', 'Row12'
);
insert into OPTABLE values (
20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
20, 20, 20, 20, 20, 20, time '00:59:30', 'bb' ,
10, 10, 10, 10, 10, 10, interval '00:59:30' hour to second, 'bba', 'Row13'
);
insert into OPTABLE values (
20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
20, 20, 20, 20, 20, 20, time '00:59:45', 'bb' ,
20, 20, 20, 20, 20, 20, interval '00:59:45' hour to second, 'bbb', 'Row14'
);
insert into OPTABLE values (
20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
20, 20, 20, 20, 20, 20, time '01:00:00', 'bb' ,
30, 30, 30, 30, 30, 30, interval '01:00:00' hour to second, 'bbc', 'Row15'
);
insert into OPTABLE values (
20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
30, 30, 30, 30, 30, 30, time '01:00:00', 'bc' ,
10, 10, 10, 10, 10, 10, null, 'bca', 'Row16'
);
insert into OPTABLE values (
20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
30, 30, 30, 30, 30, 30, time '01:00:15', 'bc' ,
20, 20, 20, 20, 20, 20, interval '01:00:15' hour to second, 'bcb', 'Row17'
);
insert into OPTABLE values (
20, 20, 20, 20, 20, 20, date '1960-01-01', 'b' ,
30, 30, 30, 30, 30, 30, time '01:00:30', 'bc' ,
30, 30, 30, 30, 30, 30, interval '01:00:30' hour to second, 'bcc', 'Row18'
);
insert into OPTABLE values (
30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
10, 10, 10, 10, 10, 10, time '01:00:45', 'ca' ,
10, 10, 10, 10, 10, 10, interval '01:00:45' hour to second, 'caa', 'Row19'
);
insert into OPTABLE values (
30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
10, 10, 10, 10, 10, 10, null, 'ca' ,
20, 20, 20, 20, 20, 20, interval '01:01:00' hour to second, 'cab', 'Row20'
);
insert into OPTABLE values (
30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
10, 10, 10, 10, 10, 10, time '01:59:00', 'ca' ,
30, 30, 30, 30, 30, 30, interval '01:59:00' hour to second, 'cac', 'Row21'
);
insert into OPTABLE values (
30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
20, 20, 20, 20, 20, 20, time '01:59:15', 'cb' ,
10, 10, 10, 10, 10, 10, interval '01:59:15' hour to second, 'cba', 'Row22'
);
insert into OPTABLE values (
30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
20, 20, 20, 20, 20, 20, time '01:59:30', 'cb' ,
20, 20, 20, 20, 20, 20, interval '01:59:30' hour to second, 'cbb', 'Row23'
);
insert into OPTABLE values (
30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
20, 20, 20, 20, 20, 20, time '01:59:45', 'cb' ,
30, 30, 30, 30, 30, 30, interval '01:59:45' hour to second, 'cbc', 'Row24'
);
insert into OPTABLE values (
30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
30, 30, 30, 30, 30, 30, time '02:00:00', 'cc' ,
10, 10, 10, 10, 10, 10, interval '02:00:00' hour to second, 'cca', 'Row25'
);
insert into OPTABLE values (
30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
30, 30, 30, 30, 30, 30, time '11:59:00', 'cc' ,
20, 20, 20, 20, 20, 20, interval '11:59:00' hour to second, 'ccb', 'Row26'
);
insert into OPTABLE values (
30, 30, 30, 30, 30, 30, date '1960-01-01', 'c' ,
30, 30, 30, 30, 30, 30, time '11:59:15', 'cc' ,
30, 30, 30, 30, 30, 30, interval '11:59:15' hour to second, 'ccc', 'Row27'
);
insert into OPTABLE values (
30, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
30, 10, 10, 10, 10, 10, time '11:59:30', 'ac' ,
40,null, -1,null,null,null, interval '11:59:30' hour to second, 'aca', 'Row28'
);
insert into OPTABLE values (
30, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
40,null, -1,null,null,null, time '11:59:45', 'aa' ,
30, 10, 10, 10, 10, 10, interval '11:59:45' hour to second, null, 'Row29'
);
insert into OPTABLE values (
30, 10, 10, 10, 10, 10, date '1960-01-01', 'a' ,
40,null, -1,null,null,null, time '12:00:00', null ,
40,null, -1,null,null,null, interval '12:00:00' hour to second, 'aaa', 'Row30'
);
insert into OPTABLE values (
40,null, -1,null,null,null, date '1960-01-01', null ,
30, 10, 10, 10, 10, 10, time '12:00:00', 'aa' ,
30, 10, 10, 10, 10, 10, interval '12:00:00' hour to second, 'aaa', 'Row31'
);
insert into OPTABLE values (
40,null, -2,null,null,null, date '1960-01-01', 'b' ,
30, 10, 10, 10, 10, 10, time '23:59:15', 'bb' ,
40, 10, 10, 10, 10, 10, interval '23:59:15' hour to second, null , 'Row32'
);
insert into OPTABLE values (
40,null, -1,null,null,null, date '1960-01-01', 'b' ,
40, 10, 10, 10, 10, 10, time '23:59:30', null ,
30,null, -1,null,null,null, interval '23:59:30' hour to second, 'bbb', 'Row33'
);
insert into OPTABLE values (
40,null, -1,null,null,null, date '1960-01-01', null ,
40,null, -1,null,null,null, time '23:59:45', 'bb' ,
40, 10, 10, 10, 10, 10, interval '23:59:45' hour to second, 'bbb', 'Row34'
);
insert into OPTABLE values (
40,null, -1,null,null,null, date '1960-01-02', 'c' ,
40,null, -1,null,null,null, time '00:00:00', 'cc' ,
50,null, -1,null,null,null, interval '24:00:00' hour to second, null , 'Row35'
);
update statistics for table OPTABLE on every column;
create index iuaaa on OPTABLE(U1 asc, u2 asc, u3 asc);
select count(*) from OPTABLE where u1=10;
select u1 from OPTABLE where u1=10;
set parserflags 1;
select * from table(index_table iuaaa) order by 1;
prepare x from select min(u1) from OPTABLE where u1=10;
explain options 'f' x;
execute x;
drop schema mytest2 cascade;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
