From: Jon Olav Hauglid Date: September 21 2010 11:33am Subject: bzr commit into mysql-5.5-runtime branch (jon.hauglid:3141) Bug#54920 List-Archive: http://lists.mysql.com/commits/118705 X-Bug: 54920 Message-Id: <201009211135.o8LBZhxx019892@rcsinet15.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============3674996998053824512==" --===============3674996998053824512== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///export/home/x/mysql-5.5-runtime-bug54920/ based on revid:jon.hauglid@stripped 3141 Jon Olav Hauglid 2010-09-21 Bug #54920 Stored functions are allowed in HANDLER statements, but broken. Before this patch, it was allowed to use stored functions in HANDLER ... READ statements. The problem was that this functionality was not really supported by the code. Proper locking would for example not be performed, and it was also possible to break replication by having stored functions that performed updates. This patch disallows the use of stored functions in HANDLER ... READ. Any such statement will now give a syntax error. This is an incompatible change and should be reflected in the documentation. Test case added to handler_myisam/handler_innodb.test. modified: mysql-test/include/handler.inc mysql-test/r/handler_innodb.result mysql-test/r/handler_myisam.result sql/sql_yacc.yy === modified file 'mysql-test/include/handler.inc' --- a/mysql-test/include/handler.inc 2010-08-12 13:50:23 +0000 +++ b/mysql-test/include/handler.inc 2010-09-21 11:33:55 +0000 @@ -1809,9 +1809,32 @@ CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1); HANDLER t1 OPEN; # This used to cause the assert ---error ER_NO_SUCH_TABLE +--error ER_PARSE_ERROR HANDLER t1 READ FIRST WHERE f1() = 1; HANDLER t1 CLOSE; DROP FUNCTION f1; DROP TABLE t1; + + +--echo # +--echo # Bug#54920 Stored functions are allowed in HANDLER statements, +--echo # but broken. +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +--enable_warnings + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE FUNCTION f1() RETURNS INT RETURN 1; +HANDLER t1 OPEN; + +--error ER_PARSE_ERROR +HANDLER t1 READ FIRST WHERE f1() = 1; + +HANDLER t1 CLOSE; +DROP FUNCTION f1; +DROP TABLE t1; === modified file 'mysql-test/r/handler_innodb.result' --- a/mysql-test/r/handler_innodb.result 2010-07-05 11:59:34 +0000 +++ b/mysql-test/r/handler_innodb.result 2010-09-21 11:33:55 +0000 @@ -1726,7 +1726,22 @@ CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1); HANDLER t1 OPEN; HANDLER t1 READ FIRST WHERE f1() = 1; -ERROR 42S02: Table 'test.t2' doesn't exist +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 +HANDLER t1 CLOSE; +DROP FUNCTION f1; +DROP TABLE t1; +# +# Bug#54920 Stored functions are allowed in HANDLER statements, +# but broken. +# +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE FUNCTION f1() RETURNS INT RETURN 1; +HANDLER t1 OPEN; +HANDLER t1 READ FIRST WHERE f1() = 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 HANDLER t1 CLOSE; DROP FUNCTION f1; DROP TABLE t1; === modified file 'mysql-test/r/handler_myisam.result' --- a/mysql-test/r/handler_myisam.result 2010-07-05 11:59:34 +0000 +++ b/mysql-test/r/handler_myisam.result 2010-09-21 11:33:55 +0000 @@ -1722,7 +1722,22 @@ CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (1); HANDLER t1 OPEN; HANDLER t1 READ FIRST WHERE f1() = 1; -ERROR 42S02: Table 'test.t2' doesn't exist +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 +HANDLER t1 CLOSE; +DROP FUNCTION f1; +DROP TABLE t1; +# +# Bug#54920 Stored functions are allowed in HANDLER statements, +# but broken. +# +DROP TABLE IF EXISTS t1; +DROP FUNCTION IF EXISTS f1; +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1), (2); +CREATE FUNCTION f1() RETURNS INT RETURN 1; +HANDLER t1 OPEN; +HANDLER t1 READ FIRST WHERE f1() = 1; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 HANDLER t1 CLOSE; DROP FUNCTION f1; DROP TABLE t1; === modified file 'sql/sql_yacc.yy' --- a/sql/sql_yacc.yy 2010-09-09 14:29:14 +0000 +++ b/sql/sql_yacc.yy 2010-09-21 11:33:55 +0000 @@ -13245,6 +13245,12 @@ handler: handler_read_or_scan where_clause opt_limit_clause { Lex->expr_allows_subselect= TRUE; + /* Stored functions are not supported for HANDLER READ. */ + if (Lex->uses_stored_routines()) + { + my_parse_error(ER(ER_SYNTAX_ERROR)); + MYSQL_YYABORT; + } } ; --===============3674996998053824512== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/jon.hauglid@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: jon.hauglid@stripped # target_branch: file:///export/home/x/mysql-5.5-runtime-bug54920/ # testament_sha1: 586f672fa8f95d48df5246ee02a43fa8e1920d2a # timestamp: 2010-09-21 13:33:59 +0200 # source_branch: file:///export/home/x/mysql-5.5-bugfixing/ # base_revision_id: jon.hauglid@stripped\ # tg3u8bsp1v9p7r7g # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWc2yrv8ABLXfgADQWPf//393 /+C////6YAq98kFAAABQFUFAAAAAEcZMmjENNDATQxNGmTEDIwmjTTCDJjjJk0YhpoYCaGJo0yYg ZGE0aaYQZMcZMmjENNDATQxNGmTEDIwmjTTCDJhKaip+STJPJ5AYqeSegCMj1HptEhgNAABxkyaM Q00MBNDE0aZMQMjCaNNMIMmEkQgATEaE00MmkYk9SntpDQjyjQnpD1PMo8CeMHfTvcOLmC/bwnxe Kzzto6vIRiknsUz2D4RKRLhnBoQjDswr4sUYEOyy6mpMyGY0sHRE/28qx18GGZb4iX92Suz6mq+m GE83AtzonmssyrgJKOpMLeH+f3D8mnbstBcvG0GkNkEJJJJNRMdzI9IaqoVCJ5BwX6XDuIP60MIq 2uCcnyWHu7sYUtAMBUNmDlbOyWyTB/GCkutfsv7qcK1R1uDGclepg1B63KxMqClUB94dYvIYG5O6 PUO3b0cMYP5hO4f+PIPplqDatqZTBUTh/97r4gfS/4BGLMiwKIYxwCVYeQMEzf/tGIRJRqZ2tkhw /wFOXEO6X2emRXyYKg0VKR/pOtquAyDEq14hmEbMDLcDRBtYRaYJj+Fb1Arhw5cnG/6mjLXJna2R 33iEvr+9j+AQQwVVCQJhjWvEHD3Jb3Fu5Oj/qz+oEZ1HevRfnRUElj5XgbT8DeHedSQMdnPn8gNR 1BDnTA5F6ve6ET4qIJjeVGFRky+yxrfUlQ5sOVhSs6ABxBOuWxwfmvv+VYTT66Q6B7ZkGTJGYdru H5jCgkGIboUjJBAiMG4eW6Jr6yeY+6lwpYlMWVnSkmsjNCYHrJyTzknsZJUkRwiKhVwlc8Zp6yJ6 3k7In606YSvS8F6UIR+DCnJfrxLPsznMAWJSC1lllNU5TfKohbPxssKBMCuqZZaCyUCGs/nC6kqO ydbVQohEYkOd0C0G7p+g0+g2+ZjWmTJxYhcXFIxAwCogYm6IHA+oJ1ZWZU7uCgFYRC0VTJA+BOXE yFeRyDANxPIYqrCEtxRKa6U+e73y/QoptHpV2Dm2ZLAvI7e07iYqoJ7cFbYT4luRIg6IiBi8Z8gV coVlhyJ6SBIs6z1MvqXGBnuY96rJ8bSd3ZWy78QmiIyyH1hM8jIojEo5VlhKmOaJxgrRQUbCofns sacia+02hv5Ss3Pe0YnPAFv2jkdjGZQn+HyDqktdIKY7ust02mIYnoVU1bxtQ5IuwJmBN1Y1FNss ouywHDnRFSLuFUSnhnx4+ymke2ukbbNQyLKyym8p0ORmd5zNtdpvNRmMcbwoY2mQ5rNDA1GwsJ9g o0FDGmzAiWGhrBc1mQOSkdvpsm7JZlF9to9EXmIETVA1yBil5yrcVZNTNn1nFbSk4lLEQU9xIJjA cYKDUMWIgC1TF5wtJFZUXFpEwL+igEzWsFNJ1QyizSRIfUwpOJoNFE6U07S/T4u3w9YJkyIDIIkF 3h6o3o902wGTLyDggfa4wyY78Rg2JWD3mZvVvAYOgeNCaykhJCQu8RF/LNJxaIJJMIHBTr5pl+gO L5A6FVWoiD8LQ+pSDUKwl/KqFAUT9xOhTsJkj6A6TpkFqD/sPqLH6geiD/Ag/kNijhXWF6/ZBtWK CgQRWpBvZakyCAKhMldsCFQLWMn1oHLBgZAMwQSwEwrRagVSUkriwHVDC3K4UAdTH9AqDCAUCuii CmVwMnggesVyB1IVgtUw2C2ILxRSkKm2j62zBMaXKvq+46ewIAuQczIuvO3k23gPiVNia1orDFRl b8jio6cx1xJrPxpMV/TMAsvx+0/ExrnSl+YW/OlE0nciGnxiihjYDVB1nSVwtRYlZjSJBr1tUIAh 0DWGwvfHU5SqcQfM7XRJB8MjbYNwKz5sdhof2nMxjiT5kDccN5wKD9ew6H5VI7l8pj+Aidh8AW3v NFb0NY5sIYkjd3HeSIdW+/SwpLFSHd7e/Q6uzE7oNrt2XhYFrBHdAAml/kEcIQDnRBBPR3xGYSiM tG1hrhz3l/CMqt2rOwVAu15khW7cD9c3NRXpDy6cZLsKkc98iu9IkaYdQNEgEt6y8ulI4JJ94HBY wkQQQCCwPSB4Rh0zIAio599xXxO8s6hyMzVMHWQHKiBme4Lg749ENZgFKTGrE1hkcDE4BME9hQBr 8whm+BLQ4lfLnqY90/nlz2cGv/dzLeZCrDZHK1DdiZDimbnEJJV0zQCR0OHXWd4erVhPMGVT9Lgv +MhPAUzJfdVJBKf2Y8ANDyoS9knFw3YkjWd5OMdJys74nKdGExggNP5DmGAYeJo22IoseUjQeCC8 F0OygZb0xtMQXXKetmWeYyUOwzC3YWJBXUbPJyQSa0R59xWD/6aubEXoIIBuniLmyNJweA3kQCzQ 2h3FQE6idW+E/SGb3ZIHEDzf75o5AqA3gvdMtYLZW/ilYEtWqN5OXnw791FOSGYTILPQMj1JXJSO /D3G/xJwj08xmQe87b9I0mK5nNDA+tjQxKjl7WRkXjeIdSUdfeV50h4GtdA0Sl1EAiv7TGHu8we4 AcBzo1IKW5IOIXE/c4qmjcLuIRC5RpLe0uPCWrPRIKxrQmUi+QQIWsjXIqtNvgQr8q3oQ9cMt5L6 cValMUAwM6HuZlenSC04mZBEmxCYnoYdA46TJEQgQCIQeTKTG4qvIB7AwpW+eaoCpLwQWOqmSLAd PA/r4GzrD2HcyDtPecL9Dsx2B2y2UjhASOf1zSsEDJdwRcCZRGNQwMDHEAwSgyBkUhQoJsrVu+Qa +zqAsmzFs0SUT3rZ3MDC5OTVcHRrL5DdI8eZYWhmqKl2lEiN0ies95QY1lJWZjoHpPA7QVJSFSkA yL4ODg2QzoPA/F/FMEUkc7ChEIZOgaCQ2IfaLP2xRrqOAyviD1IlUfjb23BT+0bwIZms5WBaBYwO M2ZYefcSCS8yc9mKGYZ2HflYE9QMw03Wy1EJ+Ao47tAjPBYMZhKqcKEs5BYK3H7Q+1mZmZtq1BvM DByTjqOxgMBPqSdWtEXinIsgzmzDOCCB3A+ZpiTDDvQkjbNEIrcAyTiIENpvgLTa/FVqsBtadIOU sGqsxMYlqC+lIZL5oYJwkMihhYj21g71K0FyW0F5FIZ520zn5B1+GCQVzl5Y4ZMqiCDBUmUlSe0p AiL8pghv1k9J9642ApSDWpgrDrJj0NCelKk6HiXTcis8WihsDxHnj1nmVFqLdpIgEImXmxBBNOX9 WnTSieMlEY9p8w7NiwHOKDMDkEGLQNC7vM8tg12gKRSmKjBeJDOfIu5IpwoSGbZV3+A= --===============3674996998053824512==--