Bug #70984 sql run error at edition 5.6.14_x64
Submitted: 23 Nov 2013 6:15 Modified: 25 Dec 2013 19:42
Reporter: chen hong Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Windows Severity:S2 (Serious)
Version:5.6.14_x64 OS:Windows
Assigned to: CPU Architecture:Any

[23 Nov 2013 6:15] chen hong
Description:
the bug is:
sql run result does not match the imaging.

How to repeat:
DDL:
CREATE TABLE `block` (
  `OID` bigint(20) NOT NULL AUTO_INCREMENT,
  `CONTENTSTAMP` varchar(255) DEFAULT NULL,
  `DESCRIPTION` varchar(255) DEFAULT NULL,
  `DOC_ID` varchar(50) DEFAULT NULL,
  `ID` varchar(50) NOT NULL,
  `IVA` varchar(255) DEFAULT NULL,
  `KNE` longtext,
  `MARK` varchar(255) DEFAULT NULL,
  `NAME` varchar(100) DEFAULT NULL,
  `OWNER` varchar(30) NOT NULL,
  `REF_VERSION` varchar(10) DEFAULT NULL,
  `REF` varchar(30) DEFAULT NULL,
  `UPDATERANGE` varchar(255) DEFAULT NULL,
  `UPDATETIME` datetime DEFAULT NULL,
  `VERSION` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`OID`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;

first run:
select * from (select block0_.OID as OID1_2_, block0_.CONTENTSTAMP as CONTENTS2_2_, block0_.DESCRIPTION as DESCRIPT3_2_, block0_.DOC_ID as DOC_ID4_2_, block0_.ID as ID5_2_, block0_.IVA as INNOVATE6_2_, block0_.KNE as KNE7_2_, block0_.MARK as MARK8_2_, block0_.NAME as NAME9_2_, block0_.OWNER as OWNER10_2_, block0_.REF_VERSION as REF_VER11_2_, block0_.REF as REF12_2_, block0_.UPDATERANGE as UPDATER13_2_, block0_.UPDATETIME as UPDATET14_2_, block0_.VERSION as VERSION15_2_ from TEST.BLOCK block0_ where (block0_.ID='N1' and block0_.VERSION='1.0.0') or (block0_.ID='N1.1' and block0_.VERSION='1.0.0') or (block0_.ID='N1.1.1' and block0_.VERSION='1.0.0') or (block0_.ID='N1.1.2' and block0_.VERSION='1.0.0') or (block0_.ID='N1.2' and block0_.VERSION='1.0.0')) as KN where KN.REF12_2_ is not null

this sql run ok and return 2 record.

second run(mistake):
select block0_.OID as OID1_2_, block0_.CONTENTSTAMP as CONTENTS2_2_, block0_.DESCRIPTION as DESCRIPT3_2_, block0_.DOC_ID as DOC_ID4_2_, block0_.ID as ID5_2_, block0_.IVA as INNOVATE6_2_, block0_.KNE as KNE7_2_, block0_.MARK as MARK8_2_, block0_.NAME as NAME9_2_, block0_.OWNER as OWNER10_2_, block0_.REF_VERSION as REF_VER11_2_, block0_.REF as REF12_2_, block0_.UPDATERANGE as UPDATER13_2_, block0_.UPDATETIME as UPDATET14_2_, block0_.VERSION as VERSION15_2_ from TEST.BLOCK block0_ where (block0_.ID='N1' and block0_.VERSION='1.0.0') or (block0_.ID='N1.1' and block0_.VERSION='1.0.0') or (block0_.ID='N1.1.1' and block0_.VERSION='1.0.0') or (block0_.ID='N1.1.2' and block0_.VERSION='1.0.0') or (block0_.ID='N1.2' and block0_.VERSION='1.0.0') and block0_.REF is not null

this sql will return 5 record which exactly ignore the "block0_.REF is not null"   condition.
[23 Nov 2013 23:02] MySQL Verification Team
Thank you for the bug report. Could you please provide the dump file with data so
it's possible to run both queries. Thanks,
[25 Nov 2013 14:03] chen hong
the example data is very simple, you just pick up the data in sql posted before.

below sql can run success. i don't know why,, i just delete some braces, you can compare the sql with before.
///////
select block0_.OID as OID1_2_, block0_.CONTENTSTAMP as CONTENTS2_2_, block0_.DESCRIPTION as DESCRIPT3_2_, block0_.DOC_ID as DOC_ID4_2_, block0_.ID as ID5_2_, block0_.IVA as INNOVATE6_2_, block0_.KNE as KNE7_2_, block0_.MARK as MARK8_2_, block0_.NAME as NAME9_2_, block0_.OWNER as OWNER10_2_, block0_.REF_VERSION as REF_VER11_2_, block0_.REF as REF12_2_, block0_.UPDATERANGE as UPDATER13_2_, block0_.UPDATETIME as UPDATET14_2_, block0_.VERSION as VERSION15_2_ from TEST.BLOCK block0_ where (block0_.ID='N1' and block0_.VERSION='1.0.0' or block0_.ID='N1.1' and block0_.VERSION='1.0.0' or block0_.ID='N1.1.1' and block0_.VERSION='1.0.0' or block0_.ID='N1.1.2' and block0_.VERSION='1.0.0' or block0_.ID='N1.2' and block0_.VERSION='1.0.0') and block0_.REF is not null
[25 Nov 2013 19:42] Sveta Smirnova
Thank you for the feedback.

But we need test data from you not because it is hard to generate (or simple), but because we need to know which actual results you get when receive those two and five records and which you expect to get. Please send us a small dump.
[26 Dec 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".