dolt icon indicating copy to clipboard operation
dolt copied to clipboard
verified publisher icon dolthub

Incorrect collation returned by case expression

Open swjeong9 opened this issue 2 months ago • 5 comments

Bug Report

Environment:

  • go-mysql-server version: 0.20
  • VERSION(): 8.0.31

Issue: CASE expression returns utf8mb4_0900_bin collation even though:

  • Both columns are utf8mb4_unicode_ci
  • Table collation is utf8mb4_unicode_ci
  • collation_connection is utf8mb4_unicode_ci
session.SetSessionVariable(ctx, "autocommit", true)
session.SetSessionVariable(ctx, "character_set_server", "utf8mb4")
session.SetSessionVariable(ctx, "collation_server", "utf8mb4_unicode_ci")
session.SetSessionVariable(ctx, "character_set_database", "utf8mb4")
session.SetSessionVariable(ctx, "collation_database", "utf8mb4_unicode_ci")
session.SetSessionVariable(ctx, "character_set_connection", "utf8mb4")
session.SetSessionVariable(ctx, "collation_connection", "utf8mb4_unicode_ci")

Reproduction:

  • Our Table Schema
-- KBO Database Schema
-- Version 1.0

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;					/* Default : 1 (ON) */	
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;		/* Default : 1 (ON) */
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
/* @@SESSION, @@, @@ 생략된 것, 모두 session 변수임을 나타냄 */

DROP SCHEMA IF EXISTS kbo;

CREATE SCHEMA kbo;
USE kbo;
	
DROP TABLE IF EXISTS SCHEDULE;	
DROP TABLE IF EXISTS TEAM;	

-------------------------------------------
-- Schema
-------------------------------------------

CREATE TABLE TEAM 
(
       TEAM_ID		CHAR(3) 	NOT NULL,
       TEAM_NAME	VARCHAR(13) NOT NULL,
       TEAM_SHORT_NAME	VARCHAR(7),
       PREV_NAME	VARCHAR(15),
       REGION_NAME	VARCHAR(8),
       ORIG_YYYY	VARCHAR(4),
       WIN_COUNT	INT,
       STADIUM_ID	CHAR(3)		NOT NULL,
       
       CONSTRAINT 	PK_TEAM		PRIMARY KEY (TEAM_ID),
       CONSTRAINT 	FK_TEAM_STADIUM	FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID)
										ON DELETE RESTRICT
										ON UPDATE CASCADE
);

CREATE TABLE SCHEDULE 
(
       SCHE_INDEX	INT 		NOT NULL,
       SCHE_DATE	DATE 		NOT NULL,
       STADIUM_ID	CHAR(3) 	NOT NULL,
       HOME_SCORE	INT,
       AWAY_SCORE	INT,
       HOMETEAM_ID	CHAR(3) 	NOT NULL,
       AWAYTEAM_ID	CHAR(3) 	NOT NULL,
       CANCEL		CHAR(1),
       
       CONSTRAINT 	PK_SCHEDULE	PRIMARY KEY (SCHE_INDEX),
       CONSTRAINT 	FK_SCHEDULE_STADIUM	FOREIGN KEY (STADIUM_ID) REFERENCES STADIUM(STADIUM_ID)
                                                                      ON DELETE RESTRICT
										ON UPDATE CASCADE,
       CONSTRAINT 	FK_SCHEDULE_HOMETEAM	FOREIGN KEY (HOMETEAM_ID) REFERENCES TEAM(TEAM_ID)
                                                                      ON DELETE RESTRICT
										ON UPDATE CASCADE,
       CONSTRAINT 	FK_SCHEDULE_AWAYTEAM	FOREIGN KEY (AWAYTEAM_ID) REFERENCES TEAM(TEAM_ID)
                                                                      ON DELETE RESTRICT
										ON UPDATE CASCADE
);

-------------------------------------------
-- Data
-------------------------------------------


INSERT INTO TEAM VALUES
('K01','KIA Tigers','KIA','Haitai Tigers','Gwangju','1982',12,'G02'),
('K02','Samsung Lions','Samsung','Samsung Lions','Daegu','1982',8,'D01'),
('K03','LG Twins','LG','MBC Chungyong','Seoul','1982',3,'S02'),
('K04','Doosan Bears','Doosan','OB Bears','Seoul','1982',6,'S02'),
('K05','KT Wiz','KT','KT Wiz','Suwon','2013',1,'S03'),
('K06','SSG Landers','SSG','SK Wyverns','Incheon','2000',5,'I01'),
('K07','Lotte Giants','Lotte','Lotte Giants','Busan','1982',2,'S01'),
('K08','Hanwha Eagles','Hanwha','Binggrae Eagles','Daejeon','1986',1,'D02'),
('K09','NC Dinos','NC','NC Dinos','Changwon','2011',1,'C01'),
('K10','Kiwoom Heroes','Kiwoom','Nexen Heroes','Seoul','2008',0,'G01');

