610 lines
34 KiB
Plaintext
610 lines
34 KiB
Plaintext
--echo #
|
|
--echo # Bug#26475312 VALGRIND MEMORY LEAK ( CMP_FIELD_VALUE(FIELD*, LONG) (SQL_EXECUTOR.CC:3598)
|
|
--echo #
|
|
|
|
CREATE TABLE H (
|
|
col_varchar_10_utf8 varchar(10) CHARACTER SET utf8,
|
|
col_varchar_255_latin1_key varchar(255) CHARACTER SET latin1,
|
|
col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1,
|
|
col_varchar_10_latin1 varchar(10) CHARACTER SET latin1,
|
|
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8,
|
|
col_varchar_255_utf8_key varchar(255) CHARACTER SET utf8,
|
|
col_int int,
|
|
col_int_key int,
|
|
col_varchar_255_utf8 varchar(255) CHARACTER SET utf8,
|
|
pk integer auto_increment,
|
|
col_varchar_255_latin1 varchar(255) CHARACTER SET latin1,
|
|
/*Indices*/
|
|
key (col_varchar_255_latin1_key ),
|
|
key (col_varchar_10_latin1_key ),
|
|
key (col_varchar_10_utf8_key ),
|
|
key (col_varchar_255_utf8_key ),
|
|
key (col_int_key ),
|
|
primary key (pk)) ENGINE=innodb;
|
|
|
|
INSERT IGNORE INTO H VALUES
|
|
('about', 'z', 'they', 'm', 'x', 'could',
|
|
3, 155189248,
|
|
'xyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiunassfsitgtnbbqukaeoubigzikncxyodlihgflmcbkqxdcvjpiqmnoorrutfxankapsmbdiicuytbwekanfyklnbjliaaebckybutdqwptyalaxaeuihzhclgqsmahtzogdxwslioxailzxafyztqpcakqukruoggiuemclpylcufjtmzxbutyghfkezxvfwojgolicczdqpqtwnugrwwcxumxofffljfhpkpddctnalygeuaugnnwxylfjdpzwvybdgtfyagjeqniouizgdzbhwahrylqwmnqzcjrhoivxidqmuikodwulsugcmwtxsadjdztkpdwbdxtvckayxskunxtompmmcbgiyedwfbjafnpcbvmxnrzvubwpnxfuzndvkxkfhjnzdvvkowuizfymqqexdtppmiznrhwfsngzbcvrynormfrjlflywpsmcsifmjuvwislbtaivizpbisvasqpyscnanwlttnqpzpdbiphzuujfksinzkcbbarimkigwdxjhscyrlmiqglyrdrwvhyoltdmpqlyzgknqzmfptjcmzeyokthfkdktzcsohicugzwgevhnsublfvasvftxhgk', NULL, 't');
|
|
|
|
CREATE TABLE DD (
|
|
col_varchar_255_latin1_key varchar(255) CHARACTER SET latin1,
|
|
col_varchar_255_utf8_key varchar(255) CHARACTER SET utf8,
|
|
col_varchar_255_latin1 varchar(255) CHARACTER SET latin1,
|
|
col_varchar_10_latin1_key varchar(10) CHARACTER SET latin1,
|
|
col_int_key int,
|
|
col_varchar_255_utf8 varchar(255) CHARACTER SET utf8,
|
|
pk integer auto_increment,
|
|
col_varchar_10_utf8 varchar(10) CHARACTER SET utf8,
|
|
col_int int,
|
|
col_varchar_10_latin1 varchar(10) CHARACTER SET latin1,
|
|
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8,
|
|
/*Indices*/
|
|
key (col_varchar_255_latin1_key ),
|
|
key (col_varchar_255_utf8_key ),
|
|
key (col_varchar_10_latin1_key ),
|
|
key (col_int_key ),
|
|
primary key (pk),
|
|
key (col_varchar_10_utf8_key )) ENGINE=innodb;
|
|
|
|
INSERT IGNORE INTO DD VALUES ('q',
|
|
'brmucbzjaeueffwxeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiunassfsitgtnbbqukaeoubigzikncxyodlihgflmcbkqxdcvjpiqmnoorrutfxankapsmbdiicuytbwekanfyklnbjliaaebckybutdqwptyalaxaeuihzhclgqsmahtzogdxwslioxailzxafyztqpcakqukruoggiuemclpylcufjtmzxbutyghfkezxvfwojgolicczdqpqtwnugrwwcxumxofffljfhpkpddctnalygeuaugnnwxylfjdpzwvybdgtfyagjeqniouizgdzbhwahrylqwmnqzcjrhoivxidqmuikodwulsugcmwtxsadjdztkpdwbdxtvckayxskunxtompmmcbgiyedwfbjafnpcbvmxnrzvubwpnxfuzndvkxkf', 'now',
|
|
'you\'re', 1811152896, 'or', NULL, 'this', 6, 'then', 'e') , ('did', 'j',
|
|
'they',
|
|
'rmucbzjaeueffwxeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiunassfsitgtnbbqukaeoubigzikncxyodlihgflmcbkqxdcvjpiqmnoorrutfxankapsmbdiicuytbwekanfyklnbjliaaebckybutdqwptyalaxaeuihzhclgqsmahtzogdxwslioxailzxafyztqpcakqukruoggiuemclpylcufjtmzxbutyghfkezxvfwojgolicczdqpqtwnugrwwcxumxofffljfhpkpddctnalygeuaugnnwxylfjdpzwvybdgtfyagjeqniouizgdzbhwahrylqwmnqzcjrhoivxidqmuikodwulsugcmwtxsadjdztkpdwbdxtvckayxskunxtompmmcbgiyedwfbjafnpcbvmxnrzvubwpnxfuzndvkxkfhjnzdvvkowu',
|
|
2087649280,
|
|
'mucbzjaeueffwxeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbm',
|
|
NULL, 'would', -64421888, 'I\'m', 'do') , ('l',
|
|
'ucbzjaeueffwxeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillur', 'yes', 'it', 8,
|
|
'cbzjaeueffwxeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgke', NULL,'bzjaeueffwxeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiunassfsitgtnbbqukaeoubigzikncxyodlihgflmcbkqxdcvjpiqmnoorrutfxankapsmbdiicuytbwekanfyklnbjliaaebckybutdqwptyalaxaeuihzhclgqsmahtzogdxwslioxailzxafyztqpcakqukruoggiuemclpylc', 5,
|
|
'zjaeueffwxeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiunassfsitgtnbbquk', 'i') ,
|
|
('d', 'l', 'u',
|
|
'jaeueffwxeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiunassfsitgtnbbqukaeoubigzikncxyodlihgflmcbkqxdcvjpiqmnoorrutfxankapsmbdiicuytbwekanfyklnbjliaaebckybutdqwptyalaxaeuihzhclgqsmahtzogdxwslioxailzxafyztqpcakqukruoggiuemclpylcufjtmzxbutyghfke', -772603904, 'yes', NULL, 'l', 1, 'p',
|
|
'aeueffwxeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiunassfsitgtnbbqukaeoubigzikncxyodlihgflmcbkqxdcvjpiqmnoorrutfxankapsmbdiicuytbwekanfyklnbjliaaebckybutdqwptyalaxaeuihzhclgqsmahtzogdxwslioxailzxafyztqpcakqukruoggiuemclpylcufjtmzxbutyghfkezxvfwojgolicczdqpqtwnugrwwcxumxofffljfhpkpddctnalygeuaugnnwxylfjdpzwvybdgtfyagjeqniouizgdzbhwahrylqwmnqzcjrhoivxidqmuikodwulsugcmwtxsadjdztkpdwbdxtvckayxskunxtompmmcbgiyedwfbjafnpcbvmxnrzvubwpnxfuz') , ('well',
|
|
'eueffwxeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkje
|
|
bunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwgg
|
|
kspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuz
|
|
jvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiunassfsitgtnbbqukaeoubigzikncxy
|
|
odli', 'of', 'on', 0,
|
|
'ueffwxeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjeb
|
|
unithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggk
|
|
spxpaoxldymllvikfrwhblzugcnlgmoz', NULL,'effwxeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkb',
|
|
-479461376,
|
|
'ffwxeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiunassfsitgtnbbqukaeoubigzikncxyodlihgflmcbkqxdcvjpiqmnoorrutfxankapsmbdiicuytbwekanfyklnbjliaaebckybutdqwptyalaxaeuihzhclgqsmahtzogdxwslioxailzxafyztqpcakqukruoggiuemcl','fwxeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiunassfsitgtnbbqukaeoubigzikncxyodlihgflmcbkqxdcvjpiqmnoorrutfxankapsmbdiicuytbwekanfyklnbjliaaebckybutdqwptyalaxaeuihzhclgqsmahtzogdxwslioxailzxafyztqpcakqukruoggiuemclpylcufjtmzxbutyghfkezxvfwojgolicczdqpqtwnugrwwcxumxofff') ,
|
|
('wxeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiunassfsitgtnbbqukaeoubigzikncxyodlihgflmcb', 'we', 'w', 'me', 0,
|
|
'xeyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmr', NULL, 'with', 6, 'for', 'b') , ('say', 'l', 't', 'b', 9,
|
|
'eyonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiunassfsitgtnbbqukaeoubigzikncxyodlihgflm', NULL, 'she', 8, 'come',
|
|
'yonrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmf')
|
|
, ('or', 'c', 'q', 'from', 559546368, 'as', NULL, 'q', -988545024, 'do',
|
|
'onrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnb') ,
|
|
('nrgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiunassfsitgtnbbqukaeoubigzikncxyodlihgflmcbkqxdcvjpiqmnoorrutfxankapsmbdiicuytbwekanfyklnbjliaaebckybutdqwptyalaxaeuihzhclgqsmahtzogdxwslioxailzxafyztqpcakqukruoggiuemclpylcufjtmzxbutyghfkezxvfwojgolicczdqpqtwnugrwwcxumxofffljfhpkpddctnalygeuaugnnwxylfjdpzwvybdgtfyagjeqniouizgdzbhwahrylqwmnqzcjrhoivxi', 'one', 'n',
|
|
'rgouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiunassfsitgtnbbqukaeoubigzikncxyodlihgflmcbkqxdcvjpiqmnoorrutfxankapsmbdiicuytbwekanfyklnbjliaaebckybutdqwptyalaxaeuihzhclgqsmahtzogdxwslioxailzxafyztqpcakqukruoggiuemclpylcufjtmzxbutyghfkezxvfwojgolicczdq', -681639936,'gouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiunassfsitgtnbbqukaeoubigzikncxyodlihgflmcbkqxdcvjpiqmnoorrutfxankapsmbdiicuytbwekanfyklnbjliaaebckybutdqwptyalaxaeuihzhclgqsmahtzogdxwslioxailzxafyztqpcakqukruoggiuemclpylcufjtmzxbutyghfkezxvfwojgolicczdqpqtwnugrwwcxumxofffljfhpkpddctnalygeuaugnnwxylfjdpzwvybdgtfyagjeqniouizgdzbhwahrylqwmnqzcjrhoivxidqmuikodwulsugcmwtxsadjdztkpdwbdxtvckayxskunxtompmmcbgiyedwfbjafnpcbvmxnrzvubwpnxfuzndvkxkf', NULL, 'n', 5, 'p','ouqbsvekyzrfizdybnlisnzpgplythqeyugzvhzjmxhmzqnbmldcuskteeyxqeesumyuohkhmoxtlpsumxsuahxrnybqsdvkiiiiatzeihtiwqjgxrspppoytvmuufhhkcgbnpemioxkjebunithymftmsroaqsxdrxhcozhqdagsxfxndeimavrxfdocfgafdxzxqoehcikppndcieybkyxmumqsbcwcmmrweiprthlrnphvebnfgjfhrkrxxorzzbedateyoomxdzgkevuyyvmusoduugukillurhwggkspxpaoxldymllvikfrwhblzugcnlgmozwbhvpytvyogbythupkkfgjqpqjgprksqcndkbsrzgiuvqgkqtqvucjdnyfqhigpgfeclnxyrkcxviqrwelsktxpoecytteiloxyzksesbdqhrcrfdzuxboyppuzjvssddkrsgvagetbssudtdsxcmpqpemiqbztutrkxougxqcpwlacgbyktskefotymojkkjnbkvkmqjgzrvmfpzwkixtxioqbzfvomgkicobwpsjeyfcaxwqxegkkiun');
|
|
|
|
SELECT
|
|
alias1 . col_int AS field1 ,
|
|
CUME_DIST() OVER ( ORDER BY alias1 . pk) AS field2 ,
|
|
alias1 . col_int_key AS field3 ,
|
|
PERCENT_RANK() OVER ( ORDER BY alias2 .col_varchar_255_utf8_key,
|
|
alias1 . col_varchar_10_latin1_key, alias1. col_varchar_255_utf8 )
|
|
AS field4 ,
|
|
alias1 . col_int_key AS field5 ,
|
|
PERCENT_RANK() OVER (
|
|
PARTITION BY alias2 .col_varchar_10_utf8, alias1 .col_int_key,
|
|
alias2 .col_varchar_255_utf8
|
|
ORDER BY alias1. col_varchar_10_latin1
|
|
ROWS CURRENT ROW ) AS field6 ,
|
|
alias1 . col_int AS field7 ,
|
|
RANK() OVER ( ORDER BY alias1 . col_int ) AS field8 ,
|
|
alias2 . pk AS field9 ,
|
|
NTILE ( 1 ) OVER (
|
|
PARTITION BY
|
|
alias1 .col_varchar_255_latin1,alias2 . col_varchar_255_latin1,
|
|
alias1. col_varchar_255_utf8_key, alias1. col_varchar_10_latin1,
|
|
alias2 . col_int_key
|
|
ORDER BY alias1 . col_int, alias1 . col_int_key)
|
|
AS field10 ,
|
|
alias1 . col_int AS field11 ,
|
|
NTILE ( 5 ) OVER ( ORDER BY alias1. col_int ) AS field12
|
|
FROM DD AS alias1 LEFT JOIN H AS alias2
|
|
ON alias1 . pk = alias2 . col_int
|
|
WHERE alias1 . pk > 3
|
|
GROUP BY
|
|
field1, alias1.pk, field3,alias2.col_varchar_255_utf8_key,
|
|
alias1.col_varchar_10_latin1_key,alias1.col_varchar_255_utf8, field5,
|
|
alias2.col_varchar_10_utf8,alias1.col_int_key,
|
|
alias2.col_varchar_255_utf8,alias1.col_varchar_10_latin1, field7,
|
|
alias1.col_int, field9,alias1.col_varchar_255_latin1,
|
|
alias2.col_varchar_255_latin1,alias1.col_varchar_255_utf8_key,
|
|
alias1.col_varchar_10_latin1,alias2.col_int_key, alias1.col_int,
|
|
alias1.col_int_key, field11,alias1.col_int
|
|
HAVING field3 >= 7
|
|
ORDER BY field10 , field6 , field4;
|
|
|
|
DROP TABLE DD,H;
|
|
|
|
--echo #
|
|
--echo # Bug#27060420 WINDOW FUNC, VIEW, CRASH IN DO_COPY_MAYBE_NULL
|
|
--echo #
|
|
CREATE TABLE t (a INT NOT NULL, b BLOB NOT NULL) ENGINE=INNODB;
|
|
CREATE VIEW v AS SELECT * FROM t;
|
|
INSERT INTO t VALUES (1, ''), (1, '');
|
|
SELECT a, PERCENT_RANK() OVER w1 FROM t GROUP BY b,1 WITH ROLLUP WINDOW w1 AS();
|
|
--echo used to fail
|
|
SELECT a, PERCENT_RANK() OVER w1 FROM v GROUP BY b,1 WITH ROLLUP WINDOW w1 AS();
|
|
DROP VIEW v;
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug#26848089 LEAD/LAG WINDOW FUNCTIONS ON QUOTED JSON STRINGS RETURNS SAME VALUE FOR ALL ROWS
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (doc JSON);
|
|
INSERT INTO t1 VALUES
|
|
('{"txt": "abcd"}'), ('{"txt": "bcde"}'),
|
|
('{"txt": "cdef"}'), ('{"txt": "defg"}');
|
|
SELECT doc->'$.txt', LAG(doc->'$.txt') OVER (ORDER BY doc->'$.txt') FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#27061487 WINDOW FUNC, CRASH IN DECIMAL2BIN
|
|
--echo #
|
|
CREATE TABLE t(a INT);
|
|
INSERT INTO t VALUES (1), (2), (3);
|
|
SELECT FIRST_VALUE(-2605.952148) OVER
|
|
(PARTITION BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
|
|
FROM t WINDOW w1 AS (PARTITION BY a);
|
|
SELECT LAST_VALUE(-2605.952148) OVER
|
|
(PARTITION BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
|
|
FROM t WINDOW w1 AS (PARTITION BY a);
|
|
--echo These don't suffer from the error as their maybe_null==true,
|
|
--echo but including tests since we updated their code in case this
|
|
--echo changes: NTH(<non-null arg>, 1) is really the same as
|
|
--echo FIRST_VALUE, for example, and could have been made
|
|
--echo maybe_null==false.
|
|
SELECT NTH_VALUE(-2605.952148, 1) OVER
|
|
(PARTITION BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
|
|
FROM t WINDOW w1 AS (PARTITION BY a);
|
|
SELECT LEAD(-2605.952148, 1) OVER
|
|
(PARTITION BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
|
|
FROM t WINDOW w1 AS (PARTITION BY a);
|
|
SELECT MAX(-2605.952148) OVER
|
|
(PARTITION BY a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
|
|
FROM t WINDOW w1 AS (PARTITION BY a);
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug #27135084: WINDOW FUNC: CRASH IN ITEM_FUNC_INT_DIV::VAL_INT
|
|
--echo #
|
|
|
|
--error ER_WRONG_ARGUMENTS
|
|
SELECT ((PERIOD_DIFF('7766-05-30','2955-06-11'))DIV(LAST_VALUE(';')OVER()));
|
|
--error ER_DATA_OUT_OF_RANGE
|
|
SELECT ((BIT_OR(COT('f?e')))DIV(LAG(' ',133,0xbb054701d650)OVER()));
|
|
--error ER_DATA_OUT_OF_RANGE
|
|
SELECT ((EXP(2778419536874221989))DIV(LAST_VALUE('{ }')OVER()));
|
|
--error ER_WRONG_ARGUMENTS
|
|
SELECT ((PERIOD_ADD(-6001,16973))DIV(LAST_VALUE('')OVER()));
|
|
--error ER_WRONG_ARGUMENTS
|
|
SELECT((LAST_VALUE(PERIOD_ADD(32733,1518714932))OVER())DIV(NULL));
|
|
--error ER_DATA_OUT_OF_RANGE
|
|
SELECT ((EXP(0x6108))DIV(LAG(NULL,21,' 4F')OVER()));
|
|
--error ER_DATA_OUT_OF_RANGE
|
|
SELECT ((FIRST_VALUE(EXP(0x41a129))OVER())DIV(-969));
|
|
|
|
--echo #
|
|
--echo # Bug #27136492: WINDOW FUNC: MAKETIME: CRASH IN MY_DECIMAL2LLDIV_T
|
|
--echo #
|
|
|
|
--error ER_DATA_OUT_OF_RANGE
|
|
SELECT MAKETIME('1',(!(999*1.421474e+308)),LAST_VALUE(-25038)RESPECT NULLS
|
|
OVER());
|
|
|
|
--echo # End of test for Bug#27135084, Bug#27136492
|
|
|
|
--echo #
|
|
--echo # Bug#27149369: ASSERTION FAILURE IN BOOL
|
|
--echo # READ_FRAME_BUFFER_ROW(INT64, WINDOW*, BOOL)
|
|
--echo #
|
|
|
|
CREATE TABLE t1 ( i INTEGER);
|
|
INSERT INTO t1 VALUES (392),(392),(1027),(1027),(1027),(1034),(1039);
|
|
|
|
SELECT i, LAST_VALUE(i) OVER w FROM t1
|
|
WINDOW w AS (ORDER BY i RANGE BETWEEN 7 PRECEDING AND 1 PRECEDING);
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug #27062031: WINDOW FUNC: CRASH IN COUNT_FIELD_TYPES() /
|
|
--echo # ASSERTION FAILED: N < M_SIZE
|
|
--echo #
|
|
|
|
|
|
CREATE TABLE t(a INT);
|
|
INSERT INTO t values (1),(2),(3),(6),(0);
|
|
|
|
#Costs vary in some of the platforms. So not printing these values.
|
|
--echo Discard w
|
|
let $query=
|
|
SELECT 1 FROM t WINDOW w AS(PARTITION BY NULL,NULL ORDER BY NULL ASC);
|
|
eval $query;
|
|
--replace_regex /(_cost": )"[0-9.]+"/\1"#"/ /(per_join": )"[0-9]+"/\1"#"/ /(per_scan": )[0-9]+/\1"#"/ /(per_join": )[0-9]+/\1"#"/
|
|
eval EXPLAIN FORMAT=JSON $query;
|
|
|
|
--echo Discard w
|
|
let $query=
|
|
SELECT 1 FROM t WINDOW w AS(PARTITION BY 1+2,3+4,5+6 ORDER BY 1+2,3+4);
|
|
eval $query;
|
|
--replace_regex /(_cost": )"[0-9.]+"/\1"#"/ /(per_join": )"[0-9]+"/\1"#"/ /(per_scan": )[0-9]+/\1"#"/ /(per_join": )[0-9]+/\1"#"/
|
|
eval EXPLAIN FORMAT=JSON $query;
|
|
|
|
--echo Discard w2
|
|
let $query=
|
|
SELECT RANK() OVER w1 FROM t WINDOW w1 AS (ORDER BY a), w2 AS (w1);
|
|
eval $query;
|
|
--replace_regex /(_cost": )"[0-9.]+"/\1"#"/ /(per_join": )"[0-9]+"/\1"#"/ /(per_scan": )[0-9]+/\1"#"/ /(per_join": )[0-9]+/\1"#"/
|
|
eval EXPLAIN FORMAT=JSON $query;
|
|
|
|
--echo Don't discard either w1 or w2. Non-optimal: could replace with
|
|
--echo one window since w1 isn't used and even if it were used, the
|
|
--echo semantics are the same.
|
|
let $query=
|
|
SELECT RANK() OVER w2 FROM t WINDOW w1 AS (ORDER BY a), w2 AS (w1);
|
|
eval $query;
|
|
--replace_regex /(_cost": )"[0-9.]+"/\1"#"/ /(per_join": )"[0-9]+"/\1"#"/ /(per_scan": )[0-9]+/\1"#"/ /(per_join": )[0-9]+/\1"#"/
|
|
eval EXPLAIN FORMAT=JSON $query;
|
|
|
|
--echo Discard w3. Non-optimal: see previous case.
|
|
let $query=
|
|
SELECT RANK() OVER w2 FROM t WINDOW w1 AS (ORDER BY a), w2 AS (w1), w3 AS (w1);
|
|
eval $query;
|
|
--replace_regex /(_cost": )"[0-9.]+"/\1"#"/ /(per_join": )"[0-9]+"/\1"#"/ /(per_scan": )[0-9]+/\1"#"/ /(per_join": )[0-9]+/\1"#"/
|
|
eval EXPLAIN FORMAT=JSON $query;
|
|
|
|
--echo Discard w1. Non-optimal: see previous case.
|
|
let $query=
|
|
SELECT RANK() OVER w3 FROM t WINDOW w1 AS (w2), w3 AS (w2), w2 AS ();
|
|
eval $query;
|
|
--replace_regex /(_cost": )"[0-9.]+"/\1"#"/ /(per_join": )"[0-9]+"/\1"#"/ /(per_scan": )[0-9]+/\1"#"/ /(per_join": )[0-9]+/\1"#"/
|
|
eval EXPLAIN FORMAT=JSON $query;
|
|
|
|
--echo Discard w2.
|
|
let $query=
|
|
SELECT RANK() OVER w1 FROM t WINDOW w2 AS (w1), w1 AS (ORDER BY a);
|
|
eval $query;
|
|
--replace_regex /(_cost": )"[0-9.]+"/\1"#"/ /(per_join": )"[0-9]+"/\1"#"/ /(per_scan": )[0-9]+/\1"#"/ /(per_join": )[0-9]+/\1"#"/
|
|
eval EXPLAIN FORMAT=JSON $query;
|
|
|
|
--echo Discard none.
|
|
let $query=
|
|
SELECT RANK() OVER w1 FROM t WINDOW w1 AS (ROWS UNBOUNDED PRECEDING);
|
|
eval $query;
|
|
--replace_regex /(_cost": )"[0-9.]+"/\1"#"/ /(per_join": )"[0-9]+"/\1"#"/ /(per_scan": )[0-9]+/\1"#"/ /(per_join": )[0-9]+/\1"#"/
|
|
eval EXPLAIN FORMAT=JSON $query;
|
|
|
|
DROP TABLE t;
|
|
|
|
--echo # End of test for Bug#27062031
|
|
|
|
--echo #
|
|
--echo # Bug #27348276: Wrong result for FIRST_VALUE, LAST_VALUE
|
|
--echo #
|
|
CREATE TABLE t(c LONGTEXT NOT NULL);
|
|
INSERT INTO t VALUES ('1'), ('1'), ('1'), ('1');
|
|
SELECT FIRST_VALUE(c) OVER w fv, LAST_VALUE(c) OVER w lv
|
|
FROM t WINDOW w AS (ORDER BY c
|
|
ROWS BETWEEN 3 FOLLOWING AND 5 FOLLOWING);
|
|
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug#27230463 WINDOW FUNC: CRASH IN DO_COPY_MAYBE_NULL()
|
|
--echo #
|
|
|
|
CREATE TABLE t(a BIT(52) NOT NULL, KEY(a));
|
|
INSERT INTO t VALUES (1), (1), (1);
|
|
SELECT EXISTS
|
|
(SELECT a, LAST_VALUE(INET_ATON(1)) OVER() FROM t WHERE BIT_OR(1));
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug#27233287: WINDOW FUNC: CRASH IN ITEM::SAVE_IN_FIELD_INNER
|
|
--echo #
|
|
|
|
CREATE TABLE t (
|
|
f1 LONGTEXT GENERATED ALWAYS AS (_utf8mb4'1') VIRTUAL NOT NULL
|
|
);
|
|
INSERT INTO t VALUES();
|
|
SELECT LEAD(f1,1,1) OVER (ORDER BY f1) FROM t GROUP BY f1 WITH ROLLUP;
|
|
SELECT LAG(f1,1,1) OVER (ORDER BY f1) FROM t GROUP BY f1 WITH ROLLUP;
|
|
SELECT LAG((f1+3/2-1+5),1,1) OVER (ORDER BY f1) FROM t GROUP BY f1 WITH ROLLUP;
|
|
SELECT COALESCE(LAG(f1,1,1) OVER (ORDER BY f1)) FROM t GROUP BY f1 WITH ROLLUP;
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug#27438725 WINDOW FUNCTION VARIANCE CAN GIVE WRONG RESULT
|
|
--echo # WITH OPTIMIZED EVALUATION
|
|
--echo #
|
|
CREATE TABLE t1(b INT);
|
|
INSERT INTO t1 VALUES (2);
|
|
INSERT INTO t1 VALUES (3);
|
|
SELECT VARIANCE(b) over w `var`,
|
|
AVG(b) OVER w `avg`,
|
|
SUM(b) OVER w `sum`,
|
|
b,
|
|
COUNT(b) OVER w count FROM t1
|
|
WINDOW w as (ORDER BY b ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
|
|
|
|
SET SESSION windowing_use_high_precision= FALSE;
|
|
|
|
SELECT VARIANCE(b) over w `var`,
|
|
AVG(b) OVER w `avg`,
|
|
SUM(b) OVER w `sum`,
|
|
b,
|
|
count(b) OVER w count FROM t1
|
|
WINDOW w as (ORDER BY b ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
|
|
|
|
SELECT VARIANCE(b) over w `var`,
|
|
AVG(b) OVER w `avg`,
|
|
SUM(b) OVER w `sum`,
|
|
b,
|
|
count(b) OVER w count,
|
|
LAST_VALUE(b) OVER w lastval FROM t1
|
|
WINDOW w as (ORDER BY b ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
|
|
|
|
INSERT INTO t1 VALUES (2);
|
|
INSERT INTO t1 VALUES (3);
|
|
|
|
SET SESSION windowing_use_high_precision= TRUE; # default
|
|
|
|
SELECT VARIANCE(b) over w `var`,
|
|
FIRST_VALUE(b) over w fv,
|
|
AVG(b) OVER w `avg`,
|
|
SUM(b) OVER w `sum`,
|
|
b,
|
|
count(b) OVER w count FROM t1
|
|
WINDOW w as (ORDER BY b ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
|
|
SET SESSION windowing_use_high_precision= FALSE;
|
|
|
|
SELECT VARIANCE(b) over w `var`,
|
|
FIRST_VALUE(b) over w fv,
|
|
AVG(b) OVER w `avg`,
|
|
SUM(b) OVER w `sum`,
|
|
b,
|
|
count(b) OVER w count FROM t1
|
|
WINDOW w as (ORDER BY b ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
|
|
SET SESSION windowing_use_high_precision= TRUE; # default
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#27452115: ASSERTION FAILED: (UCHAR*) TABLE->DEF_READ_SET.BITMAP +
|
|
--echo # TABLE->S->COLUMN_BITMAP_
|
|
--echo #
|
|
|
|
CREATE TABLE t (a BIGINT,b INT);
|
|
INSERT INTO t VALUES (9223372036854775807,1);
|
|
INSERT INTO t VALUES (1,2);
|
|
--error ER_DATA_OUT_OF_RANGE
|
|
SELECT b, CUME_DIST() OVER (
|
|
PARTITION BY b ORDER BY a RANGE BETWEEN CURRENT ROW AND 1 FOLLOWING
|
|
) FROM t;
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug#27452179: WINDOW: ASSERTION FAILED: SCALE >= 0 && PRECISION > 0
|
|
--echo # && SCALE <= PRECISION
|
|
--echo #
|
|
|
|
CREATE TABLE t(f1 INTEGER);
|
|
INSERT INTO t VALUES(0),(1),(2),(3);
|
|
SELECT * FROM (SELECT IF(1, WEEKDAY('1'), ROW_NUMBER() OVER (PARTITION BY f1))
|
|
FROM t) AS a;
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug#27453155 W->FRAME_BUFFER()->S->DB_TYPE()->DB_TYPE ==
|
|
--echo # DB_TYPE_INNODB || CNT <= 1 || (W->LA
|
|
--echo #
|
|
CREATE TABLE t(a SMALLINT);
|
|
INSERT INTO t(a) VALUES (-32768), (-1), (32767), (32767), (1);
|
|
SELECT a, COUNT(a) OVER w, LAG(1,13) RESPECT NULLS OVER w
|
|
FROM t
|
|
WINDOW w AS (ORDER BY a RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING);
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug#28105241: WINDOWS FUNC + AGGREGATES: ASSERTION FAILED:
|
|
--echo # HYBRID_TYPE == DECIMAL_RESULT
|
|
--echo #
|
|
|
|
DO ST_CROSSES(@g,SUM(SHA(@g))OVER());
|
|
DO UNHEX(SUM(@g)OVER());
|
|
DO BIT_LENGTH(AVG(@f)OVER());
|
|
DO COMPRESS(SUM(' >')OVER());
|
|
DO LTRIM(AVG(LOG2(@c))OVER());
|
|
DO (~(SUM(@f)OVER()));
|
|
DO IS_UUID(SUM(@e)OVER());
|
|
DO TO_BASE64(AVG(@d)OVER());
|
|
|
|
--echo #
|
|
--echo # Bug#28080199 UBSAN: DECIMAL_ADD, LOAD OF VALUE 53,
|
|
--echo # WHICH IS NOT A VALID VALUE FOR TYPE
|
|
--echo #
|
|
|
|
SELECT bit_count(sum(cos(-66365726))over());
|
|
SELECT bit_count(avg(cos(-66365726))over());
|
|
|
|
--echo #
|
|
--echo # Bug#28431783 ASSERTION FALSE IN ITEM::TYPE_FOR_VARIABLE()
|
|
--echo # Regression after introduction of window functions
|
|
--echo #
|
|
CREATE TABLE t(a DATETIME(6) NOT NULL)ENGINE=INNODB;
|
|
INSERT INTO t(a) VALUES('2008-01-01 00:22:33');
|
|
SELECT( SELECT a FROM ( SELECT 1 FROM t ) e ORDER BY (@f:=a) )
|
|
FROM t GROUP BY a;
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug #27452365: UBSAN: SIGNED INTEGER OVERFLOW IN
|
|
--echo # PROCESS_BUFFERED_WINDOWING_RECORD
|
|
--echo #
|
|
|
|
CREATE TABLE t(a INTEGER);
|
|
INSERT INTO t VALUES (1),(2),(3),(4);
|
|
|
|
SELECT NTILE(74) OVER(ORDER BY a ROWS BETWEEN CURRENT ROW AND
|
|
9223372036854775807 FOLLOWING) FROM t;
|
|
|
|
SELECT SUM(a) OVER(ORDER BY a ROWS BETWEEN 9223372036854775807 FOLLOWING AND
|
|
9223372036854775807 FOLLOWING) as `sum` FROM t;
|
|
|
|
SELECT SUM(a) OVER(ORDER BY a ROWS BETWEEN 9223372036854775806 FOLLOWING AND
|
|
9223372036854775807 FOLLOWING) as `sum` FROM t;
|
|
|
|
SELECT SUM(a) OVER(ORDER BY a ROWS BETWEEN 9223372036854775805 FOLLOWING AND
|
|
9223372036854775807 FOLLOWING) as `sum` FROM t;
|
|
|
|
SELECT SUM(a) OVER(ORDER BY a ROWS BETWEEN 9223372036854775807 FOLLOWING AND
|
|
9223372036854775805 FOLLOWING) as `sum` FROM t;
|
|
|
|
SELECT SUM(a) OVER(ORDER BY a ROWS BETWEEN 9223372036854775807 PRECEDING AND
|
|
9223372036854775805 PRECEDING) as `sum` FROM t;
|
|
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug#27808099: "UNKNOWN FIELD IN WINDOW ORDER BY " ERROR EVEN
|
|
--echo # WHEN FIELD IS FOUND IN TABLE
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (i INTEGER);
|
|
WITH der AS (SELECT CASE WHEN i IN ('0') THEN i END AS a FROM t1),
|
|
der1 AS (SELECT ROW_NUMBER() OVER (ORDER BY a), a FROM der)
|
|
SELECT * FROM der1;
|
|
|
|
WITH der AS (SELECT CASE WHEN i IN ('0') THEN i END AS a FROM t1),
|
|
der1 AS (SELECT ROW_NUMBER() OVER (ORDER BY a) FROM der)
|
|
SELECT * FROM der1;
|
|
|
|
WITH der AS (SELECT i AS a FROM t1),
|
|
der1 AS (SELECT ROW_NUMBER() OVER (ORDER BY a) FROM der)
|
|
SELECT * FROM der1;
|
|
|
|
--error ER_BAD_FIELD_ERROR
|
|
SELECT i AS b, ROW_NUMBER() OVER (ORDER BY b) FROM t1;
|
|
--error ER_BAD_FIELD_ERROR
|
|
SELECT i+1 AS b, ROW_NUMBER() OVER (ORDER BY b) FROM t1;
|
|
|
|
SELECT 3 AS i, ROW_NUMBER() OVER (ORDER BY i) FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug#27973860: ASSERTION FAILED: M_COUNT > 0 && M_COUNT >
|
|
--echo # M_FRAME_NULL_COUNT
|
|
--echo #
|
|
|
|
CREATE TABLE t ( a INT, b DATETIME(3));
|
|
|
|
INSERT t VALUES(1986,'9344-11-05 13:39:24.686');
|
|
INSERT t VALUES(1995,'7213-04-25 08:35:10.618');
|
|
INSERT t VALUES(1971,'9352-01-31 07:55:58.233');
|
|
SELECT SUM(a) OVER w FROM t
|
|
WINDOW w AS(ORDER BY a,b RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
|
|
|
|
DROP TABLE t;
|
|
|
|
--echo # Bug#27816506 WINDOW FUNC; ASSERTION FAILED:
|
|
--echo # CURRENT_ROW > PREV_LAST_ROWNO_IN_FRAME && LOWER_L
|
|
--echo #
|
|
|
|
SET @savmode=@@SESSION.SQL_MODE;
|
|
SET SESSION SQL_MODE='';
|
|
CREATE TABLE t(
|
|
a TEXT CHARACTER SET CP1251
|
|
GENERATED ALWAYS AS (LPAD(1,10621933,10)) VIRTUAL,
|
|
b BLOB
|
|
GENERATED ALWAYS AS (LPAD(1,10621933,10)) VIRTUAL
|
|
) ENGINE=INNODB;
|
|
INSERT INTO t VALUES (), ();
|
|
|
|
# With TEXT:
|
|
--error ER_SUBQUERY_NO_1_ROW
|
|
SET @f =
|
|
(
|
|
SELECT NTILE(39) OVER w1 FROM t
|
|
WINDOW w1 AS(ORDER BY a)
|
|
);
|
|
|
|
PREPARE stmt FROM 'SELECT NTILE(39) OVER w1 FROM t
|
|
WINDOW w1 AS(ORDER BY a)';
|
|
EXECUTE stmt;
|
|
|
|
# and with BLOB:
|
|
--error ER_SUBQUERY_NO_1_ROW
|
|
SET @f =
|
|
(
|
|
SELECT NTILE(39) OVER w1 FROM t
|
|
WINDOW w1 AS(ORDER BY b)
|
|
);
|
|
|
|
PREPARE stmt FROM 'SELECT NTILE(39) OVER w1 FROM t
|
|
WINDOW w1 AS(ORDER BY b)';
|
|
EXECUTE stmt;
|
|
|
|
DROP TABLE t;
|
|
SET SESSION SQL_MODE=@savmode;
|
|
|
|
--echo #
|
|
--echo # Bug#28672483 WRONG RESULT FROM DISTINCT AND ARITHMETIC EXPRESSION
|
|
--echo # USING WINDOW FUNCTION
|
|
--echo #
|
|
|
|
--echo Uses frame buffer
|
|
WITH tab(t, company, quote) AS (
|
|
SELECT 1 AS t, 'G' AS company, 40 AS quote
|
|
UNION SELECT 2 , 'G', 60
|
|
UNION SELECT 3 , 'S', 60
|
|
UNION SELECT 4, 'S', 20
|
|
)
|
|
SELECT DISTINCT
|
|
company,
|
|
quote - LAG(quote) OVER (PARTITION BY company ORDER BY t)
|
|
FROM tab;
|
|
|
|
--echo Does not use frame buffer
|
|
CREATE TABLE t(i INT);
|
|
INSERT INTO t VALUES (0), (0), (0), (0), (1), (0), (0), (0);
|
|
SELECT DISTINCT i, 1 + SUM(i) OVER (ORDER BY i ROWS UNBOUNDED PRECEDING) FROM t;
|
|
SELECT DISTINCT 1 + SUM(i) OVER (ORDER BY i ROWS UNBOUNDED PRECEDING) FROM t;
|
|
DROP TABLE t;
|
|
|
|
|
|
--echo #
|
|
--echo # Bug#29201831 WINDOW FUNCTION WITH RANGE FRAME: CRASH IN
|
|
--echo # READ_FRAME_BUFFER_ROW
|
|
--echo #
|
|
CREATE TABLE t(a INT, b INT);
|
|
INSERT INTO t VALUES (1,1), (2,1), (3,2), (4,2), (5,3), (6,3);
|
|
INSERT INTO t VALUES (1,1), (4,2), (NULL, 2), (NULL, NULL), (2, NULL);
|
|
SELECT a,b, FIRST_VALUE(b) OVER w AS first, LAST_VALUE (a) OVER w AS last
|
|
FROM t WINDOW w AS (ORDER BY a desc RANGE 3 PRECEDING);
|
|
DROP TABLE t;
|
|
|
|
--echo #
|
|
--echo # Bug#29328529 AGGREGATE FUNCTION RESULT IS DEPENDENT BY WINDOW IS
|
|
--echo # DEFINED DIRECTLY OR AS NAMED
|
|
--echo #
|
|
WITH RECURSIVE cte AS (
|
|
SELECT 1 num
|
|
UNION ALL
|
|
SELECT num+1 FROM cte WHERE num < 5
|
|
)
|
|
SELECT num, COUNT(*) OVER (frame) cnt_named,
|
|
COUNT(*) OVER (ORDER BY num DESC) cnt_direct
|
|
FROM cte
|
|
WINDOW frame AS (ORDER BY num DESC);
|