Bug #82331 SQL Editor incorrectly reports an error
Submitted: 25 Jul 2016 7:11 Modified: 26 Mar 2018 22:43
Reporter: David Maley Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:6.3, 6.3.7 OS:Microsoft Windows (Microsoft Windows Server 2012 R2 Standard)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[25 Jul 2016 7:11] David Maley
Description:
SQL Editor indicates a syntax error in this statement

 SELECT intBookingID, intOrderID, dtmDateTime, intDuration, intActivityID, strStatus, 
 CAST(dtmDateTime AS DATE) AS dtmDate, DATE_FORMAT(dtmDateTime, '%H:%i') AS timStart, 
 DATE_FORMAT(DATE_ADD(dtmDateTime, INTERVAL intDuration MINUTE), '%H:%i') AS timEnd 
 FROM tblBookings B 
 WHERE strStatus IN ('CONFIRMED','UNPAID') 
 AND EXISTS (SELECT * FROM tblActivityResources WHERE intResourceID = 7 AND intActivityID IN (SELECT B.intActivityID UNION SELECT intSubActivityOfID FROM tblActivities WHERE intActivityID = B.intActivityID)) AND dtmDateTime BETWEEN '2016-05-08' AND DATE_ADD(STR_TO_DATE('2016-05-08', '%Y-%m-%d'), INTERVAL 1 DAY); 

at the second SELECT of this clause

SELECT B.intActivityID UNION SELECT ..

but the query runs fine
 

This doesn't happen in 5.2.47.

How to repeat:
I guess you would need the table definitions (but remove the foreign keys)?

CREATE TABLE tblBookings (
  intBookingID	int(11)  NOT NULL AUTO_INCREMENT,
  intActivityID	int(11) NOT NULL,
  dtmDateTime	datetime NOT NULL,
  intDuration	int(11) NOT NULL,
  fltAmount	decimal(9,2) DEFAULT 0,
  intOrderID	int(11) NOT NULL,
  dtmSubmitted	datetime,
  bChargedFor	tinyint(1) DEFAULT 0,
  bPaid	tinyint(1) DEFAULT 0,
  strMarkedPaidBy	varchar(40) DEFAULT '',
  dtmMarkedPaidAt	datetime,
  strPlacedBy	varchar(40) DEFAULT '',
  strPaymentType	varchar(20) DEFAULT '',
  strStatus	varchar(20) NOT NULL,
  strNotes	varchar(255) DEFAULT '',
  intDiscountID	int(11) DEFAULT 0,
  intCoBookingID INT NULL DEFAULT NULL,
  bPriceProportionalToDuration bit NOT NULL DEFAULT b'0',
  PRIMARY KEY	(intBookingID),

  INDEX (intActivityID),
  CONSTRAINT FOREIGN KEY (intActivityID)
    REFERENCES tblActivities(intActivityID),

  INDEX (intOrderID),
  CONSTRAINT FOREIGN KEY (intOrderID)
    REFERENCES tblOrders(intOrderID)

) ENGINE = InnoDB;

CREATE TABLE `tblActivities` (
  `intActivityID` INT NOT NULL AUTO_INCREMENT,
  `intSubActivityOfID` INT NOT NULL DEFAULT '0',
  `strActivityName` VARCHAR(50) NOT NULL DEFAULT '',
  `intMinimumParticipants` INT NOT NULL DEFAULT 1,
  `strDescription` varchar(200),
  `intDefaultSessionDuration` INT NOT NULL DEFAULT 60,
# appears to be unused
  `bDisableInheritance` BIT NOT NULL DEFAULT 0,
  `intFacilityID` INT NULL,
  `bPublic` bit(1) DEFAULT b'1',
  `intAdminMinimumParticipants` int(11) DEFAULT 1,
  `intAdminMaximumParticipants` int(11) DEFAULT NULL,
  `strSuitability` varchar(100) DEFAULT NULL,
  `strImagePath` varchar(100) DEFAULT NULL,
  `strIconPath` varchar(100) DEFAULT NULL,
  `bIgnoreResourceRequirements` bit(1) NOT NULL DEFAULT b'0',
  `bIgnoreResourceInstanceRequirements` bit(1) NOT NULL DEFAULT b'0',
  `bPriceProportionalToDuration` bit(1) NOT NULL DEFAULT b'0',
  `bIgnoreStartTimeAvailability` bit(1) NOT NULL DEFAULT b'0',
  `bIncludeDescriptionInConfirmation` bit(1) NOT NULL DEFAULT b'0',
  `bNoPPinPriceDisplay` BIT(1) NOT NULL DEFAULT b'0',
  `strSubAccount` varchar(50),
  `dtmOneDayEvent` datetime NULL DEFAULT NULL, 
  `dtmNotAvailableUntil` datetime NULL DEFAULT NULL,
  `bSuspended` BIT(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`intActivityID`),

  INDEX (intFacilityID),
  CONSTRAINT FOREIGN KEY (intFacilityID)
    REFERENCES tblFacilities(intFacilityID)
) ENGINE = InnoDB;

CREATE TABLE `tblActivityResources` (
  `intActivityResourceID` INT NOT NULL AUTO_INCREMENT,
  `intActivityID` INT NOT NULL,
  `intResourceID` INT NOT NULL,
  `bImmediateMandatory` BIT(1) NOT NULL DEFAULT 1,
  `intCount` INT NOT NULL DEFAULT 1,
  `intDefaultActivityDuration` INT NOT NULL DEFAULT 0,

  PRIMARY KEY (`intActivityResourceID`),

  INDEX (intActivityID),
  CONSTRAINT FOREIGN KEY (intActivityID)
    REFERENCES tblActivities(intActivityID),

  INDEX (intResourceID),
  CONSTRAINT FOREIGN KEY (intResourceID)
    REFERENCES tblResources(intResourceID)
) ENGINE = InnoDB;
[25 Jul 2016 7:13] David Maley
shows the non-error

Attachment: false positive.png (image/png, text), 43.15 KiB.

[25 Jul 2016 7:34] Umesh Shastry
Hello David Maley,

Thank you for the report.
Observed this with WB 6.3.7 on Win7.

Thanks,
Umesh
[25 Jul 2016 7:35] Umesh Shastry
Screenshot..

Attachment: 82331.png (image/png, text), 31.84 KiB.

[26 Mar 2018 22:43] Christine Cole
Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 8.0.11 release, and here's the changelog entry:

The SQL editor underlined some keywords and reserved words in statements,
indicating a syntax error, but executed the statements successfully.

Thank you for the bug report.