You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Weishiun Tsai (JIRA)" <ji...@apache.org> on 2017/01/04 17:55:58 UTC
[jira] [Closed] (TRAFODION-2165) Select min() returns wrong result
[ https://issues.apache.org/jira/browse/TRAFODION-2165?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Weishiun Tsai closed TRAFODION-2165.
------------------------------------
Close it as the problem is no longer reproducible. It's likely fixed by other fixes.
> 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)