Unexpected Result by REGEXP

[bajinsheng]

Bug Report

I believe this is a bug as two queries return inconsistent results.
Although the view includes a GROUP BY, it does not break the only_full_group_by attribute.

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 INT);
INSERT INTO t0 VALUES (1), (2);
INSERT INTO t1 VALUES (3);
CREATE VIEW v0(c0, c1) AS SELECT t0.c0, (false NOT REGEXP t1.c0) LIKE 0 FROM t1, t0 GROUP BY t1.c0;

SELECT v0.c0 FROM v0; -- {2}
SELECT v0.c0 FROM v0 WHERE NOT v0.c1; -- {1}

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

The first query should return 2 rows because the second query returns a different result.

3. What did you see instead (Required)

The second query returns a different result as that of the first query.

4. What is your TiDB version? (Required)

| Release Version: v7.6.0-alpha-347-g598ccada40-dirty
Edition: Community
Git Commit Hash: 598ccada4065e4871bc5bce53a039cbb598a6d82
Git Branch: master
UTC Build Time: 2023-12-01 01:44:42
GoVersion: go1.21.0
Race Enabled: false
Check Table Before Drop: false
Store: unistore |

[bajinsheng]

/label fuzz/sqlancer

[XuHuaiyu]

tidb:4003 [test]> select c0 from v0;
+------+
| c0   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

tidb:4003 [test]> desc select c0 from v0;
+-------------------------------+---------+-----------+---------------+-------------------------------------------------------------+
| id                            | estRows | task      | access object | operator info                                               |
+-------------------------------+---------+-----------+---------------+-------------------------------------------------------------+
| HashAgg_10                    | 1.00    | root      |               | group by:test.t1.c0, funcs:firstrow(test.t0.c0)->test.t0.c0 |
| └─HashJoin_12                 | 2.00    | root      |               | CARTESIAN inner join                                        |
|   ├─TableReader_14(Build)     | 1.00    | root      |               | data:TableFullScan_13                                       |
|   │ └─TableFullScan_13        | 1.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                              |
|   └─TableReader_16(Probe)     | 2.00    | root      |               | data:TableFullScan_15                                       |
|     └─TableFullScan_15        | 2.00    | cop[tikv] | table:t0      | keep order:false, stats:pseudo                              |
+-------------------------------+---------+-----------+---------------+-------------------------------------------------------------+
6 rows in set (0.00 sec)

tidb:4003 [test]> drop stats t0, t1;
Query OK, 0 rows affected (0.01 sec)

tidb:4003 [test]> select c0 from v0;
+------+
| c0   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

tidb:4003 [test]> desc select c0 from v0;
+-------------------------------+--------------+-----------+---------------+-------------------------------------------------------------+
| id                            | estRows      | task      | access object | operator info                                               |
+-------------------------------+--------------+-----------+---------------+-------------------------------------------------------------+
| HashAgg_10                    | 8000.00      | root      |               | group by:test.t1.c0, funcs:firstrow(test.t0.c0)->test.t0.c0 |
| └─HashJoin_11                 | 100000000.00 | root      |               | CARTESIAN inner join                                        |
|   ├─TableReader_16(Build)     | 10000.00     | root      |               | data:TableFullScan_15                                       |
|   │ └─TableFullScan_15        | 10000.00     | cop[tikv] | table:t0      | keep order:false, stats:pseudo                              |
|   └─TableReader_14(Probe)     | 10000.00     | root      |               | data:TableFullScan_13                                       |
|     └─TableFullScan_13        | 10000.00     | cop[tikv] | table:t1      | keep order:false, stats:pseudo                              |
+-------------------------------+--------------+-----------+---------------+-------------------------------------------------------------+
6 rows in set (0.00 sec)

[XuHuaiyu]

mysql> SELECT t0.c0, (false NOT REGEXP t1.c0) LIKE 0 FROM t1, t0 GROUP BY t1.c0;
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

The definition of view breaks only_full_group_by.

[XuHuaiyu]

MySQL can raise the error when querying from the view, but tidb does not.
It would be a bug related to planner.

mysql> SELECT v0.c0 FROM v0; -- {2}
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
mysql> SELECT v0.c0 FROM v0 WHERE NOT v0.c1; -- {1}
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
mysql> SELECT t0.c0, (false NOT REGEXP t1.c0) LIKE 0 FROM t1, t0 GROUP BY t1.c0;
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