query reports ScalarFunction Format is not supported in batch mode

[wjhuang2016]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t4a8656d1` (
  `col_73` json NOT NULL,
  `col_74` date DEFAULT '1984-06-10',
  KEY `idx_39` ((cast(`col_73` as double array)),`col_74`),
  KEY `idx_40` ((cast(`col_73` as double array)),`col_74`),
  UNIQUE KEY `idx_41` (`col_74`,(cast(`col_73` as double array)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE `tld47bc815` (
  `col_1` text NOT NULL,
  PRIMARY KEY (`col_1`(3)) /*T![clustered_index] NONCLUSTERED */,
  KEY `idx_2` (`col_1`(5)),
  UNIQUE KEY `idx_3` (`col_1`(5)),
  KEY `idx_4` (`col_1`(4))
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_chinese_ci;


with cte_147 ( col_754 ) AS ( select format( t4a8656d1.col_74 , 1 ) as r0 from t4a8656d1 join tld47bc815 on t4a8656d1.col_74 = tld47bc815.col_1 where JSON_OVERLAPS( t4a8656d1.col_73 , '[0.035131302371695955,0.8429698274329702,0.2506157132646911,0.29289756835712716]' ) group by t4a8656d1.col_74,t4a8656d1.col_73 having not( IsNull( JSON_OVERLAPS( '[0.47941091010360976,0.4906971499170084]' , t4a8656d1.col_73 ) ) ) ) ( select 1,col_754 from cte_147 where IsNull( cte_147.col_754 ) order by 1,2 );

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

No error

3. What did you see instead (Required)

ERROR 1105 (HY000): other error: [components/tidb_query_expr/src/lib.rs:857]: ScalarFunction Format is not supported in batch mode

4. What is your TiDB version? (Required)

7cdf918

[hawkingrei]

in fact, it can be simplified.

select 1, r0 as col_754
from (
    select format(t4a8656d1.col_74, 1) as r0
    from t4a8656d1
    join tld47bc815 on t4a8656d1.col_74 = tld47bc815.col_1
    where JSON_OVERLAPS(t4a8656d1.col_73, '[0.035131302371695955]')
    group by t4a8656d1.col_74, t4a8656d1.col_73
) as subquery
where IsNull(subquery.r0)

Another interesting phenomenon is that if I only execute the subquery, it performs well.

+------------------------------------+----------+-----------+-----------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+
| id                                 | estRows  | task      | access object                                                         | operator info                                                                                                       |
+------------------------------------+----------+-----------+-----------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+
| Projection_12                      | 6.40     | root      |                                                                       | format(cast(test.t4a8656d1.col_74, double BINARY), 1)->Column#9                                                     |
| └─HashAgg_13                       | 6.40     | root      |                                                                       | group by:test.t4a8656d1.col_73, test.t4a8656d1.col_74, funcs:firstrow(test.t4a8656d1.col_74)->test.t4a8656d1.col_74 |
|   └─HashJoin_16                    | 10.00    | root      |                                                                       | inner join, equal:[eq(test.t4a8656d1.col_74, Column#11)]                                                            |
|     ├─Selection_17(Build)          | 8.00     | root      |                                                                       | json_overlaps(test.t4a8656d1.col_73, cast("[0.035131302371695955]", json BINARY))                                   |
|     │ └─IndexMerge_22              | 10.00    | root      |                                                                       | type: union                                                                                                         |
|     │   ├─IndexRangeScan_20(Build) | 10.00    | cop[tikv] | table:t4a8656d1, index:idx_39(cast(`col_73` as double array), col_74) | range:[0.035131302371695955,0.035131302371695955], keep order:false, stats:pseudo                                   |
|     │   └─TableRowIDScan_21(Probe) | 10.00    | cop[tikv] | table:t4a8656d1                                                       | keep order:false, stats:pseudo                                                                                      |
|     └─Projection_26(Probe)         | 10000.00 | root      |                                                                       | cast(test.tld47bc815.col_1, datetime(6) BINARY)->Column#11                                                          |
|       └─TableReader_28             | 10000.00 | root      |                                                                       | data:TableFullScan_27                                                                                               |
|         └─TableFullScan_27         | 10000.00 | cop[tikv] | table:tld47bc815                                                      | keep order:false, stats:pseudo                                                                                      |
+------------------------------------+----------+-----------+-----------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+

[hawkingrei]

before merging #54877

there is a right plan

+--------------------------------------+----------+-----------+-----------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                   | estRows  | task      | access object                                                         | operator info                                                                                                                                    |
+--------------------------------------+----------+-----------+-----------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_14                        | 6.40     | root      |                                                                       | 1->Column#10, Column#9                                                                                                                           |
| └─Projection_15                      | 6.40     | root      |                                                                       | format(cast(test.t4a8656d1.col_74, double BINARY), 1)->Column#9                                                                                  |
|   └─HashAgg_16                       | 6.40     | root      |                                                                       | group by:test.t4a8656d1.col_73, test.t4a8656d1.col_74, funcs:firstrow(test.t4a8656d1.col_74)->test.t4a8656d1.col_74                              |
|     └─HashJoin_19                    | 10.00    | root      |                                                                       | inner join, equal:[eq(test.t4a8656d1.col_74, Column#13)]                                                                                         |
|       ├─Selection_20(Build)          | 8.00     | root      |                                                                       | isnull(format(cast(test.t4a8656d1.col_74, double BINARY), 1)), json_overlaps(test.t4a8656d1.col_73, cast("[0.035131302371695955]", json BINARY)) |
|       │ └─IndexMerge_26              | 10.00    | root      |                                                                       | type: union                                                                                                                                      |
|       │   ├─IndexRangeScan_24(Build) | 10.00    | cop[tikv] | table:t4a8656d1, index:idx_39(cast(`col_73` as double array), col_74) | range:[0.035131302371695955,0.035131302371695955], keep order:false, stats:pseudo                                                                |
|       │   └─TableRowIDScan_25(Probe) | 10.00    | cop[tikv] | table:t4a8656d1                                                       | keep order:false, stats:pseudo                                                                                                                   |
|       └─Projection_32(Probe)         | 10000.00 | root      |                                                                       | cast(test.tld47bc815.col_1, datetime(6) BINARY)->Column#13                                                                                       |
|         └─TableReader_34             | 10000.00 | root      |                                                                       | data:TableFullScan_33                                                                                                                            |
|           └─TableFullScan_33         | 10000.00 | cop[tikv] | table:tld47bc815                                                      | keep order:false, stats:pseudo                                                                                                                   |
+--------------------------------------+----------+-----------+-----------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------+

(END)