Bug in function index
I found a bug in informix function index which has been confirmed
in IDS 9.21.UC4 and IDS 9.40.UC1. I am assuming that it must be
existing in IDS 9.30 also.
This is the script to test the bug:-
=========================================
create database testbug ;
create function convt_dt(p_c char(10)) returning date with (not variant)define d date ;
let d = p_c ;
return d ;
end function ;
create function convt2_dt(p_c char(10),p_c2 char(10)) returning date with (not variant)define d date ;
let d = p_c ;
return d ;
end function ;
create table table1
(
fld1 char(10),
fld2 char(10),
fld3 char(10)
);
create table table2
(fld1 integer
);
create index jjj on table1 (convt_dt(fld1)) using btree ;
create index jkk on table1 (convt_dt(fld1),convt_dt(fld2)) using btree ;
create unique index rwet on table1 (convt2_dt(fld1,fld3) desc) using btree ;
create index dddd on table2 (fld1 desc) using btree ;============================================
Once the database is created, dbschema reports the schema as follows:-
I am removing all grant and revoke statements for brevity.
===============================================
DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.UC1
Copyright (C) Informix Software, Inc., 1984-1997
grant dba to "informix";
{ TABLE "informix".table1 row size = 30 number of columns = 3 index size = 15 }
create table "informix".table1
(
fld1 char(10),
fld2 char(10),
fld3 char(10)
);
revoke all on "informix".table1 from "public";{ TABLE "informix".table2 row size = 4 number of columns = 1 index size = 9 }
create table "informix".table2
(
fld1 integer
);
create function "informix".convt_dt(p_c char(10)) returning date with (not variant)
define d date ;
let d = p_c ;
return d ;
end function ;
create function "informix".convt2_dt(p_c char(10),p_c2 char(10)) returning date with (not variant)
define d date ;
let d = p_c ;
return d ;
end function ;
create index "informix".jjj on "informix".table1 (convt_dt(fld1))
using btree ;
create index "informix".jkk on "informix".table1 (convt_dt(fld1),
convt_dt(fld2)) using btree ;
create unique index "informix".rwet on "informix".table1 (convt2_dt(fld1
desc,fld3)) using btree ;
create index "informix".dddd on "informix".table2 (fld1 desc)
using btree ;
===================================================
Notice the difference in the index rwet.
The original statement was:-
create unique index rwet on table1 (convt2_dt(fld1,fld3) desc) using btree ;
dbschema reports it as:-
create unique index rwet on table1 (convt2_dt(fld1 desc,fld3)) using btree ;
It has put desc after fld1, instead of joint fld1,fld3. In fact this is
syntactically an incorrect statement. Attempt to recreate the index based
on this syntax will fail.
Ravi
On Mon, 11 Aug 2003 09:48:29 -0400, "rkusenet" <rkusenet@sympatico.ca> wrote: >I found a bug in informix function index which has been confirmed >in IDS 9.21.UC4 and IDS 9.40.UC1. I am assuming that it must be >existing in IDS 9.30 also. > Yes, it's there . . . .in 9.30.HC3 Looks like myschema is afflicted as well . . . missing the DESC keyword on the create for index 'rwet'.....
On Tue, 12 Aug 2003 10:38:07 -0400, John Carlson <john_carlson@whsmithusa.com> wrote: >On Mon, 11 Aug 2003 09:48:29 -0400, "rkusenet" <rkusenet@sympatico.ca> >wrote: > >>I found a bug in informix function index which has been confirmed >>in IDS 9.21.UC4 and IDS 9.40.UC1. I am assuming that it must be >>existing in IDS 9.30 also. >> > >Yes, it's there . . . .in 9.30.HC3 > >Looks like myschema is afflicted as well . . . missing the DESC >keyword on the create for index 'rwet'..... Myschema features version 6.21 -- source version 2.95.
On Tue, 12 Aug 2003 10:39:10 -0400, John Carlson <john_carlson@whsmithusa.com> wrote: >On Tue, 12 Aug 2003 10:38:07 -0400, John Carlson ><john_carlson@whsmithusa.com> wrote: > >>On Mon, 11 Aug 2003 09:48:29 -0400, "rkusenet" <rkusenet@sympatico.ca> >>wrote: >> >>>I found a bug in informix function index which has been confirmed >>>in IDS 9.21.UC4 and IDS 9.40.UC1. I am assuming that it must be >>>existing in IDS 9.30 also. >>> >> >>Yes, it's there . . . .in 9.30.HC3 >> >>Looks like myschema is afflicted as well . . . missing the DESC >>keyword on the create for index 'rwet'..... > >Myschema features version 6.21 -- source version 2.95. Ditto for features version 6.24 -- Source revision 2.115 (most recent version on IIUG website).