Bug #81664 execute same sql return diffent result. one sql return wrong result.
Submitted: 1 Jun 2016 3:47 Modified: 7 Jun 2016 6:53
Reporter: Seven Wang Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.7.10-log OS:CentOS (CentOS release 6.5 (Final))
Assigned to: CPU Architecture:Any
Tags: wrong result,same sql,diffent result

[1 Jun 2016 3:47] Seven Wang
Description:
select tmp.sclasscode,tmp.sname,r.sCode,r.sName from api.bs_room r
    -> left join
    -> (
    ->         SELECT
    ->                 l.*,c.sName,c.lessonAuditState,c.changeState AS classChangeState,c.nState,c.nAudit
    ->                 cAudit
    ->         FROM
    ->                 bs_class c
    ->         JOIN
    ->                 bs_lesson l
    ->         ON
    ->                 c.nSchoolId=l.nSchoolId AND c.sCode=l.sClassCode
    ->         WHERE
    ->         c.nschoolId=1
    ->         AND l.SectBegin BETWEEN '2016-06-03 00:00:00' AND '2016-06-03 23:59:59'
    ->         AND c.nAudit!=2
    ->         AND c.nState!=3
    -> )tmp on r.nSchoolId=tmp.nSchoolId and r.sCode=tmp.sroomcode
    -> where r.nSchoolId=1 and r.sCode in ('RMBJ59038','RMBJ59001');
+------------+-------+-----------+-----------------------------------------+
| sClassCode | sName | sCode     | sName                                   |
+------------+-------+-----------+-----------------------------------------+
| NULL       | NULL  | RMBJ59001 | 海淀知春路大运村校区201教室             |
| NULL       | NULL  | RMBJ59038 | 海淀知春路大运村校区212教室             |
+------------+-------+-----------+-----------------------------------------+
2 rows in set (0.01 sec)

select tmp.sclasscode,tmp.sname,r.sCode,r.sName from api.bs_room r
    -> left join
    -> (
    ->         SELECT
    ->                 l.*,c.sName,c.lessonAuditState,c.changeState AS classChangeState,c.nState,c.nAudit
    ->                 cAudit
    ->         FROM
    ->                 bs_class c
    ->         JOIN
    ->                 bs_lesson l
    ->         ON
    ->                 c.nSchoolId=l.nSchoolId AND c.sCode=l.sClassCode
    ->         WHERE
    ->         c.nschoolId=1
    ->         AND l.SectBegin BETWEEN '2016-06-03 00:00:00' AND '2016-06-03 23:59:59'
    ->         AND c.nAudit!=2
    ->         AND c.nState!=3
    -> )tmp on r.nSchoolId=tmp.nSchoolId and r.sCode=tmp.sroomcode
    -> where r.nSchoolId=1 and r.sCode in ('RMBJ59038');
+---------------+---------------------------+-----------+-----------------------------------------+
| sClassCode    | sName                     | sCode     | sName                                   |
+---------------+---------------------------+-----------+-----------------------------------------+
| QTOEF20163002 | TOEFL基础班(Qtest)      | RMBJ59038 | 海淀知春路大运村校区212教室             |
+---------------+---------------------------+-----------+-----------------------------------------+
1 row in set (0.00 sec)

The same sql return different result only changed condition.

Two sql all contain 'RMBJ59038',but, one result`s field 'sClassCode' contains 'QTOEF20163002 ',another one isn`t.

