Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Won't Fix
-
5.3.13, 5.5(EOL), 10.0(EOL)
-
None
Description
Note: there is certainly a problem, but it's not new, and I don't see any capacity for fixing now, and besides it might well be a duplicate of a known issue. So I won't waste time on creating a proper bug report just yet.
CREATE TABLE `AA` (
|
`col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8,
|
`col_varchar_10_latin1` varchar(10) CHARACTER SET latin1,
|
`col_varchar_1024_latin1_key` varchar(1024) CHARACTER SET latin1,
|
`col_varchar_10_latin1_key` varchar(10) CHARACTER SET latin1,
|
pk integer auto_increment,
|
`col_int` int,
|
`col_varchar_1024_utf8` varchar(1024) CHARACTER SET utf8,
|
`col_varchar_1024_latin1` varchar(1024) CHARACTER SET latin1,
|
`col_int_key` int,
|
`col_varchar_10_utf8` varchar(10) CHARACTER SET utf8,
|
`col_varchar_1024_utf8_key` varchar(1024) CHARACTER SET utf8,
|
/*Indices*/
|
key (`col_varchar_10_utf8_key` ),
|
key (`col_varchar_1024_latin1_key` ),
|
key (`col_varchar_10_latin1_key` ),
|
primary key (pk),
|
key (`col_int_key` ),
|
key (`col_varchar_1024_utf8_key` )) ENGINE=myisam
|
;
|
|
|
CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW `view_AA` AS SELECT * FROM `AA`;
|
|
|
INSERT /*! IGNORE */ INTO AA VALUES ('a', 'm', 'how', 'lkgtventqcwjkuvghoiyjubvhdacxukpqnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzrdgkyirjcqtkazqtypkzqovhfuakazafsowexougvhhfmobsxouoqdumefgwkenemneyvdgrzrkoghzaonlzemdnuepcwtmatbwactscznelwfpkfjptwfadlevfwndopyjhcrgwbbmtjlmbfqsvfgvjbelwyimdxzcayqdrzailcboyokcrrkudueyhxjyozpubwlztsdjqheqvcorhwnsuihmhsjrobtlpmcw', NULL, 8, 'kgtventqcwjkuvghoiyjubvhdacxukpqnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqz', 'w', NULL, 'm', 'gtventqcwjkuvghoiyjubvhdacxukpqnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzrdgkyirjcqtkazqtypkzqovhfuakazafsowexougvhhfmobsxouoqdumefgwkenemneyvdgrzrkoghzaonlzemdnuepcwtmatbwactscznelwfp')
|
;
|
|
|
CREATE TABLE `C` (
|
`col_varchar_10_utf8` varchar(10) CHARACTER SET utf8,
|
`col_varchar_1024_utf8_key` varchar(1024) CHARACTER SET utf8,
|
`col_varchar_10_latin1_key` varchar(10) CHARACTER SET latin1,
|
`col_varchar_1024_utf8` varchar(1024) CHARACTER SET utf8,
|
`col_varchar_1024_latin1_key` varchar(1024) CHARACTER SET latin1,
|
`col_int_key` int,
|
`col_int` int,
|
`col_varchar_10_latin1` varchar(10) CHARACTER SET latin1,
|
`col_varchar_10_utf8_key` varchar(10) CHARACTER SET utf8,
|
`col_varchar_1024_latin1` varchar(1024) CHARACTER SET latin1,
|
pk integer auto_increment,
|
/*Indices*/
|
key (`col_varchar_1024_utf8_key` ),
|
key (`col_varchar_10_latin1_key` ),
|
key (`col_varchar_1024_latin1_key` ),
|
key (`col_int_key` ),
|
key (`col_varchar_10_utf8_key` ),
|
primary key (pk)) ENGINE=myisam
|
;
|
|
|
CREATE OR REPLACE ALGORITHM=TEMPTABLE VIEW `view_C` AS SELECT * FROM `C`;
|
|
|
INSERT /*! IGNORE */ INTO C VALUES ('jubvhdacxukpqnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivty', 'no', 'ubvhdacxukpqnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzrdgkyirjcqtkazqtypkzqovhfuakazafsowexougvhhfmobsxouoqdumefgwkenemneyvdgrzrkoghzaonlzemdnuepcwtmatbwactscznelwfpkfjptwfadlevfwndopyjhcrgwbbmtjlmb', 'bvhdacxukpqnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzrdgkyirjcqtkazqtypkzqovhfuakazafsowexougvhhfmobsxouoqdumefgwkenemneyvdgrzrkoghzaonlzemdnuepcwtmatbwactscznelwfpkfjptwfadlevfwndopyjhcrgwbbmtjlmbfqsvfgvjbelwyimdxzcayqdrzailcboyokcrrkudueyhxjyozpubwlztsdjqheqvcorhwnsuihmhsjrobtlpmcwcqothltpnzfelkjdpokqblunkyeqltuwalaecbfvfuuvqgnnneonuktlezchetkdzhuqsmlahniaawbvlkhwnjpwukapfykekarhapepkcnakcjbxrgruzcypblayucridfoaqoknvennudzcbgxslfhxizzwxcyjsvdrwrrgamvtehrxcpdghkbounnywttajimucabmyycnzyzdamcmllxeqcpbdovsrfjyupolxrcoxoexmibfoeyritdkknmyilvixsghhlknttgzeabiatuealdrwnmmquusetjitjitbehnmbofzkzvrqjxbritfjyjkubqnwwckdoofbglpcuerdxtxijctbkq', 'now', 1970339840, 789839872, 'r', 'c', 'r', NULL) , ('vhdacxukpqnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzrdgkyirjcqtkazqtypkzqovhfuakazafsowexougvhhfmobsxouoqdumefgwkenemneyvdgrzrkoghzaonlzemdnuepcwtmatbwactscznelwfpkfjptwfadlevfwndopyjhcrgwbbmtjlmbfqsvfgvjbelwyimdxzcayqdrzailcboyokcrrkudueyhxjyozpubwlztsdjqheqvcorhwnsuihmhsjrobtlpmcwcqothltpnzfelkjdpokqblunkyeqltuwalaecbfvfuuvqgnnneonuktlezchetkdzhuqsmlahniaawbvlkhwnjpwukapfykekarhapepkcnakcjbxrgruzcypblayucridfoaqoknvennudzcbgxslfhxizzwxcyjsvdrwrrgamvtehrxcpdghkbounnywttajimucabmyycnzyzdamcmllxeqcpbdovsrfjyupolxrcoxoexmibfoeyritdkknmyilvixsghhlknttgzeabiatuealdrwnmmquusetjitjitbehnmbofzkzvrqjxbritfjyjkubqnwwckdoofbglpcuerdxtxijctbkqoafzjyjjtwlmkhukdslasbzbmmhiqgkamlhnratinxazdkuopsgniwmemcgkhpsvnppcjtecsgjefvoppznzohkxtvsybtaqmlatulmrqgkercerqqawullosnsjuxsidpquwtvblfwvzukypwmyyzkgkplgfadhrlesggcylvjyhi', 'hdacxukpqnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzrd', 'a', 's', 'x', 7, 1744568320, 'out', 'when', 'dacxukpqnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzrdgkyirjcqtkazqtypkzqovhfuakazafsowexougvhhfmobsxouoqdumefgwkenemneyvdgrzrkoghzaonlzemdnuepcwtmatbwactscznelwfpkfjptwfadlevfwndopyjhcrgwbbmtjlmbfqsvfgvjbelwyimdxzcayqdrzailcboyokcrrkudueyhxjyozpubwlztsdjqheqvcorhwnsuihmhsjrobtlpmcwcqothltpnzfelkjdpokqblunkyeqltuwalaecbfvfuuvqg', NULL) , ('be', 'know', 'can\'t', 'on', 'acxukpqnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzrdgkyirjcqtkazqtypkzqovhfuakazafsowexougvhhfmobsxouoqdumefgwkenemneyvdgrzrkoghzaonlzemdnuepcwtmatbwactscznelwfpkfjptwfadlevfwndopyjhcrgwbbmtjlmbfqsvfgvjbelwyimdxzcayqdrzailcboyokcrrkudueyhxjyozpubwlztsdjqheqvcorhwnsuihmhsjrobtlpmcwcqothltpnzfelkjdpokqblunkyeqltuwalaecbfvfuuvqgnnneonuktlezchetkdzhuqsmlahniaawbvlkhwnjpwukapfykekarhapepkcnakcjbxrgruz', NULL, -665518080, 'cxukpqnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzrdgkyirjcqtkazqtypkzqovhfuakazafsowexougvhhfmobsxouoqdumefgwkenemneyvdgrzrkoghzaonlzemdnuepcwtmatbwactscznelwfpkfjptwf', 'hey', 'up', NULL) , ('but', 'xukpqnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzrdgkyirjcqtkazqtypkzqovhfuakazafsowexougvhhfmobsxouoqdumefgwkenemneyvdgrzrkoghzaonlzemdnuepcwtmatbwactscznelwfpkfjptwfadlevfwndopyjhcrgwbbmtjlmbfqsvfgvjbelwyimdxzcayqdrzailcboyokcrrkudueyhxjyozpubwlztsdjqheqvcorhwnsuihmhsjrobtlpmcwcqothltpnzfelkjdpokqblunkyeqltuwalaecbfvfuuvqgnnneonuktlezchetkdzhuqsmlahniaawbvlkhwnjpwukapfykekarhapepkcnakcjbxrgruzcypblayuc', 'ukpqnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzrdgkyirjcqtkazqtypkzqovhfuakazafsowexougvhhfmobsxouoqdumefgwkenemneyvdgrzrkoghzaonlzemdnuepcwtmatbwactscznelwfpkfjptwfadlevfwndopyjhcrgwbbmtjlmbfqsvfgvjbelwyimdxzcayqdrzailcboyokcrrkudueyhxjyozpubwlztsdjqheqvcorhwnsuihmhsjrobtlpmcwcqothltpnzfelkjdpokqblunkyeqltuwalaecbfvfuuvqgnnneonuktlezchetkdzhuqsmlahniaawbvlkhwnjpwukapfykekarhapepkcnakcjbxrgruzcypblayucridfoaqoknvennudzcbgxslfhxizzwxcyjsvdrwrrgamvtehrxcpdghkbounnywttajimucabmyycnzyzdamcmllxeqcpbdovsrfjyupolxrcoxoexmibfoeyritdkknmyilvixsghhlknttgzeabiatuealdrwnmmquusetjitjitbehnmbofzkzvrqjxbritfjyjkubqnwwckdoofbglpcuerdxtxijctbkqoafzjyjjtwlmkhukdslasbzbmmhiqgkamlhnratinxazdkuopsgniwmemcgkhpsvnppcjtecsgjefvoppznzohkxtvsybtaqmlatulmrqgker', 'g', 'kpqnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzrdgkyirjcqtkazqtypkzqovhfuakazafsowexougvhhfmobsxouoqdumefgwkenemneyvdgrzrkoghzaonlzemdnuepcwtmatbwactscznelwfpkfjptwfadlevfwndopyjhcrgwbbmtjlmbfqsvfgvjbelwyimdxzcayqdrzailcboyokcrrkudueyhxjyozpubwlztsdjqheqvcorhwnsuihmhsjrobtlpmcwcqothltpnzfelkjdpokqblunkyeqltuwalaecbfvfuuvqgnnneonuktlezchetk', 5, 234553344, 'c', 'l', 'why', NULL) , ('pqnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzrdgkyirjcqtkazqtypkzqovhfuakazafsowexougvhhfmobsxouoqdumefgwkenemneyvdgrzrkoghzaonlzemdnuepcwtmatbwactscznelwfpkfjptwfadlevfwndopyjhcrgwbbmtjlmbfqsvfgvjbelwyimdxzcayqdrzailcboyokcrrkudueyhxjyozpubwlztsdjqheqvcorhwnsuihmhsjrobtlpmcwcqothltpnzfelkjdpokqblunkyeqltuwalaecbfvfuuvqgnnneonuktlezchetkdzhuqsmlahniaawbvlkhwnjpwukapfykekarhapepkcnakcjbxr', 'that\'s', 'but', 'you', 'qnqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzrdgkyirjcqtkazqtypkzqovhfuakazafsowexougvhhfmobsxouoqdumefgwkenemneyvdgrzrkoghzaonlzemdnuepcwtmatbwactscznelwfpkfjptwfadlevfwndopyjhcrgwbbmtjlmbfqsvfgvjbelwyimdxzcayqdrzailcboyokcrrkudueyhxjyozpubwlztsdjqheqvcorhwnsuihmhsjrobtlpmcwcqothltpnzfelkjdpokqblunkyeqltuwalaecbfvfuuvqgnnneonuktlezchetkdzhuqsmlahniaawbvlkhwnjpwukapfykekarhapepkcnakcjbxrgruzcypblayucridfoaqoknvennudzcbgxslfhxizzwxcyjsvdrwrrgamvtehrxcpdghkbounnywttajimucabmyycnzyzdamcmllxeqcpbdovsrfjyupolxrcoxoexmibfoeyritdkknmyilvixsghhlknttgzeabiatuealdrwnmmquusetjitjitbehnmbofzkzvrqjxbritfjyjkubqnwwckdoofbglpcuerdxtxijctbkqoafzjyjjtwlmkhukdslasbzbmmhiqgkamlhnratinxazdkuopsgniwmemcgkhpsvnppcjtecsgjefvoppznzohkxtvsybtaqmlatulmrqgkercerqqawullosnsjuxsidpquwtvblfwvzukypwmyyzkgkplgfadhrlesggcylvjyhinbgreutytwwlhlqolhrqlkshfuithezebzpgtujqcxahcjnfouu', 1248264192, NULL, 'k', 'nqajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzr', 'be', NULL) , ('did', 'qajhqrblvgozfkdhqmo', 'my', 't', 'a', NULL, NULL, 'up', 'ajhqrblvgozfkdhqmouoxhunsophucuvghefknxqirxhpdwqyokwrlglgsqzfjtsoijfmlodpkxayivtyigugsqaihymtfmeieidnqghlwyixqdpldhjbzircedietpipigyfownkgigtvwgxpqzrdgkyirjcqtkazqtypkzqovhfuakazafsowexougvhhfmobsxouoqdumefgwkenemneyvdgrzrkoghzaonlzemdnuepcwtmatbwactscznelwfpkfjptwfadlevfwndopyjhcrgwbbmtjlmbfqsvfgvjbelwyimdxzcayqdrzailcboyokcrrkudueyhxjyozpubwlztsdjqheqvcorhwnsuihmhsjrobtlpmcwcqothltpnzfelkjdpokqblunkyeqltuwalaecbfvfuuvqgnnneonuktlezchetkdzhuqsmlahniaawbvlkhwnjpwukapfykekarhapepkcnakcjbxrgruzcypblayucridfoaqoknvennudzcbgxslfhxizzwxcyjsvdrwrrgamvtehrxcpdghkbounnywttajimucabmyycnzyzdamcmllxeqcpbdovsrfjyupolxrcoxoexmibfoeyritdkknmyilvixsghhlknttgzeabiatuealdrwnmmquusetjitjitbehnmbofzkzvrqjxbritfjyjkubqnwwckdoofbglpcuerdxtxijctbkqoafzjyjjtwlmkhukdslasbzbmmhiqgkamlhnratinxazdkuopsgniwmemcgkhpsvnppcjtecsgjefvoppznzohkxtvsybtaqmlatulmrqgkercerqqawullosnsjuxsidpquwtvb', 'c', NULL)
|
;
|
|
|
let $query =
|
SELECT MIN( DISTINCT alias1.col_int ) AS field1, COUNT( DISTINCT alias1.col_int ) AS field2 FROM view_AA AS alias1 RIGHT JOIN AA AS alias2 LEFT JOIN AA AS alias3 ON alias2.col_varchar_10_utf8_key = alias3.col_varchar_1024_latin1_key LEFT OUTER JOIN view_C AS alias4 ON alias2.col_int_key = alias4.col_int_key ON alias1.pk = alias4.pk WHERE alias2.pk < 9 HAVING field2 < 5 ORDER BY field1, field2 ASC
|
;
|
|
|
eval $query;
|
|
|
SET SESSION optimizer_switch = REPLACE( @@optimizer_switch, "=on", "=off" );
|
SET SESSION optimizer_switch = "in_to_exists=on";
|
|
|
eval $query;
|
|
Actual result |
+SELECT MIN( DISTINCT alias1.col_int ) AS field1, COUNT( DISTINCT alias1.col_int ) AS field2 FROM view_AA AS alias1 RIGHT JOIN AA AS alias2 LEFT JOIN AA AS alias3 ON alias2.col_varchar_10_utf8_key = alias3.col_varchar_1024_latin1_key LEFT OUTER JOIN view_C AS alias4 ON alias2.col_int_key = alias4.col_int_key ON alias1.pk = alias4.pk WHERE alias2.pk < 9 HAVING field2 < 5 ORDER BY field1, field2 ASC
|
+;
|
+field1 field2
|
+8 1
|
+SET SESSION optimizer_switch = REPLACE( @@optimizer_switch, "=on", "=off" );
|
+SET SESSION optimizer_switch = "in_to_exists=on";
|
+SELECT MIN( DISTINCT alias1.col_int ) AS field1, COUNT( DISTINCT alias1.col_int ) AS field2 FROM view_AA AS alias1 RIGHT JOIN AA AS alias2 LEFT JOIN AA AS alias3 ON alias2.col_varchar_10_utf8_key = alias3.col_varchar_1024_latin1_key LEFT OUTER JOIN view_C AS alias4 ON alias2.col_int_key = alias4.col_int_key ON alias1.pk = alias4.pk WHERE alias2.pk < 9 HAVING field2 < 5 ORDER BY field1, field2 ASC
|
+;
|
+field1 field2
|
+NULL 0
|