Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.0.20
-
None
-
centos 6.7, mariadb-jdbc 1.4.6
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
|