tiflash json bool value compare to 1 result is different from tikv

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t;
create table t(a int, b json);
alter table t set tiflash replica 1;
insert into t values (10, '{"name":"value"}'), (11, '{\"asdf\":true}'), (13, '{\"input1\":\"\\u00f6\"}');

select /*+ read_from_storage(tiflash[t]) */  JSON_EXTRACT(b, "$.\"asdf\"") = true, JSON_EXTRACT(b, "$.\"asdf\"") = false, JSON_EXTRACT(b, "$.\"asdf\"") = 1 from t where a = 11;
select /*+ read_from_storage(tikv[t]) */  JSON_EXTRACT(b, "$.\"asdf\"") = true, JSON_EXTRACT(b, "$.\"asdf\"") = false, JSON_EXTRACT(b, "$.\"asdf\"") = 1 from t where a = 11;

introduced by #48550

2. What did you expect to see? (Required)

[10:31:08]TiDB root:test> select /*+ read_from_storage(tikv[t]) */  JSON_EXTRACT(b, "$.\"asdf\"") = true, JSON_EXTRACT(b, "$.\"asdf\"") = false, JSON_EXTRACT(b, "$.\"asdf\"") = 1 from t where a = 11;
                       ->
+--------------------------------------+---------------------------------------+-----------------------------------+
| JSON_EXTRACT(b, "$.\"asdf\"") = true | JSON_EXTRACT(b, "$.\"asdf\"") = false | JSON_EXTRACT(b, "$.\"asdf\"") = 1 |
+--------------------------------------+---------------------------------------+-----------------------------------+
| 1                                    | 0                                     | 0                                 |
+--------------------------------------+---------------------------------------+-----------------------------------+
1 row in set
Time: 0.004s
[10:31:17]TiDB root:test> select /*+ read_from_storage(tiflash[t]) */  JSON_EXTRACT(b, "$.\"asdf\"") = true, JSON_EXTRACT(b, "$.\"asdf\"") = false, JSON_EXTRACT(b, "$.\"asdf\"") = 1 from t where a = 11;
+--------------------------------------+---------------------------------------+-----------------------------------+
| JSON_EXTRACT(b, "$.\"asdf\"") = true | JSON_EXTRACT(b, "$.\"asdf\"") = false | JSON_EXTRACT(b, "$.\"asdf\"") = 1 |
+--------------------------------------+---------------------------------------+-----------------------------------+
| 1                                    | 0                                     | 0                                 |
+--------------------------------------+---------------------------------------+-----------------------------------+

3. What did you see instead (Required)

[10:31:08]TiDB root:test> select /*+ read_from_storage(tikv[t]) */  JSON_EXTRACT(b, "$.\"asdf\"") = true, JSON_EXTRACT(b, "$.\"asdf\"") = false, JSON_EXTRACT(b, "$.\"asdf\"") = 1 from t where a = 11;
                       ->
+--------------------------------------+---------------------------------------+-----------------------------------+
| JSON_EXTRACT(b, "$.\"asdf\"") = true | JSON_EXTRACT(b, "$.\"asdf\"") = false | JSON_EXTRACT(b, "$.\"asdf\"") = 1 |
+--------------------------------------+---------------------------------------+-----------------------------------+
| 1                                    | 0                                     | 0                                 |
+--------------------------------------+---------------------------------------+-----------------------------------+
1 row in set
Time: 0.004s
[10:40:42]TiDB root:test> select /*+ read_from_storage(tiflash[t]) */  JSON_EXTRACT(b, "$.\"asdf\"") = true, JSON_EXTRACT(b, "$.\"asdf\"") = false, JSON_EXTRACT(b, "$.\"asdf\"") = 1 from t where a = 11;
+--------------------------------------+---------------------------------------+-----------------------------------+
| JSON_EXTRACT(b, "$.\"asdf\"") = true | JSON_EXTRACT(b, "$.\"asdf\"") = false | JSON_EXTRACT(b, "$.\"asdf\"") = 1 |
+--------------------------------------+---------------------------------------+-----------------------------------+
| 1                                    | 0                                     | 1                                 |
+--------------------------------------+---------------------------------------+-----------------------------------+
1 row in set

4. What is your TiDB version? (Required)

[10:41:25]TiDB root:test> select type,version,git_hash  from information_schema.cluster_info;
+---------+-------------+------------------------------------------+
| type    | version     | git_hash                                 |
+---------+-------------+------------------------------------------+
| tidb    | 7.6.0-alpha | 8fc13dadb250139694d608144219fb1ded041412 |
| pd      | 7.6.0-alpha | 995fcef820f43758b94a2eeef8cdd91fa08deaea |
| tikv    | 7.6.0-alpha | 213299221806959c95d05d0f2d7368e597fa9281 |
| tiflash | 7.6.0-alpha | 713bca0a4f65c3217192baf00dc00b8ea7c9dccc |
+---------+-------------+------------------------------------------+

[SeaRise]

The results for JSON_EXTRACT(b, "$.\"asdf\"") = 1 are different in the two sql's and more investigation is needed

[SeaRise]

The reason is that the result reuse mechanism of tiflash's ExpressionAction, JSON_EXTRACT(b, "$.\"asdf\"") = 1 incorrectly reuses the result of JSON_EXTRACT(b, "$.\"asdf\"") = true .
So running JSON_EXTRACT(b, "$.\"asdf\"") = true alone results is correctly

[SeaRise]

/remove label may-affects-5.4

[SeaRise]

/remove-label may-affects-5.4

[SeaRise]

/remove-label may-affects-6.1

[SeaRise]

/remove-label may-affects-6.5

[SeaRise]

/remove-label may-affects-7.1

[SeaRise]

/remove-label may-affects-7.5

[SeaRise]

should be fixed by pingcap/tiflash#8483

[SeaRise]

fixed

mysql>
mysql> select /*+ read_from_storage(tiflash[t]) */  JSON_EXTRACT(b, "$.\"asdf\"") = true, JSON_EXTRACT(b, "$.\"asdf\"") = false, JSON_EXTRACT(b,
"$.\"asdf\"") = 1 from t where a = 11;
+--------------------------------------+---------------------------------------+-----------------------------------+
| JSON_EXTRACT(b, "$.\"asdf\"") = true | JSON_EXTRACT(b, "$.\"asdf\"") = false | JSON_EXTRACT(b, "$.\"asdf\"") = 1 |
+--------------------------------------+---------------------------------------+-----------------------------------+
|                                    1 |                                     0 |                                 0 |
+--------------------------------------+---------------------------------------+-----------------------------------+
1 row in set (0.03 sec)

mysql> select /*+ read_from_storage(tikv[t]) */  JSON_EXTRACT(b, "$.\"asdf\"") = true, JSON_EXTRACT(b, "$.\"asdf\"") = false, JSON_EXTRACT(b, "$.\"asdf\"") = 1 from t where a = 11;
+--------------------------------------+---------------------------------------+-----------------------------------+
| JSON_EXTRACT(b, "$.\"asdf\"") = true | JSON_EXTRACT(b, "$.\"asdf\"") = false | JSON_EXTRACT(b, "$.\"asdf\"") = 1 |
+--------------------------------------+---------------------------------------+-----------------------------------+
|                                    1 |                                     0 |                                 0 |
+--------------------------------------+---------------------------------------+-----------------------------------+
1 row in set (0.00 sec)

[SeaRise]

/close

[ti-chi-bot]

@SeaRise: Closing this issue.

In response to this:

/close

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.