INSERT INTO SCHEDULE VALUES
(1,'2024-03-23','S03',2,6,'K05','K02','N'),
(2,'2024-03-23','S02',8,2,'K03','K08','N'),
(3,'2024-03-23','I01',5,3,'K06','K07','N'),
(4,'2024-03-23','C01',4,3,'K09','K04','N'),
(5,'2024-03-23','G02',7,5,'K01','K10','N'),
(6,'2024-03-24','S02',4,8,'K03','K08','N'),
(7,'2024-03-24','I01',7,6,'K06','K07','N'),
(8,'2024-03-24','C01',3,6,'K09','K04','N'),
(9,'2024-03-24','S03',8,11,'K05','K02','N'),
(10,'2024-03-24','G02',NULL,NULL,'K01','K10','Y'),
(11,'2024-03-26','G02',2,1,'K01','K07','N'),
(12,'2024-03-26','I01',0,6,'K06','K08','N'),
(13,'2024-03-26','C01',10,5,'K09','K10','N'),
(14,'2024-03-26','S02',4,3,'K03','K02','N'),
(15,'2024-03-26','S03',5,8,'K05','K04','N'),
(16,'2024-03-27','S02',2,2,'K03','K02','N'),
(17,'2024-03-27','I01',1,3,'K06','K08','N'),
(18,'2024-03-27','S03',8,11,'K05','K04','N'),
(19,'2024-03-27','G02',8,2,'K01','K07','N'),
(20,'2024-03-27','C01',6,2,'K09','K10','N'),
(21,'2024-03-28','C01',NULL,NULL,'K09','K10','Y'),
(22,'2024-03-28','S03',8,7,'K05','K04','N'),
(23,'2024-03-28','G02',NULL,NULL,'K01','K07','Y'),
(24,'2024-03-28','S02',18,1,'K03','K02','N'),
(25,'2024-03-28','I01',6,10,'K06','K08','N'),
(26,'2024-03-29','D02',3,2,'K08','K05','N'),
(27,'2024-03-29','S02',2,4,'K04','K01','N'),
(28,'2024-03-29','S01',3,1,'K07','K09','N'),
(29,'2024-03-29','D01',4,6,'K02','K06','N'),
(30,'2024-03-29','G01',0,3,'K10','K03','N'),
(31,'2024-03-30','D02',8,5,'K08','K05','N'),
(32,'2024-03-30','S02',8,0,'K04','K01','N'),
(33,'2024-03-30','S01',0,8,'K07','K09','N'),
(34,'2024-03-30','D01',6,9,'K02','K06','N'),
(35,'2024-03-30','G01',8,3,'K10','K03','N'),
(36,'2024-03-31','D01',3,4,'K02','K06','N'),
(37,'2024-03-31','G01',8,4,'K10','K03','N'),
(38,'2024-03-31','D02',14,3,'K08','K05','N'),
(39,'2024-03-31','S02',3,9,'K04','K01','N'),
(40,'2024-03-31','S01',7,8,'K07','K09','N'),
(41,'2024-04-02','I01',13,6,'K06','K04','N'),
(42,'2024-04-02','D01',3,8,'K02','K10','N'),
(43,'2024-04-02','S03',10,6,'K05','K01','N'),
(44,'2024-04-02','S02',5,7,'K03','K09','N'),
(45,'2024-04-02','D02',0,1,'K08','K07','N'),
(46,'2024-04-03','D02',NULL,NULL,'K08','K07','Y'),
(47,'2024-04-03','S02',5,0,'K03','K09','N'),
(48,'2024-04-03','I01',5,3,'K06','K04','N'),
(49,'2024-04-03','D01',NULL,NULL,'K02','K10','Y'),
(50,'2024-04-03','S03',1,5,'K05','K01','N'),
(51,'2024-04-04','S03',3,6,'K05','K01','N'),
(52,'2024-04-04','D01',1,10,'K02','K10','N'),
(53,'2024-04-04','I01',3,2,'K06','K04','N'),
(54,'2024-04-04','D02',6,5,'K08','K07','N'),
(55,'2024-04-04','S02',8,7,'K03','K09','N'),
(56,'2024-04-05','G01',11,7,'K10','K08','N'),
(57,'2024-04-05','S02',7,8,'K03','K05','N'),
(58,'2024-04-05','S01',3,4,'K07','K04','N'),
(59,'2024-04-05','C01',5,0,'K09','K06','N'),
(60,'2024-04-05','G02',5,2,'K01','K02','N'),
(61,'2024-04-06','G02',4,7,'K01','K02','N'),
(62,'2024-04-06','C01',16,3,'K09','K06','N'),
(63,'2024-04-06','G01',7,6,'K10','K08','N'),
(64,'2024-04-06','S02',8,4,'K03','K05','N'),
(65,'2024-04-06','S01',8,1,'K07','K04','N'),
(66,'2024-04-07','G01',4,3,'K10','K08','N'),
(67,'2024-04-07','G02',3,7,'K01','K02','N'),
(68,'2024-04-07','C01',10,1,'K09','K06','N'),
(69,'2024-04-07','S02',16,7,'K03','K05','N'),
(70,'2024-04-07','S01',7,6,'K07','K04','N'),
(71,'2024-04-09','S02',5,3,'K04','K08','N'),
(72,'2024-04-09','I01',8,5,'K06','K10','N'),
(73,'2024-04-09','S01',1,8,'K07','K02','N'),
(74,'2024-04-09','C01',1,6,'K09','K05','N'),
(75,'2024-04-09','G02',7,2,'K01','K03','N'),
(76,'2024-04-10','I01',8,4,'K06','K10','N'),
(77,'2024-04-10','G02',5,4,'K01','K03','N'),
(78,'2024-04-10','C01',3,2,'K09','K05','N'),
(79,'2024-04-10','S02',7,4,'K04','K08','N'),
(80,'2024-04-10','S01',7,10,'K07','K02','N'),
(81,'2024-04-11','S01',0,4,'K07','K02','N'),
(82,'2024-04-11','G02',8,4,'K01','K03','N'),
(83,'2024-04-11','S02',0,3,'K04','K08','N'),
(84,'2024-04-11','I01',2,5,'K06','K10','N'),
(85,'2024-04-11','C01',8,7,'K09','K05','N'),
(86,'2024-04-12','S03',8,3,'K05','K06','N'),
(87,'2024-04-12','S02',1,2,'K04','K03','N'),
(88,'2024-04-12','D01',3,8,'K02','K09','N'),
(89,'2024-04-12','G01',9,4,'K10','K07','N'),
(90,'2024-04-12','D02',4,8,'K08','K01','N'),
(91,'2024-04-13','S03',8,11,'K05','K06','N'),
(92,'2024-04-13','D01',3,4,'K02','K09','N'),
(93,'2024-04-13','G01',8,1,'K10','K07','N'),
(94,'2024-04-13','D02',9,11,'K08','K01','N'),
(95,'2024-04-13','S02',5,2,'K04','K03','N'),
(96,'2024-04-14','D01',12,5,'K02','K09','N'),
(97,'2024-04-14','S03',1,8,'K05','K06','N'),
(98,'2024-04-14','G01',7,5,'K10','K07','N'),
(99,'2024-04-14','D02',2,5,'K08','K01','N'),
(100,'2024-04-14','S02',9,5,'K04','K03','N'),
(101,'2024-04-16','C01',4,7,'K09','K08','N'),
(102,'2024-04-16','G01',6,3,'K10','K05','N'),
(103,'2024-04-16','I01',6,4,'K06','K01','N'),
(104,'2024-04-16','S02',7,2,'K03','K07','N'),
(105,'2024-04-16','D01',7,5,'K02','K04','N'),
(106,'2024-04-17','I01',3,11,'K06','K01','N'),
(107,'2024-04-17','D01',9,2,'K02','K04','N'),
(108,'2024-04-17','C01',4,3,'K09','K08','N'),
(109,'2024-04-17','G01',4,6,'K10','K05','N'),
(110,'2024-04-17','S02',6,5,'K03','K07','N'),
(111,'2024-04-18','C01',NULL,NULL,'K09','K08','Y'),
(112,'2024-04-18','S02',2,9,'K03','K07','N'),
(113,'2024-04-18','D01',5,2,'K02','K04','N'),
(114,'2024-04-18','G01',0,3,'K10','K05','N'),
(115,'2024-04-18','I01',7,5,'K06','K01','N'),
(116,'2024-04-19','D02',6,1,'K08','K02','N'),
(117,'2024-04-19','G02',4,3,'K01','K09','N'),
(118,'2024-04-19','S02',19,8,'K04','K10','N'),
(119,'2024-04-19','I01',1,4,'K06','K03','N'),
(120,'2024-04-19','S01',4,3,'K07','K05','N'),
(121,'2024-04-20','D02',0,1,'K08','K02','N'),
(122,'2024-04-20','S02',NULL,NULL,'K04','K10','Y'),
(123,'2024-04-20','I01',NULL,NULL,'K06','K03','Y'),
(124,'2024-04-20','S01',NULL,NULL,'K07','K05','Y'),
(125,'2024-04-20','G02',9,2,'K01','K09','N'),
(126,'2024-04-21','G02',4,15,'K01','K09','N'),
(127,'2024-04-21','S02',4,8,'K04','K10','N'),
(128,'2024-04-21','I01',8,10,'K06','K03','N'),
(129,'2024-04-21','S01',9,9,'K07','K05','N'),
(130,'2024-04-21','S02',2,1,'K04','K10','N'),
(131,'2024-04-21','I01',5,5,'K06','K03','N'),
(132,'2024-04-21','S01',7,5,'K07','K05','N'),
(133,'2024-04-21','D02',3,5,'K08','K02','N'),
(134,'2024-04-23','S02',4,3,'K04','K09','N'),
(135,'2024-04-23','S01',NULL,NULL,'K07','K06','Y'),
(136,'2024-04-23','D01',7,3,'K02','K03','N'),
(137,'2024-04-23','S03',9,6,'K05','K08','N'),
(138,'2024-04-23','G01',2,5,'K10','K01','N'),
(139,'2024-04-24','S02',1,3,'K04','K09','N'),
(140,'2024-04-24','S01',7,12,'K07','K06','N'),
(141,'2024-04-24','D01',6,0,'K02','K03','N'),
(142,'2024-04-24','S03',7,1,'K05','K08','N'),
(143,'2024-04-24','G01',4,6,'K10','K01','N'),
(144,'2024-04-25','S03',9,0,'K05','K08','N'),
(145,'2024-04-25','G01',2,13,'K10','K01','N'),
(146,'2024-04-25','D01',2,8,'K02','K03','N'),
(147,'2024-04-25','S02',7,3,'K04','K09','N'),
(148,'2024-04-25','S01',6,3,'K07','K06','N'),
(149,'2024-04-26','G01',0,3,'K10','K02','N'),
(150,'2024-04-26','D02',5,10,'K08','K04','N'),
(151,'2024-04-26','S02',7,6,'K03','K01','N'),
(152,'2024-04-26','I01',5,2,'K06','K05','N'),
(153,'2024-04-26','C01',4,0,'K09','K07','N'),
(154,'2024-04-27','G01',0,11,'K10','K02','N'),
(155,'2024-04-27','C01',2,0,'K09','K07','N'),
(156,'2024-04-27','I01',2,5,'K06','K05','N'),
(157,'2024-04-27','S02',6,3,'K03','K01','N'),
(158,'2024-04-27','D02',10,5,'K08','K04','N'),
(159,'2024-04-28','G01',6,11,'K10','K02','N'),
(160,'2024-04-28','C01',5,3,'K09','K07','N'),
(161,'2024-04-28','I01',11,6,'K06','K05','N'),
(162,'2024-04-28','S02',7,10,'K03','K01','N'),
(163,'2024-04-28','D02',8,17,'K08','K04','N'),
(164,'2024-04-30','D02',8,2,'K08','K06','N'),
(165,'2024-04-30','S02',4,0,'K04','K02','N'),
(166,'2024-04-30','S01',7,9,'K07','K10','N'),
(167,'2024-04-30','C01',8,0,'K09','K03','N'),
(168,'2024-04-30','G02',4,11,'K01','K05','N'),
(169,'2024-05-01','G02',9,1,'K01','K05','N'),
(170,'2024-05-01','C01',1,10,'K09','K03','N'),
(171,'2024-05-01','S01',3,6,'K07','K10','N'),
(172,'2024-05-01','S02',2,9,'K04','K02','N'),
(173,'2024-05-01','D02',7,8,'K08','K06','N'),
(174,'2024-05-02','G02',5,12,'K01','K05','N'),
(175,'2024-05-02','D02',3,4,'K08','K06','N'),
(176,'2024-05-02','S02',2,4,'K04','K02','N'),
(177,'2024-05-02','S01',6,5,'K07','K10','N'),
(178,'2024-05-02','C01',4,5,'K09','K03','N'),
(179,'2024-05-03','D01',7,8,'K02','K07','N'),
(180,'2024-05-03','I01',5,19,'K06','K09','N'),
(181,'2024-05-03','S03',2,1,'K05','K10','N'),
(182,'2024-05-03','G02',2,4,'K01','K08','N'),
(183,'2024-05-03','S02',4,6,'K03','K04','N'),
(184,'2024-05-04','I01',6,8,'K06','K09','N'),
(185,'2024-05-04','S03',6,2,'K05','K10','N'),
(186,'2024-05-04','G02',10,2,'K01','K08','N'),
(187,'2024-05-04','S02',2,3,'K03','K04','N'),
(188,'2024-05-04','D01',2,9,'K02','K07','N'),
(189,'2024-05-05','S02',NULL,NULL,'K03','K04','Y'),
(190,'2024-05-05','G02',NULL,NULL,'K01','K08','Y'),
(191,'2024-05-05','I01',NULL,NULL,'K06','K09','Y'),
(192,'2024-05-05','D01',NULL,NULL,'K02','K07','Y'),
(193,'2024-05-05','S03',NULL,NULL,'K05','K10','Y'),
(194,'2024-05-07','S02',2,4,'K03','K06','N'),
(195,'2024-05-07','S01',NULL,NULL,'K07','K08','Y'),
(196,'2024-05-07','D01',NULL,NULL,'K02','K01','Y'),
(197,'2024-05-07','S03',NULL,NULL,'K05','K09','Y'),
(198,'2024-05-07','G01',4,13,'K10','K04','N'),
(199,'2024-05-08','G01',1,6,'K10','K04','N'),
(200,'2024-05-08','S03',7,6,'K05','K09','N'),
(201,'2024-05-08','S01',6,1,'K07','K08','N'),
(202,'2024-05-08','S02',8,5,'K03','K06','N'),
(203,'2024-05-08','D01',2,4,'K02','K01','N'),
(204,'2024-05-09','S02',3,1,'K03','K06','N'),
(205,'2024-05-09','S01',18,5,'K07','K08','N'),
(206,'2024-05-09','D01',5,2,'K02','K01','N'),
(207,'2024-05-09','S03',6,2,'K05','K09','N'),
(208,'2024-05-09','G01',2,5,'K10','K04','N'),
(209,'2024-05-10','G02',2,4,'K01','K06','N'),
(210,'2024-05-10','C01',10,3,'K09','K02','N'),
(211,'2024-05-10','S01',1,9,'K07','K03','N'),
(212,'2024-05-10','S02',7,3,'K04','K05','N'),
(213,'2024-05-10','D02',5,4,'K08','K10','N'),
(214,'2024-05-11','D02',1,7,'K08','K10','N'),
(215,'2024-05-11','S02',NULL,NULL,'K04','K05','Y'),
(216,'2024-05-11','S01',1,2,'K07','K03','N'),
(217,'2024-05-11','C01',2,3,'K09','K02','N'),
(218,'2024-05-11','G02',NULL,NULL,'K01','K06','Y'),
(219,'2024-05-12','G02',6,10,'K01','K06','N'),
(220,'2024-05-12','C01',2,7,'K09','K02','N'),
(221,'2024-05-12','S02',12,4,'K04','K05','N'),
(222,'2024-05-12','S02',8,4,'K04','K05','N'),
(223,'2024-05-12','S01',4,6,'K07','K03','N'),
(224,'2024-05-12','D02',8,3,'K08','K10','N'),
(225,'2024-05-12','G02',3,2,'K01','K06','N'),
(226,'2024-05-14','I01',9,2,'K06','K02','N'),
(227,'2024-05-14','S02',0,5,'K03','K10','N'),
(228,'2024-05-14','S03',7,4,'K05','K07','N'),
(229,'2024-05-14','D02',5,5,'K08','K09','N'),
(230,'2024-05-14','G02',5,8,'K01','K04','N'),
(231,'2024-05-15','D02',1,16,'K08','K09','N'),
(232,'2024-05-15','G02',8,4,'K01','K04','N'),
(233,'2024-05-15','I01',NULL,NULL,'K06','K02','Y'),
(234,'2024-05-15','S02',NULL,NULL,'K03','K10','Y'),
(235,'2024-05-15','S03',NULL,NULL,'K05','K07','Y'),
(236,'2024-05-16','G02',7,7,'K01','K04','N'),
(237,'2024-05-16','S03',0,2,'K05','K07','N'),
(238,'2024-05-16','I01',4,12,'K06','K02','N'),
(239,'2024-05-16','S02',5,6,'K03','K10','N'),
(240,'2024-05-16','D02',3,4,'K08','K09','N'),
(241,'2024-05-17','S02',1,5,'K04','K07','N'),
(242,'2024-05-17','D01',7,5,'K02','K08','N'),
(243,'2024-05-17','C01',4,7,'K09','K01','N'),
(244,'2024-05-17','S03',5,13,'K05','K03','N'),
(245,'2024-05-17','G01',4,5,'K10','K06','N'),
(246,'2024-05-18','S02',8,3,'K04','K07','N'),
(247,'2024-05-18','D01',9,8,'K02','K08','N'),
(248,'2024-05-18','C01',2,7,'K09','K01','N'),
(249,'2024-05-18','S03',6,7,'K05','K03','N'),
(250,'2024-05-18','G01',0,3,'K10','K06','N'),
(251,'2024-05-19','S02',3,3,'K04','K07','N'),
(252,'2024-05-19','D01',2,12,'K02','K08','N'),
(253,'2024-05-19','C01',1,2,'K09','K01','N'),
(254,'2024-05-19','S03',10,4,'K05','K03','N'),
(255,'2024-05-19','G01',10,3,'K10','K06','N'),
(256,'2024-05-21','D02',8,4,'K08','K03','N'),
(257,'2024-05-21','S02',8,6,'K04','K06','N'),
(258,'2024-05-21','S01',6,1,'K07','K01','N'),
(259,'2024-05-21','D01',5,8,'K02','K05','N'),
(260,'2024-05-21','G01',3,5,'K10','K09','N'),
(261,'2024-05-22','S02',3,1,'K04','K06','N'),
(262,'2024-05-22','D01',3,1,'K02','K05','N'),
(263,'2024-05-22','G01',3,4,'K10','K09','N'),
(264,'2024-05-22','D02',7,5,'K08','K03','N'),
(265,'2024-05-22','S01',4,2,'K07','K01','N'),
(266,'2024-05-23','D02',4,8,'K08','K03','N'),
(267,'2024-05-23','S02',10,3,'K04','K06','N'),
(268,'2024-05-23','S01',10,6,'K07','K01','N'),
(269,'2024-05-23','D01',2,9,'K02','K05','N'),
(270,'2024-05-23','G01',6,4,'K10','K09','N'),
(271,'2024-05-24','S02',11,4,'K03','K09','N'),
(272,'2024-05-24','I01',5,7,'K06','K08','N'),
(273,'2024-05-24','S01',5,11,'K07','K02','N'),
(274,'2024-05-24','S03',5,4,'K05','K10','N'),
(275,'2024-05-24','G02',5,7,'K01','K04','N'),
(276,'2024-05-25','S03',5,2,'K05','K10','N'),
(277,'2024-05-25','S01',7,6,'K07','K02','N'),
(278,'2024-05-25','I01',2,4,'K06','K08','N'),
(279,'2024-05-25','G02',6,2,'K01','K04','N'),
(280,'2024-05-25','S02',10,6,'K03','K09','N'),
(281,'2024-05-26','S02',6,3,'K03','K09','N'),
(282,'2024-05-26','G02',5,2,'K01','K04','N'),
(283,'2024-05-26','S03',NULL,NULL,'K05','K10','Y'),
(284,'2024-05-26','S01',9,1,'K07','K02','N'),
(285,'2024-05-26','I01',NULL,NULL,'K06','K08','Y'),
(286,'2024-05-28','C01',8,11,'K09','K01','N'),
(287,'2024-05-28','D02',12,3,'K08','K07','N'),
(288,'2024-05-28','S02',3,12,'K04','K05','N'),
(289,'2024-05-28','I01',5,7,'K06','K03','N'),
(290,'2024-05-28','D01',3,4,'K02','K10','N'),
(291,'2024-05-29','C01',3,6,'K09','K01','N'),
(292,'2024-05-29','I01',4,13,'K06','K03','N'),
(293,'2024-05-29','S02',12,6,'K04','K05','N'),
(294,'2024-05-29','D01',5,11,'K02','K10','N'),
(295,'2024-05-29','D02',3,0,'K08','K07','N'),
(296,'2024-05-30','D02',15,0,'K08','K07','N'),
(297,'2024-05-30','S02',6,1,'K04','K05','N'),
(298,'2024-05-30','I01',8,2,'K06','K03','N'),
(299,'2024-05-30','D01',4,2,'K02','K10','N'),
(300,'2024-05-30','C01',2,11,'K09','K01','N'),
(301,'2024-05-31','D01',8,6,'K02','K08','N'),
(302,'2024-05-31','G02',5,8,'K01','K05','N'),
(303,'2024-05-31','G01',2,4,'K10','K06','N'),
(304,'2024-05-31','S02',3,6,'K04','K03','N'),
(305,'2024-05-31','S01',13,5,'K07','K09','N'),
(306,'2024-06-01','G01',0,9,'K10','K06','N'),
(307,'2024-06-01','G02',4,2,'K01','K05','N'),
(308,'2024-06-01','S02',5,8,'K04','K03','N'),
(309,'2024-06-01','S01',2,4,'K07','K09','N'),
(310,'2024-06-01','D01',6,4,'K02','K08','N'),
(311,'2024-06-02','S02',1,9,'K04','K03','N'),
(312,'2024-06-02','G01',2,6,'K10','K06','N'),
(313,'2024-06-02','S01',13,4,'K07','K09','N'),
(314,'2024-06-02','D01',1,0,'K02','K08','N'),
(315,'2024-06-02','G02',3,11,'K01','K05','N'),
(316,'2024-06-04','I01',1,3,'K06','K02','N'),
(317,'2024-06-04','C01',1,4,'K09','K04','N'),
(318,'2024-06-04','S03',2,8,'K05','K08','N'),
(319,'2024-06-04','G02',0,6,'K01','K07','N'),
(320,'2024-06-04','S02',3,11,'K03','K10','N'),
(321,'2024-06-05','S02',4,2,'K03','K10','N'),
(322,'2024-06-05','I01',7,3,'K06','K02','N'),
(323,'2024-06-05','C01',3,4,'K09','K04','N'),
(324,'2024-06-05','S03',2,12,'K05','K08','N'),
(325,'2024-06-05','G02',3,9,'K01','K07','N'),
(326,'2024-06-06','I01',4,0,'K06','K02','N'),
(327,'2024-06-06','G02',5,4,'K01','K07','N'),
(328,'2024-06-06','S02',8,4,'K03','K10','N'),
(329,'2024-06-06','C01',4,8,'K09','K04','N'),
(330,'2024-06-06','S03',0,6,'K05','K08','N'),
(331,'2024-06-07','S03',7,8,'K05','K03','N'),
(332,'2024-06-07','G01',7,5,'K10','K02','N'),
(333,'2024-06-07','S02',6,5,'K04','K01','N'),
(334,'2024-06-07','D02',2,6,'K08','K09','N'),
(335,'2024-06-07','S01',11,7,'K07','K06','N'),
(336,'2024-06-08','D02',2,4,'K08','K09','N'),
(337,'2024-06-08','S02',9,8,'K04','K01','N'),
(338,'2024-06-08','S01',NULL,NULL,'K07','K06','Y'),
(339,'2024-06-08','S03',2,8,'K05','K03','N'),
(340,'2024-06-08','G01',10,3,'K10','K02','N'),
(341,'2024-06-09','S01',5,3,'K07','K06','N'),
(342,'2024-06-09','S03',11,2,'K05','K03','N'),
(343,'2024-06-09','D02',3,3,'K08','K09','N'),
(344,'2024-06-09','S01',1,5,'K07','K06','N'),
(345,'2024-06-09','G01',1,7,'K10','K02','N'),
(346,'2024-06-09','S02',2,8,'K04','K01','N'),
(347,'2024-06-11','S02',1,6,'K04','K08','N'),
(348,'2024-06-11','S01',2,5,'K07','K10','N'),
(349,'2024-06-11','D01',6,4,'K02','K03','N'),
(350,'2024-06-11','C01',8,6,'K09','K05','N'),
(351,'2024-06-11','I01',7,6,'K06','K01','N'),
(352,'2024-06-12','S01',9,2,'K07','K10','N'),
(353,'2024-06-12','I01',7,13,'K06','K01','N'),
(354,'2024-06-12','S02',3,4,'K04','K08','N'),
(355,'2024-06-12','C01',8,11,'K09','K05','N'),
(356,'2024-06-12','D01',5,4,'K02','K03','N'),
(357,'2024-06-13','I01',7,1,'K06','K01','N'),
(358,'2024-06-13','S02',9,6,'K04','K08','N'),
(359,'2024-06-13','S01',18,10,'K07','K10','N'),
(360,'2024-06-13','D01',6,3,'K02','K03','N'),
(361,'2024-06-13','C01',5,3,'K09','K05','N'),
(362,'2024-06-14','D02',4,11,'K08','K06','N'),
(363,'2024-06-14','S02',5,3,'K03','K07','N'),
(364,'2024-06-14','C01',4,7,'K09','K02','N'),
(365,'2024-06-14','G01',4,6,'K10','K04','N'),
(366,'2024-06-14','S03',1,11,'K05','K01','N'),
(367,'2024-06-15','D02',1,9,'K08','K06','N'),
(368,'2024-06-15','S02',8,9,'K03','K07','N'),
(369,'2024-06-15','C01',6,4,'K09','K02','N'),
(370,'2024-06-15','S03',1,2,'K05','K01','N'),
(371,'2024-06-15','G01',1,4,'K10','K04','N'),
(372,'2024-06-16','G01',8,2,'K10','K04','N'),
(373,'2024-06-16','D02',4,1,'K08','K06','N'),
(374,'2024-06-16','S02',9,8,'K03','K07','N'),
(375,'2024-06-16','C01',5,4,'K09','K02','N'),
(376,'2024-06-16','S03',1,3,'K05','K01','N'),
(377,'2024-06-18','D01',3,8,'K02','K06','N'),
(378,'2024-06-18','S03',6,4,'K05','K07','N'),
(379,'2024-06-18','C02',3,0,'K08','K10','N'),
(380,'2024-06-18','S02',6,2,'K04','K09','N'),
(381,'2024-06-18','G02',11,4,'K01','K03','N'),
(382,'2024-06-19','C02',14,11,'K08','K10','N'),
(383,'2024-06-19','G02',5,7,'K01','K03','N'),
(384,'2024-06-19','S03',5,13,'K05','K07','N'),
(385,'2024-06-19','D01',13,2,'K02','K06','N'),
(386,'2024-06-19','S02',5,7,'K04','K09','N'),
(387,'2024-06-20','C02',0,7,'K08','K10','N'),
(388,'2024-06-20','G02',6,5,'K01','K03','N'),
(389,'2024-06-20','S03',7,6,'K05','K07','N'),
(390,'2024-06-20','S02',2,0,'K04','K09','N'),
(391,'2024-06-20','D01',4,0,'K02','K06','N'),
(392,'2024-06-21','I01',2,3,'K06','K09','N'),
(393,'2024-06-21','S02',2,3,'K03','K05','N'),
(394,'2024-06-21','G02',8,3,'K01','K08','N'),
(395,'2024-06-21','G01',5,2,'K10','K07','N'),
(396,'2024-06-21','D01',7,4,'K02','K04','N'),
(397,'2024-06-22','G02',NULL,NULL,'K01','K08','Y'),
(398,'2024-06-22','G01',1,6,'K10','K07','N'),
(399,'2024-06-22','I01',12,4,'K06','K09','N'),
(400,'2024-06-22','D01',NULL,NULL,'K02','K04','Y'),
(401,'2024-06-22','S02',NULL,NULL,'K03','K05','Y'),
(402,'2024-06-23','D01',10,4,'K02','K04','N'),
(403,'2024-06-23','G01',2,10,'K10','K07','N'),
(404,'2024-06-23','S02',7,2,'K03','K05','N'),
(405,'2024-06-23','D01',7,4,'K02','K04','N'),
(406,'2024-06-23','G02',8,9,'K01','K08','N'),
(407,'2024-06-23','I01',6,18,'K06','K09','N'),
(408,'2024-06-23','S02',3,4,'K03','K05','N'),
(409,'2024-06-23','G02',4,1,'K01','K08','N'),
(410,'2024-06-25','D02',5,4,'K08','K04','N'),
(411,'2024-06-25','S02',4,0,'K03','K02','N'),
(412,'2024-06-25','I01',1,6,'K06','K05','N'),
(413,'2024-06-25','S01',15,15,'K07','K01','N'),
(414,'2024-06-25','G01',6,5,'K10','K09','N'),
(415,'2024-06-26','G01',10,7,'K10','K09','N'),
(416,'2024-06-26','I01',10,5,'K06','K05','N'),
(417,'2024-06-26','S02',2,1,'K03','K02','N'),
(418,'2024-06-26','S01',6,4,'K07','K01','N'),
(419,'2024-06-26','D02',8,15,'K08','K04','N'),
(420,'2024-06-27','D02',8,3,'K08','K04','N'),
(421,'2024-06-27','S02',1,2,'K03','K02','N'),
(422,'2024-06-27','I01',8,16,'K06','K05','N'),
(423,'2024-06-27','S01',11,2,'K07','K01','N'),
(424,'2024-06-27','G01',9,7,'K10','K09','N'),
(425,'2024-06-28','G02',6,17,'K01','K10','N'),
(426,'2024-06-28','S02',10,0,'K04','K06','N'),
(427,'2024-06-28','S01',6,4,'K07','K08','N'),
(428,'2024-06-28','C01',8,2,'K09','K03','N'),
(429,'2024-06-28','S03',5,4,'K05','K02','N'),
(430,'2024-06-29','G02',NULL,NULL,'K01','K10','Y'),
(431,'2024-06-29','S03',NULL,NULL,'K05','K02','Y'),
(432,'2024-06-29','C01',NULL,NULL,'K09','K03','Y'),
(433,'2024-06-29','S01',NULL,NULL,'K07','K08','Y'),
(434,'2024-06-29','S02',0,6,'K04','K06','N'),
(435,'2024-06-30','G02',NULL,NULL,'K01','K10','Y'),
(436,'2024-06-30','S01',NULL,NULL,'K07','K08','Y'),
(437,'2024-06-30','S03',2,1,'K05','K02','N'),
(438,'2024-06-30','S01',NULL,NULL,'K07','K08','Y'),
(439,'2024-06-30','C01',NULL,NULL,'K09','K03','Y'),
(440,'2024-06-30','S03',2,2,'K05','K02','N'),
(441,'2024-06-30','G02',NULL,NULL,'K01','K10','Y'),
(442,'2024-06-30','S02',1,3,'K04','K06','N'),
(443,'2024-06-30','C01',6,9,'K09','K03','N'),
(444,'2024-07-02','C01',NULL,NULL,'K09','K06','Y'),
(445,'2024-07-02','D02',5,6,'K08','K05','N'),
(446,'2024-07-02','D01',5,9,'K02','K01','N'),
(447,'2024-07-02','S02',NULL,NULL,'K04','K07','Y'),
(448,'2024-07-02','G01',4,2,'K10','K03','N'),
(449,'2024-07-03','D02',2,3,'K08','K05','N'),
(450,'2024-07-03','S02',13,8,'K04','K07','N'),
(451,'2024-07-03','D01',4,6,'K02','K01','N'),
(452,'2024-07-03','C01',4,1,'K09','K06','N'),
(453,'2024-07-03','G01',4,1,'K10','K03','N'),
(454,'2024-07-04','D02',13,5,'K08','K05','N'),
(455,'2024-07-04','S02',6,3,'K04','K07','N'),
(456,'2024-07-04','D01',3,8,'K02','K01','N'),
(457,'2024-07-04','C01',2,0,'K09','K06','N'),
(458,'2024-07-04','G01',2,3,'K10','K03','N'),
(459,'2024-07-09','S03',NULL,NULL,'K05','K04','Y'),
(460,'2024-07-09','D01',NULL,NULL,'K02','K09','Y'),
(461,'2024-07-09','G01',5,3,'K10','K08','N'),
(462,'2024-07-09','I01',7,4,'K06','K07','N'),
(463,'2024-07-09','S02',4,11,'K03','K01','N'),
(464,'2024-07-10','G01',0,7,'K10','K08','N'),
(465,'2024-07-10','I01',1,6,'K06','K07','N'),
(466,'2024-07-10','D01',15,6,'K02','K09','N'),
(467,'2024-07-10','S03',7,6,'K05','K04','N'),
(468,'2024-07-10','S02',2,5,'K03','K01','N'),
(469,'2024-07-11','G01',5,4,'K10','K08','N'),
(470,'2024-07-11','S03',1,12,'K05','K04','N'),
(471,'2024-07-11','D01',6,4,'K02','K09','N'),
(472,'2024-07-11','I01',5,4,'K06','K07','N'),
(473,'2024-07-11','S02',2,4,'K03','K01','N'),
(474,'2024-07-12','S01',4,5,'K07','K05','N'),
(475,'2024-07-12','S02',5,9,'K04','K02','N'),
(476,'2024-07-12','D02',6,0,'K08','K03','N'),
(477,'2024-07-12','C01',9,2,'K09','K10','N'),
(478,'2024-07-12','G02',6,14,'K01','K06','N'),
(479,'2024-07-13','D02',3,7,'K08','K03','N'),
(480,'2024-07-13','S02',8,4,'K04','K02','N'),
(481,'2024-07-13','S01',3,6,'K07','K05','N'),
(482,'2024-07-13','G02',9,15,'K01','K06','N'),
(483,'2024-07-13','C01',11,5,'K09','K10','N'),
(484,'2024-07-14','C01',NULL,NULL,'K09','K10','Y'),
(485,'2024-07-14','S01',NULL,NULL,'K07','K05','Y'),
(486,'2024-07-14','S02',2,6,'K04','K02','N'),
(487,'2024-07-14','G02',13,4,'K01','K06','N'),
(488,'2024-07-14','D02',4,8,'K08','K03','N'),
(489,'2024-07-16','U01',4,0,'K07','K04','N'),
(490,'2024-07-16','G01',3,4,'K10','K05','N'),
(491,'2024-07-16','G02',NULL,NULL,'K01','K02','Y'),
(492,'2024-07-16','C01',NULL,NULL,'K09','K08','Y'),
(493,'2024-07-16','S02',NULL,NULL,'K03','K06','Y'),
(494,'2024-07-17','U01',6,2,'K07','K04','N'),
(495,'2024-07-17','G01',2,9,'K10','K05','N'),
(496,'2024-07-17','G02',10,5,'K01','K02','N'),
(497,'2024-07-17','C01',5,1,'K09','K08','N'),
(498,'2024-07-17','S02',12,9,'K03','K06','N'),
(499,'2024-07-18','G01',8,12,'K10','K05','N'),
(500,'2024-07-18','U01',2,3,'K07','K04','N'),
(501,'2024-07-18','C01',4,3,'K09','K08','N'),
(502,'2024-07-18','G02',10,4,'K01','K02','N'),
(503,'2024-07-18','S02',NULL,NULL,'K03','K06','Y'),
(504,'2024-07-19','I01',9,3,'K06','K10','N'),
(505,'2024-07-19','S02',16,7,'K03','K04','N'),
(506,'2024-07-19','S03',6,0,'K05','K09','N'),
(507,'2024-07-19','D01',6,10,'K02','K07','N'),
(508,'2024-07-19','D02',3,7,'K08','K01','N'),
(509,'2024-07-20','I01',NULL,NULL,'K06','K10','Y'),
(510,'2024-07-20','S03',NULL,NULL,'K05','K09','Y'),
(511,'2024-07-20','D01',21,4,'K02','K07','N'),
(512,'2024-07-20','S02',NULL,NULL,'K03','K04','Y'),
(513,'2024-07-20','D02',4,8,'K08','K01','N'),
(514,'2024-07-21','I01',2,7,'K06','K10','N'),
(515,'2024-07-21','D01',6,5,'K02','K07','N'),
(516,'2024-07-21','D02',7,8,'K08','K01','N'),
(517,'2024-07-21','S03',2,8,'K05','K09','N'),
(518,'2024-07-21','S02',6,3,'K03','K04','N'),
(519,'2024-07-23','S02',6,3,'K04','K10','N'),
(520,'2024-07-23','S01',1,2,'K07','K03','N'),
(521,'2024-07-23','S03',NULL,NULL,'K05','K06','Y'),
(522,'2024-07-23','G02',8,1,'K01','K09','N'),
(523,'2024-07-23','D02',6,5,'K08','K02','N'),
(524,'2024-07-24','D02',3,2,'K08','K02','N'),
(525,'2024-07-24','S02',7,4,'K04','K10','N'),
(526,'2024-07-24','S01',NULL,NULL,'K07','K03','Y'),
(527,'2024-07-24','S03',5,3,'K05','K06','N'),
(528,'2024-07-24','G02',7,0,'K01','K09','N'),
(529,'2024-07-25','S01',6,9,'K07','K03','N'),
(530,'2024-07-25','S03',4,2,'K05','K06','N'),
(531,'2024-07-25','G02',4,7,'K01','K09','N'),
(532,'2024-07-25','D02',NULL,NULL,'K08','K02','Y'),
(533,'2024-07-25','S02',1,6,'K04','K10','N'),
(534,'2024-07-26','S02',NULL,NULL,'K03','K08','Y'),
(535,'2024-07-26','I01',6,1,'K06','K04','N'),
(536,'2024-07-26','D01',1,4,'K02','K05','N'),
(537,'2024-07-26','C01',9,2,'K09','K07','N'),
(538,'2024-07-26','G01',5,4,'K10','K01','N'),
(539,'2024-07-27','S02',NULL,NULL,'K03','K08','Y'),
(540,'2024-07-27','I01',4,3,'K06','K04','N'),
(541,'2024-07-27','D01',3,2,'K02','K05','N'),
(542,'2024-07-27','C01',2,9,'K09','K07','N'),
(543,'2024-07-27','G01',6,5,'K10','K01','N'),
(544,'2024-07-28','G01',3,4,'K10','K01','N'),
(545,'2024-07-28','S02',6,9,'K03','K08','N'),
(546,'2024-07-28','I01',3,0,'K06','K04','N'),
(547,'2024-07-28','D01',3,4,'K02','K05','N'),
(548,'2024-07-28','C01',6,10,'K09','K07','N'),
(549,'2024-07-30','S02',1,7,'K03','K02','N'),
(550,'2024-07-30','I01',11,5,'K06','K07','N'),
(551,'2024-07-30','S03',4,6,'K05','K08','N'),
(552,'2024-07-30','G02',7,12,'K01','K04','N'),
(553,'2024-07-30','G01',6,2,'K10','K09','N'),
(554,'2024-07-31','G01',0,9,'K10','K09','N'),
(555,'2024-07-31','S03',7,18,'K05','K08','N'),
(556,'2024-07-31','G02',6,30,'K01','K04','N'),
(557,'2024-07-31','S02',11,5,'K03','K02','N'),
(558,'2024-07-31','I01',12,11,'K06','K07','N'),
(559,'2024-08-01','I01',2,4,'K06','K07','N'),
(560,'2024-08-01','S03',8,14,'K05','K08','N'),
(561,'2024-08-01','G02',0,1,'K01','K04','N'),
(562,'2024-08-01','G01',14,9,'K10','K09','N'),
(563,'2024-08-01','S02',0,7,'K03','K02','N'),
(564,'2024-08-02','D02',10,3,'K08','K01','N'),
(565,'2024-08-02','S02',4,6,'K04','K10','N'),
(566,'2024-08-02','D01',4,3,'K02','K06','N'),
(567,'2024-08-02','C01',7,9,'K09','K05','N'),
(568,'2024-08-02','U01',NULL,NULL,'K07','K03','Y'),
(569,'2024-08-03','D02',3,7,'K08','K01','N'),
(570,'2024-08-03','D01',12,4,'K02','K06','N'),
(571,'2024-08-03','C01',NULL,NULL,'K09','K05','Y'),
(572,'2024-08-03','U01',8,3,'K07','K03','N'),
(573,'2024-08-03','S02',5,15,'K04','K10','N'),
(574,'2024-08-04','D02',NULL,NULL,'K08','K01','Y'),
(575,'2024-08-04','S02',NULL,NULL,'K04','K10','Y'),
(576,'2024-08-04','D01',8,3,'K02','K06','N'),
(577,'2024-08-04','U01',NULL,NULL,'K07','K03','Y'),
(578,'2024-08-04','C01',15,5,'K09','K05','N'),
(579,'2024-08-06','S02',7,6,'K04','K03','N'),
(580,'2024-08-06','S01',6,5,'K07','K09','N'),
(581,'2024-08-06','D01',5,8,'K02','K08','N'),
(582,'2024-08-06','G02',2,0,'K01','K05','N'),
(583,'2024-08-06','G01',2,6,'K10','K06','N'),
(584,'2024-08-07','S02',8,4,'K04','K03','N'),
(585,'2024-08-07','G01',2,6,'K10','K06','N'),
(586,'2024-08-07','S01',14,7,'K07','K09','N'),
(587,'2024-08-07','D01',10,6,'K02','K08','N'),
(588,'2024-08-07','G02',2,13,'K01','K05','N'),
(589,'2024-08-08','S02',3,10,'K04','K03','N'),
(590,'2024-08-08','S01',NULL,NULL,'K07','K09','Y'),
(591,'2024-08-08','D01',4,6,'K02','K08','N'),
(592,'2024-08-08','G02',0,1,'K01','K05','N'),
(593,'2024-08-08','G01',3,0,'K10','K06','N'),
(594,'2024-08-09','S02',10,9,'K03','K09','N'),
(595,'2024-08-09','I01',11,13,'K06','K04','N'),
(596,'2024-08-09','S03',10,6,'K05','K07','N'),
(597,'2024-08-09','G02',9,8,'K01','K02','N'),
(598,'2024-08-09','D02',7,5,'K08','K10','N'),
(599,'2024-08-10','G02',NULL,NULL,'K01','K02','Y'),
(600,'2024-08-10','S02',9,3,'K03','K09','N'),
(601,'2024-08-10','I01',6,10,'K06','K04','N'),
(602,'2024-08-10','S03',0,2,'K05','K07','N'),
(603,'2024-08-10','D02',1,3,'K08','K10','N'),
(604,'2024-08-11','I01',11,9,'K06','K04','N'),
(605,'2024-08-11','S02',4,3,'K03','K09','N'),
(606,'2024-08-11','D02',3,7,'K08','K10','N'),
(607,'2024-08-11','S03',7,9,'K05','K07','N'),
(608,'2024-08-11','G02',4,5,'K01','K02','N'),
(609,'2024-08-13','D02',2,3,'K08','K03','N'),
(610,'2024-08-13','S02',NULL,NULL,'K04','K07','Y'),
(611,'2024-08-13','D01',7,2,'K02','K05','N'),
(612,'2024-08-13','C01',0,2,'K09','K06','N'),
(613,'2024-08-13','G01',0,2,'K10','K01','N'),
(614,'2024-08-14','D02',9,5,'K08','K03','N'),
(615,'2024-08-14','D01',3,1,'K02','K05','N'),
(616,'2024-08-14','C01',NULL,NULL,'K09','K06','Y'),
(617,'2024-08-14','G01',2,1,'K10','K01','N'),
(618,'2024-08-14','S02',2,12,'K04','K07','N'),
(619,'2024-08-15','G01',1,12,'K10','K01','N'),
(620,'2024-08-15','D02',3,17,'K08','K03','N'),
(621,'2024-08-15','S02',4,3,'K04','K07','N'),
(622,'2024-08-15','C01',5,10,'K09','K06','N'),
(623,'2024-08-15','D01',3,5,'K02','K05','N'),
(624,'2024-08-16','S02',2,3,'K03','K01','N'),
(625,'2024-08-16','I01',1,2,'K06','K08','N'),
(626,'2024-08-16','S01',4,0,'K07','K10','N'),
(627,'2024-08-16','C01',3,7,'K09','K02','N'),
(628,'2024-08-16','S03',0,5,'K05','K04','N'),
(629,'2024-08-17','I01',5,8,'K06','K08','N'),
(630,'2024-08-17','C01',4,5,'K09','K02','N'),
(631,'2024-08-17','S03',2,3,'K05','K04','N'),
(632,'2024-08-17','S02',4,14,'K03','K01','N'),
(633,'2024-08-17','S01',7,8,'K07','K10','N'),
(634,'2024-08-18','S02',0,4,'K03','K01','N'),
(635,'2024-08-18','I01',1,7,'K06','K08','N'),
(636,'2024-08-18','S01',5,4,'K07','K10','N'),
(637,'2024-08-18','C01',3,5,'K09','K02','N'),
(638,'2024-08-18','S03',5,4,'K05','K04','N'),
(639,'2024-08-20','C02',3,2,'K08','K09','N'),
(640,'2024-08-20','S03',2,3,'K05','K10','N'),
(641,'2024-08-20','G02',NULL,NULL,'K01','K07','Y'),
(642,'2024-08-20','S02',4,3,'K03','K06','N'),
(643,'2024-08-20','P01',3,0,'K02','K04','N'),
(644,'2024-08-21','S02',1,5,'K03','K06','N'),
(645,'2024-08-21','S03',5,0,'K05','K10','N'),
(646,'2024-08-21','G02',6,5,'K01','K07','N'),
(647,'2024-08-21','P01',2,5,'K02','K04','N'),
(648,'2024-08-21','C02',2,8,'K08','K09','N'),
(649,'2024-08-22','S02',13,3,'K03','K06','N'),
(650,'2024-08-22','S03',3,2,'K05','K10','N'),
(651,'2024-08-22','G02',6,4,'K01','K07','N'),
(652,'2024-08-22','C02',NULL,NULL,'K08','K09','Y'),
(653,'2024-08-22','P01',NULL,NULL,'K02','K04','Y'),
(654,'2024-08-23','I01',6,11,'K06','K05','N'),
(655,'2024-08-23','S02',4,7,'K04','K08','N'),
(656,'2024-08-23','D01',5,3,'K02','K07','N'),
(657,'2024-08-23','G01',9,3,'K10','K03','N'),
(658,'2024-08-23','C01',17,4,'K09','K01','N'),
(659,'2024-08-24','S02',6,7,'K04','K08','N'),
(660,'2024-08-24','I01',3,9,'K06','K05','N'),
(661,'2024-08-24','D01',3,11,'K02','K07','N'),
(662,'2024-08-24','C01',0,2,'K09','K01','N'),
(663,'2024-08-24','G01',0,7,'K10','K03','N'),
(664,'2024-08-25','D01',10,5,'K02','K07','N'),
(665,'2024-08-25','C01',8,2,'K09','K01','N'),
(666,'2024-08-25','S02',1,3,'K04','K08','N'),
(667,'2024-08-25','G01',6,4,'K10','K03','N'),
(668,'2024-08-25','I01',4,3,'K06','K05','N'),
(669,'2024-08-27','S01',3,1,'K07','K08','N'),
(670,'2024-08-27','C01',7,13,'K09','K04','N'),
(671,'2024-08-27','G02',10,4,'K01','K06','N'),
(672,'2024-08-27','G01',0,1,'K10','K02','N'),
(673,'2024-08-27','S02',6,1,'K03','K05','N'),
(674,'2024-08-28','S02',4,8,'K03','K05','N'),
(675,'2024-08-28','S01',0,7,'K07','K08','N'),
(676,'2024-08-28','C01',1,8,'K09','K04','N'),
(677,'2024-08-28','G02',7,6,'K01','K06','N'),
(678,'2024-08-28','G01',5,9,'K10','K02','N'),
(679,'2024-08-29','G01',0,1,'K10','K02','N'),
(680,'2024-08-29','S02',7,8,'K03','K05','N'),
(681,'2024-08-29','S01',14,11,'K07','K08','N'),
(682,'2024-08-29','C01',10,2,'K09','K04','N'),
(683,'2024-08-29','G02',4,10,'K01','K06','N'),
(684,'2024-08-30','S03',7,11,'K05','K03','N'),
(685,'2024-08-30','G01',2,8,'K10','K07','N'),
(686,'2024-08-31','D01',13,15,'K02','K01','N'),
(687,'2024-08-31','I01',2,7,'K06','K09','N'),
(688,'2024-08-31','D02',2,6,'K08','K05','N'),
(689,'2024-08-31','S02',4,7,'K04','K07','N'),
(690,'2024-09-01','D01',5,6,'K02','K01','N'),
(691,'2024-09-01','I01',2,8,'K06','K09','N'),
(692,'2024-09-01','S02',3,4,'K04','K07','N'),
(693,'2024-09-03','D02',7,1,'K08','K04','N'),
(694,'2024-09-03','D01',5,1,'K02','K07','N'),
(695,'2024-09-03','C01',11,5,'K09','K10','N'),
(696,'2024-09-03','G02',7,5,'K01','K03','N'),
(697,'2024-09-04','S01',7,5,'K07','K05','N'),
(698,'2024-09-04','D01',7,3,'K02','K04','N'),
(699,'2024-09-04','C01',13,6,'K09','K10','N'),
(700,'2024-09-04','G02',4,5,'K01','K08','N'),
(701,'2024-09-04','S02',5,0,'K03','K06','N'),
(702,'2024-09-05','S02',2,4,'K03','K06','N'),
(703,'2024-09-05','S01',2,12,'K07','K05','N'),
(704,'2024-09-05','C01',7,12,'K09','K10','N'),
(705,'2024-09-05','G02',4,3,'K01','K08','N'),
(706,'2024-09-06','C01',3,9,'K09','K05','N'),
(707,'2024-09-06','S01',2,7,'K07','K02','N'),
(708,'2024-09-06','S02',1,3,'K03','K08','N'),
(709,'2024-09-06','G02',14,0,'K01','K10','N'),
(710,'2024-09-07','S02',9,3,'K03','K08','N'),
(711,'2024-09-07','D01',1,9,'K02','K09','N'),
(712,'2024-09-07','S03',2,12,'K05','K04','N'),
(713,'2024-09-07','G02',6,2,'K01','K10','N'),
(714,'2024-09-07','S01',1,1,'K07','K06','N'),
(715,'2024-09-08','S02',14,3,'K03','K08','N'),
(716,'2024-09-08','S01',6,11,'K07','K06','N'),
(717,'2024-09-08','D01',10,2,'K02','K09','N'),
(718,'2024-09-08','G02',5,2,'K01','K10','N'),
(719,'2024-09-10','S02',1,2,'K03','K07','N'),
(720,'2024-09-10','I01',5,0,'K06','K08','N'),
(721,'2024-09-10','S03',11,8,'K05','K09','N'),
(722,'2024-09-10','G01',7,1,'K10','K04','N'),
(723,'2024-09-11','D02',1,10,'K08','K02','N'),
(724,'2024-09-11','S02',5,9,'K03','K10','N'),
(725,'2024-09-11','I01',2,10,'K06','K07','N'),
(726,'2024-09-11','S03',2,1,'K05','K09','N'),
(727,'2024-09-12','D02',1,7,'K08','K02','N'),
(728,'2024-09-12','S02',NULL,NULL,'K03','K10','Y'),
(729,'2024-09-12','S03',10,4,'K05','K09','N'),
(730,'2024-09-12','G02',10,0,'K01','K07','N'),
(731,'2024-09-13','S02',NULL,NULL,'K04','K09','Y'),
(732,'2024-09-13','I01',NULL,NULL,'K06','K03','Y'),
(733,'2024-09-13','S01',4,8,'K07','K08','N'),
(734,'2024-09-14','I01',11,9,'K06','K02','N'),
(735,'2024-09-14','S01',12,9,'K07','K08','N'),
(736,'2024-09-14','S02',2,1,'K04','K05','N'),
(737,'2024-09-14','C01',7,10,'K09','K03','N'),
(738,'2024-09-14','G02',3,2,'K01','K10','N'),
(739,'2024-09-15','C01',4,1,'K09','K03','N'),
(740,'2024-09-15','S01',16,9,'K07','K08','N'),
(741,'2024-09-15','I01',14,9,'K06','K02','N'),
(742,'2024-09-15','G02',5,10,'K01','K10','N'),
(743,'2024-09-16','S02',5,4,'K04','K10','N'),
(744,'2024-09-16','S03',5,11,'K05','K01','N'),
(745,'2024-09-17','S02',8,4,'K04','K02','N'),
(746,'2024-09-17','I01',2,0,'K06','K01','N'),
(747,'2024-09-17','S01',7,3,'K07','K03','N'),
(748,'2024-09-17','C01',1,4,'K09','K08','N'),
(749,'2024-09-17','G01',2,4,'K10','K05','N'),
(750,'2024-09-18','S01',3,5,'K07','K03','N'),
(751,'2024-09-18','C01',5,2,'K09','K08','N'),
(752,'2024-09-18','S03',6,8,'K05','K02','N'),
(753,'2024-09-19','I01',8,6,'K06','K10','N'),
(754,'2024-09-19','S02',9,4,'K04','K01','N'),
(755,'2024-09-19','S01',4,7,'K07','K03','N'),
(756,'2024-09-19','C01',6,7,'K09','K08','N'),
(757,'2024-09-19','S03',12,5,'K05','K02','N'),
(758,'2024-09-20','C01',NULL,NULL,'K09','K07','Y'),
(759,'2024-09-20','S02',NULL,NULL,'K03','K04','Y'),
(760,'2024-09-20','I01',NULL,NULL,'K06','K10','Y'),
(761,'2024-09-21','S02',7,14,'K03','K04','N'),
(762,'2024-09-21','D02',NULL,NULL,'K08','K07','Y'),
(763,'2024-09-21','D01',NULL,NULL,'K02','K10','Y'),
(764,'2024-09-21','S03',1,4,'K05','K06','N'),
(765,'2024-09-21','S02',2,0,'K03','K04','N'),
(766,'2024-09-21','G02',NULL,NULL,'K01','K09','Y'),
(767,'2024-09-22','D02',8,4,'K08','K07','N'),
(768,'2024-09-22','S02',9,5,'K03','K04','N'),
(769,'2024-09-22','D01',9,8,'K02','K10','N'),
(770,'2024-09-22','S03',2,6,'K05','K06','N'),
(771,'2024-09-22','G02',NULL,NULL,'K01','K09','Y'),
(772,'2024-09-23','G02',5,3,'K01','K02','N'),
(773,'2024-09-23','D02',2,3,'K08','K07','N'),
(774,'2024-09-23','S02',8,4,'K04','K06','N'),
(775,'2024-09-24','S02',10,5,'K04','K09','N'),
(776,'2024-09-24','S03',5,1,'K05','K07','N'),
(777,'2024-09-24','G02',7,1,'K01','K02','N'),
(778,'2024-09-24','G01',5,4,'K10','K08','N'),
(779,'2024-09-24','I01',5,14,'K06','K03','N'),
(780,'2024-09-25','S02',2,4,'K03','K08','N'),
(781,'2024-09-25','D01',6,3,'K02','K10','N'),
(782,'2024-09-25','C01',2,8,'K09','K06','N'),
(783,'2024-09-25','G02',2,5,'K01','K07','N'),
(784,'2024-09-26','S02',6,3,'K03','K10','N'),
(785,'2024-09-26','S01',3,4,'K07','K04','N'),
(786,'2024-09-26','C01',1,10,'K09','K06','N'),
(787,'2024-09-27','S03',8,7,'K05','K10','N'),
(788,'2024-09-27','S01',13,6,'K07','K09','N'),
(789,'2024-09-27','D02',8,0,'K08','K01','N'),
(790,'2024-09-28','D02',2,6,'K08','K06','N'),
(791,'2024-09-28','S01',8,12,'K07','K01','N'),
(792,'2024-09-28','D01',4,11,'K02','K03','N'),
(793,'2024-09-28','C01',3,4,'K09','K04','N'),
(794,'2024-09-28','S03',10,7,'K05','K10','N'),
(795,'2024-09-29','D02',2,7,'K08','K09','N'),
(796,'2024-09-30','I01',7,2,'K06','K10','N'),
(797,'2024-09-30','G02',10,5,'K01','K09','N'),
(798,'2024-10-01','C01',1,5,'K09','K07','N');
  • The Query
