[Info-Ingres] Friday fun. Strange Date field group by beha..

I've been working with Ingres support on an interesting problem this
week. The answer suprised me.

This query produced extra rows in the testing environment. The same data
had no problem in production.
(The original query is of course much more complex)

SELECT
best_before = date_gmt(pd.best_before),
on_hand_cartons = SUM(pd.orig_pallet_units)
FROM pallet_detail pd
WHERE pd.pallet_no IN ( 566547, 566587)
AND pd.catlog_code = '03702016'
GROUP BY pd.best_before


Production
best_before on_hand_cartons
2008_12_31 00:00:00 GMT | 100

Test
best_before on_hand_cartons
2008_12_31 00:00:00 GMT 50
2008_12_31 00:00:00 GMT 50


Both test and production are running the same version with some
differences in configuration.
The problem occurs only when OPF_HASH_JOIN=on.

We isolated the data to a new table, unloaded and reloaded the table
with just 2 rows. With the help of Ingres Support we discovered the date
fields were sometimes being stored with an old format. Most likely one
or more machines on the network are running an older version of Ingres
Net or using a different mechanism to inject data. The client is
running mainly OpenROAD 4.1sp2 and Ingres Net 2.5. There are also some
data feeds coming via odbc and esql/c and probably some abf running on
the server. Server is Ingres 2006sp2 on Solaris 10.


Here is a summary/highlights of the findings:
===================

Select pd.best_before, hex(pd.best_before) from pallet_detail_doug pd

best_before col2
31-dec-2008 1D0007D8000C001F00000000
31-dec-2008 5D0007D8000C001F00000000

1D and 5D represents the dn_status. The dn_status is potentially causing
the two values to be hashed to different buckets, and effectively being
treated as two distinct dates.

1D breaks down as
AD_DN_ABSOLUTE 0x01 /* Absolute point in time */
AD_DN_YEARSPEC 0x04 /* Year specified */
AD_DN_MONTHSPEC 0x08 /* Month specified */
AD_DN_DAYSPEC 0x10 /* Day specified */
(Absolute date, with year, month and day specified)

5D is the same as 1D, plus 0x40:
AD_DN_AFTER_EPOCH 0x40 /* after 02-sep-1752 */

The use of AD_DN_AFTER_EPOCH and AD_DN_BEFORE_EPOCH was deprecated back
in 23-apr-2001 by the fix for bug 104518.

"...any Ingres front-end tool that sends a binary date to the backend
without the fix
for Bug 104518 will introduce the extra date bits; tools like QBF. This
problem was
present in some versions of Ingres 2.0 and 2.5 but was fixed before the
release of
Ingres 2.6 and 2006....
.... if used to manipulate date data in any database table would have
been responsible
for adding the extra date bits that causes the GROUP BY problem in this
issue."


Work around/ fixes:
Upgrade Ingres Net on the client PC with a patch which includes fix for
104518
An ascii unload / reload will fix the current data problem.
Run cnvdate - a utility that will check all the tables in all DBs and
produce SQL to fix the bad rows.

===================

My 2 bits: If the hash join algorithm is reacting to the "faulty data" I
feel it is a server bug. Regardless of the client application the
server data should be clean. I don't have control and cannot test the
version of ingres net the client is running. In the recent past, I've
seen alphanumeric data injected into MSSQL date fields. I've always
boasted to my clients that Ingres does not have these sorts of problems.

We have yet to find the culprit application. I still think it is
OpenROAD not abf. More testing is due Monday.


Paul White

Peerless IT Pty Ltd
1 Thomas Holmes St
Maribyrnong VIC 3032
Phone : 03 9319 8100
Mobile : 0414 681 799
Internet : www.peerlessit.com.au