README.md

Aurora Bug

Engine Aurora MySQL 5.7.12

DB instance class db.r3.4xlarge

Test

• Run init.sql
• Run this select:

SELECT
  *
FROM tmp_entry AS e
  LEFT JOIN tmp_tree_entry t ON t.rootId = e.id
  LEFT JOIN tmp_root_entry_brand reb ON reb.rootEntryId = e.id
  LEFT JOIN tmp_brand b ON b.id = reb.brandId AND b.isArchived = FALSE
WHERE e.id = 1;

Expected

(1, 1, 1, 1, 11, null, null, null);
(1, 2, 1, 1, 11, null, null, null);

Actual Result

(1, 1, 1, 1, 11, null, null, null);
(1, 2, 1, 1, 11, 'null', 0, 0);

init.sql

DROP TABLE IF EXISTS `tmp_entry`;
CREATE TABLE `tmp_entry` (
  `id` INT NOT NULL,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin;

DROP TABLE IF EXISTS `tmp_tree_entry`;
CREATE TABLE `tmp_tree_entry` (
  `id` INT NOT NULL,
  `rootId` INT NOT NULL,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin;

DROP TABLE IF EXISTS `tmp_root_entry_brand`;
CREATE TABLE `tmp_root_entry_brand` (
  `rootEntryId`   INT NOT NULL,
  `brandId`       INT DEFAULT NULL,
  PRIMARY KEY (`rootEntryId`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin;

DROP TABLE IF EXISTS `tmp_brand`;
CREATE TABLE `tmp_brand` (
  `document`   JSON                                                                                      NOT NULL,
  `id`         INT   GENERATED ALWAYS AS (document->>'$.id') STORED                                 NOT NULL,
  `isArchived` TINYINT(1) GENERATED ALWAYS AS (json_contains(`document`, 'true', '$.isArchived')) STORED NOT NULL,
  PRIMARY KEY `id` (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin;

INSERT INTO `tmp_entry` (id) VALUES (1);
INSERT INTO `tmp_entry` (id) VALUES (2);
INSERT INTO `tmp_tree_entry` (id, rootId) VALUES (1, 1);
INSERT INTO `tmp_tree_entry` (id, rootId) VALUES (2, 1);
INSERT INTO `tmp_root_entry_brand` (rootEntryId, brandId) VALUES (1, 11);
INSERT INTO `tmp_brand` (document) VALUES (CAST('{"id":11,"isArchived":true}' AS JSON));