SELECT
    DATE_FORMAT(S.SCHE_DATE, '%Y-%m-%d') AS MatchDate,
    T_HOME.TEAM_NAME AS HomeTeam,
    T_AWAY.TEAM_NAME AS AwayTeam,
    S.HOME_SCORE,
    S.AWAY_SCORE,
    CASE
        WHEN S.HOME_SCORE > S.AWAY_SCORE THEN T_HOME.TEAM_NAME
        WHEN S.AWAY_SCORE > S.HOME_SCORE THEN T_AWAY.TEAM_NAME
    END AS Winner
FROM SCHEDULE AS S
JOIN TEAM AS T_HOME ON S.HOMETEAM_ID = T_HOME.TEAM_ID
JOIN TEAM AS T_AWAY ON S.AWAYTEAM_ID = T_AWAY.TEAM_ID
WHERE ABS(S.HOME_SCORE - S.AWAY_SCORE) >= 5
ORDER BY MatchDate ASC, ABS(S.HOME_SCORE - S.AWAY_SCORE) DESC, Winner ASC;
  • When I try COLLATION function to CASE WHEN ... THEN ... END, It was returning utf8mb4_0900_bin even I set all of the collation to the utf8mb4_unicode_ci.

swjeong9 avatar Oct 18 '25 07:10 swjeong9

