DROP DATABASE IF EXISTS mcol5812;
CREATE DATABASE mcol5812;
USE mcol5812;
CREATE TABLE `cs_test_exclude` (
`username` varchar(128) NOT NULL DEFAULT '',
`location_id` int(11) NOT NULL,
`inclusive` tinyint(1) NOT NULL DEFAULT 0,
`gbd_round_id` int(11) NOT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `cs_test_lhh` (
`location_set_version_id` int(11) NOT NULL,
`location_set_id` int(11) NOT NULL,
`location_id` int(11) NOT NULL,
`parent_id` int(11) NOT NULL,
`top_prnt` varchar(200) DEFAULT NULL,
`level` int(11) NOT NULL,
`is_estimate` tinyint(1) NOT NULL DEFAULT 0,
`most_detailed` tinyint(4) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
INSERT INTO `cs_test_exclude` VALUES
('Value1',62,0,7),('Value1',63,0,7),('Value1',72,0,7),('Value1',4841,1,7),('Value1',4842,1,7),
('Value1',4843,1,7),('Value1',4844,1,7),('Value1',4846,1,7),('Value1',4849,1,7),
('Value1',4850,1,7),('Value1',4851,1,7),('Value1',4852,1,7),('Value1',4853,1,7),
('Value1',4854,1,7),('Value1',4855,1,7),('Value1',4856,1,7),('Value1',4857,1,7),
('Value1',4859,1,7),('Value1',4860,1,7),('Value1',4861,1,7),('Value1',4862,1,7),
('Value1',4863,1,7),('Value1',4864,1,7),('Value1',4865,1,7),('Value1',4867,1,7),
('Value1',4868,1,7),('Value1',4869,1,7),('Value1',4870,1,7),('Value1',4871,1,7),
('Value1',4872,1,7),('Value1',4873,1,7),('Value1',4874,1,7),('Value1',4875,1,7),
('Value1',44538,1,7);
INSERT INTO `cs_test_lhh` VALUES
(1,958664876,246190364,1249,'62',2,0,0),(2,203385377,71291823,1248,'4875',4,0,0),
(3,330636575,599349854,1248,'72',2,1,0),(4,83193844,69601420,1248,'4841',2,1,2),
(5,880576199,919233821,1251,'62',2,0,1),(6,423710106,372141638,1250,'113',4,0,0),
(7,964870752,706911487,1250,'63',5,0,1),(8,811505779,288644012,1248,'72',8,1,1),
(9,863527754,867131640,1248,'63',4,1,1),(10,997630965,903920363,1250,'114',1,0,0),
(11,67092268,643249095,1248,'63',7,1,1),(12,3886045,783815976,1251,'72',2,0,1),
(13,358564822,831193421,1248,'114',3,1,0),(14,204755399,806303011,1249,'111',1,0,0),
(15,547308388,662836201,1248,'4842',8,0,0),(16,218564918,481884787,1248,'4842',4,1,0),
(17,639801213,326560225,1248,'44538',8,0,1),(18,319975567,930174978,1248,'111',3,0,2),
(19,228530438,909067697,1251,'44538',3,1,2),(20,989807004,760825441,1251,'114',9,0,2),
(21,71143451,705834119,1247,'4843',4,0,0),(22,298278074,557207242,1251,'113',3,1,1),
(23,18903111,627537731,1248,'4875',4,0,1),(24,858596664,687567389,1251,'4841',6,1,1),
(25,925293899,954442412,1251,'63',6,1,2),(26,754240682,449170390,1251,'44538',9,1,0),
(27,622033315,581225374,1248,'4843',2,0,1),(28,253668752,416962235,1247,'4842',8,0,1),
(29,614116441,218551159,1247,'44538',3,0,0),(30,376548495,224463308,1251,'4841',5,0,0),
(31,330713421,600474630,1248,'4841',2,0,0),(32,988170605,257929822,1247,'114',3,1,2),
(33,392917009,680986028,1247,'63',7,1,2),(34,248884530,727794041,1251,'4841',7,1,2),
(35,762392593,389261523,1250,'63',6,1,1),(36,288099880,107542410,1248,'62',2,1,1),
(37,927146744,882593710,1250,'4875',6,1,1),(38,469238982,704069569,1248,'4843',9,0,2),
(39,467061012,323832344,1247,'63',9,0,0),(40,917004206,684981061,1248,'4842',5,1,2),
(41,688679370,705739286,1249,'72',6,0,0),(42,408049260,30613069,1251,'44538',9,0,1),
(43,54000735,273162722,1247,'72',4,0,2),(44,164671784,211068500,1250,'72',2,0,2),
(45,488378905,578003329,1249,'4843',7,0,0),(46,758198951,55172361,1248,'113',2,0,1),
(47,266894388,746743719,1251,'4843',3,0,1),(48,71736102,694360233,1247,'72',3,1,0),
(49,957245979,914867965,1248,'112',7,0,1),(50,301352564,397696525,1248,'72',9,1,0),
(51,512235534,718530555,1248,'63',5,1,0),(52,839568220,712650810,1248,'63',3,0,0),
(53,575910842,926645552,1251,'112',1,1,1),(54,628010357,106713826,1250,'115',7,1,1),
(55,500726159,757662497,1247,'72',9,0,0),(56,435533157,345284473,1249,'62',1,0,1),
(57,216127503,408342278,1249,'112',5,0,2),(58,632536551,494012796,1250,'4842',2,1,1),
(59,808450911,944931619,1247,'111',4,0,0),(60,401138482,966265756,1250,'4841',3,1,2),
(61,306189266,971603360,1251,'113',1,0,1),(62,383512515,97432722,1249,'4843',9,1,0),
(63,823002329,380225321,1249,'62',8,1,1),(64,850675079,272242917,1248,'72',7,1,1),
(65,546386018,339705923,1248,'4841',2,0,2),(66,972614758,839392045,1247,'114',5,0,0),
(67,754887111,844479876,1251,'4841',4,0,0),(68,96863327,157587773,1249,'62',6,1,2),
(69,359206490,874831340,1247,'114',4,0,0),(70,568416298,378659531,1247,'113',5,1,0),
(71,770107723,799396801,1248,'62',2,0,0),(72,267553306,348071087,1251,'111',4,0,1),
(73,352593522,980460449,1251,'4841',8,1,0),(74,733381379,666045858,1248,'111',8,0,1),
(75,412482330,927782012,1249,'72',9,1,1),(76,592328197,401156054,1247,'115',1,0,1),
(77,564885656,439254004,1250,'72',5,1,0),(78,453881920,646915758,1251,'4843',5,0,1),
(79,7041781,245431073,1247,'4841',8,0,1),(80,389362773,540027369,1250,'62',6,1,1),
(81,859161520,920208461,1248,'4842',7,1,1),(82,702478077,414598228,1251,'44538',1,0,1),
(83,82773827,410201619,1248,'113',5,0,1),(84,881657037,174790010,1247,'111',4,0,0),
(85,163860768,214199912,1250,'4841',5,1,0),(86,950914751,901637761,1250,'44538',9,1,0),
(87,34289392,352757936,1250,'4841',3,1,1),(88,597054039,821408137,1247,'63',6,1,0),
(89,114114883,334611378,1247,'111',3,0,0),(90,735863059,223069230,1251,'114',6,1,1),
(91,636187531,343234972,1248,'62',6,0,2),(92,765890679,85421062,1248,'4843',6,1,2),
(93,172538451,891634438,1251,'62',3,1,2),(94,9107635,10400866,1248,'63',4,1,0),
(95,995075180,943586298,1248,'113',9,0,1),(96,646629630,886808768,1249,'113',6,0,1),
(97,885926569,634024443,1250,'111',7,1,0),(98,320024878,276165077,1249,'4841',2,1,1),
(99,193259578,966379021,1247,'4842',1,0,1),(100,798259742,56493273,1251,'4841',7,1,0);
CREATE FUNCTION `cs_test_function`(p_username VARCHAR(128), p_gbd_round_id INT(11)) RETURNS text CHARSET utf8mb3 COLLATE utf8mb3_general_ci
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE exclude_set TEXT;
SELECT GROUP_CONCAT(CONCAT(location_id,IF(inclusive,'($|,)',',')) SEPARATOR '|')
INTO exclude_set
FROM cs_test_exclude
WHERE username=p_username
AND gbd_round_id=p_gbd_round_id
GROUP BY username;
IF exclude_set IS NOT NULL THEN
RETURN(CONCAT(',(',exclude_set,')'));
END IF;
RETURN('^$');
END $$
SELECT location_id
FROM  cs_test_lhh
WHERE (top_prnt NOT REGEXP cs_test_function('Value1', 7));
location_id
246190364
71291823
599349854
69601420
919233821
372141638
706911487
288644012
867131640
903920363
643249095
783815976
831193421
806303011
662836201
481884787
326560225
930174978
909067697
760825441
705834119
557207242
627537731
687567389
954442412
449170390
581225374
416962235
218551159
224463308
600474630
257929822
680986028
727794041
389261523
107542410
882593710
704069569
323832344
684981061
705739286
30613069
273162722
211068500
578003329
55172361
746743719
694360233
914867965
397696525
718530555
712650810
926645552
106713826
757662497
345284473
408342278
494012796
944931619
966265756
971603360
97432722
380225321
272242917
339705923
839392045
844479876
157587773
874831340
378659531
799396801
348071087
980460449
666045858
927782012
401156054
439254004
646915758
245431073
540027369
920208461
414598228
410201619
174790010
214199912
901637761
352757936
821408137
334611378
223069230
343234972
85421062
891634438
10400866
943586298
886808768
634024443
276165077
966379021
56493273
DROP FUNCTION IF EXISTS `cs_test_function`;
DROP DATABASE mcol5812;