-- Schema
CREATE DATABASE `test1` CHARACTER SET=UTF8 COLLATE=utf8_general_ci;
USE `test1`;

CREATE TABLE `test1`.`task1` (
  `TaskNum` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `TaskListNum` BIGINT(20) NOT NULL,
  `DateTask` DATE NOT NULL DEFAULT '0001-01-01',
  `KeyNum` BIGINT(20) NOT NULL,
  `Descript` TEXT DEFAULT NULL,
  `TaskStatus` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
  `IsRepeating` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
  `DateType` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
  `FromNum` BIGINT(20) NOT NULL,
  `ObjectType` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
  `DateTimeEntry` DATETIME NOT NULL DEFAULT '0001-01-01 00:00:00',
  `UserNum` BIGINT(20) NOT NULL,
  `DateTimeFinished` DATETIME NOT NULL,
  `PriorityDefNum` BIGINT(20) NOT NULL,
  `ReminderGroupId` VARCHAR(20) NOT NULL,
  `ReminderType` SMALLINT(6) NOT NULL,
  `ReminderFrequency` INT(11) NOT NULL,
  `DateTimeOriginal` DATETIME NOT NULL DEFAULT '0001-01-01 00:00:00',
  `SecDateTEdit` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
  PRIMARY KEY (`TaskNum`),
  KEY `indexTaskListNum` (`TaskListNum`),
  KEY `KeyNum` (`KeyNum`),
  KEY `PriorityDefNum` (`PriorityDefNum`),
  KEY `SecDateTEdit` (`SecDateTEdit`),
  KEY `DateTimeOriginal` (`DateTimeOriginal`),
  KEY `TaskStatus` (`TaskStatus`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `test1`.`taskancestor1` (
  `TaskAncestorNum` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `TaskNum` BIGINT(20) NOT NULL,
  `TaskListNum` BIGINT(20) NOT NULL,
  PRIMARY KEY (`TaskAncestorNum`),
  KEY `TaskNum` (`TaskNum`),
  KEY `TaskListNum` (`TaskListNum`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `test1`.`tasklist1` (
  `TaskListNum` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `Descript` VARCHAR(255) DEFAULT '',
  `Parent` BIGINT(20) NOT NULL,
  `DateTL` DATE NOT NULL DEFAULT '0001-01-01',
  `IsRepeating` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
  `DateType` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
  `FromNum` BIGINT(20) NOT NULL,
  `ObjectType` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
  `DateTimeEntry` DATETIME NOT NULL DEFAULT '0001-01-01 00:00:00',
  `GlobalTaskFilterType` TINYINT(4) NOT NULL,
  `TaskListStatus` TINYINT(4) NOT NULL,
  PRIMARY KEY (`TaskListNum`),
  KEY `indexParent` (`Parent`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `test1`.`tasksubscription1` (
  `TaskSubscriptionNum` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `UserNum` BIGINT(20) NOT NULL,
  `TaskListNum` BIGINT(20) NOT NULL,
  `TaskNum` BIGINT(20) NOT NULL,
  PRIMARY KEY (`TaskSubscriptionNum`),
  KEY `UserNum` (`UserNum`),
  KEY `TaskNum` (`TaskNum`),
  KEY `TaskListNum` (`TaskListNum`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- Example data
INSERT INTO `test1`.`task1` SELECT
    nums.num, LEFT(nums.num, 1), '0001-01-01', 0, 'Text', (MOD(nums.num,2)*2), 0,0,0,0, NOW(),1, IF(MOD(nums.num,2),NOW(), '0001-01-01 00:00:00'), 10,'',0,0,NOW(), NOW()
FROM
(
    SELECT CONCAT(n0.num,n1.num)+1 AS 'num'
    FROM (SELECT 1 AS 'num' UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) AS n0
    JOIN (SELECT 1 AS 'num' UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) AS n1
    ORDER BY num
) nums
WHERE nums.num BETWEEN 11 AND 29;
ANALYZE TABLE `test1`.`task1`;

INSERT INTO `test1`.`tasklist1` VALUES(1,'List1',0,'0001-01-01',0,0,0,0,NOW(),1,0);
INSERT INTO `test1`.`tasklist1` VALUES(2,'List2',0,'0001-01-01',0,0,0,0,NOW(),1,0);
ANALYZE TABLE `test1`.`tasklist1`;

INSERT INTO `test1`.`tasksubscription1` VALUES(1,1,1,0);
INSERT INTO `test1`.`tasksubscription1` VALUES(2,1,2,0);
ANALYZE TABLE `test1`.`tasksubscription1`;

INSERT INTO `test1`.`taskancestor1` (TaskNum, TaskListNum)
SELECT
    task1.TaskNum, task1.TaskListNum
FROM `test1`.`task1`;
ANALYZE TABLE `test1`.`taskancestor1`;

-- Broken query
SET SESSION optimizer_switch='split_materialized=on'; -- Change to off to get different results
SELECT
    tasklist1.*,
    COALESCE(unreadtasks1.Count,0) 'NewTaskCount',
    t2.Descript 'ParentDesc1',
    t3.Descript 'ParentDesc2'
FROM tasklist1
LEFT JOIN tasksubscription1
    ON tasksubscription1.TaskListNum = tasklist1.TaskListNum
LEFT JOIN tasklist1 t2
    ON t2.TaskListNum = tasklist1.Parent
LEFT JOIN tasklist1 t3
    ON t3.TaskListNum = t2.Parent
LEFT JOIN (
    SELECT
        taskancestor1.TaskListNum,
        COUNT(*) 'Count'
    FROM taskancestor1
    INNER JOIN task1
        ON task1.TaskNum = taskancestor1.TaskNum
        AND NOT(COALESCE(task1.ReminderGroupId,'') != '' AND task1.DateTimeEntry > NOW())
    WHERE task1.TaskStatus = 0
    GROUP BY taskancestor1.TaskListNum
) unreadtasks1
    ON unreadtasks1.TaskListNum = tasklist1.TaskListNum
WHERE tasksubscription1.UserNum = 1
    AND tasksubscription1.TaskListNum != 0
ORDER BY tasklist1.Descript,tasklist1.DateTimeEntry;