We're sorry we missed this and we're looking at it now.

timsehn avatar Nov 10 '25 23:11 timsehn

Seems like the issue might be from us not respecting the collation when it's set in the session variable. I suspect that the original tables don't have the right collation, and as a result, CASE wouldn't either. Looping in @Hydrocharged to take a look.

I tried repro'ing with TestSingleScript and called SetSessionVariable in TestScriptWithEngine, adding the following lines

err := ctx.Session.SetSessionVariable(ctx, "character_set_server", "utf8mb4")
require.NoError(t, err)
err = ctx.Session.SetSessionVariable(ctx, "collation_server", "utf8mb4_unicode_ci")
require.NoError(t, err)
err = ctx.Session.SetSessionVariable(ctx, "character_set_database", "utf8mb4")
require.NoError(t, err)
err = ctx.Session.SetSessionVariable(ctx, "collation_database", "utf8mb4_unicode_ci")
require.NoError(t, err)
err = ctx.Session.SetSessionVariable(ctx, "character_set_connection", "utf8mb4")
require.NoError(t, err)
err = ctx.Session.SetSessionVariable(ctx, "collation_connection", "utf8mb4_unicode_ci")
require.NoError(t, err)

Then I created a table in the SetUpScript and queried show create table [table_name] in the assertions, which showed that the table's collation was utf8mb4_0900_bin.

