isnull() inconsistency in result 1

[205xld]

Hi Folks

A colleague is using Java with a driver supplied by HXTT to insert data into my foxpro database. Almost everything works fine, except a single process where an insert adds some NULL entries that do not behave in a consistent manner.

The data looks visually OK when I browse the table, but some other code fails to read the data and I've resolved a test that identifies the 'broken' records:

SELECT * FROM cmsmsg ;
WHERE ISNULL(clsendid) ;
INTO CURSOR curGoodNulls

SELECT * FROM cmsmsg ;
WHERE ISNULL(clsendid) == .T. ;
INTO CURSOR curAllNulls

SELECT * ;
FROM curAllNulls ;
WHERE cmsmsgid NOT in ;
(SELECT cmsmsgid FROM curGoodNulls) ;
INTO CURSOR curBadNulls

Of course curBadNulls should be empty - but it isn't! I can fix the broken records in vfp with an update/replace setting the NULLed fields to '' and then resetting to .NULL. (NB: just setting straight to NULL doesn't fix it).

I am assuming the issue is related to a fault with the driver, but I can see this could be a long slow investigation. Has anyone seen this before, or has anyone an insight into how a NULL could behave in this way? I've not seen it before in 12yrs using Foxpro...

Thanks for any help/info you can give

Regards

Graham

 

[Olaf Doschke]

Yes, this looks funny. From your desciption I can deduct the field in question is a char type field? Is it a normal char, char binary or varchar or is it a memo?

ISNULL(clsendid) == .T. will have the exact same result as ISNULL(clsendid), the only reason you get different results is, you execute this one after another and the data changes in the meantime.
But technically ISNULLL() returns only .T. and .F. (it's obviously never returning .NULL.) and only .T. == .T.. Besides == only makes sense for comparsion of string values, neither for logical nor numerical values, but it doesn't harm.

I fear you are only a victim of the changes in between executing the first and the second query.

Please try that on a DBF you copied and you work on this file exclusive with your first and second query. If you then still get a difference I'd like to see the DBF.

From what I know NULLABLE fields cause extra bytes in each records, 1 Byte is added for every 8 nullable fields and each bit is used up.

You can see for yourself how that works:

Create Cursor curTest (i int)
? Recsize() && 5 = 1 for deleteion mark and 4 int
Create Cursor curTest (i int NULL)
? Recsize() && 6 = one more for NULL bit
Create Cursor curTest (i int, j int)
? Recsize() && 9 = 1 for deletion mark and 8 for two ints
Create Cursor curTest (i int NULL, j int NULL)
? Recsize() && 10 = one mor for NULL bits (not 11, as the NULL bytes are used bitwise)
Create Cursor curTest (i int, j int, k int, l int, m int, n int, o int, p int)
? Recsize() && 33
Create Cursor curTest (i int NULL, j int NULL, k int NULL, l int NULL, m int NULL, n int NULL, o int NULL, p int NULL)
? Recsize() && 34
Create Cursor curTest (i int, j int, k int, l int, m int, n int, o int, p int, q int)
? Recsize() && 37
Create Cursor curTest (i int NULL, j int NULL, k int NULL, l int NULL, m int NULL, n int NULL, o int NULL, p int NULL, q int NULL)
? Recsize() && 39, now two bytes are added for 9 nullable fields.

To sum it up, NULLs arer denoted in bits at a record end and there are only the values 0 or 1. I don't see how that can fail or differ, someone really will need to write the string .NULL. into the char fields to visually have .NULL., while the value isn't null, but both ISNULL(".NULL.") and ISNULL(".NULL.")==.T. are .F.

Bye, Olaf.

 

[Mike Lewis]

I wonder if this is one of those cases where you need to add NOFILTER. I suggest you try adding that at least to the first two SELECTs, and see if you notice a difference.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[205xld]

Hi Olaf

Yes, it's a normal c(12) field with a regular index.

I'm the only person running code against this table at the moment, so there's no reason anything should change between selects, but I've modified my code (as below) to open the table exclusively and I still get the same result:

SET EXCLUSIVE OFF
SET DATE BRITISH
SET CENTURY on
CLOSE TABLES all
SET DELETED ON
SET SAFETY OFF

cData = '\\Oxford9\system\test\sas\data\'

USE cData + 'cmsmsg' IN 0 exclusive

SELECT * FROM cmsmsg ;
WHERE ISNULL(clsendid) ;
INTO CURSOR curGoodNulls

SELECT * FROM cmsmsg ;
WHERE ISNULL(clsendid) = .T. ;
INTO CURSOR curAllNulls

SELECT * ;
FROM curAllNulls ;
WHERE cmsmsgid NOT in ;
(SELECT cmsmsgid FROM curGoodNulls) ;
INTO CURSOR curBadNulls

SELECT curBadNulls

I've removed the file from the database (I've checked and it still behaves the same), zipped it and attached it.