How to repeat:
1. bs_lesson datas:
select nschoolid,sclasscode,sroomcode,sectbegin from bs_lesson l where l.nschoolid=1 and l.sroomcode='RMBJ59001' and l.sectbegin>'2015-05-01'; 
+-----------+------------+-----------+---------------------+
| nschoolid | sclasscode | sroomcode | sectbegin           |
+-----------+------------+-----------+---------------------+
|         1 | TFP15112   | RMBJ59001 | 2015-05-30 08:30:00 |
|         1 | TFP15112   | RMBJ59001 | 2015-05-30 11:40:00 |
|         1 | TFP15112   | RMBJ59001 | 2015-05-31 08:30:00 |
|         1 | TFP15112   | RMBJ59001 | 2015-05-31 11:40:00 |
|         1 | TFP15112   | RMBJ59001 | 2015-06-06 08:30:00 |
|         1 | TFP15112   | RMBJ59001 | 2015-06-06 11:40:00 |
|         1 | TFP15112   | RMBJ59001 | 2015-06-07 08:30:00 |
|         1 | TFP15112   | RMBJ59001 | 2015-06-07 11:40:00 |
|         1 | TSD153028  | RMBJ59001 | 2015-08-02 13:30:00 |
|         1 | TSD153028  | RMBJ59001 | 2015-08-02 15:40:00 |
|         1 | TSD153028  | RMBJ59001 | 2015-08-04 13:30:00 |
|         1 | TSD153028  | RMBJ59001 | 2015-08-04 15:40:00 |
|         1 | TSD153028  | RMBJ59001 | 2015-08-06 13:30:00 |
|         1 | TSD153028  | RMBJ59001 | 2015-08-06 15:40:00 |
|         1 | TSD153028  | RMBJ59001 | 2015-08-08 13:30:00 |
|         1 | TSD153028  | RMBJ59001 | 2015-08-08 15:40:00 |
|         1 | TS153025   | RMBJ59001 | 2015-06-14 08:00:00 |
|         1 | TS153025   | RMBJ59001 | 2015-06-14 10:10:00 |
|         1 | TS153025   | RMBJ59001 | 2015-07-05 08:00:00 |
|         1 | TS153025   | RMBJ59001 | 2015-07-05 10:10:00 |
|         1 | TS153025   | RMBJ59001 | 2015-07-12 08:00:00 |
|         1 | TS153025   | RMBJ59001 | 2015-07-12 10:10:00 |
|         1 | TS153025   | RMBJ59001 | 2015-07-18 08:00:00 |
|         1 | TS153025   | RMBJ59001 | 2015-07-18 10:10:00 |
|         1 | TS153025   | RMBJ59001 | 2015-06-20 08:00:00 |
|         1 | TS153025   | RMBJ59001 | 2015-06-20 10:10:00 |
|         1 | TS153025   | RMBJ59001 | 2015-07-04 08:00:00 |
|         1 | TS153025   | RMBJ59001 | 2015-07-04 10:10:00 |
|         1 | TS153025   | RMBJ59001 | 2015-07-11 08:00:00 |
|         1 | TS153025   | RMBJ59001 | 2015-07-11 10:10:00 |
|         1 | TSD153027  | RMBJ59001 | 2015-08-01 13:30:00 |
|         1 | TSD153027  | RMBJ59001 | 2015-08-01 15:40:00 |
|         1 | TSD153027  | RMBJ59001 | 2015-08-03 13:30:00 |
|         1 | TSD153027  | RMBJ59001 | 2015-08-03 15:40:00 |
|         1 | TSD153027  | RMBJ59001 | 2015-08-05 13:30:00 |
|         1 | TSD153027  | RMBJ59001 | 2015-08-05 15:40:00 |
|         1 | TSD153027  | RMBJ59001 | 2015-08-07 13:30:00 |
|         1 | TSD153027  | RMBJ59001 | 2015-08-07 15:40:00 |
|         1 | TS153095   | RMBJ59001 | 2015-08-17 13:30:00 |
|         1 | TS153095   | RMBJ59001 | 2015-08-17 15:40:00 |
|         1 | TS153095   | RMBJ59001 | 2015-08-18 13:30:00 |
|         1 | TS153095   | RMBJ59001 | 2015-08-18 15:40:00 |
|         1 | TS153095   | RMBJ59001 | 2015-08-19 13:30:00 |
|         1 | TS153095   | RMBJ59001 | 2015-08-19 15:40:00 |
|         1 | TS153025   | RMBJ59001 | 2015-06-13 08:00:00 |
|         1 | TS153025   | RMBJ59001 | 2015-06-13 10:10:00 |
|         1 | TS153025   | RMBJ59001 | 2015-06-27 08:00:00 |
|         1 | TS153025   | RMBJ59001 | 2015-06-27 10:10:00 |
|         1 | TS153025   | RMBJ59001 | 2015-06-21 08:00:00 |
|         1 | TS153025   | RMBJ59001 | 2015-06-21 10:10:00 |
|         1 | TS153025   | RMBJ59001 | 2015-06-28 08:00:00 |
|         1 | TS153025   | RMBJ59001 | 2015-06-28 10:10:00 |
|         1 | TS153025   | RMBJ59001 | 2015-07-19 08:00:00 |
|         1 | TS153025   | RMBJ59001 | 2015-07-19 10:10:00 |
|         1 | TSD153029  | RMBJ59001 | 2015-08-09 13:30:00 |
|         1 | TSD153029  | RMBJ59001 | 2015-08-09 15:40:00 |
|         1 | TSD153029  | RMBJ59001 | 2015-08-15 13:30:00 |
|         1 | TSD153029  | RMBJ59001 | 2015-08-15 15:40:00 |
|         1 | TSD153029  | RMBJ59001 | 2015-08-15 18:30:00 |
|         1 | TSD153029  | RMBJ59001 | 2015-08-16 13:30:00 |
|         1 | TSD153029  | RMBJ59001 | 2015-08-16 15:40:00 |
|         1 | TSD153029  | RMBJ59001 | 2015-08-16 18:30:00 |
|         1 | TSD153030  | RMBJ59001 | 2015-08-10 13:30:00 |
|         1 | TSD153030  | RMBJ59001 | 2015-08-10 15:40:00 |
|         1 | TSD153030  | RMBJ59001 | 2015-08-10 18:30:00 |
|         1 | TSD153030  | RMBJ59001 | 2015-08-12 13:30:00 |
|         1 | TSD153030  | RMBJ59001 | 2015-08-12 15:40:00 |
|         1 | TSD153030  | RMBJ59001 | 2015-08-12 18:30:00 |
|         1 | TSD153030  | RMBJ59001 | 2015-08-14 13:30:00 |
|         1 | TSD153030  | RMBJ59001 | 2015-08-14 15:40:00 |
+-----------+------------+-----------+---------------------+