CASE determines its type using GeneralizeTypes. Note the TODO to implement checking if collations are compatible. However, this shouldn't be an issue with the example CASE because both types are text so the generalized type should be the same collation as one of the original types.

Another collation issue I see with GeneralizeTypes is that it defaults to LongText, which is hardcoded to the "default collation" (Collation_utf8mb4_0900_bin). But again, this shouldn't be an issue with this particular CASE since both branches are text types.

angelamayxie avatar Nov 11 '25 23:11 angelamayxie

Greetings!

For these variables:

  • character_set_database
  • collation_database

These are exposed as dynamic session (and global) system variables, meaning that they're able to be modified. However, their true purpose to purely informational. From the MySQL documentation:

Assigning a value to the session character_set_database and collation_database system variables is deprecated and assignments produce a warning. Expect the session variables to become read-only (and assignments to them to produce an error) in a future version of MySQL in which it remains possible to access the session variables to determine the database character set and collation for the default database.

For these variables:

  • character_set_connection
  • collation_connection

These are used to define the interpretation of incoming data. Therefore, as an example, the inserted string "KIA Tigers" is interpreted as the utf8mb4_unicode_ci collation and utf8mb4 character set, which could still be converted to the table collation if they differed. This shouldn't have any impact on the query or error encountered.

