Bug #71981 | Strange result on explain | ||
---|---|---|---|
Submitted: | 8 Mar 2014 20:24 | Modified: | 13 Mar 2014 11:15 |
Reporter: | Daniël van Eeden (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
Version: | 5.6.16 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | explain |
[8 Mar 2014 20:24]
Daniël van Eeden
[8 Mar 2014 20:28]
Daniël van Eeden
From the JSON output: "materialized_from_subquery": { "using_temporary_table": true, "dependent": false, "cacheable": true, "query_block": { "select_id": 3, "table": { "table_name": "u14", "access_type": "ALL", "rows": -9223372036854775808, "filtered": 100, "materialized_from_subquery": { "using_temporary_table": true, "dependent": false, "cacheable": true, "query_block": { "select_id": 5, "table": { "table_name": "u13", "access_type": "ALL", "rows": -9223372036854775808, "filtered": 100,
[10 Mar 2014 10:19]
MySQL Verification Team
Hello Daniel, Thank you for the bug report. Verified as described. Thanks, Umesh
[10 Mar 2014 10:21]
MySQL Verification Team
// This can be easily triggered with >8 users NULL | | 3 | DERIVED | <derived5> | ALL | NULL | NULL | NULL | NULL | -9223372036854775808 | NULL | | 5 | DERIVED | <derived6> | ALL | NULL | NULL | NULL | NULL | -9223372036854775808 | NULL | | 6 | DERIVED | <derived7> | ALL | NULL | NULL | NULL | NULL | -9223372036854775808 | NULL | | 7 | DERIVED | <derived8> | ALL | NULL | NULL | NULL | NULL | -9223372036854775808 | NULL | | 8 | DERIVED | <derived9> | ALL | NULL | NULL | NULL | NULL | -9223372036854775808 | NULL | | 9 | DERIVED | <derived10> | ALL | NULL | NULL | NULL | NULL | -9223372036854775808 | NULL | | 10 | DERIVED | <derived11> | ALL | NULL | NULL | NULL | NULL | -9223372036854775808 | NULL | | 11 | DERIVED | <derived12> | ALL | NULL | NULL | NULL | NULL | -9223372036854775808 | NULL | | 12 | DERIVED | <derived13> | ALL | NULL | NULL | NULL | NULL | -9223372036854775808 | NULL | | 13 | DERIVED | <derived14> | ALL | NULL | NULL | NULL | NULL | -9223372036854775808 | NULL | | 14 | DERIVED | <derived15> | ALL | NULL | NULL | NULL | NULL | -9223372036854775808 | NULL | | 15 | DERIVED | <derived16> | ALL | NULL | NULL | NULL | NULL | -9223372036854775808 | NULL | | 16 | DERIVED | <derived17> | ALL | NULL | NULL | NULL | NULL | -9223372036854775808 | NULL | | 17 | DERIVED | <derived18> | ALL | NULL | NULL | NULL | NULL | -9223372036854775808 | Using temporary; Using filesort | 47 rows in set (0.00 sec) mysql> select user from mysql.user; +------+ | user | +------+ | | | root | | root | | root | | root | | root | | | | root | +------+ 8 rows in set (0.00 sec) mysql> select user,host from mysql.user; +------+--------------+ | user | host | +------+--------------+ | | | | root | | | root | % | | root | 127.0.0.1 | | root | ::1 | | root | cluster-repo | | | localhost | | root | localhost | +------+--------------+ 8 rows in set (0.00 sec)
[13 Mar 2014 9:12]
Hartmut Holzgraefe
I can't reproduce this, using a self compiled 64bit binary (no special CMake options used besides -DCMAKE_INSTALL_PREFIX) *************************** 18. row *************************** id: 11 select_type: DERIVED table: <derived12> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 558545864083283968 Extra: NULL
[13 Mar 2014 9:13]
Hartmut Holzgraefe
Debian Jessie/Sid, uname -a: Linux hartmut-server 3.11-2-amd64 #1 SMP Debian 3.11.10-1 (2013-12-04) x86_64 GNU/Linux
[13 Mar 2014 10:20]
Daniël van Eeden
I've binlogs and PS enabled: log-bin performance-schema-instrument='%=on' performance-schema-consumer-events_stages_current=on performance-schema-consumer-events_stages_history=on performance-schema-consumer-events_stages_history_long=on performance-schema-consumer-events_statements_history=on performance-schema-consumer-events_statements_history_long=on performance-schema-consumer-events_waits_current=on performance-schema-consumer-events_waits_history=on performance-schema-consumer-events_waits_history_long=on But I can reproduce it with these options removed from the config. I've compiled on Ubuntu 13.10 (saucy). gcc version 4.8.1 (Ubuntu/Linaro 4.8.1-10ubuntu9) Linux kernel 3.11.0-18-generic
[13 Mar 2014 10:35]
Daniël van Eeden
I can reproduce it with a 5.6.15 tarball binary release for x86_64 and mysql sandbox. Also on Ubuntu 13.10. mysqld --version reports: Ver 5.6.15 for linux-glibc2.5 on x86_64 (MySQL Community Server (GPL))
[13 Mar 2014 10:39]
Daniël van Eeden
Trace from mysql 5.6.15 debug
Attachment: mysql_trace.bz2 (application/x-bzip, text), 43.75 KiB.
[13 Mar 2014 10:53]
Daniël van Eeden
optimizer trace output
Attachment: opt_trace.bz2 (application/x-bzip, text), 8.48 KiB.
[13 Mar 2014 10:55]
Daniël van Eeden
From the optimizer trace: "rows": 9223372036854775808, "cost": 461168601842738816
[13 Mar 2014 11:15]
Daniël van Eeden
add explain tag