select nschoolid,sclasscode,sroomcode,sectbegin from bs_lesson l where l.nschoolid=1 and l.sroomcode='RMBJ59038';
+-----------+---------------+-----------+---------------------+
| nschoolid | sclasscode    | sroomcode | sectbegin           |
+-----------+---------------+-----------+---------------------+
|         1 | NB161158      | RMBJ59038 | 2016-06-28 13:00:00 |
|         1 | NB161158      | RMBJ59038 | 2016-06-28 15:10:00 |
|         1 | NB161158      | RMBJ59038 | 2016-06-29 13:00:00 |
|         1 | NB161158      | RMBJ59038 | 2016-06-29 15:10:00 |
|         1 | NB161158      | RMBJ59038 | 2016-06-30 13:00:00 |
|         1 | NB161158      | RMBJ59038 | 2016-06-30 15:10:00 |
|         1 | NB161158      | RMBJ59038 | 2016-07-01 13:00:00 |
|         1 | NB161158      | RMBJ59038 | 2016-07-01 15:10:00 |
|         1 | NB161158      | RMBJ59038 | 2016-07-02 13:00:00 |
|         1 | NB161158      | RMBJ59038 | 2016-07-02 15:10:00 |
|         1 | NB161158      | RMBJ59038 | 2016-07-03 13:00:00 |
|         1 | NB161158      | RMBJ59038 | 2016-07-03 15:10:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-04 08:30:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-04 10:10:00 |
|         1 | NB161158      | RMBJ59038 | 2016-07-04 13:00:00 |
|         1 | NB161158      | RMBJ59038 | 2016-07-04 15:10:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-05 08:30:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-05 10:10:00 |
|         1 | NB161158      | RMBJ59038 | 2016-07-05 13:00:00 |
|         1 | NB161158      | RMBJ59038 | 2016-07-05 15:10:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-06 08:30:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-06 10:10:00 |
|         1 | NB161158      | RMBJ59038 | 2016-07-06 13:00:00 |
|         1 | NB161158      | RMBJ59038 | 2016-07-06 15:10:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-07 08:30:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-07 10:10:00 |
|         1 | NB161158      | RMBJ59038 | 2016-07-07 13:00:00 |
|         1 | NB161158      | RMBJ59038 | 2016-07-07 15:10:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-08 08:30:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-08 10:10:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-08 13:00:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-08 15:10:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-09 08:30:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-09 10:10:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-09 13:00:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-09 15:10:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-10 08:30:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-10 10:10:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-10 13:00:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-10 15:10:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-11 08:30:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-11 10:10:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-11 13:00:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-11 15:10:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-12 08:30:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-12 10:10:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-12 13:00:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-12 15:10:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-13 08:30:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-13 10:10:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-13 13:00:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-13 15:10:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-14 08:30:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-14 10:10:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-14 13:00:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-14 15:10:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-15 08:30:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-15 10:10:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-15 13:00:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-15 15:10:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-16 08:30:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-16 10:10:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-16 13:00:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-16 15:10:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-17 08:30:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-17 10:10:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-17 13:00:00 |
|         1 | NB161159      | RMBJ59038 | 2016-07-17 15:10:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-18 08:30:00 |
|         1 | NQ1640        | RMBJ59038 | 2016-07-18 10:10:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-19 08:30:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-19 10:40:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-20 08:30:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-20 10:40:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-21 08:30:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-21 10:40:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-22 08:30:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-22 10:40:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-23 08:30:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-23 10:40:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-24 08:30:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-24 10:40:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-25 08:30:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-25 10:40:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-26 08:30:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-26 10:40:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-27 08:30:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-27 10:40:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-28 08:30:00 |
|         1 | NC16237       | RMBJ59038 | 2016-07-28 10:40:00 |
|         1 | NC16238       | RMBJ59038 | 2016-07-29 08:30:00 |
|         1 | NC16238       | RMBJ59038 | 2016-07-29 10:40:00 |
|         1 | NC16238       | RMBJ59038 | 2016-07-30 08:30:00 |
|         1 | NC16238       | RMBJ59038 | 2016-07-30 10:40:00 |
|         1 | NC16238       | RMBJ59038 | 2016-07-31 08:30:00 |
|         1 | NC16238       | RMBJ59038 | 2016-07-31 10:40:00 |
|         1 | NC16238       | RMBJ59038 | 2016-08-01 08:30:00 |
|         1 | NC16238       | RMBJ59038 | 2016-08-01 10:40:00 |
|         1 | NC16238       | RMBJ59038 | 2016-08-02 08:30:00 |
|         1 | NC16238       | RMBJ59038 | 2016-08-02 10:40:00 |
|         1 | NC16238       | RMBJ59038 | 2016-08-03 08:30:00 |
|         1 | NC16238       | RMBJ59038 | 2016-08-03 10:40:00 |
|         1 | NC16238       | RMBJ59038 | 2016-08-04 08:30:00 |
|         1 | NC16238       | RMBJ59038 | 2016-08-04 10:40:00 |
|         1 | NC16238       | RMBJ59038 | 2016-08-05 08:30:00 |
|         1 | NC16238       | RMBJ59038 | 2016-08-05 10:40:00 |
|         1 | NC16238       | RMBJ59038 | 2016-08-06 08:30:00 |
|         1 | NC16238       | RMBJ59038 | 2016-08-06 10:40:00 |
|         1 | NC16238       | RMBJ59038 | 2016-08-07 08:30:00 |
|         1 | NC16238       | RMBJ59038 | 2016-08-07 10:40:00 |
|         1 | QTOEF20163002 | RMBJ59038 | 2016-05-23 19:00:00 |
|         1 | QTOEF20163002 | RMBJ59038 | 2016-05-27 19:00:00 |
|         1 | QTOEF20163002 | RMBJ59038 | 2016-05-30 19:00:00 |
|         1 | QTOEF20163002 | RMBJ59038 | 2016-06-03 19:00:00 |
|         1 | QTOEF20163002 | RMBJ59038 | 2016-06-06 19:00:00 |
|         1 | QTOEF20163002 | RMBJ59038 | 2016-06-10 19:00:00 |
|         1 | QTOEF20163002 | RMBJ59038 | 2016-06-13 19:00:00 |
|         1 | QTOEF20163002 | RMBJ59038 | 2016-06-17 19:00:00 |
|         1 | QTOEF20163002 | RMBJ59038 | 2016-06-20 19:00:00 |
|         1 | QTOEF20163002 | RMBJ59038 | 2016-06-24 19:00:00 |
|         1 | QTOEF20163002 | RMBJ59038 | 2016-06-27 19:00:00 |
|         1 | QTOEF20163002 | RMBJ59038 | 2016-07-01 19:00:00 |
|         1 | QTOEF20163002 | RMBJ59038 | 2016-07-04 19:00:00 |
|         1 | QTOEF20163002 | RMBJ59038 | 2016-07-08 19:00:00 |
|         1 | QTOEF20163002 | RMBJ59038 | 2016-07-11 19:00:00 |
|         1 | NCM15222      | RMBJ59038 | 2015-12-06 13:00:00 |
|         1 | NCM15222      | RMBJ59038 | 2015-12-06 14:40:00 |
|         1 | NCM15222      | RMBJ59038 | 2015-12-13 13:00:00 |
|         1 | NCM15222      | RMBJ59038 | 2015-12-13 14:40:00 |
|         1 | NCM15222      | RMBJ59038 | 2015-12-27 13:00:00 |
|         1 | NCM15222      | RMBJ59038 | 2015-12-27 14:40:00 |
|         1 | NCM15222      | RMBJ59038 | 2016-01-10 13:00:00 |
|         1 | NCM15222      | RMBJ59038 | 2016-01-10 14:40:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-17 08:30:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-17 11:40:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-17 15:00:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-17 18:10:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-18 08:30:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-18 11:40:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-18 15:00:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-18 18:10:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-19 08:30:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-19 11:40:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-19 15:00:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-19 18:10:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-20 08:30:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-20 11:40:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-20 15:00:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-20 18:10:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-21 08:30:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-21 11:40:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-21 15:00:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-21 18:10:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-22 08:30:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-22 11:40:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-22 15:00:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-22 18:10:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-23 08:30:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-23 11:40:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-23 15:00:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-23 18:10:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-24 08:30:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-24 11:40:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-24 15:00:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-24 18:10:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-25 08:30:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-25 11:40:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-25 15:00:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-25 18:10:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-26 08:30:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-26 11:40:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-26 15:00:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-26 18:10:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-27 08:30:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-27 11:40:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-27 15:00:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-27 18:10:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-28 08:30:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-28 11:40:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-28 15:00:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-28 18:10:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-29 08:30:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-29 11:40:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-29 15:00:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-29 18:10:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-30 08:30:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-30 11:40:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-30 15:00:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-30 18:10:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-31 08:30:00 |
|         1 | NQC16117      | RMBJ59038 | 2016-01-31 11:40:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-31 15:00:00 |
|         1 | NQC16123      | RMBJ59038 | 2016-01-31 18:10:00 |
|         1 | NCM15221      | RMBJ59038 | 2015-12-05 13:00:00 |
|         1 | NCM15221      | RMBJ59038 | 2015-12-05 14:40:00 |
|         1 | NCM15221      | RMBJ59038 | 2015-12-12 13:00:00 |
|         1 | NCM15221      | RMBJ59038 | 2015-12-12 14:40:00 |
|         1 | NCM15221      | RMBJ59038 | 2015-12-19 13:00:00 |
|         1 | NCM15221      | RMBJ59038 | 2015-12-19 14:40:00 |
|         1 | NCM15221      | RMBJ59038 | 2015-12-26 13:00:00 |
|         1 | NCM15221      | RMBJ59038 | 2015-12-26 14:40:00 |
|         1 | NCM15221      | RMBJ59038 | 2016-01-02 13:00:00 |
|         1 | NCM15221      | RMBJ59038 | 2016-01-02 14:40:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-11 08:30:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-11 10:40:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-12 08:30:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-12 10:40:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-13 08:30:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-13 10:40:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-14 08:30:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-14 10:40:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-15 08:30:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-15 10:40:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-16 08:30:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-16 10:40:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-17 08:30:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-17 10:40:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-18 08:30:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-18 10:40:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-19 08:30:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-19 10:40:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-20 08:30:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-20 10:40:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-21 08:30:00 |
|         1 | NBM16111      | RMBJ59038 | 2016-02-21 10:40:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-03-06 08:30:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-03-06 10:10:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-03-13 08:30:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-03-13 10:10:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-03-20 08:30:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-03-20 10:10:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-03-27 08:30:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-03-27 10:10:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-04-10 08:30:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-04-10 10:10:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-04-17 08:30:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-04-17 10:10:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-04-24 08:30:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-04-24 10:10:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-05-08 08:30:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-05-08 10:10:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-05-15 08:30:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-05-15 10:10:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-05-22 08:30:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-05-22 10:10:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-05-29 08:30:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-05-29 10:10:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-06-05 08:30:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-06-05 10:10:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-06-19 08:30:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-06-19 10:10:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-06-26 08:30:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-06-26 10:10:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-07-03 08:30:00 |
|         1 | NQ16602       | RMBJ59038 | 2016-07-03 10:10:00 |
+-----------+---------------+-----------+---------------------+

