JSON_QUOTE() in derived table is not working in Tidb:

[ramanich1]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

set sql_mode=DEFAULT;
drop table if exists t1,t2;
CREATE TABLE t1 (pk INTEGER,col_varchar JSON NOT NULL,PRIMARY KEY (pk));
CREATE TABLE t2 (pk INTEGER,col_date_key DATE NOT NULL,PRIMARY KEY (pk));
SELECT * FROM (SELECT alias2.pk AS field1
FROM t1 AS alias1 LEFT OUTER JOIN t2 AS alias2
ON JSON_QUOTE(JSON_EXTRACT(alias1.col_varchar,'$.varc')) = alias2.col_date_key) as dt;

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

mysql> SELECT * FROM (SELECT alias2.pk AS field1
    -> FROM t1 AS alias1 LEFT OUTER JOIN t2 AS alias2
    -> ON JSON_QUOTE(JSON_EXTRACT(alias1.col_varchar,'$.varc')) = alias2.col_date_key) as dt;
Empty set (0.00 sec)

3. What did you see instead (Required)

mysql> SELECT * FROM (SELECT alias2.pk AS field1
    -> FROM t1 AS alias1 LEFT OUTER JOIN t2 AS alias2
    -> ON JSON_QUOTE(JSON_EXTRACT(alias1.col_varchar,'$.varc')) = alias2.col_date_key) as dt;
ERROR 3064 (HY000): Incorrect type for argument 1 in function json_quote.

4. What is your TiDB version? (Required)

| Release Version: v5.4.0-alpha-67-g17edc5758
Edition: Community
Git Commit Hash: 17edc5758fbf865cada7e156318c41d3ee8a7501
Git Branch: master
UTC Build Time: 2021-11-16 02:57:45
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[xiongjiwei]

it is because t1 has no data, if you insert some data into t1, you will get the same error. i.e.

insert into t1 values (2, '{"varc":1}');
mysql> SELECT * FROM (SELECT alias2.pk AS field1 FROM t1 AS alias1 LEFT OUTER JOIN t2 AS alias2 ON JSON_QUOTE(JSON_EXTRACT(alias1.col_varchar,'$.varc')) = alias2.col_date_key) as dt;
ERROR 3064 (HY000): Incorrect type for argument 1 in function json_quote.