如何从头到脚彻底解决一个MySQL Bug？华为云数据库高级专家带你看

select  
  subq_0.c2 as c0
from 
  (select  
        ref_6.C_STATE asc0, 
        case whenref_6.C_PHONE is not NULL then ref_5.C_ID else ref_5.C_ID end
           asc1, 
        floor(
          ref_3.c_id)as c2
      from 
       sqltester.t0_hash_partition_p1_view as ref_0
             right join sqltester.t4 as ref_1
             on (EXISTS (
                 select  
                     ref_1.c_middle as c0
                   from 
                     sqltester.t1 as ref_2
                   where ((false) 
                       and ((true) 
                         or (true))) 
                     or (false)
                   ))
            innerjoin sqltester.t0_range_key_subpartition_sub_view as ref_3
            on(EXISTS (
               select  
                   ref_0.c_credit as c0, 
                   ref_1.c_street_1 as c1, 
                   ref_4.c_credit_lim as c2, 
                   ref_3.c_credit as c3
                 from 
                   sqltester.t0_hash_partition_p1 as ref_4
                 where true
                 ))
          left joinsqltester.t10 as ref_5
            innerjoin sqltester.t11 as ref_6
            on(true)
          on (((pi() isnot NULL)) 
             and (false))
      where (((ref_5.C_D_ID isnot NULL) 
            or(ref_3.c_middle is not NULL)) 
      )) as subq_0
where (EXISTS (
          select  
             subq_0.c0 as c0, 
             pi() as c1, 
             ref_11.c_street_1 as c2, 
             ref_11.c_discount as c3, 
             pi() as c4
           from 
             sqltester.t0_partition_sub_view_mixed_001 as ref_11))
group by 1
order by 1;
复制

SELECT count(*)
FROM
  (SELECT 1
   FROM sqltester.t4 AS ref_1
   INNER JOIN sqltester.t4 AS ref_3 ON (EXISTS
                                         (SELECT 1
                                           FROMsqltester.t4 AS ref_4
                                           WHERE TRUE ))
   LEFT JOIN sqltester.t10 AS ref_5 ON (FALSE)
   WHERE (((ref_5.C_D_ID IS NOT NULL)
           OR (ref_3.c_middle IS NOT NULL))))AS subq_0
 
执行计划如下：
-> Aggregate: count(0) (cost=2.75 rows=0)
   -> Filter: ((ref_5.C_D_ID is not null) or(ref_3.c_middle is null)) (cost=2.75 rows=0)
       -> Inner hash join(no condition) (cost=2.75 rows=0)
          -> Index scan on ref_3 using ndx_c_middle (cost=0.13 rows=50)
          -> Hash
              -> Inner hash join (no condition) (cost=1.50 rows=0)
                  -> Index scan on ref_1 using ndx_c_id (cost=6.25 rows=50)
                  -> Hash
                      -> Left hash join (no condition) (cost=0.25 rows=0)
                          -> Limit: 1 row(s) (cost=312.50 rows=1)
                             ->Index scan on ref_4 using ndx_c_id (cost=312.50 rows=50)
                          -> Hash
                              -> Zero rows (Impossible filter) (cost=0.00..0.00 rows=0)
复制

SELECT count(*)
FROM
  (SELECT 1
   FROM sqltester.t4 AS ref_1
   INNER JOIN sqltester.t4 AS ref_3 ON (EXISTS
                                          (SELECT 1
                                           FROM sqltester.t4 AS ref_4
                                           WHERE TRUE ))
   LEFT JOIN sqltester.t10 AS ref_5 ON (FALSE)
   WHERE (((ref_5.C_D_ID IS NOT NULL) or(ref_3.c_middle IS NOT NULL))))AS subq_0;
+
+
| count(*) |
+
+
|     2500 |
+
+
1 row in set (0.00 sec)
复制

SELECT count(*)
FROM
  (SELECT 1
   FROM sqltester.t4 AS ref_1
   INNER JOIN sqltester.t4 AS ref_3 ON (EXISTS
                                          (SELECT 1
                                           FROM sqltester.t4 AS ref_4
                                           WHERE TRUE ))
   LEFT JOIN sqltester.t10 AS ref_5 ON (FALSE)
   WHERE (((ref_5.C_D_ID IS NOT NULL))))assubq_0;
+
+
| count(*) |
+
+
|     2500 |
+
+
1 row in set (0.01 sec)
复制

Breakpoint 1, ZeroRowsIterator::SetNullRowFlag(this=0x7f92a413d510, is_null_row=false)
   at mywork/mysql-sql/sql/basic_row_iterators.h:398
398          assert(m_child_iterator != nullptr);
(gdb) n
399          m_child_iterator->SetNullRowFlag(is_null_row);
(gdb) s
std::unique_ptr<RowIterator,Destroy_only<RowIterator> >::operator-> (this=0x7f92a413d520)
   at/opt/simon/taurus/mysql-root/src/tools/gcc-9.3.0/include/c++/9.3.0/bits/unique_ptr.h:355
355               returnget();
(gdb) fin
Run till exit from #0 std::unique_ptr<RowIterator,Destroy_only<RowIterator> >::operator-> (
   this=0x7f92a413d520)
   at/opt/simon/taurus/mysql-root/src/tools/gcc-9.3.0/include/c++/9.3.0/bits/unique_ptr.h:355
ZeroRowsIterator::SetNullRowFlag (this=0x7f92a413d510,is_null_row=false)
  at/home/simon/mywork/mysql-sql/sql/basic_row_iterators.h:399
399          m_child_iterator->SetNullRowFlag(is_null_row);
Value returned is $1 = (RowIterator *) 0x7f92a413d4d0
(gdb) s
TableRowIterator::SetNullRowFlag (this=0x7f92a413d4d0,is_null_row=false)
   at/home/simon/mywork/mysql-sql/sql/records.cc:229
229          if(is_null_row) {
(gdb) n
232          m_table->reset_null_row();
(gdb)
234        }
复制