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:
None 
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
Description:
A strange number of rows is returned for a explain output.

*************************** 18. row ***************************
           id: 11
  select_type: DERIVED
        table: <derived12>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: -9223372036854775808
        Extra: NULL

I've set the security vulnerability flag as I don't known if this can't be abused or not. Please remove this flag if possible.

How to repeat:
use the query from http://bugs.mysql.com/file.php?id=21148&bug_id=71973
[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] Umesh Shastry
Hello Daniel,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[10 Mar 2014 10:21] Umesh Shastry
// 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