Inconsistent result when using hash partition and view

[Nickelth]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 BOOL NOT NULL DEFAULT false )PARTITION BY HASH(c0) PARTITIONS 10;
CREATE OR REPLACE VIEW v0(c0) AS SELECT (('k')|(((t0.c0)AND(t0.c0)))) FROM t0 GROUP BY FROM_BASE64(NULL);
CREATE OR REPLACE VIEW v1(c0) AS SELECT (('k')|(((t0.c0)AND(t0.c0)))) FROM t0 GROUP BY FROM_BASE64(NULL);
INSERT INTO t0 VALUES (true) ON DUPLICATE KEY UPDATE c0=((t0.c0)|('-'));
INSERT HIGH_PRIORITY IGNORE  INTO t0 VALUES (true);
INSERT INTO t0(c0) VALUES (false), (false), (true) ON DUPLICATE KEY UPDATE c0=t0.c0;

set @@tidb_build_stats_concurrency=10;
select * from v0;
select * from v1;

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

Two queries should produce same result

3. What did you see instead (Required)

Sometimes queries will produce inconsistent results. The results are unstable.

4. What is your TiDB version? (Required)

8.0.11-TiDB-v8.2.0-alpha-94-g81a7cf5 TiDB Server (Apache License 2.0) Community Edition, MySQL 8.0 compatible

[Nickelth]

Here is another testcase:

CREATE TABLE t0(c0 BOOL ZEROFILL UNIQUE )PARTITION BY HASH(c0) PARTITIONS 10;
create view v0 as select * from t0 group by FROM_BASE64(NULL);
create view v1 as select * from t0 group by FROM_BASE64(NULL);
REPLACE INTO t0(c0) VALUES (true), (NULL);
select v0.c0 from v0 where 376578142;
select v1.c0 from v1 where 376578142;

If you run last two queries multiple times, different results will be returned:

[djshow832]

I tried the result for the statements in the issue:

mysql> select * from v0;
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from v1;
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.01 sec)

mysql> select * from v0;
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.01 sec)

mysql> select * from v1;
+------+
| c0   |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.00 sec)

Even when you try the same view, the result is different.

[mjonss]

And the warning is:

tidb> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'k' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

[mjonss]

Seems related to ONLY_FULL_GROUP_BY sql_mode session variable handling through a view; running the query directly without going through a view shows:

tidb> SELECT (('k')|(((t0.c0)AND(t0.c0)))) FROM t0 GROUP BY FROM_BASE64(NULL);
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t0.c0' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[mjonss]

I consider this as 'not a bug', due to order is not guaranteed when reading from multiple data regions, by default every partition will have its own data range(s).
So doing GROUP BY, without sql_mode=only_full_group_by will take the first returned row, which is not deterministic in a distributed system, with multiple data regions. It is similar to how LIMIT would work.
Simpler test case:

tidb> set @@sql_mode='';
Query OK, 0 rows affected (0.00 sec)

tidb> create table t (a int) partition by hash(a) partitions 2;
Query OK, 0 rows affected (0.08 sec)

tidb> insert into t values (0),(1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

tidb> select * from t group by null;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

tidb> select * from t group by null;
+------+
| a    |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

tidb> select * from t limit 1;
+------+
| a    |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

tidb> select * from t limit 1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Please feel free to create a new issue if the sql_mode handling for views are not the expected, since I have not looked into this part.

[Nickelth]

I consider this as 'not a bug', due to order is not guaranteed when reading from multiple data regions, by default every partition will have its own data range(s). So doing GROUP BY, without sql_mode=only_full_group_by will take the first returned row, which is not deterministic in a distributed system, with multiple data regions. It is similar to how LIMIT would work. Simpler test case:

tidb> set @@sql_mode='';
Query OK, 0 rows affected (0.00 sec)

tidb> create table t (a int) partition by hash(a) partitions 2;
Query OK, 0 rows affected (0.08 sec)

tidb> insert into t values (0),(1);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

tidb> select * from t group by null;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

tidb> select * from t group by null;
+------+
| a    |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

tidb> select * from t limit 1;
+------+
| a    |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

tidb> select * from t limit 1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Please feel free to create a new issue if the sql_mode handling for views are not the expected, since I have not looked into this part.

It seems that using view will ignore sql_mode='ONLY_FULL_GROUP_BY', is this an expected behavior?

[mjonss]

After some searching, I think the following issues are duplicates of this:

• Selecting from a view returns inconsistent results #22767
• Unexpected Results by MERGE_JOIN #46564
• Unexpected Slow Query and Result by INL_JOIN #46600
• Unexpected Result by REGEXP #49107
  And also ending up in sql_mode is not taken into account for views.

Related bugs:
#46572

I will fill in the details in #53175 to cover the SQL_MODE handling in VIEWs.