| 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: | |
| Category: | Connector / ODBC | Severity: | S3 (Non-critical) |
| Version: | 3.51.14.00 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[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.
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;