[MDEV-10413] The SQL results are different according to the index. Created: 2016-07-21  Updated: 2016-07-21  Resolved: 2016-07-21

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.0.20
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: sungwon.han Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

centos 6.7, mariadb-jdbc 1.4.6

Attachments: File my.cnf     File tb_bug_dump.sql.gz    

 Description   

I found that the SQL results are different according to the index. This data of MDL_SFFX_CD(column) in table maybe affect this result. I provide my.cnf and dump file(purified). Please, Tell me why the SQL result is different. 

CREATE TABLE `tb_bug` (
 
  `pk_id` bigint(20) NOT NULL AUTO_INCREMENT,
 
  `SHIPMENT_ID` varchar(20) COLLATE utf8_bin DEFAULT NULL,
 
  `BL_NO` varchar(30) ,
 
  `SUBSDR_CD` varchar(8) COLLATE utf8_bin NOT NULL ,
 
  `MDL_SFFX_CD` varchar(50) COLLATE utf8_bin DEFAULT NULL ,
 
  `TOT_GROSS_CBM` double NOT NULL DEFAULT '0',
 
  `MDL_GROSS_CBM` double NOT NULL DEFAULT '0',
 
  PRIMARY KEY (`pk_id`),
 
  KEY `tb_bug_n01` (`SUBSDR_CD`,`MDL_SFFX_CD`),
 
  KEY `tb_bug_n02` (`SUBSDR_CD`)
 
) ENGINE=InnoDB AUTO_INCREMENT=1414096 DEFAULT CHARSET=utf8 COLLATE=utf8_bin


SQL#1 : using index(tb_bug_n01)
 
SELECT SHIPMENT_ID
 
             , BL_NO
 
             , MAX( TOT_GROSS_CBM )
 
             , SUM( MDL_GROSS_CBM ) AS NEW_TOT_CBM
 
          FROM tb_bug /*! USE INDEX(tb_bug_n01) */
 
         WHERE SUBSDR_CD         = 'EKHQ'
 
         GROUP BY SHIPMENT_ID, BL_NO  
HAVING MAX(TOT_GROSS_CBM) <> SUM(MDL_GROSS_CBM)
 
1 rows returned
 
 
 
SQL#2 : using index(tb_bug_n02)
 
SELECT SHIPMENT_ID
 
             , BL_NO
 
             , MAX( TOT_GROSS_CBM )
 
             , SUM( MDL_GROSS_CBM ) AS NEW_TOT_CBM
 
          FROM tb_bug /*! USE INDEX(tb_bug_n02) */
 
         WHERE SUBSDR_CD         = 'EKHQ'
 
         GROUP BY SHIPMENT_ID, BL_NO  
HAVING MAX(TOT_GROSS_CBM) <> SUM(MDL_GROSS_CBM)
 
4374 rows returned



 Comments   
Comment by Elena Stepanova [ 2016-07-21 ]

If you remove the HAVING clause, you'll see the reason of the problem right away.
You are using (not) equal comparison on double values, it is non-deterministic. Please refer to http://dev.mysql.com/doc/refman/5.5/en/problems-with-float.html for more details, it contains a very similar example.

Please consider using DECIMAL type instead.

Comment by sungwon.han [ 2016-07-21 ]

Thanks for response.

Generated at Thu Feb 08 07:42:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.