You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-07-30 19:23:07 +03:00
ca6c35abdd
Using the stored function's return value as an argument for another function was handled incorrectly, leading to a server crash.
122 lines
6.8 KiB
Plaintext
122 lines
6.8 KiB
Plaintext
--source ../include/have_columnstore.inc
|
|
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mcol5812;
|
|
--enable_warnings
|
|
|
|
CREATE DATABASE mcol5812;
|
|
USE mcol5812;
|
|
|
|
# DROP TABLE IF EXISTS `cs_test_exclude`;
|
|
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;
|
|
|
|
# DROP TABLE IF EXISTS `cs_test_lhh`;
|
|
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);
|
|
|
|
DELIMITER $$;
|
|
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 $$
|
|
DELIMITER ;$$
|
|
|
|
SELECT location_id
|
|
FROM cs_test_lhh
|
|
WHERE (top_prnt NOT REGEXP cs_test_function('Value1', 7));
|
|
|
|
# cleanup
|
|
DROP FUNCTION IF EXISTS `cs_test_function`;
|
|
DROP DATABASE mcol5812;
|