Bug #27755 SELECT IsNull(field) yields True for NOT NULL column (MS Access)
Submitted: 11 Apr 2007 12:40 Modified: 15 Apr 2007 12:54
Reporter: stefan hoffmann Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51.14.00 OS:Windows
Assigned to: CPU Architecture:Any

[11 Apr 2007 12:40] stefan hoffmann
Description:
When querying a linked table in MS Access with the IsNull() function, it returns a True for VARCHAR(n) NOT NULL columns containing an empty string ('').

How to repeat:
Table create:

DROP TABLE IF EXISTS `testNull`;
CREATE TABLE `testNull` (
  `ID` varchar(32) collate latin1_general_ci NOT NULL default '',
  `TITLE` varchar(255) collate latin1_general_ci NOT NULL default '',
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO `testNull` VALUES ('1', '');

MS Access Query on linked table:

SELECT testnull.ID, testnull.TITLE, IsNull([TITLE]) AS Ausdr1
FROM testnull;
[11 Apr 2007 20:07] MySQL Verification Team
IsNull Test on Access

Attachment: isnull.png (image/x-png, text), 48.39 KiB.

[11 Apr 2007 20:09] MySQL Verification Team
I was unable to repeat the behavior reported. I attached a picturer
with the result on Access 2003.
[11 Apr 2007 20:15] MySQL Verification Team
Thank you for the bug report. I was not able to repeat with MSAcess 2003.
Please see picture attached.
[12 Apr 2007 10:12] stefan hoffmann
design view in Access

Attachment: query_designview.JPG (image/jpeg, text), 46.28 KiB.

[12 Apr 2007 10:12] stefan hoffmann
result in Access

Attachment: query_result.JPG (image/jpeg, text), 21.23 KiB.

[12 Apr 2007 10:12] stefan hoffmann
data in MySQL query browser

Attachment: query_browser.JPG (image/jpeg, text), 29.26 KiB.

[12 Apr 2007 10:13] stefan hoffmann
I'm not using a passthrough query, I query a linked table. See attached images.
[15 Apr 2007 11:55] Tonci Grgin
Hi Stefan, I'll retry tests with additional info you provided.
[15 Apr 2007 12:54] Tonci Grgin
Stefan. I done the test just like you described and got the same results, but I interpret them differently. If there is a bug here, it's in Jet engine, I think. Here's why:
 - MySQL server general query log shows no query issued with IsNULL
 - MyODBC SQL log shows no query issued with IsNULL
 - MyODBC trace log shows no query issued with IsNULL
 - Linked table shows correct result (as in Miguel's picture)
What I think is happening is that Access guesses (or better evaluates itself) the value of IsNULL field producing incorrect result.

MyODBC sql log:
-- Query logging
--
--  Driver name: MySQL ODBC 3.51 Driver  Version: 03.51.14
-- Timestamp: 070415 14:30:39

SELECT Config, nValue FROM MSysConf;
SHOW TABLES FROM `mysql` like '%';
SHOW KEYS FROM `bug27755`;
SELECT `ID` ,`TITLE`  FROM `bug27755` ;
COMMIT;
COMMIT;
SELECT `bug27755`.`ID` FROM `bug27755` ;
SELECT `ID`,`TITLE`  FROM `bug27755`  WHERE `ID` = '1';
SELECT `bug27755`.`ID` FROM `bug27755` ;
SELECT `ID`,`TITLE`  FROM `bug27755`  WHERE `ID` = '1';

Server log:
070415 14:30:39	     16 Connect     root@localhost on test
		     16 Query       SET SQL_AUTO_IS_NULL=0
		     16 Query       SELECT Config, nValue FROM MSysConf
		     16 Query       show tables
		     16 Query       SHOW TABLES FROM `mysql` like '%'
070415 14:30:45	     16 Field List  bug27755 
		     16 Field List  bug27755 
		     16 Query       SHOW KEYS FROM `bug27755`
		     16 Query       SELECT `ID` ,`TITLE`  FROM `bug27755`
		     16 Query       SET AUTOCOMMIT=0
		     16 Query       COMMIT
		     16 Query       SET AUTOCOMMIT=1
		     16 Query       SET AUTOCOMMIT=0
		     16 Query       COMMIT
		     16 Query       SET AUTOCOMMIT=1
070415 14:31:06	     16 Query       SELECT `bug27755`.`ID` FROM `bug27755`
		     16 Query       SELECT `ID`,`TITLE`  FROM `bug27755`  WHERE `ID` = '1'
070415 14:36:09	     16 Query       SELECT `bug27755`.`ID` FROM `bug27755`
		     16 Query       SELECT `ID`,`TITLE`  FROM `bug27755`  WHERE `ID` = '1'
070415 14:36:16	     16 Quit       

If you can provide more info and/or different view of problem, please reopen the report.