For these variables:

  • character_set_server
  • collation_server

I'm setting them in the same way that @angelamayxie has described (in TestScriptWithEngine via TestSingleScript) and I'm getting the correct behavior. That is, I've taken the entire SQL file provided, set each statement as an assertion, added the aforementioned session variables via the context, and finished the assertions with SHOW CREATE TABLE TEAM; and SHOW CREATE TABLE SCHEDULE;. For both of these tables, I'm getting COLLATE=utf8mb4_unicode_ci".

To be completely sure, I removed the aforementioned session variables from being set in the context, and changed them to be SQL statement assertions:

SET @@character_set_server="utf8mb4";
SET @@collation_server="utf8mb4_unicode_ci";

Even with this, I observed the correct results.

In addition, I ran the query presented in the original issue and surrounded the CASE statement with the COLLATION function:

SELECT
    DATE_FORMAT(S.SCHE_DATE, '%Y-%m-%d') AS MatchDate,
    T_HOME.TEAM_NAME AS HomeTeam,
    T_AWAY.TEAM_NAME AS AwayTeam,
    S.HOME_SCORE,
    S.AWAY_SCORE,
    COLLATION(CASE
        WHEN S.HOME_SCORE > S.AWAY_SCORE THEN T_HOME.TEAM_NAME
        WHEN S.AWAY_SCORE > S.HOME_SCORE THEN T_AWAY.TEAM_NAME
    END) AS Winner
