[ https://issues.apache.org/jira/browse/TRAFODION-2165?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Hans Zeller resolved TRAFODION-2165. ------------------------------------ Resolution: Resolved Fix Version/s: 2.1-incubating This problem does not happen anymore, but unfortunately I can't pinpoint where and how it got fixed. > 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 > Assignee: Hans Zeller > Fix For: 2.1-incubating > > > 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)