Re: Bug in function index
Hi.
I've searched through our bug database and don't see one logged against
dbschema with the description that you've given. It is definitely a bug and
I can see where the code is generating the 'DESC' index option and it's not
right. If you had specified the default 'ASC' instead, then it would work
since the default is not generated.
I'm sure you know, that the workaround is to edit the incorrect dbschema
output.
thanks. davis.
"rkusenet"
<rkusenet@sympati To: informix-list@iiug.org
co.ca> cc:
Sent by: Subject: Bug in function index
owner-informix-li
st@iiug.org
08/11/2003 06:48
AM
Please respond to
"rkusenet"
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
sending to informix-list