MySQL Bugs: #454: Error on JDBC/ODBC query

Bug #454	Error on JDBC/ODBC query
Submitted:	20 May 2003 3:01	Modified:	28 Jul 2003 4:37
Reporter:	Giovanni Monferdini	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	4.1 alpha	OS:	Linux (Linux Red Hat 7.1 (ODBC Win2k))
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
On JDBC/ODBC connector we have this problem :

This Select (that logically are the same but where are different) get different result!!

SELECT GP_VERS, GP_SIGLA, GP_TIPO, GP_COD_PER, GP_COD_SPE, GP_DATA_PER_DA, GP_DATA_PER_A, GP_DATA_SPE_DA, GP_DATA_SPE_A, GP_PER_MOVIM, GP_DATA_ELIM, GP_DESC_PER, GP_DESC_SPE  FROM TPPEGP WHERE GP_SIGLA = '01' AND  NOT GP_DATA_SPE_DA > '20030519' AND  NOT GP_DATA_SPE_A < '20030519'
 
On jdbc-odbc this select return wrongly 0 record 
On odbc this stessa select return correctly 1 record
 
The select that work correctly is this:
 
SELECT GP_VERS, GP_SIGLA, GP_TIPO, GP_COD_PER, GP_COD_SPE, GP_DATA_PER_DA, GP_DATA_PER_A, GP_DATA_SPE_DA, GP_DATA_SPE_A, GP_PER_MOVIM, GP_DATA_ELIM, GP_DESC_PER, GP_DESC_SPE  FROM TPPEGP WHERE GP_SIGLA = '01' AND  GP_DATA_SPE_DA <= '20030519' AND  GP_DATA_SPE_A >= '20030519'

How to repeat:
Create this table and index,insert one row and try!!

CREATE TABLE     TPPEGP(
GP_VERS             CHAR(2)  NOT NULL,
GP_SIGLA            CHAR(3)  NOT NULL,
GP_TIPO             CHAR(1)  NOT NULL,
GP_COD_PER          CHAR(4)  NOT NULL,
GP_COD_SPE          CHAR(3)  NOT NULL,
GP_DATA_PER_DA      CHAR(8)  NOT NULL,
GP_DATA_PER_A       CHAR(8)  NOT NULL,
GP_DATA_SPE_DA      CHAR(8)  NOT NULL,
GP_DATA_SPE_A       CHAR(8)  NOT NULL,
GP_PER_MOVIM        CHAR(1)  NOT NULL,
GP_DATA_ELIM        CHAR(8)  NOT NULL,
GP_DESC_PER         CHAR(30) NOT NULL,
GP_DESC_SPE         CHAR(30) NOT NULL)
;

CREATE UNIQUE INDEX     TPPEGP_KEY1 ON     TPPEGP
   (GP_SIGLA    , GP_TIPO    , GP_COD_PER    , GP_COD_SPE    );

CREATE UNIQUE INDEX     TPPEGP_KEY2 ON     TPPEGP
(GP_SIGLA    , GP_TIPO    , GP_DATA_SPE_DA    , GP_DATA_SPE_A    );

CREATE UNIQUE INDEX     TPPEGP_KEY3 ON     TPPEGP
(GP_SIGLA DESC, GP_TIPO DESC, GP_COD_PER DESC, GP_COD_SPE DESC,
GP_PER_MOVIM DESC);

CREATE UNIQUE INDEX     TPPEGP_KEY4 ON     TPPEGP
(GP_SIGLA DESC, GP_TIPO DESC, GP_DATA_SPE_DA DESC,
 GP_DATA_SPE_A DESC, GP_PER_MOVIM DESC);

Thanks a lot!!

the table was altered with TYPE=INNODB

Giovanni,
Can you please clarify if this bug is ODBC, and JDBC related or it works same way with command-line client too?

Please also include in "How-to-repeat" some data which are enough to denostrate problem and also both "Correct" and "Wrong" results.

Now your report say:
> Create this table and index,insert one row and try!!

It is not clear what exactly should we observe at this point.

This is the extract of output for TPPEGP from mysqldump 

We use the SUN JDBC:ODBC bridge                sun.jdbc.odbc.JdbcOdbcDriver

the ODBC return 1 rows --> OK
the JDBC-ODBC return 0 rows ---> NOT OK

-- Table structure for table 'TPPEGP'
--

