From: Sergey Glukhov Date: June 21 2011 10:03am Subject: bzr commit into mysql-5.1 branch (sergey.glukhov:3649) Bug#11766594 List-Archive: http://lists.mysql.com/commits/139552 X-Bug: 11766594 Message-Id: <201106211014.p5LAENTQ026626@acsmt357.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0026405286109000361==" --===============0026405286109000361== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/gluh/MySQL/mysql-5.1/ based on revid:sergey.glukhov@stripped 3649 Sergey Glukhov 2011-06-21 Bug#11766594 59736: SELECT DISTINCT.. INCORRECT RESULT WITH DETERMINISTIC FUNCTION IN WHERE C There is an optimization of DISTINCT in JOIN::optimize() which depends on THD::used_tables value. Each SELECT statement inside SP resets used_tables value(see mysql_select()) and it leads to wrong result. The fix is to replace THD::used_tables with LEX::used_tables. @ mysql-test/r/sp.result test case @ mysql-test/t/sp.test test case @ sql/sql_base.cc THD::used_tables is replaced with LEX::used_tables @ sql/sql_class.cc THD::used_tables is replaced with LEX::used_tables @ sql/sql_class.h THD::used_tables is replaced with LEX::used_tables @ sql/sql_insert.cc THD::used_tables is replaced with LEX::used_tables @ sql/sql_lex.cc THD::used_tables is replaced with LEX::used_tables @ sql/sql_lex.h THD::used_tables is replaced with LEX::used_tables @ sql/sql_prepare.cc THD::used_tables is replaced with LEX::used_tables @ sql/sql_select.cc THD::used_tables is replaced with LEX::used_tables modified: mysql-test/r/sp.result mysql-test/t/sp.test sql/sql_base.cc sql/sql_class.cc sql/sql_class.h sql/sql_insert.cc sql/sql_lex.cc sql/sql_lex.h sql/sql_prepare.cc sql/sql_select.cc === modified file 'mysql-test/r/sp.result' --- a/mysql-test/r/sp.result 2010-02-09 10:30:50 +0000 +++ b/mysql-test/r/sp.result 2011-06-21 10:03:04 +0000 @@ -7053,6 +7053,25 @@ init_connect SET @@GLOBAL.init_connect= @old_init_connect; DROP PROCEDURE p2; DROP PROCEDURE p5; +# +# Bug#11766594 59736: SELECT DISTINCT.. INCORRECT RESULT WITH DETERMINISTIC FUNCTION IN WHERE C +# +CREATE TABLE t1 (a INT, b INT, KEY(b)); +CREATE TABLE t2 (c INT, d INT, KEY(c)); +INSERT INTO t1 VALUES (1,1),(1,1),(1,2); +INSERT INTO t2 VALUES (1,1),(1,2); +CREATE FUNCTION f1() RETURNS INT DETERMINISTIC +BEGIN +DECLARE a int; +-- SQL statement inside +SELECT 1 INTO a; +RETURN a; +END $ +SELECT COUNT(DISTINCT d) FROM t1, t2 WHERE a = c AND b = f1(); +COUNT(DISTINCT d) +2 +DROP FUNCTION f1; +DROP TABLE t1, t2; # ------------------------------------------------------------------ # -- End of 5.1 tests # ------------------------------------------------------------------ === modified file 'mysql-test/t/sp.test' --- a/mysql-test/t/sp.test 2009-12-23 13:44:03 +0000 +++ b/mysql-test/t/sp.test 2011-06-21 10:03:04 +0000 @@ -8350,6 +8350,33 @@ SET @@GLOBAL.init_connect= @old_init_con DROP PROCEDURE p2; DROP PROCEDURE p5; +--echo # +--echo # Bug#11766594 59736: SELECT DISTINCT.. INCORRECT RESULT WITH DETERMINISTIC FUNCTION IN WHERE C +--echo # + +CREATE TABLE t1 (a INT, b INT, KEY(b)); +CREATE TABLE t2 (c INT, d INT, KEY(c)); +INSERT INTO t1 VALUES (1,1),(1,1),(1,2); +INSERT INTO t2 VALUES (1,1),(1,2); + +DELIMITER $; + +CREATE FUNCTION f1() RETURNS INT DETERMINISTIC +BEGIN + DECLARE a int; + -- SQL statement inside + SELECT 1 INTO a; + RETURN a; +END $ + +DELIMITER ;$ + +SELECT COUNT(DISTINCT d) FROM t1, t2 WHERE a = c AND b = f1(); + +DROP FUNCTION f1; +DROP TABLE t1, t2; + + --echo # ------------------------------------------------------------------ --echo # -- End of 5.1 tests --echo # ------------------------------------------------------------------ === modified file 'sql/sql_base.cc' --- a/sql/sql_base.cc 2011-05-16 20:04:01 +0000 +++ b/sql/sql_base.cc 2011-06-21 10:03:04 +0000 @@ -7576,7 +7576,7 @@ bool setup_fields(THD *thd, Item **ref_p if (item->with_sum_func && item->type() != Item::SUM_FUNC_ITEM && sum_func_list) item->split_sum_func(thd, ref_pointer_array, *sum_func_list); - thd->used_tables|= item->used_tables(); + thd->lex->used_tables|= item->used_tables(); thd->lex->current_select->cur_pos_in_select_list++; } thd->lex->current_select->is_item_list_lookup= save_is_item_list_lookup; @@ -7923,7 +7923,7 @@ insert_fields(THD *thd, Name_resolution_ views and natural joins this update is performed inside the loop below. */ if (table) - thd->used_tables|= table->map; + thd->lex->used_tables|= table->map; /* Initialize a generic field iterator for the current table reference. @@ -8008,7 +8008,7 @@ insert_fields(THD *thd, Name_resolution_ field_table= nj_col->table_ref->table; if (field_table) { - thd->used_tables|= field_table->map; + thd->lex->used_tables|= field_table->map; field_table->covering_keys.intersect(field->part_of_key); field_table->merge_keys.merge(field->part_of_key); field_table->used_fields++; @@ -8016,7 +8016,7 @@ insert_fields(THD *thd, Name_resolution_ } } else - thd->used_tables|= item->used_tables(); + thd->lex->used_tables|= item->used_tables(); thd->lex->current_select->cur_pos_in_select_list++; } /* === modified file 'sql/sql_class.cc' --- a/sql/sql_class.cc 2011-05-21 08:43:54 +0000 +++ b/sql/sql_class.cc 2011-06-21 10:03:04 +0000 @@ -647,7 +647,6 @@ THD::THD() is_slave_error= thread_specific_used= FALSE; hash_clear(&handler_tables_hash); tmp_table=0; - used_tables=0; cuted_fields= sent_row_count= row_count= 0L; limit_found_rows= 0; row_count_func= -1; === modified file 'sql/sql_class.h' --- a/sql/sql_class.h 2011-05-18 14:40:01 +0000 +++ b/sql/sql_class.h 2011-06-21 10:03:04 +0000 @@ -1732,13 +1732,6 @@ public: */ ha_rows examined_row_count; - /* - The set of those tables whose fields are referenced in all subqueries - of the query. - TODO: possibly this it is incorrect to have used tables in THD because - with more than one subquery, it is not clear what does the field mean. - */ - table_map used_tables; USER_CONN *user_connect; CHARSET_INFO *db_charset; /* === modified file 'sql/sql_insert.cc' --- a/sql/sql_insert.cc 2011-05-16 20:04:01 +0000 +++ b/sql/sql_insert.cc 2011-06-21 10:03:04 +0000 @@ -629,7 +629,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t lock_type= table_list->lock_type; thd_proc_info(thd, "init"); - thd->used_tables=0; + thd->lex->used_tables=0; values= its++; value_count= values->elements; @@ -777,7 +777,7 @@ bool mysql_insert(THD *thd,TABLE_LIST *t } else { - if (thd->used_tables) // Column used in values() + if (thd->lex->used_tables) // Column used in values() restore_record(table,s->default_values); // Get empty record else { === modified file 'sql/sql_lex.cc' --- a/sql/sql_lex.cc 2011-02-22 21:03:32 +0000 +++ b/sql/sql_lex.cc 2011-06-21 10:03:04 +0000 @@ -358,6 +358,7 @@ void lex_start(THD *thd) lex->server_options.port= -1; lex->is_lex_started= TRUE; + lex->used_tables= 0; DBUG_VOID_RETURN; } === modified file 'sql/sql_lex.h' --- a/sql/sql_lex.h 2011-02-22 21:03:32 +0000 +++ b/sql/sql_lex.h 2011-06-21 10:03:04 +0000 @@ -1828,6 +1828,14 @@ typedef struct st_lex : public Query_tab uint create_select_pos; bool create_select_in_comment; + /* + The set of those tables whose fields are referenced in all subqueries + of the query. + TODO: possibly this it is incorrect to have used tables in THD because + with more than one subquery, it is not clear what does the field mean. + */ + table_map used_tables; + st_lex(); virtual ~st_lex() === modified file 'sql/sql_prepare.cc' --- a/sql/sql_prepare.cc 2011-05-16 20:04:01 +0000 +++ b/sql/sql_prepare.cc 2011-06-21 10:03:04 +0000 @@ -1382,7 +1382,7 @@ static int mysql_test_select(Prepared_st if (open_normal_and_derived_tables(thd, tables, 0)) goto error; - thd->used_tables= 0; // Updated by setup_fields + thd->lex->used_tables= 0; // Updated by setup_fields /* JOIN::prepare calls @@ -1551,7 +1551,7 @@ static bool select_like_stmt_test(Prepar if (specific_prepare && (*specific_prepare)(thd)) DBUG_RETURN(TRUE); - thd->used_tables= 0; // Updated by setup_fields + thd->lex->used_tables= 0; // Updated by setup_fields /* Calls JOIN::prepare */ DBUG_RETURN(lex->unit.prepare(thd, 0, setup_tables_done_option)); === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-04-22 07:20:55 +0000 +++ b/sql/sql_select.cc 2011-06-21 10:03:04 +0000 @@ -406,7 +406,7 @@ fix_inner_refs(THD *thd, List &all if (!ref->fixed && ref->fix_fields(thd, 0)) return TRUE; - thd->used_tables|= item->used_tables(); + thd->lex->used_tables|= item->used_tables(); } return false; } @@ -1632,7 +1632,7 @@ JOIN::optimize() if (exec_tmp_table1->distinct) { - table_map used_tables= thd->used_tables; + table_map used_tables= thd->lex->used_tables; JOIN_TAB *last_join_tab= join_tab+tables-1; do { @@ -2526,7 +2526,7 @@ mysql_select(THD *thd, Item ***rref_poin if (!(join= new JOIN(thd, fields, select_options, result))) DBUG_RETURN(TRUE); thd_proc_info(thd, "init"); - thd->used_tables=0; // Updated by setup_fields + thd->lex->used_tables=0; // Updated by setup_fields err= join->prepare(rref_pointer_array, tables, wild_num, conds, og_num, order, group, having, proc_param, select_lex, unit); @@ -16949,7 +16949,7 @@ static void select_describe(JOIN *join, need_order=0; extra.append(STRING_WITH_LEN("; Using filesort")); } - if (distinct & test_all_bits(used_tables,thd->used_tables)) + if (distinct & test_all_bits(used_tables,thd->lex->used_tables)) extra.append(STRING_WITH_LEN("; Distinct")); for (uint part= 0; part < tab->ref.key_parts; part++) --===============0026405286109000361== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/sergey.glukhov@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: sergey.glukhov@stripped\ # 2htyadqjct4235aw # target_branch: file:///home/gluh/MySQL/mysql-5.1/ # testament_sha1: e490256ac4676109a654b9357ba7c01d1110b408 # timestamp: 2011-06-21 14:03:12 +0400 # source_branch: bzr+ssh://sgluhov@stripped/bzrroot\ # /server/mysql-5.1/ # base_revision_id: sergey.glukhov@stripped\ # ervaj1qz1nrzbar0 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWVPSKBMACVr/gFRwBgBdd/// f///4L////RgEB9e321sdddbRlAAAA0MC0LWqBLYxIBRIFrZlYAiEZEZAGIANDQ0A0AAAaBoAA5h NGRoaGQwjQyGmjQAYjJkAwgGA5hNGRoaGQwjQyGmjQAYjJkAwgGASEiIygBqn6KeEZE8jUbKDI00 yHqDQAA0BFQhNDRqeEBMCMISeTQo9T0I00eU0AAyASSAmhoTI00IASPUbUTT9QmhkNGhiABmk1KE /4OEZUgcWO41bJtzN1XdXX1nb23nj/pmuyxe8gzIBgeMTjbifJHht4ot+geswIrCUIUkYgPgmyEK VkYlmpRiKHRDLk9vcZS48ntS7qYYvIz3m84bdK9INuubGAGTFcyBNPrIMh19TKHY6Xo0dzoxnO1i kqT/5FpW56+AAzA2sHEQcZBP8bH85+2r5hcUn9YwqevoJ/QTMI20NtjbY22JttDfi+NdKQbF8vWc hxWHq5QynLm0mmNFosFpGyyEYzaUYsMDNOx88iUujTaNXeTyehx2ZHTDTlerNaL4cH9+M8ZvnhPS t46t+H63XF8e85tJ+B/zyaHiVn9ywsNRkDh9ge9CTdYQ/Sw+P6jQ0GLYNu4qp1m9XmCoH3QGp8eT fCZjcF5nmoFy3vuIjGbmBfP09LaXuGocyxw8D8YurctIYWegiedrnH4QEdUjGQHT6JJEc1n95/2H M5n9RxwGHjjgoohpeAAgIUon7D4apwKoENcIB6xEwAFbE2+u1ZC0cUq67pLs78fX28D2hFmY+WIG P0iIaaYNy3/M/f1ICjISOf0Mx76fna1cug4HSdg5xdSh/AxjMXgdn19/ckGVB3Q4l4u6CGkDHyW1 A8phITCLfUlvC4XamUGCFw/iiH2APmBvYXA5id9yNx3sHK0jb4LydTOKuMZwVsRb/AUwGAWJ3xXn m5a2Y4fMsuS3M8tQasDQZ0qUVKJqpVEyNooQGwJK+DTYQl4eQn3bQ2HzPpETJkqxCZpvASsCPjrw qsIcmUxamIpi5DvK1Mvr8iEaMFixMBcRE8DkKCxjhhFaIRZCMikgLiqzsiBDbKWFhJWATJcBRViV EaTRlpHFZIWK5zGlbGdhItucoTgTg9CHgUAYIRV9fhYb2kTAqOLy80kzmtjSEnCCu4cq1iWECJZh 7+QfAKIeYiobnZMwFqIhHmtYtZCO4pAGyrvhOrtxLAJkgMZOF9Sz4KynBVjjZtDWTdNtn57c5365 4Ct+4xuJg/21fkCsIqXGLxNjx9m+6txNHrAvLKgRWDEYmN4oJgdA1PTSk1HtdbsNauJYcfiQi7qJ XtMzEbFgbjUREmbdICSbgRJDxvcvwXfI58HO+Sx98gi4ZnGtviETinrVBxEyEDnqjcsjeZWSVXAL LscQWRxTiNw/BnFhfKlgC7sCscCpHY2joGRkQMol0iMSQ5sSRssB5ixI6QlgXoWo1xeYmG4cdWZE 4dCY+J2rYBXqimqJ6uoBBfWupY8VXLMnzDM7dnpJcZuQi2ushzyRfcR3KlBhTHFhOpG99zyZApbE BQkmy2LVGFIUZNUT0uZEdaPJAdFYTIRLAhAxJmxePA+dbANqrljFc1FUqX76vwMjeQmXPmAnymPT S0VgKyScUGlPYBcjpIvLN5eW2snNewnUGvNvCJKRz6iywlzXI1eQ+QCBwZIkRkaLM3EYGjxiY4Zj 3K4h7gPRdav1WQxLfkz+W0mk9pQm6zKTx2uQFswwtLA4ULtBoVHmhM22rE7eAGpdeNWq5FTI68JX YnfKY8zUgKzujihHBcU1pEpfp67gFgSqb2NS0pSZIzIQuwYxOi9V2WrLsXxLmFVzxXNbs8NXjhst VJ5wfqmdrIiorgwCocSZa5PJDx9IveONHdJqHLAL081884EOswwLwgY1eMxFhjeSXVO1kjElzJjQ KHcrifEqePWPXJYsBuWM+PDSZDZCHrQiyWRYaGRePch5K6J8AF76Fy4UfUoXKFU/OJjImrT2KEsB x3WallN141CJ0N0ipMsuWBYIkGXjlXu3+Wnj1VvBXo0xPgTHE5FpMsGIDPmROBwWqwWDteTydbte TkaA4NWzbddC3fdghPbRPmiUhgGUDRSTnPEm84GO4geV6d28U/DDEHGQ7rMgzNxmSCbHIt3Fmyhi Y2mZTe858qXUxMWxfuiUiz4DmzvYTkySePJERwrCAxv7sH0KLiOLCJcWoRC1kImvKpuImcuu0e8w 3l9g44mQ85HEfuKuTl+rhboB8/0CPYg4MLoN3MhrbgjPYgTMwlJDSL447zuCxETGkVTSLya3oyvh V25e+VYIUyTySCvB6/QTYjtOYfXrcDcKIDwXf7Uh6Uw/5E0cqWkbTGjrEctDBDAuc/nEejtElgeL JRxCWj5hewNo+o7hboYNNppDD9hVc4mfyNqjwrCwxUIoGxFMPvSJZNHpTDqoPwW0JhsiYVfMek9N 8Z9wECZMuQyIB+IWKSGCxB+AVCVkKRf+pBJz8iZrgzL/NMoSAJLalSxKKKPcf4MxVAggYyXEKm5G oOTJw4HJEEokR6IjCgmEsBVb3RIJMFwqBvVqVpAUVcG5iKLw9xULVADMlNMDgZXgoLlcERlcDLgm Q9IZTFUVhYhk8sAbAFJKYvtwBPHHiDA5VyNgYFaUdvyAPY7BOkwkRJkaExDGidPdQqQjwAI82R0D J5es7ZpBeQgJ9ZhPgZfVr+Ra/WVGUifIj8iRYH3v7rDBmRSh+zig5VeID6V8xu5GZQyIb194Caww NihBSSLkfjawkfFvzf+k7xM+2fHjj9GyoM+zBY4JgWmSZjUIsubsVVYIfp2qC1pY4BfWpzXrdgDe BuLDqPsWT9JrT5s3B4AaD7vWky8zPkZtJI5lRlOhneKYjmddjJwYyZLDVLAYzTWfgFUgB/Dp3sGV zr5Gnm2sTtd1r7VA9YnExj40SJCGglZHhjmKnQaTcatZA6m82EcjEvMIUneyLjsw0FztAaiA/Q9h Ivv2Zzt7ZASHq2mIzmdo3UwtYBKHASIgXwbBQ8GunRxaCQYl4v7wGbM9GooHB1ihJ6A6hOfb3nj2 HaOKneGIbROwd4sggbgVhAqZbmAxBTIltpTAgXIRQm1EkFh0T1euo8LztPDq99SPfMGo1V6NBEqL 3vdlynTFoceEBzBhjk3eD16uvfa28JbAie7hI9m9e9xZaE73aaboQGDEd46A5hmZV5mdpXxicSp7 0tvR1W3NDP2FyCHwsea94sVy8jTxcULhHiI5YTbJkDtgRNCYs5sO+1UdI2Ooll2KsxVXQ2GAcwzF WZ6SC5uBLj55krOS1AvqoLd0zU74W/7j3jaN7eEHJ01MVO5gDsYJrghCNGDNu+GNVjyYpVnZjL2i UQZQVJZK1KlIQiMyYU7pIyVJkTgq0YDC7VXWdAjvIkTSTHeGi1geByAvXSh5HYbx5aPO1WvOCyKm JbQmRLi2ZQwGPYoDkiBjkpEiZcaJHMDQ+JN7hHs6CQEkph4wAzkibGwzOllPDcyedzNBzNje3N+q CpBV3mZxxfQV5BRqAwtXdwIN4bAPJKXJ3IaCEcgEpCa3OzzdgpP119GSPvhDLjOgSqaaNjjZSiGR Vk569YDAxbwGFKGiAbZe1+p25gvjzDj28lWhjvGir8fy4qbnM6ilBIBgCp3DHUjnyVpOSge3rwmp tRtw0l8BK0bItbB2CZuVD6t8ioTXndrrJMAHspYvBsd215uB8HC8O6G+p0spub3uKtuA7LXUq8Ef Fx9zifVxDpobZIRqFqihQ7IRY9XIgwIKoMxElLwtHZpgiJuIUCBAVid0ph85kE9XvghmEpCZ4WNr HHCPpyYjaXSZ352Cvr16ZD0Z50QvWGf0uLzozTulyiaMLOpoMjrdL2cZyIGPRaBjqxqZVIRX5lRR X5FnwlYq5muKvhCFo7vUHJFgjos52cUzmQmT5QbBRHX+1TpMDzWYHYqAexXg4ZCZgdDWydjz04fB mYyzgRs+FzLW87ukPt3iJGsWHjTYwJb8jvECxHsscfDdO3kvZEL55mSrndp2CY4AJqcsxxeDb20J dISApvfg/e1KUWWAYmt730bXAhyb69bbkA69Uum7C7SB4vNmdmzG+VDwfqv5gOB0NrteCJ3wUTse yzQBR7WBp4+LvYmv+EXc8HY7yzaqlWXBTNBNF9c8ypQkhN4Qhv9JKOSmgDc3LwwWC5AZqIElZ2Uw 9i6K2YmGXQc5t5rlV7HtVIwGRgRzOlZhGHKIYHQrOAh4em00iZh3KzTAmk5Aj1zFyDtPoSGEO+Vi 9O3HB0mlnWEAwiWWNLoRmEz5wdHR0vo0vGt/aW/IpBDe3JJJJJJJJJJJJJKA4UQCqVpFJFJFJFJF JFLVSqjWI4EjhYLgmzohPHi9B/YtFxXopIypxv2KuiRJS4Tc5OW0nw8GTA0c23AVYDuUUhcvK0wx CjxDfKCHp2n5ZGIbBKg3V2YaVu3uqXNrOkOMz0FQESYiYh/f/dwfn/Jj8t0AyAs09F0yPM0lILE9 C1XLyfFlvvqkDHzZnexfMBmcAHpqZPByENzMVWPl0ZvtZPItC5nVe5pemQZszEnYT629joNaEIfF O6KEpBCC5XKBje3K+bz9jMK53E5N2FutfeEyO1k3NZfJwqmZrbambJ7oYmMQOfua2hyM/E1u3K8L lS+yprY6mbzffpJ3zcKJsYTaGmLIVhkAbuDraGTW+5xAaHA3MWjc30tTyZZoCbWHXgzOVrtyu+tw u1yvg5E/+LuSKcKEgp6RQJg= --===============0026405286109000361==--