Description:
When temporary is used, the alias of duplicate column would be changed to last one. In that case, the result is like "| period_code_NONE_ | cost_id_4 | cost_id_4 | cost_id_4 | cost_id_4 |".
How to repeat:
CREATE TABLE `t1` (
`period_id` bigint(20) DEFAULT NULL,
`period_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL ,
`yid` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL ,
`id` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL ,
`pubts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`) USING BTREE,
KEY `i_lh_yid` (`yid`),
KEY `i_lh_acc_book_period_pcode` (`period_id`,`period_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC ;
CREATE TABLE `t2` (
`pid` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL ,
`yid` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL ,
`id` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL ,
`pubts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`period_id` bigint(20) DEFAULT NULL ,
`period_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL ,
PRIMARY KEY (`id`) USING BTREE,
KEY `i_lb_pid` (`pid`) ,
KEY `i_lb_ytent_body_book_periodcode` (`yid`,`period_code`),
KEY `i_lb_ytent_body_book_periodid` (`yid`,`period_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC ;
CREATE TABLE `t3` (
`pid` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL ,
`yid` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL ,
`id` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL ,
`pubts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`) USING BTREE,
KEY `i_lg_yid` (`yid`),
KEY `i_lg_pid` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;
insert into t3 (pid, yid, id, pubts) values ("bid_1","yid_1", "gid_1", now());
insert into t2 (pid, yid, id, pubts) values ("id_1","yid_1", "bid_1", now());
insert into t1 (yid, id, pubts, period_id) values ("yid_1", "id_1", now(), 1);
SELECT
tmp.period_code AS period_code_NONE_,
tmp.cost_id AS cost_id_1,
tmp.cost_id AS cost_id_2,
tmp.cost_id AS cost_id_3,
tmp.cost_id AS cost_id_4
FROM
(
SELECT
'' AS cost_id ,
comp.period_code
FROM
t1 main
LEFT JOIN
t2 comp
ON
comp.pid = main.id
AND comp.yid = 'yid_1'
LEFT JOIN
t3 ledgerGG
ON
ledgerGG.pid = comp.id
AND ledgerGG.yid = 'yid_1'
WHERE
main.period_id IN (1) )tmp
ORDER BY
period_code_NONE_ DESC
LIMIT
0, 20;
Description: When temporary is used, the alias of duplicate column would be changed to last one. In that case, the result is like "| period_code_NONE_ | cost_id_4 | cost_id_4 | cost_id_4 | cost_id_4 |". How to repeat: CREATE TABLE `t1` ( `period_id` bigint(20) DEFAULT NULL, `period_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL , `yid` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL , `id` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL , `pubts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , PRIMARY KEY (`id`) USING BTREE, KEY `i_lh_yid` (`yid`), KEY `i_lh_acc_book_period_pcode` (`period_id`,`period_code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC ; CREATE TABLE `t2` ( `pid` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL , `yid` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL , `id` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL , `pubts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , `period_id` bigint(20) DEFAULT NULL , `period_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL , PRIMARY KEY (`id`) USING BTREE, KEY `i_lb_pid` (`pid`) , KEY `i_lb_ytent_body_book_periodcode` (`yid`,`period_code`), KEY `i_lb_ytent_body_book_periodid` (`yid`,`period_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC ; CREATE TABLE `t3` ( `pid` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL , `yid` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL , `id` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL , `pubts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , PRIMARY KEY (`id`) USING BTREE, KEY `i_lg_yid` (`yid`), KEY `i_lg_pid` (`pid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC; insert into t3 (pid, yid, id, pubts) values ("bid_1","yid_1", "gid_1", now()); insert into t2 (pid, yid, id, pubts) values ("id_1","yid_1", "bid_1", now()); insert into t1 (yid, id, pubts, period_id) values ("yid_1", "id_1", now(), 1); SELECT tmp.period_code AS period_code_NONE_, tmp.cost_id AS cost_id_1, tmp.cost_id AS cost_id_2, tmp.cost_id AS cost_id_3, tmp.cost_id AS cost_id_4 FROM ( SELECT '' AS cost_id , comp.period_code FROM t1 main LEFT JOIN t2 comp ON comp.pid = main.id AND comp.yid = 'yid_1' LEFT JOIN t3 ledgerGG ON ledgerGG.pid = comp.id AND ledgerGG.yid = 'yid_1' WHERE main.period_id IN (1) )tmp ORDER BY period_code_NONE_ DESC LIMIT 0, 20;