Description:
I create a simple CTE and use IF() in select.
Everything works fine when i select * from the CTE. However, when CTE is used in the IN() clause, it produces wired result.
How to repeat:
-- ----------------------------
-- Table structure for Friendship
-- ----------------------------
DROP TABLE IF EXISTS `Friendship`;
CREATE TABLE `Friendship` (
`user1_id` int(0) NOT NULL,
`user2_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of Friendship
-- ----------------------------
INSERT INTO `Friendship` VALUES (1, 2);
INSERT INTO `Friendship` VALUES (1, 3);
INSERT INTO `Friendship` VALUES (3, 4);
INSERT INTO `Friendship` VALUES (3, 6);
INSERT INTO `Friendship` VALUES (3, 7);
INSERT INTO `Friendship` VALUES (4, 6);
INSERT INTO `Friendship` VALUES (4, 7);
INSERT INTO `Friendship` VALUES (5, 6);
SET FOREIGN_KEY_CHECKS = 1;
-- ----------------------------
-- Table structure for Likes
-- ----------------------------
DROP TABLE IF EXISTS `Likes`;
CREATE TABLE `Likes` (
`user_id` int(0) NOT NULL,
`page_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of Likes
-- ----------------------------
INSERT INTO `Likes` VALUES (1, 18);
INSERT INTO `Likes` VALUES (1, 17);
INSERT INTO `Likes` VALUES (1, 12);
INSERT INTO `Likes` VALUES (2, 17);
INSERT INTO `Likes` VALUES (2, 11);
INSERT INTO `Likes` VALUES (2, 12);
INSERT INTO `Likes` VALUES (4, 11);
INSERT INTO `Likes` VALUES (4, 15);
INSERT INTO `Likes` VALUES (4, 18);
INSERT INTO `Likes` VALUES (5, 16);
INSERT INTO `Likes` VALUES (5, 15);
INSERT INTO `Likes` VALUES (5, 12);
INSERT INTO `Likes` VALUES (6, 19);
INSERT INTO `Likes` VALUES (6, 18);
INSERT INTO `Likes` VALUES (7, 13);
INSERT INTO `Likes` VALUES (7, 14);
INSERT INTO `Likes` VALUES (7, 16);
SET FOREIGN_KEY_CHECKS = 1;
with tmp1 as (
select if (user1_id = 1, user2_id, user1_id) as user_id
from Friendship
where user1_id = 1 || user2_id = 1
), tmp2 AS (
SELECT page_id
FROM Likes
WHERE user_id = 1
)
-- HERE IS THE SQL
select *
from Likes
where user_id IN (select * from tmp1)
Description: I create a simple CTE and use IF() in select. Everything works fine when i select * from the CTE. However, when CTE is used in the IN() clause, it produces wired result. How to repeat: -- ---------------------------- -- Table structure for Friendship -- ---------------------------- DROP TABLE IF EXISTS `Friendship`; CREATE TABLE `Friendship` ( `user1_id` int(0) NOT NULL, `user2_id` int(0) NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of Friendship -- ---------------------------- INSERT INTO `Friendship` VALUES (1, 2); INSERT INTO `Friendship` VALUES (1, 3); INSERT INTO `Friendship` VALUES (3, 4); INSERT INTO `Friendship` VALUES (3, 6); INSERT INTO `Friendship` VALUES (3, 7); INSERT INTO `Friendship` VALUES (4, 6); INSERT INTO `Friendship` VALUES (4, 7); INSERT INTO `Friendship` VALUES (5, 6); SET FOREIGN_KEY_CHECKS = 1; -- ---------------------------- -- Table structure for Likes -- ---------------------------- DROP TABLE IF EXISTS `Likes`; CREATE TABLE `Likes` ( `user_id` int(0) NOT NULL, `page_id` int(0) NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of Likes -- ---------------------------- INSERT INTO `Likes` VALUES (1, 18); INSERT INTO `Likes` VALUES (1, 17); INSERT INTO `Likes` VALUES (1, 12); INSERT INTO `Likes` VALUES (2, 17); INSERT INTO `Likes` VALUES (2, 11); INSERT INTO `Likes` VALUES (2, 12); INSERT INTO `Likes` VALUES (4, 11); INSERT INTO `Likes` VALUES (4, 15); INSERT INTO `Likes` VALUES (4, 18); INSERT INTO `Likes` VALUES (5, 16); INSERT INTO `Likes` VALUES (5, 15); INSERT INTO `Likes` VALUES (5, 12); INSERT INTO `Likes` VALUES (6, 19); INSERT INTO `Likes` VALUES (6, 18); INSERT INTO `Likes` VALUES (7, 13); INSERT INTO `Likes` VALUES (7, 14); INSERT INTO `Likes` VALUES (7, 16); SET FOREIGN_KEY_CHECKS = 1; with tmp1 as ( select if (user1_id = 1, user2_id, user1_id) as user_id from Friendship where user1_id = 1 || user2_id = 1 ), tmp2 AS ( SELECT page_id FROM Likes WHERE user_id = 1 ) -- HERE IS THE SQL select * from Likes where user_id IN (select * from tmp1)