Bug #1313 EXPLAIN gives wrong number of read records in case of FULL SCAN

• Submitted: 16 Sep 2003 15:18
• Modified: 27 Sep 2008 9:07
• Reporter: Konstantin Osipov (OCA)
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: All
• OS: Any (All)
• Assigned to: Konstantin Osipov
• CPU Architecture: Any

[16 Sep 2003 15:18] Konstantin Osipov

Description:
CREATE TABLE t1 (
  a char(5) NOT NULL,
  b char(4) NOT NULL,
  KEY (a),
  KEY (b)
);

INSERT INTO t1 VALUES ('A','B'),('b','A'),('C','c'),('D','E'),('a','a');
explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B;

explain prints:*************************** 1. row ***************************
        table: t1
         type: ALL
possible_keys: a
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: 
*************************** 2. row ***************************
        table: t2
         type: ALL
possible_keys: b
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4

Number of rows for t2 should be equal count(*) from t2 (= 5)

How to repeat:
see description

Suggested fix:
Fix explain support

[27 Sep 2008 9:07] Konstantin Osipov

Somehow this got fixed.

mysql> explain select t1.*,t2.* from t1,t1 as t2 where t1.A=t2.B\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: a
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ALL
possible_keys: b
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where; Using join buffer
2 rows in set (0.00 sec)

This is 6.0