Description:
This bug might be related to bug 114627 that was verified but not fixed so far.
When running Query A that contains an additional subquery which in fact does nothing at all, the Query will NEVER FINISH (or not finish within reasonable time).
When running Query B that does not contain this ditional subquery which in fact does nothing at all, the Query will finish within 0.08 seconds (in my case).
There is something seriously wrong, probably with index selction in conjunction with subqueries.
How to repeat:
delimiter //
DROP TABLE receipttest
//
CREATE TABLE `receipttest` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`mandate_id` int unsigned NOT NULL DEFAULT '0',
`ordernumber` char(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`paymode_id` int unsigned NOT NULL DEFAULT '0',
`subtotal` double NOT NULL DEFAULT '0',
`total` double NOT NULL DEFAULT '0',
`fname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`lname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`company` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`address1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`address2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`city` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`state` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`zip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`country` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'CH',
`phoneday` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`phonenight` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
`proformaopen` double NOT NULL DEFAULT '0',
`open` double NOT NULL DEFAULT '0',
`receipttype_id` int unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `ordernumber` (`ordernumber`),
FULLTEXT KEY `fname` (`fname`,`lname`,`company`,`address1`,`city`,`zip`)
) ENGINE=InnoDB AUTO_INCREMENT=141353 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
//
drop procedure generatetestrows
//
create procedure generatetestrows(in num_rows int)
begin
declare i int default 1;
while i <= num_rows do
insert ignore into receipttest (ordernumber) values (substring(rand(),3));
set i = i + 1;
end while;
end
//
call generatetestrows(150000)
//
DROP FUNCTION IF EXISTS `FIND_BEST_RECEIPTID`
//
CREATE FUNCTION FIND_BEST_RECEIPTID(inname TEXT, inamount DOUBLE, inmandate TEXT)
RETURNS TEXT
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
DECLARE outreceiptid INT UNSIGNED;
WITH sq0 AS
(
SELECT
r.`id` AS receiptid,
MATCH(r.`company`,r.`fname`,r.`lname`,r.`address1`,r.`zip`,r.`city`) AGAINST (inname) AS p
FROM
`receipttest` AS r
WHERE
1
ORDER BY
p
DESC
LIMIT 1
)
SELECT receiptid INTO outreceiptid FROM sq0 WHERE 1;
RETURN(outreceiptid);
END
//
delimiter ;
Query A (does not work):
WITH sq5 AS
(
WITH sq3 AS
(
SELECT 'a'
)
SELECT
FIND_BEST_RECEIPTID('Frederic Steinfels, Somestreet 110b, 9999 Zippi CH',1000,1) AS ordernumberfromtext
FROM
sq3
WHERE 1
)
SELECT sq5.* FROM sq5,receipt AS r WHERE r.`id`=sq5.ordernumberfromtext
;
Query B (does work):
WITH sq5 AS
(
SELECT
FIND_BEST_RECEIPTID('Frederic Steinfels, Somestreet 110b, 9999 Zippi CH',1000,1) AS ordernumberfromtext
WHERE 1
)
SELECT sq5.* FROM sq5,receipttest AS r WHERE r.`id`=sq5.ordernumberfromtext
;