FROM SCHEDULE AS S
JOIN TEAM AS T_HOME ON S.HOMETEAM_ID = T_HOME.TEAM_ID
JOIN TEAM AS T_AWAY ON S.AWAYTEAM_ID = T_AWAY.TEAM_ID
WHERE ABS(S.HOME_SCORE - S.AWAY_SCORE) >= 5
ORDER BY MatchDate ASC, ABS(S.HOME_SCORE - S.AWAY_SCORE) DESC, Winner ASC;

For each row, I received utf8mb4_unicode_ci as the collation. I'm not able to reproduce any of the errors observed, however Angela was able to observe incorrect results, so I'm unsure of the difference between our setups. @swjeong9, just to make sure, you're on the latest version of Dolt correct? We did fix an issue last year surrounding character_set_server, so perhaps you're using a version that does not have the fix?

Hydrocharged avatar Nov 12 '25 15:11 Hydrocharged

We did not use Dolt but rather used the go-mysql-server library directly. Could the following usage have been the issue? The version was v0.20.0.

import (
...
	sqle "github.com/dolthub/go-mysql-server"
	"github.com/dolthub/go-mysql-server/memory"
	"github.com/dolthub/go-mysql-server/sql"
)

swjeong9 avatar Nov 13 '25 03:11 swjeong9

I just checked our last released version (May 2025) and it does exhibit the behavior that you're observing, so it appears that it has been fixed in the time since then. For now, I suggest updating to the tip of our main branch using (most current at the time of writing this comment):

go get github.com/dolthub/go-mysql-server@7b8fb200b5ae59da910c615fd954f63caa8d7d3d

You can also set the session variables directly in your SQL file, which will work on the version that you're on, but I definitely suggest updating in general for the various improvements that it will bring.

SET @@character_set_server="utf8mb4";
SET @@collation_server="utf8mb4_unicode_ci";

Hydrocharged avatar Nov 13 '25 12:11 Hydrocharged