MySQL Bugs: #62334: 'IS NOT NULL' not supported

Bug #62334	'IS NOT NULL' not supported
Submitted:	2 Sep 2011 17:04	Modified:	8 Sep 2011 13:37
Reporter:	Helge Weissig	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.8/revision 984	OS:	Linux (2.6.18-128.el5 x86_64))
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
This problem is described in detail here:
https://forums.oracle.com/forums/thread.jspa?messageID=9823046&#9823046 . Basically, the ODBC connector does not seem to support 'IS NULL' or 'IS NOT NULL' on columns of any datatype:

SQL> select * from "test"@mysql where "dbl" is not null;
select * from "test"@mysql where "dbl" is not null
                                       *
ERROR at line 1:
ORA-02070: database MYSQL does not support  IS NOT NULL in this context

SQL> 

How to repeat:
On mysql side:

create table test(test_id int primary key auto_increment, string varchar(255), dbl double, flt float);
insert into test values(null, "first", null, 1), (null, "second", 2, null), (null, null, 3, 3);

On oracle side:

select from "test"@mysql where "dbl" is not null;

Hi Helge,

Have you built the ODBC driver rev 984 yourself?
I am asking because it can be sensitive to the version of UnixODBC and MySQL Client library.

Here is the output I get in my SQLPLUS:

SQL> select * from "test62334"@myodbc5 where "dbl" is not null;

   test_id    string       dbl        flt
----------------------------------------------
         2    second        2
         3                  3          3
   test_id    string       dbl        flt
----------------------------------------------

I used sources from revision 984, UnixODBC 2.2.14 and MySQL Client library 5.5.15.

Hi Bogdan,

I compiled both the ODBC connector and unixODBC (2.3.0) myself. The mysql client library (5.0.77) was installed from the EL5 distribution.