CREATE TABLE TPPEGP (
  GP_VERS char(2) character set latin1 NOT NULL default '',
  GP_SIGLA char(3) character set latin1 NOT NULL default '',
  GP_TIPO char(1) character set latin1 NOT NULL default '',
  GP_COD_PER char(4) character set latin1 NOT NULL default '',
  GP_COD_SPE char(3) character set latin1 NOT NULL default '',
  GP_DATA_PER_DA char(8) character set latin1 NOT NULL default '',
  GP_DATA_PER_A char(8) character set latin1 NOT NULL default '',
  GP_DATA_SPE_DA char(8) character set latin1 NOT NULL default '',
  GP_DATA_SPE_A char(8) character set latin1 NOT NULL default '',
  GP_PER_MOVIM char(1) character set latin1 NOT NULL default '',
  GP_DATA_ELIM char(8) character set latin1 NOT NULL default '',
  GP_DESC_PER char(30) character set latin1 NOT NULL default '',
  GP_DESC_SPE char(30) character set latin1 NOT NULL default '',
  UNIQUE KEY TPPEGP_KEY1 (GP_SIGLA,GP_TIPO,GP_COD_PER,GP_COD_SPE),
  UNIQUE KEY TPPEGP_KEY2 (GP_SIGLA,GP_TIPO,GP_DATA_SPE_DA,GP_DATA_SPE_A),
  UNIQUE KEY TPPEGP_KEY3 (GP_SIGLA,GP_TIPO,GP_COD_PER,GP_COD_SPE,GP_PER_MOVIM),
  UNIQUE KEY TPPEGP_KEY4 (GP_SIGLA,GP_TIPO,GP_DATA_SPE_DA,GP_DATA_SPE_A,GP_PER_M
OVIM)
) TYPE=InnoDB;

-- Dumping data for table 'TPPEGP'
--

