Bug #112768 Inconsistent results when tables are JOINed in a different order

• Submitted: 19 Oct 2023 7:15
• Modified: 19 Oct 2023 10:12
• Reporter: Wang Ke
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.1.0, 8.0.34
• OS: Any
• CPU Architecture: Any

[19 Oct 2023 7:15] Wang Ke

Description:
Hello, I found a test case, when I use different hints to affect its query plan, it seems to come to distinct results: one gives an error and the other gives an empty result.

```
CREATE TABLE t0 ( c0 INT );
CREATE TABLE t1 ( c2 DOUBLE );
CREATE VIEW v0 AS SELECT t1.c2 FROM t1 WHERE json_extract( t1.c2 , '$.key' ) = 'abc' ;
INSERT INTO t1 VALUES (1.0);
SELECT * FROM t0 JOIN v0;

mysql> SELECT /*+ JOIN_ORDER(t1, t0) */ * FROM t0 JOIN v0;
ERROR 3146 (22032): Invalid data type for JSON data in argument 1 to function json_extract; a JSON string or JSON type is required.

mysql> SELECT /*+ JOIN_ORDER(t0, t1) */ * FROM t0 JOIN v0;
Empty set (0.00 sec)
```

I'm curious if this behavior is reasonable, if they should all give an error, or if they should all get an empty result set?

How to repeat:
Just run the test case above.

[19 Oct 2023 7:57] MySQL Verification Team

Hello Ke Wang,

Thank you for the report and test case.

regards,
Umesh

[19 Oct 2023 10:12] Roy Lyseng

Posted by developer:
 
This is not a bug. EXPLAIN ANALYZE for the query that succeeds tells us:

-> Inner hash join (no condition)  (cost=0.7 rows=1) (actual time=0.119..0.119 rows=0 loops=1)
    -> Filter: (json_extract(t1.c2,'$.key') = 'abc')  (cost=0.35 rows=1) (never executed)
        -> Table scan on t1  (cost=0.35 rows=1) (never executed)
    -> Hash
        -> Table scan on t0  (cost=0.35 rows=1) (actual time=0.0799..0.0799 rows=0 loops=1)

Thus, the predicate that may cause an error is never evaluated and thus the query completes normally.
The inverse join order will evaluate the predicate and cause an error.