Re: 'Clever' behaviour

Ken Miles (kenm@vcd.hp.com) wrote: : Which is to say "Remember your quotes or suffer the consequences!" I too : wish there were a way to control how Informix will try to assume what : we mean and let us define certain rules for implicit type conversion. We : have lost a lot of time chasing subtle bugs like the queries below. They : both parse just fine but they return different results. : select * from stores:orders where order_date = 07/24/1994 ; : select * from stores:orders where order_date = '07/24/1994'; : Ken Miles : kenm@vcd.hp.com : Opinions my own and all other standard disclaimers. My point entirely. Although we are unlikely to hit this bug most of the time with 4GL and ESQL applications, it does not take much to create a situation in which this bug could arise. What I would really like to know is exactly what the engine does when it attempts to resolve such a query. The amount of time taken appears to be longer than a sequential search of the database. Is it making an equivalent index as I assume? My actual index and query data is something like:- create table process ( bunch_num char(12), about 50 other irrelevant fields.. ) create index bunch_idx on process (bunch_num); The bunch_num field generally contains an eleven digit number with a leading zero. The leading zero is *very* important. (That's why it's a char field) Querying with:- select some fields from process where bunch_num=062113007942 takes *ages* adding quotes aroung the bunch_num argument is instant as one would expect. Mike.