2. bs_class datas:
select c.nSchoolId,c.sCode,c.sName,c.nAudit,c.nState from bs_class c where c.sCode='QTOEF20163002';
+-----------+---------------+---------------------------+--------+--------+
| nSchoolId | sCode         | sName                     | nAudit | nState |
+-----------+---------------+---------------------------+--------+--------+
|         1 | QTOEF20163002 | TOEFL基础班(Qtest)      |      1 |      0 |
+-----------+---------------+---------------------------+--------+--------+
1 row in set (0.61 sec)

3.one sql:
select tmp.sclasscode,tmp.sname,r.sCode,r.sName from api.bs_room r
     left join
     (
             SELECT
                     l.*,c.sName,c.lessonAuditState,c.changeState AS classChangeState,c.nState,c.nAudit
                     cAudit
             FROM
                     bs_class c
             JOIN
                     bs_lesson l
             ON
                     c.nSchoolId=l.nSchoolId AND c.sCode=l.sClassCode
             WHERE
             c.nschoolId=1
             AND l.SectBegin BETWEEN '2016-06-03 00:00:00' AND '2016-06-03 23:59:59'
             AND c.nAudit!=2
             AND c.nState!=3
     )tmp on r.nSchoolId=tmp.nSchoolId and r.sCode=tmp.sroomcode
     where r.nSchoolId=1 and r.sCode in ('RMBJ59038','RMBJ59001');
