From: Jorgen Loland Date: August 11 2011 5:52am Subject: bzr push into mysql-trunk branch (jorgen.loland:3354 to 3355) Bug#12798270 List-Archive: http://lists.mysql.com/commits/140591 X-Bug: 12798270 Message-Id: <20110811055239.BBFA03CE@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3355 Jorgen Loland 2011-08-11 BUG#12798270 - ASSERTION `!TAB->SORTED' FAILED IN JOIN_READ_KEY2 An ASSERT that ordering is not requested on a table when eq_ref is used to access it was added by bug 11764737. The rationale is that eq_ref will only return a single row, so ordering becomes meaningless. In this bug, the ASSERT triggered because the optimizer did request ordering on a table accessed through eq_ref. This request for ordered output has been there since long but surfaced due to the newly added ASSERT. The fix is in make_join_readinfo() and is to not require an index to return rows in sorted order when eq_ref access method is chosen. @ mysql-test/r/group_by.result Added regression test for BUG#12798270 @ mysql-test/t/group_by.test Added regression test for BUG#12798270 @ sql/sql_select.cc In make_join_readinfo(): Do not require an index to return rows in sorted order if eq_ref is used. modified: mysql-test/r/group_by.result mysql-test/t/group_by.test sql/sql_select.cc 3354 Marc Alff 2011-08-11 [merge] Merge mysql-trunk-stage --> mysql-trunk WL#5378 PERFORMANCE SCHEMA SUMMARY BY USER / HOST added: mysql-test/suite/perfschema/include/connection_cleanup.inc mysql-test/suite/perfschema/include/connection_load.inc mysql-test/suite/perfschema/include/connection_setup.inc mysql-test/suite/perfschema/include/no_protocol.inc mysql-test/suite/perfschema/include/wait_for_pfs_thread_count.inc mysql-test/suite/perfschema/r/connection.result mysql-test/suite/perfschema/r/connection_3a.result mysql-test/suite/perfschema/r/connection_3a_3u.result mysql-test/suite/perfschema/r/connection_3u.result mysql-test/suite/perfschema/r/ddl_accounts.result mysql-test/suite/perfschema/r/ddl_esgs_by_account_by_event_name.result mysql-test/suite/perfschema/r/ddl_esgs_by_host_by_event_name.result mysql-test/suite/perfschema/r/ddl_esgs_by_user_by_event_name.result mysql-test/suite/perfschema/r/ddl_esms_by_account_by_event_name.result mysql-test/suite/perfschema/r/ddl_esms_by_host_by_event_name.result mysql-test/suite/perfschema/r/ddl_esms_by_user_by_event_name.result mysql-test/suite/perfschema/r/ddl_ews_by_account_by_event_name.result mysql-test/suite/perfschema/r/ddl_ews_by_host_by_event_name.result mysql-test/suite/perfschema/r/ddl_ews_by_user_by_event_name.result mysql-test/suite/perfschema/r/ddl_hosts.result mysql-test/suite/perfschema/r/ddl_users.result mysql-test/suite/perfschema/r/dml_accounts.result mysql-test/suite/perfschema/r/dml_esgs_by_account_by_event_name.result mysql-test/suite/perfschema/r/dml_esgs_by_host_by_event_name.result mysql-test/suite/perfschema/r/dml_esgs_by_user_by_event_name.result mysql-test/suite/perfschema/r/dml_esms_by_account_by_event_name.result mysql-test/suite/perfschema/r/dml_esms_by_host_by_event_name.result mysql-test/suite/perfschema/r/dml_esms_by_user_by_event_name.result mysql-test/suite/perfschema/r/dml_ews_by_account_by_event_name.result mysql-test/suite/perfschema/r/dml_ews_by_host_by_event_name.result mysql-test/suite/perfschema/r/dml_ews_by_user_by_event_name.result mysql-test/suite/perfschema/r/dml_hosts.result mysql-test/suite/perfschema/r/dml_users.result mysql-test/suite/perfschema/r/event_aggregate_no_a.result mysql-test/suite/perfschema/r/event_aggregate_no_a_no_h.result mysql-test/suite/perfschema/r/event_aggregate_no_a_no_u.result mysql-test/suite/perfschema/r/event_aggregate_no_a_no_u_no_h.result mysql-test/suite/perfschema/r/event_aggregate_no_h.result mysql-test/suite/perfschema/r/event_aggregate_no_u.result mysql-test/suite/perfschema/r/event_aggregate_no_u_no_h.result mysql-test/suite/perfschema/r/pfs_upgrade_lc0.result.THIS mysql-test/suite/perfschema/r/pfs_upgrade_lc1.result.THIS mysql-test/suite/perfschema/r/pfs_upgrade_lc2.result.THIS mysql-test/suite/perfschema/r/schema_lc0.result.THIS mysql-test/suite/perfschema/r/schema_lc1.result.THIS mysql-test/suite/perfschema/r/schema_lc2.result.THIS mysql-test/suite/perfschema/r/start_server_no_account.result mysql-test/suite/perfschema/r/start_server_no_host.result mysql-test/suite/perfschema/r/start_server_no_user.result mysql-test/suite/perfschema/r/start_server_no_waits_history.result mysql-test/suite/perfschema/r/start_server_no_waits_history_long.result mysql-test/suite/perfschema/t/connection.test mysql-test/suite/perfschema/t/connection_3a-master.opt mysql-test/suite/perfschema/t/connection_3a.test mysql-test/suite/perfschema/t/connection_3a_3u-master.opt mysql-test/suite/perfschema/t/connection_3a_3u.test mysql-test/suite/perfschema/t/connection_3u-master.opt mysql-test/suite/perfschema/t/connection_3u.test mysql-test/suite/perfschema/t/ddl_accounts.test mysql-test/suite/perfschema/t/ddl_esgs_by_account_by_event_name.test mysql-test/suite/perfschema/t/ddl_esgs_by_host_by_event_name.test mysql-test/suite/perfschema/t/ddl_esgs_by_user_by_event_name.test mysql-test/suite/perfschema/t/ddl_esms_by_account_by_event_name.test mysql-test/suite/perfschema/t/ddl_esms_by_host_by_event_name.test mysql-test/suite/perfschema/t/ddl_esms_by_user_by_event_name.test mysql-test/suite/perfschema/t/ddl_ews_by_account_by_event_name.test mysql-test/suite/perfschema/t/ddl_ews_by_host_by_event_name.test mysql-test/suite/perfschema/t/ddl_ews_by_user_by_event_name.test mysql-test/suite/perfschema/t/ddl_hosts.test mysql-test/suite/perfschema/t/ddl_users.test mysql-test/suite/perfschema/t/dml_accounts.test mysql-test/suite/perfschema/t/dml_esgs_by_account_by_event_name.test mysql-test/suite/perfschema/t/dml_esgs_by_host_by_event_name.test mysql-test/suite/perfschema/t/dml_esgs_by_user_by_event_name.test mysql-test/suite/perfschema/t/dml_esms_by_account_by_event_name.test mysql-test/suite/perfschema/t/dml_esms_by_host_by_event_name.test mysql-test/suite/perfschema/t/dml_esms_by_user_by_event_name.test mysql-test/suite/perfschema/t/dml_ews_by_account_by_event_name.test mysql-test/suite/perfschema/t/dml_ews_by_host_by_event_name.test mysql-test/suite/perfschema/t/dml_ews_by_user_by_event_name.test mysql-test/suite/perfschema/t/dml_hosts.test mysql-test/suite/perfschema/t/dml_users.test mysql-test/suite/perfschema/t/event_aggregate_no_a-master.opt mysql-test/suite/perfschema/t/event_aggregate_no_a.test mysql-test/suite/perfschema/t/event_aggregate_no_a_no_h-master.opt mysql-test/suite/perfschema/t/event_aggregate_no_a_no_h.test mysql-test/suite/perfschema/t/event_aggregate_no_a_no_u-master.opt mysql-test/suite/perfschema/t/event_aggregate_no_a_no_u.test mysql-test/suite/perfschema/t/event_aggregate_no_a_no_u_no_h-master.opt mysql-test/suite/perfschema/t/event_aggregate_no_a_no_u_no_h.test mysql-test/suite/perfschema/t/event_aggregate_no_h-master.opt mysql-test/suite/perfschema/t/event_aggregate_no_h.test mysql-test/suite/perfschema/t/event_aggregate_no_u-master.opt mysql-test/suite/perfschema/t/event_aggregate_no_u.test mysql-test/suite/perfschema/t/event_aggregate_no_u_no_h-master.opt mysql-test/suite/perfschema/t/event_aggregate_no_u_no_h.test mysql-test/suite/perfschema/t/start_server_no_account-master.opt mysql-test/suite/perfschema/t/start_server_no_account.test mysql-test/suite/perfschema/t/start_server_no_host-master.opt mysql-test/suite/perfschema/t/start_server_no_host.test mysql-test/suite/perfschema/t/start_server_no_user-master.opt mysql-test/suite/perfschema/t/start_server_no_user.test mysql-test/suite/perfschema/t/start_server_no_waits_history-master.opt mysql-test/suite/perfschema/t/start_server_no_waits_history.test mysql-test/suite/perfschema/t/start_server_no_waits_history_long-master.opt mysql-test/suite/perfschema/t/start_server_no_waits_history_long.test mysql-test/suite/sys_vars/r/pfs_accounts_size_basic.result mysql-test/suite/sys_vars/r/pfs_hosts_size_basic.result mysql-test/suite/sys_vars/r/pfs_users_size_basic.result mysql-test/suite/sys_vars/t/pfs_accounts_size_basic-master.opt mysql-test/suite/sys_vars/t/pfs_accounts_size_basic.test mysql-test/suite/sys_vars/t/pfs_hosts_size_basic-master.opt mysql-test/suite/sys_vars/t/pfs_hosts_size_basic.test mysql-test/suite/sys_vars/t/pfs_users_size_basic-master.opt mysql-test/suite/sys_vars/t/pfs_users_size_basic.test storage/perfschema/cursor_by_account.cc storage/perfschema/cursor_by_account.h storage/perfschema/cursor_by_host.cc storage/perfschema/cursor_by_host.h storage/perfschema/cursor_by_thread.cc storage/perfschema/cursor_by_thread.h storage/perfschema/cursor_by_user.cc storage/perfschema/cursor_by_user.h storage/perfschema/pfs_account.cc storage/perfschema/pfs_account.h storage/perfschema/pfs_host.cc storage/perfschema/pfs_host.h storage/perfschema/pfs_user.cc storage/perfschema/pfs_user.h storage/perfschema/table_accounts.cc storage/perfschema/table_accounts.h storage/perfschema/table_esgs_by_account_by_event_name.cc storage/perfschema/table_esgs_by_account_by_event_name.h storage/perfschema/table_esgs_by_host_by_event_name.cc storage/perfschema/table_esgs_by_host_by_event_name.h storage/perfschema/table_esgs_by_user_by_event_name.cc storage/perfschema/table_esgs_by_user_by_event_name.h storage/perfschema/table_esms_by_account_by_event_name.cc storage/perfschema/table_esms_by_account_by_event_name.h storage/perfschema/table_esms_by_host_by_event_name.cc storage/perfschema/table_esms_by_host_by_event_name.h storage/perfschema/table_esms_by_user_by_event_name.cc storage/perfschema/table_esms_by_user_by_event_name.h storage/perfschema/table_ews_by_account_by_event_name.cc storage/perfschema/table_ews_by_account_by_event_name.h storage/perfschema/table_ews_by_host_by_event_name.cc storage/perfschema/table_ews_by_host_by_event_name.h storage/perfschema/table_ews_by_user_by_event_name.cc storage/perfschema/table_ews_by_user_by_event_name.h storage/perfschema/table_hosts.cc storage/perfschema/table_hosts.h storage/perfschema/table_users.cc storage/perfschema/table_users.h storage/perfschema/unittest/pfs_account-oom-t.cc storage/perfschema/unittest/pfs_host-oom-t.cc storage/perfschema/unittest/pfs_user-oom-t.cc modified: mysql-test/r/information_schema.result mysql-test/r/mysqld--help-notwin.result mysql-test/r/mysqld--help-win.result mysql-test/suite/perfschema/include/event_aggregate_cleanup.inc mysql-test/suite/perfschema/include/event_aggregate_load.inc mysql-test/suite/perfschema/include/event_aggregate_setup.inc mysql-test/suite/perfschema/include/schema.inc mysql-test/suite/perfschema/include/stage_setup.inc mysql-test/suite/perfschema/include/start_server_common.inc mysql-test/suite/perfschema/include/table_aggregate_cleanup.inc mysql-test/suite/perfschema/include/table_aggregate_load.inc mysql-test/suite/perfschema/include/table_aggregate_setup.inc mysql-test/suite/perfschema/r/checksum.result mysql-test/suite/perfschema/r/csv_table_io.result mysql-test/suite/perfschema/r/event_aggregate.result mysql-test/suite/perfschema/r/indexed_table_io.result mysql-test/suite/perfschema/r/information_schema.result mysql-test/suite/perfschema/r/innodb_table_io.result mysql-test/suite/perfschema/r/memory_table_io.result mysql-test/suite/perfschema/r/merge_table_io.result mysql-test/suite/perfschema/r/multi_table_io.result mysql-test/suite/perfschema/r/myisam_file_io.result mysql-test/suite/perfschema/r/myisam_table_io.result mysql-test/suite/perfschema/r/ortho_iter.result mysql-test/suite/perfschema/r/part_table_io.result mysql-test/suite/perfschema/r/pfs_upgrade.result mysql-test/suite/perfschema/r/privilege_table_io.result mysql-test/suite/perfschema/r/rollback_table_io.result mysql-test/suite/perfschema/r/schema.result mysql-test/suite/perfschema/r/start_server_innodb.result mysql-test/suite/perfschema/r/start_server_no_cond_class.result mysql-test/suite/perfschema/r/start_server_no_cond_inst.result mysql-test/suite/perfschema/r/start_server_no_file_class.result mysql-test/suite/perfschema/r/start_server_no_file_inst.result mysql-test/suite/perfschema/r/start_server_no_mutex_class.result mysql-test/suite/perfschema/r/start_server_no_mutex_inst.result mysql-test/suite/perfschema/r/start_server_no_rwlock_class.result mysql-test/suite/perfschema/r/start_server_no_rwlock_inst.result mysql-test/suite/perfschema/r/start_server_no_setup_actors.result mysql-test/suite/perfschema/r/start_server_no_setup_objects.result mysql-test/suite/perfschema/r/start_server_no_stage_class.result mysql-test/suite/perfschema/r/start_server_no_stages_history.result mysql-test/suite/perfschema/r/start_server_no_stages_history_long.result mysql-test/suite/perfschema/r/start_server_no_statement_class.result mysql-test/suite/perfschema/r/start_server_no_statements_history.result mysql-test/suite/perfschema/r/start_server_no_statements_history_long.result mysql-test/suite/perfschema/r/start_server_no_table_hdl.result mysql-test/suite/perfschema/r/start_server_no_table_inst.result mysql-test/suite/perfschema/r/start_server_no_thread_class.result mysql-test/suite/perfschema/r/start_server_no_thread_inst.result mysql-test/suite/perfschema/r/start_server_nothing.result mysql-test/suite/perfschema/r/start_server_off.result mysql-test/suite/perfschema/r/start_server_on.result mysql-test/suite/perfschema/r/table_aggregate_global_2u_2t.result mysql-test/suite/perfschema/r/table_aggregate_global_2u_3t.result mysql-test/suite/perfschema/r/table_aggregate_global_4u_2t.result mysql-test/suite/perfschema/r/table_aggregate_global_4u_3t.result mysql-test/suite/perfschema/r/table_aggregate_hist_2u_2t.result mysql-test/suite/perfschema/r/table_aggregate_hist_2u_3t.result mysql-test/suite/perfschema/r/table_aggregate_hist_4u_2t.result mysql-test/suite/perfschema/r/table_aggregate_hist_4u_3t.result mysql-test/suite/perfschema/r/table_aggregate_off.result mysql-test/suite/perfschema/r/table_aggregate_thread_2u_2t.result mysql-test/suite/perfschema/r/table_aggregate_thread_2u_3t.result mysql-test/suite/perfschema/r/table_aggregate_thread_4u_2t.result mysql-test/suite/perfschema/r/table_aggregate_thread_4u_3t.result mysql-test/suite/perfschema/r/table_io_aggregate_global_2u_2t.result mysql-test/suite/perfschema/r/table_io_aggregate_global_2u_3t.result mysql-test/suite/perfschema/r/table_io_aggregate_global_4u_2t.result mysql-test/suite/perfschema/r/table_io_aggregate_global_4u_3t.result mysql-test/suite/perfschema/r/table_io_aggregate_hist_2u_2t.result mysql-test/suite/perfschema/r/table_io_aggregate_hist_2u_3t.result mysql-test/suite/perfschema/r/table_io_aggregate_hist_4u_2t.result mysql-test/suite/perfschema/r/table_io_aggregate_hist_4u_3t.result mysql-test/suite/perfschema/r/table_io_aggregate_thread_2u_2t.result mysql-test/suite/perfschema/r/table_io_aggregate_thread_2u_3t.result mysql-test/suite/perfschema/r/table_io_aggregate_thread_4u_2t.result mysql-test/suite/perfschema/r/table_io_aggregate_thread_4u_3t.result mysql-test/suite/perfschema/r/table_lock_aggregate_global_2u_2t.result mysql-test/suite/perfschema/r/table_lock_aggregate_global_2u_3t.result mysql-test/suite/perfschema/r/table_lock_aggregate_global_4u_2t.result mysql-test/suite/perfschema/r/table_lock_aggregate_global_4u_3t.result mysql-test/suite/perfschema/r/table_lock_aggregate_hist_2u_2t.result mysql-test/suite/perfschema/r/table_lock_aggregate_hist_2u_3t.result mysql-test/suite/perfschema/r/table_lock_aggregate_hist_4u_2t.result mysql-test/suite/perfschema/r/table_lock_aggregate_hist_4u_3t.result mysql-test/suite/perfschema/r/table_lock_aggregate_thread_2u_2t.result mysql-test/suite/perfschema/r/table_lock_aggregate_thread_2u_3t.result mysql-test/suite/perfschema/r/table_lock_aggregate_thread_4u_2t.result mysql-test/suite/perfschema/r/table_lock_aggregate_thread_4u_3t.result mysql-test/suite/perfschema/r/table_schema.result mysql-test/suite/perfschema/r/temp_table_io.result mysql-test/suite/perfschema/r/trigger_table_io.result mysql-test/suite/perfschema/r/view_table_io.result mysql-test/suite/perfschema/t/checksum.test mysql-test/suite/perfschema/t/start_server_nothing-master.opt mysql-test/suite/perfschema/t/start_server_nothing.test mysql-test/t/information_schema.test mysys/my_thr_init.c scripts/mysql_system_tables.sql sql/sys_vars.cc storage/perfschema/CMakeLists.txt storage/perfschema/ha_perfschema.cc storage/perfschema/pfs.cc storage/perfschema/pfs_engine_table.cc storage/perfschema/pfs_events_stages.cc storage/perfschema/pfs_events_stages.h storage/perfschema/pfs_events_statements.cc storage/perfschema/pfs_events_statements.h storage/perfschema/pfs_events_waits.cc storage/perfschema/pfs_events_waits.h storage/perfschema/pfs_instr.cc storage/perfschema/pfs_instr.h storage/perfschema/pfs_instr_class.cc storage/perfschema/pfs_instr_class.h storage/perfschema/pfs_server.cc storage/perfschema/pfs_server.h storage/perfschema/pfs_setup_actor.cc storage/perfschema/pfs_setup_object.cc storage/perfschema/pfs_stat.h storage/perfschema/pfs_visitor.cc storage/perfschema/pfs_visitor.h storage/perfschema/table_esgs_global_by_event_name.cc storage/perfschema/table_esms_global_by_event_name.cc storage/perfschema/table_helper.cc storage/perfschema/table_helper.h storage/perfschema/table_threads.cc storage/perfschema/table_threads.h storage/perfschema/unittest/CMakeLists.txt storage/perfschema/unittest/pfs-t.cc storage/perfschema/unittest/pfs_instr-oom-t.cc storage/perfschema/unittest/pfs_instr-t.cc storage/perfschema/unittest/pfs_instr_class-oom-t.cc storage/perfschema/unittest/stub_pfs_global.h === modified file 'mysql-test/r/group_by.result' --- a/mysql-test/r/group_by.result 2011-07-22 07:53:39 +0000 +++ b/mysql-test/r/group_by.result 2011-08-11 05:52:13 +0000 @@ -1987,3 +1987,18 @@ EXECUTE stmt; DEALLOCATE PREPARE stmt; DROP TABLE t1, t2; # End of Bug #11766429 +# +# Bug#12798270: ASSERTION `!TAB->SORTED' FAILED IN JOIN_READ_KEY2 +# +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1); +CREATE TABLE t2 (pk int PRIMARY KEY); +INSERT INTO t2 VALUES (10); +CREATE VIEW v1 AS SELECT t2.pk FROM t2; +SELECT v1.pk +FROM t1 LEFT JOIN v1 ON t1.i = v1.pk +GROUP BY v1.pk; +pk +DROP VIEW v1; +DROP TABLE t1,t2; +# End of Bug#12798270 === modified file 'mysql-test/t/group_by.test' --- a/mysql-test/t/group_by.test 2011-06-10 09:52:57 +0000 +++ b/mysql-test/t/group_by.test 2011-08-11 05:52:13 +0000 @@ -1382,3 +1382,24 @@ DEALLOCATE PREPARE stmt; DROP TABLE t1, t2; --echo # End of Bug #11766429 + +--echo # +--echo # Bug#12798270: ASSERTION `!TAB->SORTED' FAILED IN JOIN_READ_KEY2 +--echo # + +CREATE TABLE t1 (i int); +INSERT INTO t1 VALUES (1); + +CREATE TABLE t2 (pk int PRIMARY KEY); +INSERT INTO t2 VALUES (10); + +CREATE VIEW v1 AS SELECT t2.pk FROM t2; + +SELECT v1.pk +FROM t1 LEFT JOIN v1 ON t1.i = v1.pk +GROUP BY v1.pk; + +DROP VIEW v1; +DROP TABLE t1,t2; + +--echo # End of Bug#12798270 === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2011-08-05 10:26:13 +0000 +++ b/sql/sql_select.cc 2011-08-11 05:52:13 +0000 @@ -12185,7 +12185,11 @@ make_join_readinfo(JOIN *join, ulonglong tab->read_record.unlock_row= rr_unlock_row; tab->next_select=sub_select; /* normal select */ tab->cache_idx_cond= 0; - tab->sorted= sorted; + /* + For eq_ref there is at most one join match for each row from + previous tables so ordering is not useful. + */ + tab->sorted= (tab->type != JT_EQ_REF) ? sorted : false; sorted= false; // only first must be sorted table->status= STATUS_GARBAGE | STATUS_NOT_FOUND; pick_table_access_method (tab); No bundle (reason: useless for push emails).