Thanks again for your help

 

[205xld]

Hi Mike

Not sure what 'those cases' are but this isn't one of them - no change. Do keep the suggestions coming though!

Regards

Graham

 

[vgulielmus]

I suppose is an index issue.

SELECT * FROM cmsmsg ;
WHERE ISNULL(clsendid)

reported o records, but displayed one row.
After a reindex all was ok.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

I viewed the CDX with FAR Manager. Now I'm sure the CDX was outdated.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

That's a simple explanation, there is an index on ISNULL(clsendid) and it's not updated with the HXTT driver. That's why I also recently said I haven't seen anything in 15 years of doing VFP, that works with DBFs in a thoroughly way. Something always is missing, eg index support. Handling indexes indeed is complicated, as the CDX file structure is not as easy to understand and the schema isn't fully documented and explained in detail.

The difference is not in writing in NULLs wrong, but in not having the value in the index.

Sys(3054,1)

? "-- query1:"
SELECT * FROM cmsmsg ;
WHERE ISNULL(clsendid) ;
INTO CURSOR curGoodNulls

? "-- query2:"
SELECT * FROM cmsmsg ;
WHERE ISNULL(clsendid) = .T. ;
INTO CURSOR curAllNulls

The first query uses the index and has no result, as the record isn't in the index

The second query does not use the index, as your where clause doesn't fit. Interestingly it could be used, too, as in principle the expressions are equal, but the optimization doesn't see as far. Astonishingly enough the optimizer sees the index clsendid on the field can also be used to optimize ISNULL(clsendid), if just looking for the index nodes with .NULL. value, it doesn't see the same lookup of index nodes would also optimize ISNULL(clsendid) = .T., the optimizer might give up on the = already, in the general case a comparison of a function(field) = some value can't be optimized with an index on the field.

So that's all. HXTT puts null into the DBF file correctly, but not into the CDX index tag(s) and so optimized queries won't put these records into the result. CDX is just not supported by HXTT and that has this negative effect.

Bye, Olaf.

 

[205xld]

Thanks Olaf. Great explanation.

Thanks to everyone else too for their help.

 

[Olaf Doschke]

Just a small correction: I said "there is an index on ISNULL(clsendid)"
In fact there only is an index on the plain field value clsendid.

The rest still applies: rushmore optimizes ISNULL(clsendid) with the index on clsendid, but doesn't do so with the expression ISNULL(clsendid)=.T. and overall the CDX not being updated is the root cause.

Bye, Olaf.

 

[205xld]

Epilogue:

My colleague reported the issue to HXTT and they seem to have successfully resolved it overnight.

Nice to be able to report good support from a driver provider!

Regards

Graham

 

[Olaf Doschke]

I see, I have looked at their site and the JDBC driver offers a lot of things even the VFP sql engine doesn't (eg INTERSECT | EXCEPT | MINUS aside of UNION). Makes me wonder how it could cope with indexes on expressions, even just simple expressions using native VFP functions.

I bet, they couldn't update an index tag on a key expression calling into a user defined function. Even VFP itself will need SET PROCEDURE to point to the function before the expression could be evaluated. If they offer that, then they would offer almost the full set of VFP functionality, the full VFP language. That's what you would also need to support DBC and its stored procs.

Bye, Olaf.