CREATE DATABASE semijoin_test;

USE semijoin_test;



CREATE TABLE `T1` (
	`T1Id` BIGINT(20) NOT NULL,
	`Code` VARCHAR(20),
	PRIMARY KEY (`T1Id`)
)
COLLATE='utf8mb4_bin'
ENGINE=InnoDB;

CREATE TABLE `T2` (
	`T2Id` BIGINT(20) NOT NULL,
	`T1IdRef` BIGINT(20) NOT NULL,
	`Code` VARCHAR(20),
	PRIMARY KEY (`T2Id`),
	INDEX `FK_T2_T1Id` (`T1IdRef`),
	CONSTRAINT `FK_T2_T1Id` FOREIGN KEY (`T1IdRef`) REFERENCES `T1` (`T1Id`)
)
COLLATE='utf8mb4_bin'
ENGINE=InnoDB;


CREATE TABLE `T3` (
	`T3IdRef` BIGINT(20) NOT NULL,
	`T2IdRef` BIGINT(20) NOT NULL,
	`SequenceNumber` INT(10) NOT NULL,
	PRIMARY KEY (`T3IdRef`, `T2IdRef`),
	INDEX `FK_T3_T2Id` (`T2IdRef`),
	CONSTRAINT `FK_T3_T2Id` FOREIGN KEY (`T2IdRef`) REFERENCES `T2` (`T2Id`)
)
COLLATE='utf8mb4_bin'
ENGINE=InnoDB;

-- Load up dummy data (needed to reproduce issue)
INSERT INTO `T1` (T1Id) VALUES (100001),(100017),(100018),(100026),(100027),(100028),(100029),(100030),(100031),(100032),(100033),(100034),(100035),(100036),(100037),(100038),(100040),(100041),(100042),(100043),(100044),(100045),(100046),(100047);

INSERT IGNORE INTO `T2` (`T2Id`, `T1IdRef`)
	SELECT T1Id, T1Id FROM `T1`;

-- Now the test Data
INSERT IGNORE INTO `T1` VALUES (200001, 'a');
INSERT IGNORE INTO `T2` (`T2Id`, `T1IdRef`) VALUES (200011, 200001),(200012, 200001),(200013, 200001);
INSERT IGNORE INTO `T3` VALUES (1, 200011, 1),  (1, 200012, 2), (1, 200013, 3);
