diff options
Diffstat (limited to 'mysql-test/t/func_misc.test')
| -rw-r--r-- | mysql-test/t/func_misc.test | 207 |
1 files changed, 203 insertions, 4 deletions
diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test index 916a952fa3a..26e379299fc 100644 --- a/mysql-test/t/func_misc.test +++ b/mysql-test/t/func_misc.test @@ -398,6 +398,209 @@ SELECT GET_LOCK('ul1', NULL); SELECT GET_LOCK('ul1', -1); --echo # +--echo # MDEV-8624 MariaDB hangs on query with many logical condition +--echo # +CREATE TABLE `t1` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `submitdate` datetime DEFAULT NULL, + `lastpage` int(11) DEFAULT NULL, + `startlanguage` varchar(20) COLLATE utf8_unicode_ci NOT NULL, + `token` varchar(36) COLLATE utf8_unicode_ci DEFAULT NULL, + `datestamp` datetime NOT NULL, + `startdate` datetime NOT NULL, + `ipaddr` text COLLATE utf8_unicode_ci, + `refurl` text COLLATE utf8_unicode_ci, + `57813X540X1723` text COLLATE utf8_unicode_ci, + `57813X540X1724` text COLLATE utf8_unicode_ci, + `57813X540X1725` text COLLATE utf8_unicode_ci, + `57813X540X1726` double DEFAULT NULL, + `57813X540X1909` double DEFAULT NULL, + `57813X541X17271` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17272` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17273` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17274` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17275` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17276` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X541X17281` text COLLATE utf8_unicode_ci, + `57813X541X17282` text COLLATE utf8_unicode_ci, + `57813X541X17283` text COLLATE utf8_unicode_ci, + `57813X541X17284` text COLLATE utf8_unicode_ci, + `57813X541X17285` text COLLATE utf8_unicode_ci, + `57813X541X17286` text COLLATE utf8_unicode_ci, + `57813X542X18131` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18132` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18133` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18134` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18135` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18136` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18137` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18138` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18139` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X542X18141` text COLLATE utf8_unicode_ci, + `57813X542X18142` text COLLATE utf8_unicode_ci, + `57813X542X18143` text COLLATE utf8_unicode_ci, + `57813X542X18144` text COLLATE utf8_unicode_ci, + `57813X542X18145` text COLLATE utf8_unicode_ci, + `57813X542X18146` text COLLATE utf8_unicode_ci, + `57813X542X18147` text COLLATE utf8_unicode_ci, + `57813X542X18148` text COLLATE utf8_unicode_ci, + `57813X542X18149` text COLLATE utf8_unicode_ci, + `57813X543X18451` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18452` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18453` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18454` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18455` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18456` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X543X18461` text COLLATE utf8_unicode_ci, + `57813X543X18462` text COLLATE utf8_unicode_ci, + `57813X543X18463` text COLLATE utf8_unicode_ci, + `57813X543X18464` text COLLATE utf8_unicode_ci, + `57813X543X18465` text COLLATE utf8_unicode_ci, + `57813X543X18466` text COLLATE utf8_unicode_ci, + `57813X544X18711` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18712` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18713` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18714` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18715` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18716` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18717` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18718` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X544X18721` text COLLATE utf8_unicode_ci, + `57813X544X18722` text COLLATE utf8_unicode_ci, + `57813X544X18723` text COLLATE utf8_unicode_ci, + `57813X544X18724` text COLLATE utf8_unicode_ci, + `57813X544X18725` text COLLATE utf8_unicode_ci, + `57813X544X18726` text COLLATE utf8_unicode_ci, + `57813X544X18727` text COLLATE utf8_unicode_ci, + `57813X544X18728` text COLLATE utf8_unicode_ci, + `57813X546X1902` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X546X1903` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X546X1904` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL, + `57813X545X1901` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `lime_survey_57813_idx` (`token`), + KEY `57813X540X1723` (`57813X540X1723`(100)), + KEY `57813X540X1724` (`57813X540X1724`(100)), + KEY `57813X540X1726` (`57813X540X1726`), + KEY `57813X540X1725` (`57813X540X1725`(100)), + KEY `57813X546X1902` (`57813X546X1902`), + KEY `57813X546X1903` (`57813X546X1903`), + KEY `57813X546X1904` (`57813X546X1904`) +); + +SELECT +COUNT(*) as `N`, +ROUND( + ( + SUM( + ( + ( + IF( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99', 57813X541X17271, 0 ) + + IF( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99', 57813X541X17272, 0 ) + + IF( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99', 57813X541X17273, 0 ) + + IF( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99', 57813X541X17274, 0 ) + + IF( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99', 57813X541X17275, 0 ) + + IF( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99', 57813X541X17276, 0 ) + + IF( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99', 57813X542X18131, 0 ) + + IF( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99', 57813X542X18132, 0 ) + + IF( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99', 57813X542X18133, 0 ) + + IF( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99', 57813X542X18134, 0 ) + + IF( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99', 57813X542X18135, 0 ) + + IF( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99', 57813X542X18136, 0 ) + + IF( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99', 57813X542X18137, 0 ) + + IF( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99', 57813X542X18138, 0 ) + + IF( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99', 57813X542X18139, 0 ) + + IF( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99', 57813X543X18451, 0 ) + + IF( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99', 57813X543X18452, 0 ) + + IF( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99', 57813X543X18453, 0 ) + + IF( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99', 57813X543X18454, 0 ) + + IF( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99', 57813X543X18455, 0 ) + + IF( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99', 57813X543X18456, 0 ) + + IF( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99', 57813X544X18711, 0 ) + + IF( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99', 57813X544X18712, 0 ) + + IF( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99', 57813X544X18713, 0 ) + + IF( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99', 57813X544X18714, 0 ) + + IF( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99', 57813X544X18715, 0 ) + + IF( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99', 57813X544X18716, 0 ) + + IF( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99', 57813X544X18717, 0 ) + + IF( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99', 57813X544X18718, 0 ) + ) + / + ( + IF( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99', 1, 0 ) + + IF( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99', 1, 0 ) + + IF( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99', 1, 0 ) + + IF( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99', 1, 0 ) + + IF( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99', 1, 0 ) + + IF( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99', 1, 0 ) + + IF( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99', 1, 0 ) + + IF( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99', 1, 0 ) + + IF( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99', 1, 0 ) + + IF( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99', 1, 0 ) + + IF( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99', 1, 0 ) + + IF( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99', 1, 0 ) + + IF( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99', 1, 0 ) + + IF( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99', 1, 0 ) + + IF( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99', 1, 0 ) + + IF( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99', 1, 0 ) + + IF( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99', 1, 0 ) + + IF( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99', 1, 0 ) + + IF( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99', 1, 0 ) + + IF( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99', 1, 0 ) + + IF( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99', 1, 0 ) + + IF( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99', 1, 0 ) + + IF( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99', 1, 0 ) + + IF( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99', 1, 0 ) + + IF( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99', 1, 0 ) + + IF( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99', 1, 0 ) + + IF( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99', 1, 0 ) + + IF( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99', 1, 0 ) + + IF( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99', 1, 0 ) + ) + ) + ) + / COUNT(*) ), 4) as `AVG` +FROM `t1` +WHERE `submitdate` IS NOT NULL +AND ( + ( 57813X541X17271 IS NOT NULL AND 57813X541X17271 != '' AND 57813X541X17271 != '99' ) OR + ( 57813X541X17272 IS NOT NULL AND 57813X541X17272 != '' AND 57813X541X17272 != '99' ) OR + ( 57813X541X17273 IS NOT NULL AND 57813X541X17273 != '' AND 57813X541X17273 != '99' ) OR + ( 57813X541X17274 IS NOT NULL AND 57813X541X17274 != '' AND 57813X541X17274 != '99' ) OR + ( 57813X541X17275 IS NOT NULL AND 57813X541X17275 != '' AND 57813X541X17275 != '99' ) OR + ( 57813X541X17276 IS NOT NULL AND 57813X541X17276 != '' AND 57813X541X17276 != '99' ) OR + ( 57813X542X18131 IS NOT NULL AND 57813X542X18131 != '' AND 57813X542X18131 != '99' ) OR + ( 57813X542X18132 IS NOT NULL AND 57813X542X18132 != '' AND 57813X542X18132 != '99' ) OR + ( 57813X542X18133 IS NOT NULL AND 57813X542X18133 != '' AND 57813X542X18133 != '99' ) OR + ( 57813X542X18134 IS NOT NULL AND 57813X542X18134 != '' AND 57813X542X18134 != '99' ) OR + ( 57813X542X18135 IS NOT NULL AND 57813X542X18135 != '' AND 57813X542X18135 != '99' ) OR + ( 57813X542X18136 IS NOT NULL AND 57813X542X18136 != '' AND 57813X542X18136 != '99' ) OR + ( 57813X542X18137 IS NOT NULL AND 57813X542X18137 != '' AND 57813X542X18137 != '99' ) OR + ( 57813X542X18138 IS NOT NULL AND 57813X542X18138 != '' AND 57813X542X18138 != '99' ) OR + ( 57813X542X18139 IS NOT NULL AND 57813X542X18139 != '' AND 57813X542X18139 != '99' ) OR + ( 57813X543X18451 IS NOT NULL AND 57813X543X18451 != '' AND 57813X543X18451 != '99' ) OR + ( 57813X543X18452 IS NOT NULL AND 57813X543X18452 != '' AND 57813X543X18452 != '99' ) OR + ( 57813X543X18453 IS NOT NULL AND 57813X543X18453 != '' AND 57813X543X18453 != '99' ) OR + ( 57813X543X18454 IS NOT NULL AND 57813X543X18454 != '' AND 57813X543X18454 != '99' ) OR + ( 57813X543X18455 IS NOT NULL AND 57813X543X18455 != '' AND 57813X543X18455 != '99' ) OR + ( 57813X543X18456 IS NOT NULL AND 57813X543X18456 != '' AND 57813X543X18456 != '99' ) OR + ( 57813X544X18711 IS NOT NULL AND 57813X544X18711 != '' AND 57813X544X18711 != '99' ) OR + ( 57813X544X18712 IS NOT NULL AND 57813X544X18712 != '' AND 57813X544X18712 != '99' ) OR + ( 57813X544X18713 IS NOT NULL AND 57813X544X18713 != '' AND 57813X544X18713 != '99' ) OR + ( 57813X544X18714 IS NOT NULL AND 57813X544X18714 != '' AND 57813X544X18714 != '99' ) OR + ( 57813X544X18715 IS NOT NULL AND 57813X544X18715 != '' AND 57813X544X18715 != '99' ) OR + ( 57813X544X18716 IS NOT NULL AND 57813X544X18716 != '' AND 57813X544X18716 != '99' ) OR + ( 57813X544X18717 IS NOT NULL AND 57813X544X18717 != '' AND 57813X544X18717 != '99' ) OR + ( 57813X544X18718 IS NOT NULL AND 57813X544X18718 != '' AND 57813X544X18718 != '99' ) ) +AND 57813X540X1723 = 'Test'; + +drop table t1; + +--echo # +--echo # End of 5.5 tests +--echo # + +--echo # --echo # GET_LOCK, RELEASE_LOCK, IS_USED_LOCK functions test --echo # @@ -574,10 +777,6 @@ select is_free_lock(repeat('a', 193)); --error ER_TOO_LONG_IDENT select release_lock(repeat('a', 193)); ---echo # ---echo # End of 5.5 tests ---echo # - --echo --echo # -- |