Hi All,

For those of you who do not know, the coersion rules in Ingres were
changed in 9.2.0. with the (unexpected?) result that certain queries
now produce different results over previous versions of Ingres!

Here are a couple of examples for you to try:-

Example 1.

create table test_table
(
line integer4 not null with default,
random_date date not null with default
);
\p\g
insert into test_table values (1,'01-feb-2011');
insert into test_table values (2,'31-dec-2011');
insert into test_table values (3,'31-oct-2009');
\p\g
select min(ifnull(random_date, '')) as first_date,
ifnull(min(random_date), '') as second_date,
min(random_date) as third_date
from test_table;
\p\g
drop table test_table;
\p\g
commit;
\p\g


Example 2.

declare global temporary table session.testing(a integer) on commit
preserve rows with norecovery;
\p\g
insert into session.testing select 1 union select '1';
\p\g
select * from session.testing;
\p\g
insert into session.testing select 1 union select ''; \p\g select *
from session.testing;
\p\g


We have an Issue open with Ingres Corp. and they have logged Bug
122585.
Its got them scratching their heads for sure.

My questions to the group are:-

1. Who else has come across this problem? If so, please can you share
your examples?
2. What do you plan to do about it?
3. What should Ingres do to fix it?

On Sep 24, 2009, at 9:23 AM, rthdavid wrote:
Put it back the way it was. There's a debate running but I'm firmly
on the
side of reverting it to the old way.

The SQL Standard actually says that in a CASE-like expression, if any
expression is a string type, the result should be a string type.
COALESCE
and NULLIF are defined in terms of the CASE expression, and IFNULL
can be considered as a 2-expression form of COALESCE. However!
a) that's not how IFNULL is actually implemented, and b) that's not how
it used to work. It used to apply a type hierarchy that had dates
higher than
strings, so ifnull(date,string) was date.

Since IFNULL is not a standard function, I'm arguing that it should
revert
to the traditional way. CASE-based functions can operate according
to the standard. (I think the Standard's type hierarchy is blazingly
stupid,
but there you go.)

Karl