dbs/mysql-server
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
mysql-server/mysql-test/t/window_functions_bugs.test
Ho Sy Tan 4a107fd10e 2025-03-05 14:31:08
2025-03-05 14:31:37 +07:00

1325 lines
57 KiB
Text
Raw Permalink Blame History

--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 utf8mb3,
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 utf8mb3,
col_varchar_255_utf8_key varchar(255) CHARACTER SET utf8mb3,
col_int int,
col_int_key int,
col_varchar_255_utf8 varchar(255) CHARACTER SET utf8mb3,
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 utf8mb3,
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 utf8mb3,
pk integer auto_increment,
col_varchar_10_utf8 varchar(10) CHARACTER SET utf8mb3,
col_int int,
col_varchar_10_latin1 varchar(10) CHARACTER SET latin1,
col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8mb3,
/*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 m_nullable==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 m_nullable==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"#"/
--skip_if_hypergraph # Depends on the query plan.
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"#"/
--skip_if_hypergraph # Depends on the query plan.
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"#"/
--skip_if_hypergraph # Depends on the query plan.
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"#"/
--skip_if_hypergraph # Depends on the query plan.
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"#"/
--skip_if_hypergraph # Depends on the query plan.
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"#"/
--skip_if_hypergraph # Depends on the query plan.
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"#"/
--skip_if_hypergraph # Depends on the query plan.
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"#"/
--skip_if_hypergraph # Depends on the query plan.
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,10622,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);
--echo #
--echo # Bug#32028154: INCORRECT ERROR PROPAGATION FROM WINDOW FUNCTIONS
--echo #
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_ARRAY(FIRST_VALUE(CAST('' AS JSON)) OVER (), '');
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_ARRAY(NTH_VALUE(CAST('' AS JSON), 1) OVER (), '');
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_ARRAY(LEAD(CAST('true' AS JSON), 2, CAST('' AS JSON)) OVER (), '');
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_ARRAY(JSON_ARRAYAGG(CAST('' AS JSON)) OVER (), '');
--echo #
--echo # Bug #32802301: REGRESSION: TYPE_CONVERSION_STATUS FIELD_VARSTRING::STORE: ASSERTION `!TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET(TABLE->WRITE_SET, FIELD_INDEX()))' FAILED.
--echo #
CREATE TABLE t1 (a TIMESTAMP NOT NULL);
INSERT INTO t1 VALUES ('2000-01-01 00:00:00');
SELECT a + INTERVAL(LAST_VALUE(1) OVER ()) SECOND FROM (SELECT a FROM t1) q;
DROP TABLE t1;
--echo #
--echo # Bug#32820802: WRONG RESULT WITH WINDOW FUNCTION AS PART OF EXPRESSION
--echo #
CREATE TABLE t1 (
f1 INTEGER,
t1_partkey INTEGER
);
CREATE TABLE t2 (
t2_partkey INTEGER PRIMARY KEY
);
INSERT INTO t1 VALUES (34, 1), (10, 1), (4, 1), (6, 1),
(9, 2), (64, 2), (31,2);
INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7);
ANALYZE TABLE t1, t2;
SELECT 0.2 * AVG(f1) OVER w AS avg, f1
FROM t1 JOIN t2 ON t1_partkey = t2_partkey
WINDOW w AS (PARTITION BY t1_partkey);
DROP TABLE t1, t2;
--echo #
--echo # Bug#32328576: BOOL PROCESS_BUFFERED_WINDOWING_RECORD: ASSERTION `!ROW_ADDED
--echo # && !FOUND_FIRST' FAILED.
--echo #
CREATE TABLE t(f1 SET('a','b'), f2 INTEGER);
INSERT INTO t VALUES ('b',1), ('a',2), ('a,b',3), ('b',0), ('a',1);
SELECT FIRST_VALUE(f2) OVER(ORDER BY f1 RANGE CURRENT ROW) FROM t;
DROP TABLE t;
--echo #
--echo # Bug #33012642: WL#14419: ASSERTION `*TO_LEN >= 2 + FROM->SIGN' FAILED
--echo #
CREATE TABLE t1 ( a INTEGER NOT NULL );
INSERT INTO t1 VALUES (4);
# Make sure we don't try to sort-ahead on a, SUM(a) before the grouping.
SELECT a, SUM(a) OVER () AS s
FROM t1
GROUP BY a
ORDER BY a, s;
DROP TABLE t1;
--echo #
--echo # Bug #33014346: WL#14419: ASSERTION `RECEIVER.HASSECONDARYENGINECOSTHOOK()' FAILED
--echo #
CREATE TABLE t1 ( a INTEGER, b INTEGER );
# The (a,b,m) ordering is pre-reduced and combined with (a,b),
# so we can't ignore it for GROUP BY on the grounds that it
# used to contain an aggregate.
SELECT ROW_NUMBER() OVER (ORDER BY a), COUNT(*) AS m
FROM t1
GROUP BY a, b
ORDER BY a, b, m;
DROP TABLE t1;
--echo #
--echo # Bug #33041041: WL#14419: SIG 11 IN WINDOW::APPLY_TEMP_TABLE|SQL/WINDOW.CC
--echo #
CREATE TABLE t1 ( a INTEGER );
PREPARE stmt FROM "SELECT RANK() OVER (ORDER BY a) FROM t1 GROUP BY a";
EXECUTE stmt;
EXECUTE stmt;
DROP TABLE t1;
--echo #
--echo # Bug #33067989: WL#14419: RESULT MISMATCH SEEN WITH WINDOW FUNCTION IN SUBQUERY
--echo #
CREATE TABLE t1 ( a INTEGER );
INSERT INTO t1 VALUES (0), (1);
SELECT a
FROM t1 AS outer_t1
WHERE (a,a) IN (
SELECT MAX(a) OVER (), a FROM t1
);
DROP TABLE t1;
--echo #
--echo # Bug #33072114: WL#14419: ASSERTION `STATIC_CAST<SIZE_T>(END_BIT_NUM) <= CAPACITY()' FAILED
--echo #
CREATE TABLE t1 ( a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER, f INTEGER, g INTEGER, h INTEGER, i INTEGER, j INTEGER, k INTEGER );
SELECT
CUME_DIST() OVER (ORDER BY t1.a)
FROM
t1
NATURAL JOIN t1 AS t2
NATURAL JOIN t1 AS t3
NATURAL JOIN t1 AS t4
GROUP BY t1.a
WITH ROLLUP;
DROP TABLE t1;
--echo #
--echo # Bug #33028408: WL#14419: ASSERTION `M_COUNT >= M_FRAME_NULL_COUNT && M_FRAME_NULL_COUNT > 0' FA
--echo #
CREATE TABLE t1 ( pk INTEGER );
INSERT INTO t1 VALUES (2);
ANALYZE TABLE t1;
SELECT SUM(pk) OVER (ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) FROM t1 GROUP BY pk WITH ROLLUP;
DROP TABLE t1;
--echo #
--echo # Bug #33036143: WL#14419: ASSERTION `M_INDEX_CURSOR.IS_POSITIONED()' FAILED.
--echo #
CREATE TABLE t1 ( a LONGTEXT, b INTEGER );
INSERT INTO t1 VALUES ('1', 0);
SELECT
a,
SUM(1) OVER (ORDER BY b),
SUM(1) OVER (ORDER BY b DESC)
FROM t1;
DROP TABLE t1;
--echo #
--echo # Bug #33067260: WL#14419: SIG11 IN MY_FILL_8BIT|STRINGS/CTYPE-SIMPLE.CC
--echo #
CREATE TABLE t1 (a INTEGER, b VARCHAR(255));
INSERT INTO t1 VALUES (1,'x');
INSERT INTO t1 VALUES (-3,'y');
CREATE TABLE t2 ( a INTEGER );
SET sql_mode='';
SELECT MIN(a) AS field1 FROM t1 WHERE b IN ( SELECT a FROM t2 ) ORDER BY LEAD(a, 5, 7) OVER (ORDER BY b);
SELECT MIN(a), LEAD(a, 5, 7) OVER (ORDER BY b) AS field1 FROM t1 WHERE b IN ( SELECT a FROM t2 ) ORDER BY b;
SET sql_mode=DEFAULT;
DROP TABLE t1, t2;
--echo #
--echo # Bug #33075752: WL#14419: ASSERTION `N < SIZE()' FAILED|INCLUDE/PREALLOCED_ARRAY.H
--echo #
CREATE TABLE t1 ( a INTEGER );
SELECT DISTINCT
0 AS field1,
RANK() OVER (ORDER BY a) AS field2,
LAST_VALUE(a) OVER (ORDER BY a + 1) AS field3
FROM t1
GROUP BY a, field1 WITH ROLLUP
ORDER BY field1, field2, field3;
DROP TABLE t1;
--echo #
--echo # Bug #33181670: Recent regression: Memory leak with window functions and user variables
--echo #
CREATE TABLE t1 (a INTEGER);
INSERT INTO t1 VALUES (1);
SELECT LAST_VALUE(0) OVER (ORDER BY (@f:='x')) FROM t1;
# NOTE: To actually show the bug in MTR, a shutdown; needs to be inserted here.
DROP TABLE t1;
--echo #
--echo # Bug#33399696: Switch slice before reaching execution end
--echo #
CREATE TABLE t(a INT, b DOUBLE);
INSERT INTO t VALUES(1, 2.1);
INSERT INTO t VALUES(2, 1.4);
INSERT INTO t VALUES(1, 2.56);
INSERT INTO t VALUES(2, 2.23);
SELECT a, SUM(b)+1 AS cnt,
ROW_NUMBER() OVER (ORDER BY SUM(b) DESC) AS rn
FROM t GROUP BY a ORDER BY rn;
SELECT a, ROUND(SUM(b), 2) AS cnt,
ROW_NUMBER() OVER (ORDER BY SUM(b) DESC) AS rn
FROM t GROUP BY a ORDER BY rn DESC;
DROP TABLE t;
--echo #
--echo # Bug#33415082:Hypergraph: Query with window function crashes in do_add()
--echo #
CREATE TABLE v0 (v1 INTEGER);
INSERT INTO v0 VALUES (1);
let $query=
SELECT v1, (SELECT v1 FROM (SELECT AVG(0) OVER (), v1) AS v2) FROM v0;
# Query fails only when executed multiple times.
eval $query;
eval $query;
eval $query;
DROP TABLE v0;
--echo #
--echo # Bug#33930766 hypergraph: Assert failure in ApplyWindowFunctions() in query with 64 windows
--echo #
CREATE TABLE t(
col1 INT, col2 INT,
col3 INT, col4 INT, col5 INT,
col6 INT, col7 INT, col8 INT, col9 INT, col10 INT,
col11 INT, col12 INT, col13 INT, col14 INT, col15 INT,
col16 INT, col17 INT, col18 INT, col19 INT, col20 INT,
col21 INT, col22 INT, col23 INT, col24 INT, col25 INT,
col26 INT, col27 INT, col28 INT, col29 INT, col30 INT,
col31 INT, col32 INT, col33 INT, col34 INT, col35 INT,
col36 INT, col37 INT, col38 INT, col39 INT, col40 INT,
col41 INT, col42 INT, col43 INT, col44 INT, col45 INT,
col46 INT, col47 INT, col48 INT, col49 INT, col50 INT,
col51 INT, col52 INT, col53 INT, col54 INT, col55 INT,
col56 INT, col57 INT, col58 INT, col59 INT, col60 INT,
col61 INT, col62 INT, col63 INT, col64 INT, col65 INT,
col66 INT, col67 INT, col68 INT, col69 INT, col70 INT);
--let $i= 0
--let $error= 0
while ($i < 10)
{
eval INSERT INTO t VALUES(
$i+1, $i+2, $i+3, $i+4, $i+5, $i+6, $i+7, $i+8, $i+9, $i+10,
$i+11, $i+12, $i+13, $i+14, $i+15, $i+16, $i+17, $i+18, $i+19, $i+20,
$i+21, $i+22, $i+23, $i+24, $i+25, $i+26, $i+27, $i+28, $i+29, $i+30,
$i+31, $i+32, $i+33, $i+34, $i+35, $i+36, $i+37, $i+38, $i+39, $i+40,
$i+41, $i+42, $i+43, $i+44, $i+45, $i+46, $i+47, $i+48, $i+49, $i+50,
$i+51, $i+52, $i+53, $i+54, $i+55, $i+56, $i+57, $i+58, $i+59, $i+60,
$i+61, $i+62, $i+63, $i+64, $i+65, $i+66, $i+67, $i+68, $i+69, $i+70);
--inc $i
}
SELECT
FIRST_VALUE(col1) OVER (ORDER BY col1) AS wf1,
FIRST_VALUE(col1) OVER (ORDER BY col2) AS wf2,
FIRST_VALUE(col1) OVER (ORDER BY col3) AS wf3,
FIRST_VALUE(col1) OVER (ORDER BY col4) AS wf4,
FIRST_VALUE(col1) OVER (ORDER BY col5) AS wf5,
FIRST_VALUE(col1) OVER (ORDER BY col6) AS wf6,
FIRST_VALUE(col1) OVER (ORDER BY col7) AS wf7,
FIRST_VALUE(col1) OVER (ORDER BY col8) AS wf8,
FIRST_VALUE(col1) OVER (ORDER BY col9) AS wf9,
FIRST_VALUE(col1) OVER (ORDER BY col10) AS wf10,
FIRST_VALUE(col1) OVER (ORDER BY col11) AS wf11,
FIRST_VALUE(col1) OVER (ORDER BY col12) AS wf12,
FIRST_VALUE(col1) OVER (ORDER BY col13) AS wf13,
FIRST_VALUE(col1) OVER (ORDER BY col14) AS wf14,
FIRST_VALUE(col1) OVER (ORDER BY col15) AS wf15,
FIRST_VALUE(col1) OVER (ORDER BY col16) AS wf16,
FIRST_VALUE(col1) OVER (ORDER BY col17) AS wf17,
FIRST_VALUE(col1) OVER (ORDER BY col18) AS wf18,
FIRST_VALUE(col1) OVER (ORDER BY col19) AS wf19,
FIRST_VALUE(col1) OVER (ORDER BY col20) AS wf20,
FIRST_VALUE(col1) OVER (ORDER BY col21) AS wf21,
FIRST_VALUE(col1) OVER (ORDER BY col22) AS wf22,
FIRST_VALUE(col1) OVER (ORDER BY col23) AS wf23,
FIRST_VALUE(col1) OVER (ORDER BY col24) AS wf24,
FIRST_VALUE(col1) OVER (ORDER BY col25) AS wf25,
FIRST_VALUE(col1) OVER (ORDER BY col26) AS wf26,
FIRST_VALUE(col1) OVER (ORDER BY col27) AS wf27,
FIRST_VALUE(col1) OVER (ORDER BY col28) AS wf28,
FIRST_VALUE(col1) OVER (ORDER BY col29) AS wf29,
FIRST_VALUE(col1) OVER (ORDER BY col30) AS wf30,
FIRST_VALUE(col1) OVER (ORDER BY col31) AS wf31,
FIRST_VALUE(col1) OVER (ORDER BY col32) AS wf32,
FIRST_VALUE(col1) OVER (ORDER BY col33) AS wf33,
FIRST_VALUE(col1) OVER (ORDER BY col34) AS wf34,
FIRST_VALUE(col1) OVER (ORDER BY col35) AS wf35,
FIRST_VALUE(col1) OVER (ORDER BY col36) AS wf36,
FIRST_VALUE(col1) OVER (ORDER BY col37) AS wf37,
FIRST_VALUE(col1) OVER (ORDER BY col38) AS wf38,
FIRST_VALUE(col1) OVER (ORDER BY col39) AS wf39,
FIRST_VALUE(col1) OVER (ORDER BY col40) AS wf40,
FIRST_VALUE(col1) OVER (ORDER BY col41) AS wf41,
FIRST_VALUE(col1) OVER (ORDER BY col42) AS wf42,
FIRST_VALUE(col1) OVER (ORDER BY col43) AS wf43,
FIRST_VALUE(col1) OVER (ORDER BY col44) AS wf44,
FIRST_VALUE(col1) OVER (ORDER BY col45) AS wf45,
FIRST_VALUE(col1) OVER (ORDER BY col46) AS wf46,
FIRST_VALUE(col1) OVER (ORDER BY col47) AS wf47,
FIRST_VALUE(col1) OVER (ORDER BY col48) AS wf48,
FIRST_VALUE(col1) OVER (ORDER BY col49) AS wf49,
FIRST_VALUE(col1) OVER (ORDER BY col50) AS wf50,
FIRST_VALUE(col1) OVER (ORDER BY col51) AS wf51,
FIRST_VALUE(col1) OVER (ORDER BY col52) AS wf52,
FIRST_VALUE(col1) OVER (ORDER BY col53) AS wf53,
FIRST_VALUE(col1) OVER (ORDER BY col54) AS wf54,
FIRST_VALUE(col1) OVER (ORDER BY col55) AS wf55,
FIRST_VALUE(col1) OVER (ORDER BY col56) AS wf56,
FIRST_VALUE(col1) OVER (ORDER BY col57) AS wf57,
FIRST_VALUE(col1) OVER (ORDER BY col58) AS wf58,
FIRST_VALUE(col1) OVER (ORDER BY col59) AS wf59,
FIRST_VALUE(col1) OVER (ORDER BY col60) AS wf60,
FIRST_VALUE(col1) OVER (ORDER BY col61) AS wf61,
FIRST_VALUE(col1) OVER (ORDER BY col62) AS wf62,
FIRST_VALUE(col1) OVER (ORDER BY col63) AS wf63,
FIRST_VALUE(col1) OVER (ORDER BY col64) AS wf64,
FIRST_VALUE(col1) OVER (ORDER BY col65) AS wf65,
FIRST_VALUE(col1) OVER (ORDER BY col66) AS wf66,
FIRST_VALUE(col1) OVER (ORDER BY col67) AS wf67,
FIRST_VALUE(col1) OVER (ORDER BY col68) AS wf68,
FIRST_VALUE(col1) OVER (ORDER BY col69) AS wf69,
FIRST_VALUE(col1) OVER (ORDER BY col70) AS wf70
FROM t;
SELECT DISTINCT
FIRST_VALUE(col1) OVER (ORDER BY col1) AS wf1,
FIRST_VALUE(col1) OVER (ORDER BY col2) AS wf2,
FIRST_VALUE(col1) OVER (ORDER BY col3) AS wf3,
FIRST_VALUE(col1) OVER (ORDER BY col4) AS wf4,
FIRST_VALUE(col1) OVER (ORDER BY col5) AS wf5,
FIRST_VALUE(col1) OVER (ORDER BY col6) AS wf6,
FIRST_VALUE(col1) OVER (ORDER BY col7) AS wf7,
FIRST_VALUE(col1) OVER (ORDER BY col8) AS wf8,
FIRST_VALUE(col1) OVER (ORDER BY col9) AS wf9,
FIRST_VALUE(col1) OVER (ORDER BY col10) AS wf10,
FIRST_VALUE(col1) OVER (ORDER BY col11) AS wf11,
FIRST_VALUE(col1) OVER (ORDER BY col12) AS wf12,
FIRST_VALUE(col1) OVER (ORDER BY col13) AS wf13,
FIRST_VALUE(col1) OVER (ORDER BY col14) AS wf14,
FIRST_VALUE(col1) OVER (ORDER BY col15) AS wf15,
FIRST_VALUE(col1) OVER (ORDER BY col16) AS wf16,
FIRST_VALUE(col1) OVER (ORDER BY col17) AS wf17,
FIRST_VALUE(col1) OVER (ORDER BY col18) AS wf18,
FIRST_VALUE(col1) OVER (ORDER BY col19) AS wf19,
FIRST_VALUE(col1) OVER (ORDER BY col20) AS wf20,
FIRST_VALUE(col1) OVER (ORDER BY col21) AS wf21,
FIRST_VALUE(col1) OVER (ORDER BY col22) AS wf22,
FIRST_VALUE(col1) OVER (ORDER BY col23) AS wf23,
FIRST_VALUE(col1) OVER (ORDER BY col24) AS wf24,
FIRST_VALUE(col1) OVER (ORDER BY col25) AS wf25,
FIRST_VALUE(col1) OVER (ORDER BY col26) AS wf26,
FIRST_VALUE(col1) OVER (ORDER BY col27) AS wf27,
FIRST_VALUE(col1) OVER (ORDER BY col28) AS wf28,
FIRST_VALUE(col1) OVER (ORDER BY col29) AS wf29,
FIRST_VALUE(col1) OVER (ORDER BY col30) AS wf30,
FIRST_VALUE(col1) OVER (ORDER BY col31) AS wf31,
FIRST_VALUE(col1) OVER (ORDER BY col32) AS wf32,
FIRST_VALUE(col1) OVER (ORDER BY col33) AS wf33,
FIRST_VALUE(col1) OVER (ORDER BY col34) AS wf34,
FIRST_VALUE(col1) OVER (ORDER BY col35) AS wf35,
FIRST_VALUE(col1) OVER (ORDER BY col36) AS wf36,
FIRST_VALUE(col1) OVER (ORDER BY col37) AS wf37,
FIRST_VALUE(col1) OVER (ORDER BY col38) AS wf38,
FIRST_VALUE(col1) OVER (ORDER BY col39) AS wf39,
FIRST_VALUE(col1) OVER (ORDER BY col40) AS wf40,
FIRST_VALUE(col1) OVER (ORDER BY col41) AS wf41,
FIRST_VALUE(col1) OVER (ORDER BY col42) AS wf42,
FIRST_VALUE(col1) OVER (ORDER BY col43) AS wf43,
FIRST_VALUE(col1) OVER (ORDER BY col44) AS wf44,
FIRST_VALUE(col1) OVER (ORDER BY col45) AS wf45,
FIRST_VALUE(col1) OVER (ORDER BY col46) AS wf46,
FIRST_VALUE(col1) OVER (ORDER BY col47) AS wf47,
FIRST_VALUE(col1) OVER (ORDER BY col48) AS wf48,
FIRST_VALUE(col1) OVER (ORDER BY col49) AS wf49,
FIRST_VALUE(col1) OVER (ORDER BY col50) AS wf50,
FIRST_VALUE(col1) OVER (ORDER BY col51) AS wf51,
FIRST_VALUE(col1) OVER (ORDER BY col52) AS wf52,
FIRST_VALUE(col1) OVER (ORDER BY col53) AS wf53,
FIRST_VALUE(col1) OVER (ORDER BY col54) AS wf54,
FIRST_VALUE(col1) OVER (ORDER BY col55) AS wf55,
FIRST_VALUE(col1) OVER (ORDER BY col56) AS wf56,
FIRST_VALUE(col1) OVER (ORDER BY col57) AS wf57,
FIRST_VALUE(col1) OVER (ORDER BY col58) AS wf58,
FIRST_VALUE(col1) OVER (ORDER BY col59) AS wf59,
FIRST_VALUE(col1) OVER (ORDER BY col60) AS wf60,
FIRST_VALUE(col1) OVER (ORDER BY col61) AS wf61,
FIRST_VALUE(col1) OVER (ORDER BY col62) AS wf62,
FIRST_VALUE(col1) OVER (ORDER BY col63) AS wf63,
FIRST_VALUE(col1) OVER (ORDER BY col64) AS wf64,
FIRST_VALUE(col1) OVER (ORDER BY col65) AS wf65,
FIRST_VALUE(col1) OVER (ORDER BY col66) AS wf66,
FIRST_VALUE(col1) OVER (ORDER BY col67) AS wf67,
FIRST_VALUE(col1) OVER (ORDER BY col68) AS wf68,
FIRST_VALUE(col1) OVER (ORDER BY col69) AS wf69,
FIRST_VALUE(col1) OVER (ORDER BY col70) AS wf70
FROM t;
DROP TABLE t;
--echo #
--echo # Bug#34431996 MySQL window function first_value wrong
--echo #
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER, f3 INTEGER);
INSERT INTO t1 VALUES (1,1,1),
(2,1,20),
(3,1,300),
(4,1,4000);
SELECT f1,f2,f3,
FIRST_VALUE(f3) OVER w AS 'FIRST_VALUE',
LAG(f3) OVER w AS 'LAG',
NTH_VALUE(f3, 4) OVER w AS 'NTH_VALUE'
FROM t1 WINDOW w AS (PARTITION BY f2 ORDER BY f3
ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING);
DROP TABLE t1;
--echo #
--echo # Bug#34099408: Result mismatch seen with DENSE_RANK window
--echo # function
--echo #
CREATE TABLE t1 (f1 INTEGER, f2 INTEGER);
INSERT INTO t1 VALUES (1,1),(1,2),(2,1),(2,2);
SELECT f1, f2, DENSE_RANK() OVER (ORDER BY f1), RANK() OVER (ORDER BY f1)
FROM t1 GROUP BY f1,f2 WITH ROLLUP;
SELECT f1, f2, DENSE_RANK() OVER (ORDER BY f1), RANK() OVER (ORDER BY f1)
FROM t1 GROUP BY f1,f2;
DROP TABLE t1;
--echo #
--echo # Bug#35013880: Wrong result for AVG() OVER(ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
--echo #
# int & decimal share code paths for AVG so no need to test DECIMAL separately
CREATE TABLE t (n INTEGER, r FLOAT);
INSERT INTO t VALUES (1, 1.0), (2, 2.0), (3, 3.0), (4, 4.0);
--echo # No high precision for floating point, lest we don't use inversion.
--echo # With no inversion we do not see the error.
SET SESSION windowing_use_high_precision=FALSE;
SELECT n
, SUM(n) OVER w AS "sum(n)"
, COUNT(*) OVER w AS "count(n)"
, AVG(n) OVER w AS "avg(n)"
, SUM(n) OVER w / COUNT(*) OVER w AS "sum(n)/count(n)",
r
, SUM(r) OVER w AS "sum(r)"
, COUNT(*) OVER w AS "count(r)"
, AVG(r) OVER w AS "avg(r)"
, SUM(r) OVER w / COUNT(*) OVER w AS "sum(r)/count(r)"
FROM t WINDOW w AS (ORDER BY n ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
ORDER BY n;
SET SESSION windowing_use_high_precision=default;
--echo # Was correct even before, because AVG(r) changes the code path even
--echo # for AVG(n): only with inversion did we see the error.
SELECT n
, SUM(n) OVER w AS "sum(n)"
, COUNT(*) OVER w AS "count(n)"
, AVG(n) OVER w AS "avg(n)"
, SUM(n) OVER w / COUNT(*) OVER w AS "sum(n)/count(n)",
r
, SUM(r) OVER w AS "sum(r)"
, COUNT(*) OVER w AS "count(r)"
, AVG(r) OVER w AS "avg(r)"
, SUM(r) OVER w / COUNT(*) OVER w AS "sum(r)/count(r)"
FROM t WINDOW w AS (ORDER BY n ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
ORDER BY n;
DROP TABLE t;
--echo #
--echo # Bug#33064174 PERCENT_RANK() RETURNS WRONG RESULT
--echo #
CREATE TABLE t1 ( a TIME );
INSERT INTO t1 VALUES ('02:00:00');
INSERT INTO t1 VALUES (NULL);
SELECT a, PERCENT_RANK() OVER (ORDER BY a) FROM t1;
DROP TABLE t1;
--echo #
--echo # Bug#33069747 ASSERTION `M_COUNT >= M_FRAME_NULL_COUNT && M_FRAME_NULL_COUNT > 0' FAILED
--echo #
CREATE TABLE t1 ( a INTEGER, b INTEGER );
INSERT INTO t1 VALUES (0,NULL);
INSERT INTO t1 VALUES (NULL,1);
ANALYZE TABLE t1;
SELECT 1 FROM t1
ORDER BY BIT_OR(a) OVER (ORDER BY b ROWS CURRENT ROW);
SELECT SUM(a) OVER w
FROM t1
WINDOW w AS (ORDER BY b ROWS CURRENT ROW)
ORDER BY SUM(b) OVER w;
SELECT a, b, SUM(a) OVER w, SUM(b) OVER w
FROM t1
WINDOW w AS (ORDER BY b ROWS CURRENT ROW)
ORDER BY SUM(b) OVER w;
DROP TABLE t1;
--echo #
--echo # Bug#35118579 hypergraph ASAN crash in do_sub
--echo #
CREATE TABLE t1(pk INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c1 VARCHAR(10) DEFAULT NULL,
c2 VARCHAR(10) DEFAULT NULL,
c3 INT, KEY(c1));
INSERT INTO t1(c1, c2, c3) VALUES('am', 'it', 1);
SET SQL_MODE='';
SELECT DISTINCT BIT_XOR( c2 )
FROM t1
WINDOW w1 AS (ORDER BY c2),
w2 AS (ORDER BY c3 ROWS UNBOUNDED PRECEDING)
ORDER BY ( EXPORT_SET( c2,
c1,
SUM( pk ) OVER w1 ,
FIRST_VALUE( pk ) OVER w2
)
);
SET SQL_MODE=default;
DROP TABLE t1;
--echo #
--echo # Bug#35340987 Prepared SELECT returns different results on
--echo # consecutive executions.
--echo #
CREATE TABLE t(i INT);
INSERT INTO t(i) VALUES (100), (101);
PREPARE stmt1 FROM "
SELECT a.i, (LAST_VALUE(a.i) OVER outer_window) = a.i AS result
FROM (SELECT LAG(i) OVER inner_window AS i_lag,
i AS i
FROM t
WINDOW inner_window AS (ORDER BY i) ) AS a
WINDOW outer_window AS (ORDER BY a.i)";
EXECUTE stmt1;
EXECUTE stmt1;
DROP PREPARE stmt1;
DROP TABLE t;
--echo #
--echo # Bug#35380604 issue with window functions in stored procedures
--echo #
CREATE TABLE table1(id INT);
INSERT INTO table1 VALUES(1),(2),(3),(4),(5),(6);
DELIMITER $;
CREATE PROCEDURE test2()
BEGIN
SELECT COUNT(*) OVER (ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING) AS a,
COUNT(*) OVER (ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING) + 1 AS "a+1",
COUNT(*) OVER (ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING) - 1 AS "a-1",
COUNT(*) OVER () AS b,
COUNT(*) OVER () + 1 AS "b+1",
COUNT(*) OVER () - 1 AS "b-1" FROM table1 ;
END $
DELIMITER ;$
CALL test2();
CALL test2();
DROP PROCEDURE test2;
DROP TABLE table1;
--echo #
--echo # Bug#35390341 Assertion `m_count > 0 && m_count > m_frame_null_count' failed.
--echo #
CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (NULL), (NULL), (-2128216064);
SELECT AVG( @A := c1 ) OVER (ROWS 1 PRECEDING) FROM t1;
SELECT @A;
DROP TABLE t1;
# Repro from Bug#11764371 decorated with windows to test
# against possible locking issue mentioned therein.
CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (0);
# original
SELECT DISTINCT
POW( COUNT(*), @a:=(SELECT 1 FROM t1 LEFT JOIN
t1 AS t2
ON @a)
) AS b
FROM t1 GROUP BY a;
# window func using setvar added in outer layer
SELECT DISTINCT
pow( COUNT(*), @a:=(SELECT 1 FROM t1 LEFT JOIN
t1 t2
ON @a)
) AS `pow`,
AVG( @a:= a ) OVER (ROWS 1 PRECEDING) AS `avg`
FROM t1 GROUP BY a;
SELECT @a;
# window func using setvar added in subquery
SELECT DISTINCT
pow( COUNT(*), @a:=(SELECT MAX( @a := t1.a) OVER w
FROM t1
LEFT JOIN
t1 t2
ON @a
WINDOW w AS (ROWS 1 PRECEDING)
)
) AS `pow`
FROM t1 GROUP BY a;
SELECT @a;
DROP TABLE t1;
--echo #
--echo # Bug#36008133 Assertion `false' failed in replace_embedded_rollup_references_with_tmp_fields
--echo #
CREATE TABLE t1(f1 INTEGER);
SELECT @A := (CUME_DIST() OVER () + f1 + RANK() OVER ()) FROM t1 GROUP BY f1 WITH ROLLUP;
DROP TABLE t1;
--echo #
--echo # Bug#35537311 nth_value window function assertion error
--echo #
CREATE TABLE t0(c1 TINYINT UNSIGNED, c5 BIT);
INSERT INTO t0 VALUES
(0,NULL),
(0,NULL),
(0,NULL),
(0,NULL),
(0,NULL),
(12,_binary '\0'),
(135,_binary '\0'),
(193,_binary '\0'),
(206,_binary '\0'),
(244,_binary '\0'),
(255,NULL),
(255,NULL),
(255,NULL);
SELECT NTH_VALUE(t0.c1, 97) OVER (
ORDER BY t0.c1
RANGE BETWEEN 99 FOLLOWING AND 51 FOLLOWING)
FROM t0;
TRUNCATE t0;
INSERT INTO t0(c1) VALUES (135), (206), (193), (244), (255), (255), (255);
# additional assert found during review
SELECT NTH_VALUE(c1, 2) OVER (ORDER BY c1 RANGE BETWEEN 60 FOLLOWING AND 100 FOLLOWING) FROM t0;
DROP TABLE t0;
--echo #
--echo # Bug#36200398: Wrong empty result set with hypergraph, windowing
--echo # and derived tables
--echo #
CREATE TABLE t(i INT);
INSERT INTO t VALUES (1), (2), (3);
SELECT i, COUNT(*) OVER w FROM (SELECT i, i = 2 AS cmp FROM t) AS dt
WHERE cmp > 0 WINDOW w AS (PARTITION BY cmp);
SELECT i, COUNT(*) OVER w FROM (SELECT i, i = 2 AS cmp FROM t) AS dt
WHERE cmp > 0 WINDOW w AS (ORDER BY cmp);
DROP TABLE t;
Reference in a new issue View git blame Copy permalink