Interbase 2007 bug?

[Jim Norris]

I have recently installed Interbase 2007 (8.0.0.27), and have a particular
problem.

I have a table (SYS) with 3 fields (IEN, SN, SIDEAB), with the following
data:
103 1 0
103 2 0
103 3 0
103 1 1
103 2 1
103 3 1

The scripts:
"select * from SYS where IEN=103 and SIDEAB=0" returns empty.
"select * from SYS where IEN=103 and SIDEAB=1" returns correctly 3 entries.

If I change the 1st script to:-
"select * from SYS where IEN=103 and SIDEAB=NULL" it returns 3 entries
correctly!

The other twist; is that this NULL problem only occurs when SIDEAB is the
last field in the table.
Anyone else had this NULL and ZERO problem?

I haven't yet tried on IB7.5....

[Craig Stuntz [TeamB]]

Please give full DDL for the table. Also, if that's not the *precise*
SQL you're using please give that, too.

(some_field) = NULL should *always* be false.

--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Borland newsgroup denizen Sergio González has a new CD of
Irish music out, and it's good: http://tinyurl.com/7hgfr

[Jim Norris]

Hi,

Yes, I made the typo when I said "=NULL", it should have been "IS NULL".

I have experimented with this since, and I can say the following....
( IB 7.5 this doesn't happen )

My actual table definition is:-

CREATE TABLE SYSTEMLAYOUT
(
SLN INTEGER NOT NULL PRIMARY KEY,
IEN INTEGER NOT NULL,
SNT INTEGER NOT NULL,
SN INTEGER NOT NULL,
SIDEAB INTEGER NOT NULL
);

When I populate this manually through IB console using dummy numbers e.g.
1 103 1 1 0
2 103 1 1 0
3 103 1 1 0
4 103 1 1 1
5 103 1 1 1
6 103 1 1 1

When I run the query (below) through my app (C++ builder using IBX), or
through SQL in IBConsole it returns an empty set.
"select * from SYSTEMLAYOUT where IEN=103 and SIDEAB=0"

Also, as previously noted:-
"select * from SYSTEMLAYOUT where IEN=103 and SIDEAB IS NULL" returns 3
(zeroed) entries incorrectly!

If I now turn the table population 'upside down', as below, the same script
works as expected. Weird!
1 103 1 1 1
2 103 1 1 1
3 103 1 1 1
4 103 1 1 0
5 103 1 1 0
6 103 1 1 0

What's even more baffling, is after I turned the table population upside yet
again (as originally populated) , it works perfectly OK now.
Also, when I 'dropped' the SN field then 'added' it, so that SN was the last
field in the table, and everything worked as expected from the get go.

I hope this isn't me!!
Thanks for the reply..

[Quinn Wildman (CodeGear Developer Support)]

I cannot reproduce this behavior. I suspect your sideab values are
really null and not 0 and your client is somehow displaying them as 0. 0
and null are definitely different values and queried for differently.