Re: NVL changing data type of a datetime?

jbesson wrote: > Yup, that works. > > Trouble with using that is it dictates the format of any datetime > that's selected, so something like extend(current,year to day) will > have HH:MM:SS in it. > > That's bound to break our code somewhere and might cause more trouble > than NVL's misbehaviour. > > It's easy enough for me to code around the problem, my real issue is > that if a customer changes to IDS 9.40.UC7 then our software is likely > to break. Fixing up all the code will be a nightmare. Maybe we should > just tell them to avoid that version of Informix. I agree - setting GL_DATETIME is a cure that's worse than the disease; don't do it. It will screw up any datetime type that isn't a DATETIME YEAR TO SECOND. I'm not sure what happened, but the change of behaviour is suspicious. Have you checked the manual to see what the return type of NVL() is suposed to be? Is it the type of the first value, or the type of the value returned, or some composite type? ...the manual (version 10) says: NVL evaluates expression1. If expression1 is not NULL, then NVL returns the value of expression1. If expression1 is NULL, NVL returns the value of expression2. The expressions expression1 and expression2 can be of any data type, as long as they can be cast to a common compatible data type. The common type for the examples shown could legitimately include the 3 decimal places. The original example uses a column 'time' of type DATETIME YEAR TO SECOND: > select > time, > nvl(time,CURRENT), > nvl(time,CURRENT year to second), > nvl(time,extend('2005-01-01 00:00:00', year to fraction)) > from tbl; > > With IDS 9.40.UC7 that query gives me this: > > time 2005-01-01 00:00:00 > (expression) 2005-01-01 00:00:00.000 > (expression) 2005-01-01 00:00:00 > (expression) 2005-01-01 00:00:00.000 These values are consistent with the 'common compatible data type' criterion. The first value is to units SECOND(0); the second is to units SECOND(3) - oh, FRACTION(3) in the current versions of IDS - because unqualified CURRENT is equivalent to CURRENT YEAR TO FRACTION(3); the third to units SECOND; and the fourth is again to FRACTION(3). If the same verbiage appears in the 9.40 manual, then - if I'm interpreting it correctly - the 9.40.UC7 behaviour matches the manual and the older behaviour is erroneous. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

The 9.40 manual has exactly the same explanation of NVL, which is pretty vague, but I agree that the new 9.40 UC7 behaviour seems "more correct". I've played some more with datetimes and also char columns and it seems to me that the "old" behaviour of NVL with datetimes is anomolous. With chars, the data type returned is that of the longest argument, i.e. NVLing a char(3) and a char(5) always gives you a char(5) value - no matter which way round the arguments are and no matter which is null or not. This is consistent with all versions of Informix I've tried. On the other hand, with datetimes (for all versions I've tried apart from 9.40.UC7), the data type returned is that of the first argument. IDS 9.40.UC7 behaves the same way as it does for chars, i.e. it takes the higher precision data type. Although the 9.40 UC7 way seems better, it does mess up our code. I'm trying to get my hands on an installation of the latest 10.0 server to see if that has the new or old behaviour.

jbesson wrote: > The 9.40 manual has exactly the same explanation of NVL, which is > pretty vague, but I agree that the new 9.40 UC7 behaviour seems "more > correct". > > I've played some more with datetimes and also char columns and it seems > to me that the "old" behaviour of NVL with datetimes is anomolous. > > With chars, the data type returned is that of the longest argument, > i.e. NVLing a char(3) and a char(5) always gives you a char(5) value - > no matter which way round the arguments are and no matter which is null > or not. This is consistent with all versions of Informix I've tried. > > On the other hand, with datetimes (for all versions I've tried apart > from 9.40.UC7), the data type returned is that of the first argument. > IDS 9.40.UC7 behaves the same way as it does for chars, i.e. it takes > the higher precision data type. > > Although the 9.40 UC7 way seems better, it does mess up our code. I'm > trying to get my hands on an installation of the latest 10.0 server to > see if that has the new or old behaviour. If, as I would suspect, the Informix team deliberately fixed the behaviour of NVL in 9.40.UC7, we almost certainly either already did or very shortly will put the same fix into IDS 10.00. it would be sensible to assume that the writing is on the wall - and fix your code to use CURRENT YEAR TO SECOND instead of just CURRENT. You might postpone the pain - mostly by coincidence - but that's all. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

I just tried IDS 10.00.FC4 and it has the new behaviour. We'll just have to inspect and fix all our code that uses NVL on datetimes :( Thanks for your input Jonathan.