INSERT INTO TPPEGP VALUES ('01','01','0','2000','001','20000101','20001231','200
00101','20000131','M','00000000','2000','GEN');
INSERT INTO TPPEGP VALUES ('01','01','0','2000','002','20000101','20001231','200
00201','20000229','M','00000000','2000','FEB');
INSERT INTO TPPEGP VALUES ('01','01','0','2000','003','20000101','20001231','200
00301','20000331','M','00000000','2000','MAR');
INSERT INTO TPPEGP VALUES ('01','01','0','2000','004','20000101','20001231','200
00401','20000430','M','00000000','2000','APR');
INSERT INTO TPPEGP VALUES ('01','01','0','2000','005','20000101','20001231','200
00501','20000531','M','00000000','2000','MAG');
INSERT INTO TPPEGP VALUES ('01','01','0','2000','006','20000101','20001231','200
00601','20000630','M','00000000','2000','GIU');
INSERT INTO TPPEGP VALUES ('01','01','0','2000','007','20000101','20001231','200
00701','20000731','M','00000000','2000','LUG');
INSERT INTO TPPEGP VALUES ('01','01','0','2000','008','20000101','20001231','200
00801','20000831','M','00000000','2000','AGO');
INSERT INTO TPPEGP VALUES ('01','01','0','2000','009','20000101','20001231','200
00901','20000930','M','00000000','2000','SET');
INSERT INTO TPPEGP VALUES ('01','01','0','2000','010','20000101','20001231','200
01001','20001031','M','00000000','2000','OTT');
INSERT INTO TPPEGP VALUES ('01','01','0','2000','011','20000101','20001231','200
01101','20001130','M','00000000','2000','NOV');
INSERT INTO TPPEGP VALUES ('01','01','0','2000','012','20000101','20001231','200
01201','20001231','M','00000000','2000','DIC');
INSERT INTO TPPEGP VALUES ('01','01','0','2001','001','20010101','20011231','200
10101','20010131','M','00000000','2001','GENNAIO');
INSERT INTO TPPEGP VALUES ('01','01','0','2001','002','20010101','20011231','200
10201','20010228','M','00000000','2001','FEBBRAIO');
INSERT INTO TPPEGP VALUES ('01','01','0','2001','003','20010101','20011231','200
10301','20010331','M','00000000','2001','MARZO');
INSERT INTO TPPEGP VALUES ('01','01','0','2001','004','20010101','20011231','200
10401','20010430','M','00000000','2001','APRILE');
INSERT INTO TPPEGP VALUES ('01','01','0','2001','005','20010101','20011231','200
10501','20010531','M','00000000','2001','MAGGIO');
INSERT INTO TPPEGP VALUES ('01','01','0','2001','006','20010101','20011231','200
10601','20010630','M','00000000','2001','GIUGNO');
INSERT INTO TPPEGP VALUES ('01','01','0','2001','007','20010101','20011231','200
10701','20010731','M','00000000','2001','LUGLIO');
INSERT INTO TPPEGP VALUES ('01','01','0','2001','008','20010101','20011231','200
10801','20010831','M','00000000','2001','AGOSTO');
INSERT INTO TPPEGP VALUES ('01','01','0','2001','009','20010101','20011231','200
10901','20010930','M','00000000','2001','SETTEMBRE');
INSERT INTO TPPEGP VALUES ('01','01','0','2001','010','20010101','20011231','200
11001','20011031','M','00000000','2001','OTTOBRE');
INSERT INTO TPPEGP VALUES ('01','01','0','2001','011','20010101','20011231','200
11101','20011130','M','00000000','2001','NOVEMBRE');
INSERT INTO TPPEGP VALUES ('01','01','0','2001','012','20010101','20011231','200
11201','20011231','M','00000000','2001','DICEMBRE');
INSERT INTO TPPEGP VALUES ('01','01','0','2002','001','20020101','20021231','200
20101','20020131','M','00000000','2002','GENNAIO');
INSERT INTO TPPEGP VALUES ('01','01','0','2002','002','20020101','20021231','200
20201','20020228','M','00000000','2002','FEBBRAIO');
INSERT INTO TPPEGP VALUES ('01','01','0','2002','003','20020101','20021231','200
20301','20020331','M','00000000','2002','MARZO');
INSERT INTO TPPEGP VALUES ('01','01','0','2002','004','20020101','20021231','200
20401','20020430','M','00000000','2002','APRILE');
INSERT INTO TPPEGP VALUES ('01','01','0','2002','005','20020101','20021231','200
20501','20020531','M','00000000','2002','MAGGIO');
INSERT INTO TPPEGP VALUES ('01','01','0','2002','006','20020101','20021231','200
20601','20020630','M','00000000','2002','GIUGNO');
INSERT INTO TPPEGP VALUES ('01','01','0','2002','007','20020101','20021231','200
20701','20020731','M','00000000','2002','LUGLIO');
INSERT INTO TPPEGP VALUES ('01','01','0','2002','008','20020101','20021231','200
20801','20020831','M','00000000','2002','AGOSTO');
INSERT INTO TPPEGP VALUES ('01','01','0','2002','009','20020101','20021231','200
20901','20020930','M','00000000','2002','SETTEMBRE');
INSERT INTO TPPEGP VALUES ('01','01','0','2002','010','20020101','20021231','200
21001','20021031','M','00000000','2002','OTTOBRE');
INSERT INTO TPPEGP VALUES ('01','01','0','2002','011','20020101','20021231','200
21101','20021130','M','00000000','2002','NOVEMBRE');
INSERT INTO TPPEGP VALUES ('01','01','0','2002','012','20020101','20021231','200
21201','20021231','M','00000000','2002','DICEMBRE');
INSERT INTO TPPEGP VALUES ('01','01','0','2003','001','20030101','20031231','200
30101','20030131','M','00000000','2003','GENNAIO');
INSERT INTO TPPEGP VALUES ('01','01','0','2003','002','20030101','20031231','200
30201','20030228','M','00000000','2003','FEBBRAIO');
INSERT INTO TPPEGP VALUES ('01','01','0','2003','003','20030101','20031231','200
30301','20030331','M','00000000','2003','MARZO');
INSERT INTO TPPEGP VALUES ('01','01','0','2003','004','20030101','20031231','200
30401','20030430','M','00000000','2003','APRILE');
INSERT INTO TPPEGP VALUES ('01','01','0','2003','005','20030101','20031231','200
30501','20030531','M','00000000','2003','MAGGIO');
INSERT INTO TPPEGP VALUES ('01','01','0','2003','006','20030101','20031231','200
30601','20030630','M','00000000','2003','GIUGNO');
INSERT INTO TPPEGP VALUES ('01','01','0','2003','007','20030101','20031231','200
30701','20030731','M','00000000','2003','LUGLIO');
INSERT INTO TPPEGP VALUES ('01','01','0','2003','008','20030101','20031231','200
30801','20030831','M','00000000','2003','AGOSTO');
INSERT INTO TPPEGP VALUES ('01','01','0','2003','009','20030101','20031231','200
30901','20030930','M','00000000','2003','SETTEMBRE');
INSERT INTO TPPEGP VALUES ('01','01','0','2003','010','20030101','20031231','200
31001','20031031','M','00000000','2003','OTTOBRE');
INSERT INTO TPPEGP VALUES ('01','01','0','2003','011','20030101','20031231','200
31101','20031130','M','00000000','2003','NOVEMBRE');
INSERT INTO TPPEGP VALUES ('01','01','0','2003','012','20030101','20031231','200
31201','20031231','M','00000000','2003','DICEMBRE');

You shouldn't use the JDBC:ODBC bridge, as there is a Type-IV driver for MySQL, Connector/J. See http://www.mysql.com/products/connector-j/

The JDBC:ODBC bridge is known to be _very_, _very_ buggy.

Once again. Please do not use the JDBC-ODBC bridge. The bug is with the JDBC-ODBC bridge, which is software that comes from Sun Microsystems, not MySQL, therefore we can not provide support for problems with it. You should use the Type-IV JDBC driver for MySQL, Connector/J if you want to use MySQL from Java. Even Sun advises that you not use the JDBC-ODBC bridge if there is a native JDBC driver for your database.

If this problem happens when you use Connector/J, re-open this ticket.