In transaction select result is incorrect.

[coocood]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

create table Mammal (
   animal bigint not null,
    pregnant bit,
    birthdate date,
    mammalZoo_id bigint,
    name varchar(255),
    primary key (animal)
) engine=InnoDB;
create table Animal (
   id bigint not null auto_increment,
    description varchar(255),
    body_weight float,
    mother_id bigint,
    father_id bigint,
    zoo_id bigint,
    serialNumber varchar(255),
    primary key (id)
) engine=InnoDB;
create table Human (
   mammal bigint not null,
    name_first varchar(255),
    name_initial char(1),
    name_last varchar(255),
    nickName varchar(255),
    height_centimeters double precision not null,
    intValue integer,
    floatValue float,
    bigDecimalValue decimal(19,2),
    bigIntegerValue decimal(19,2),
    primary key (mammal)
) engine=InnoDB;
create table Human_friends (
   human1 bigint,
    human2 bigint not null
) engine=InnoDB;

begin;

insert into Animal (description, body_weight, mother_id, father_id, zoo_id, serialNumber) values (null, 0.0, null, null, null, null);
insert into Mammal (pregnant, birthdate, animal) values (0, null, 1);
insert into Human (name_first, name_initial, name_last, nickName, height_centimeters, intValue, floatValue, bigDecimalValue, bigIntegerValue, mammal) values ('Steve', 'L', 'Ebersole', null, 73.0 * 2.54E0, 0, 0.0, null, null, 1);
insert into Animal (description, body_weight, mother_id, father_id, zoo_id, serialNumber) values (null, 0.0, null, null, null, null);
insert into Mammal (pregnant, birthdate, animal) values (0, null, 2);
insert into Human (name_first, name_initial, name_last, nickName, height_centimeters, intValue, floatValue, bigDecimalValue, bigIntegerValue, mammal) values ('John', 'Q', 'Doe', null, 50.0 * 2.54E0, 0, 0.0, null, null, 2);
insert into Human_friends (human1, human2) values (1, 2);
insert into Human_friends (human1, human2) values (2, 1);

SELECT human0_.mammal                      AS id1_4_, 
       human0_2_.description               AS descript2_4_, 
       human0_2_.body_weight               AS body_wei3_4_, 
       human0_2_.mother_id                 AS mother_i4_4_, 
       human0_2_.father_id                 AS father_i5_4_, 
       human0_2_.zoo_id                    AS zoo_id6_4_, 
       human0_2_.serialnumber              AS serialNu7_4_, 
       human0_1_.pregnant                  AS pregnant2_28_, 
       human0_1_.birthdate                 AS birthdat3_28_, 
       human0_.name_first                  AS name_fir2_18_, 
       human0_.name_initial                AS name_ini3_18_, 
       human0_.name_last                   AS name_las4_18_, 
       human0_.nickname                    AS nickName5_18_, 
       human0_.height_centimeters / 2.54E0 AS height_c6_18_, 
       human0_.intvalue                    AS intValue7_18_, 
       human0_.floatvalue                  AS floatVal8_18_, 
       human0_.bigdecimalvalue             AS bigDecim9_18_, 
       human0_.bigintegervalue             AS bigInte10_18_ 
FROM   human_friends friends1_ 
       INNER JOIN human human0_ 
               ON friends1_.human2 = human0_.mammal 
       INNER JOIN mammal human0_1_ 
               ON human0_.mammal = human0_1_.animal 
       INNER JOIN animal human0_2_ 
               ON human0_.mammal = human0_2_.id 
WHERE  friends1_.human1 = 1 
       AND ( human0_.height_centimeters / 2.54E0 NOT BETWEEN 49 AND 51 ); 

2. What did you expect to see?

Empty set (0.00 sec)

3. What did you see instead?

1 row in set (0.01 sec)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

Release Version: v3.0.0-beta.1-167-g5f0346216
Git Commit Hash: 5f03462166c976ca2a229f00b8ae5e2c6c102fbd
Git Branch: master
UTC Build Time: 2019-05-05 04:43:36
GoVersion: go version go1.12 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

[jackysp]

I've checked this bug on release 2.1 and 2.0.
Release 2.1 has this bug.

[jackysp]

The two plans are nearly the same, except

not(and(ge(div(human0_.height_centimeters, 2.54), 49), le(div(human0_.height_centimeters, 2.54), 51)))

this is correct on 2.0, but on 2.1 it is

not(and(lt(div(human0_.height_centimeters, 2.54), 49), gt(div(human0_.height_centimeters, 2.54), 51)))

2.1:

id	count	task	operator info
Projection_14	19.53	root	human0_.mammal, human0_2_.description, human0_2_.body_weight, human0_2_.mother_id, human0_2_.father_id, human0_2_.zoo_id, human0_2_.serialnumber, human0_1_.pregnant, human0_1_.birthdate, human0_.name_first, human0_.name_initial, human0_.name_last, human0_.nickname, div(human0_.height_centimeters, 2.54), human0_.intvalue, human0_.floatvalue, human0_.bigdecimalvalue, human0_.bigintegervalue
└─HashRightJoin_16	19.53	root	inner join, inner:HashRightJoin_18, equal:[eq(human0_.mammal, human0_2_.id)]
  ├─HashRightJoin_18	15.62	root	inner join, inner:HashRightJoin_20, equal:[eq(human0_.mammal, human0_1_.animal)]
  │ ├─HashRightJoin_20	12.50	root	inner join, inner:UnionScan_21, equal:[eq(friends1_.human2, human0_.mammal)]
  │ │ ├─UnionScan_21	10.00	root	eq(friends1_.human1, 1)
  │ │ │ └─TableReader_24	10.00	root	data:Selection_23
  │ │ │   └─Selection_23	10.00	cop	eq(friends1_.human1, 1)
  │ │ │     └─TableScan_22	10000.00	cop	table:Human_friends, range:[-inf,+inf], keep order:false, stats:pseudo
  │ │ └─UnionScan_25	8000.00	root	not(and(lt(div(human0_.height_centimeters, 2.54), 49), gt(div(human0_.height_centimeters, 2.54), 51)))
  │ │   └─TableReader_28	8000.00	root	data:Selection_27
  │ │     └─Selection_27	8000.00	cop	or(lt(div(human0_.height_centimeters, 2.54), 49), gt(div(human0_.height_centimeters, 2.54), 51))
  │ │       └─TableScan_26	10000.00	cop	table:Human, range:[-inf,+inf], keep order:false, stats:pseudo
  │ └─UnionScan_29	10000.00	root	
  │   └─TableReader_31	10000.00	root	data:TableScan_30
  │     └─TableScan_30	10000.00	cop	table:Mammal, range:[-inf,+inf], keep order:false, stats:pseudo
  └─UnionScan_32	10000.00	root	
    └─TableReader_34	10000.00	root	data:TableScan_33
      └─TableScan_33	10000.00	cop	table:Animal, range:[-inf,+inf], keep order:false, stats:pseudo

2.0:

+------------------+------------------+-------------------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| id               | parents          | children                      | task | operator info                                                                                                                                                                                                                                                                                                                                                                                           | count    |
+------------------+------------------+-------------------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| Projection_14    |                  | HashRightJoin_16              | root | human0_.mammal, human0_2_.description, human0_2_.body_weight, human0_2_.mother_id, human0_2_.father_id, human0_2_.zoo_id, human0_2_.serialnumber, human0_1_.pregnant, human0_1_.birthdate, human0_.name_first, human0_.name_initial, human0_.name_last, human0_.nickname, div(human0_.height_centimeters, 2.54), human0_.intvalue, human0_.floatvalue, human0_.bigdecimalvalue, human0_.bigintegervalue | 19.53    |
| HashRightJoin_16 | Projection_14    | HashRightJoin_18,UnionScan_32 | root | inner join, inner:HashRightJoin_18, equal:[eq(human0_.mammal, human0_2_.id)]                                                                                                                                                                                                                                                                                                                            | 19.53    |
| HashRightJoin_18 | HashRightJoin_16 | HashRightJoin_20,UnionScan_29 | root | inner join, inner:HashRightJoin_20, equal:[eq(human0_.mammal, human0_1_.animal)]                                                                                                                                                                                                                                                                                                                        | 15.62    |
| HashRightJoin_20 | HashRightJoin_18 | UnionScan_21,UnionScan_25     | root | inner join, inner:UnionScan_21, equal:[eq(friends1_.human2, human0_.mammal)]                                                                                                                                                                                                                                                                                                                            | 12.50    |
| UnionScan_21     | HashRightJoin_20 | TableReader_24                | root | eq(friends1_.human1, 1)                                                                                                                                                                                                                                                                                                                                                                                 | 10.00    |
| TableReader_24   | UnionScan_21     |                               | root | data:Selection_23                                                                                                                                                                                                                                                                                                                                                                                       | 10.00    |
| Selection_23     |                  | TableScan_22                  | cop  | eq(friends1_.human1, 1)                                                                                                                                                                                                                                                                                                                                                                                 | 10.00    |
| TableScan_22     | Selection_23     |                               | cop  | table:Human_friends, range:[-inf,+inf], keep order:false                                                                                                                                                                                                                                                                                                                                                | 10000.00 |
| UnionScan_25     | HashRightJoin_20 | TableReader_28                | root | not(and(ge(div(human0_.height_centimeters, 2.54), 49), le(div(human0_.height_centimeters, 2.54), 51)))                                                                                                                                                                                                                                                                                                  | 8000.00  |
| TableReader_28   | UnionScan_25     |                               | root | data:Selection_27                                                                                                                                                                                                                                                                                                                                                                                       | 8000.00  |
| Selection_27     |                  | TableScan_26                  | cop  | or(lt(div(human0_.height_centimeters, 2.54), 49), gt(div(human0_.height_centimeters, 2.54), 51))                                                                                                                                                                                                                                                                                                        | 8000.00  |
| TableScan_26     | Selection_27     |                               | cop  | table:Human, range:[-inf,+inf], keep order:false                                                                                                                                                                                                                                                                                                                                                        | 10000.00 |
| UnionScan_29     | HashRightJoin_18 | TableReader_31                | root |                                                                                                                                                                                                                                                                                                                                                                                                         | 10000.00 |
| TableReader_31   | UnionScan_29     |                               | root | data:TableScan_30                                                                                                                                                                                                                                                                                                                                                                                       | 10000.00 |
| TableScan_30     |                  |                               | cop  | table:Mammal, range:[-inf,+inf], keep order:false                                                                                                                                                                                                                                                                                                                                                       | 10000.00 |
| UnionScan_32     | HashRightJoin_16 | TableReader_34                | root |                                                                                                                                                                                                                                                                                                                                                                                                         | 10000.00 |
| TableReader_34   | UnionScan_32     |                               | root | data:TableScan_33                                                                                                                                                                                                                                                                                                                                                                                       | 10000.00 |
| TableScan_33     |                  |                               | cop  | table:Animal, range:[-inf,+inf], keep order:false                                                                                                                                                                                                                                                                                                                                                       | 10000.00 |
+------------------+------------------+-------------------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+