+------------+-------+-----------+-----------------------------------------+
| sClassCode | sName | sCode     | sName                                   |
+------------+-------+-----------+-----------------------------------------+
| NULL       | NULL  | RMBJ59001 | 海淀知春路大运村校区201教室             |
| NULL       | NULL  | RMBJ59038 | 海淀知春路大运村校区212教室             |
+------------+-------+-----------+-----------------------------------------+
2 rows in set (0.01 sec)

4.two sql:
select tmp.sclasscode,tmp.sname,r.sCode,r.sName from api.bs_room r
     left join
     (
             SELECT
                     l.*,c.sName,c.lessonAuditState,c.changeState AS classChangeState,c.nState,c.nAudit
                     cAudit
             FROM
                     bs_class c
             JOIN
                     bs_lesson l
             ON
                     c.nSchoolId=l.nSchoolId AND c.sCode=l.sClassCode
             WHERE
             c.nschoolId=1
             AND l.SectBegin BETWEEN '2016-06-03 00:00:00' AND '2016-06-03 23:59:59'
             AND c.nAudit!=2
             AND c.nState!=3
     )tmp on r.nSchoolId=tmp.nSchoolId and r.sCode=tmp.sroomcode
     where r.nSchoolId=1 and r.sCode in ('RMBJ59038');
+---------------+---------------------------+-----------+-----------------------------------------+
| sClassCode    | sName                     | sCode     | sName                                   |
+---------------+---------------------------+-----------+-----------------------------------------+
| QTOEF20163002 | TOEFL基础班(Qtest)      | RMBJ59038 | 海淀知春路大运村校区212教室             |
+---------------+---------------------------+-----------+-----------------------------------------+
1 row in set (0.00 sec)
[1 Jun 2016 11:00] MySQL Verification Team
Please try last released version 5.7.12 if the issue continues provide the complete test case (create table and insert data statements).
[2 Jun 2016 1:15] Seven Wang
ok,I will update the version,thx.
[7 Jun 2016 6:53] Seven Wang
update to version 5.7.12.