Panic triggered at core.(*LogicalProjection).TryToGetChildProp (planner/core/exhaust_physical_plans.go:2500)

[JZuming]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Setup the database:

CREATE TABLE t1 (c7 double);
CREATE TABLE t4 (c18 double);
CREATE TABLE t5 (c23 double, c24 varchar(100));

Testcase

select
  exists (
    select
        FIRST_VALUE(ref_24.c7) over w0 as c1,
        min(ref_23.c23) over w0 as c3
      from
        t1 as ref_24
      window w0 as (partition by exists (
            select
                ref_23.c24 as c2
              from
                t4 as ref_26
              where ref_24.c7 >= ref_26.c18))
    ) as c1
from
  t5 as ref_23;

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

No error was triggered.

3. What did you see instead (Required)

ERROR 1105 (HY000): runtime error: index out of range [-1]

TiDB log:

err="runtime error: index out of range [-1]"] [stack="github.com/pingcap/tidb/server.(*clientConn).Run.func1
    /root/tidb/server/conn.go:1042
runtime.gopanic
    /usr/local/go/src/runtime/panic.go:884
github.com/pingcap/tidb/executor.(*Compiler).Compile.func1
    /root/tidb/executor/compiler.go:54
runtime.gopanic
    /usr/local/go/src/runtime/panic.go:884
runtime.goPanicIndex
    /usr/local/go/src/runtime/panic.go:113
github.com/pingcap/tidb/planner/core.(*LogicalProjection).TryToGetChildProp
    /root/tidb/planner/core/exhaust_physical_plans.go:2500
github.com/pingcap/tidb/planner/core.(*LogicalProjection).exhaustPhysicalPlans
    /root/tidb/planner/core/exhaust_physical_plans.go:2512
github.com/pingcap/tidb/planner/core.(*baseLogicalPlan).findBestTask
    /root/tidb/planner/core/find_best_task.go:412
github.com/pingcap/tidb/planner/core.(*baseLogicalPlan).enumeratePhysicalPlans4Task
    /root/tidb/planner/core/find_best_task.go:224
github.com/pingcap/tidb/planner/core.(*baseLogicalPlan).findBestTask
    /root/tidb/planner/core/find_best_task.go:452
github.com/pingcap/tidb/planner/core.(*baseLogicalPlan).enumeratePhysicalPlans4Task
    /root/tidb/planner/core/find_best_task.go:224
github.com/pingcap/tidb/planner/core.(*baseLogicalPlan).findBestTask
    /root/tidb/planner/core/find_best_task.go:452
github.com/pingcap/tidb/planner/core.(*baseLogicalPlan).enumeratePhysicalPlans4Task
    /root/tidb/planner/core/find_best_task.go:224
github.com/pingcap/tidb/planner/core.(*baseLogicalPlan).findBestTask
    /root/tidb/planner/core/find_best_task.go:452
github.com/pingcap/tidb/planner/core.physicalOptimize
    /root/tidb/planner/core/optimizer.go:1136
github.com/pingcap/tidb/planner/core.DoOptimize
    /root/tidb/planner/core/optimizer.go:305
github.com/pingcap/tidb/planner.optimize
    /root/tidb/planner/optimize.go:467
github.com/pingcap/tidb/planner.Optimize
    /root/tidb/planner/optimize.go:295
github.com/pingcap/tidb/executor.(*Compiler).Compile
    /root/tidb/executor/compiler.go:98
github.com/pingcap/tidb/session.(*session).ExecuteStmt
    /root/tidb/session/session.go:2139
github.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt
    /root/tidb/server/driver_tidb.go:252
github.com/pingcap/tidb/server.(*clientConn).handleStmt
    /root/tidb/server/conn.go:2054
github.com/pingcap/tidb/server.(*clientConn).handleQuery
    /root/tidb/server/conn.go:1851
github.com/pingcap/tidb/server.(*clientConn).dispatch
    /root/tidb/server/conn.go:1337
github.com/pingcap/tidb/server.(*clientConn).Run
    /root/tidb/server/conn.go:1120
github.com/pingcap/tidb/server.(*Server).onConn
    /root/tidb/server/server.go:675"

4. What is your TiDB version? (Required)

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.1.0-alpha-27-gf5ca27ef3
Edition: Community
Git Commit Hash: f5ca27e
Git Branch: master
UTC Build Time: 2023-03-23 13:57:53
GoVersion: go1.20.2
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[fixdb]

/assign @terry1purcell

[ti-chi-bot]

@fixdb: GitHub didn't allow me to assign the following users: terry1purcell.

Note that only pingcap members with read permissions, repo collaborators and people who have commented on this issue/PR can be assigned. Additionally, issues/PRs can only have 10 assignees at the same time.
For more information please see the contributor guide

In response to this:

/assign @terry1purcell

Instructions for interacting with me using PR comments are available here. If you have questions or suggestions related to my behavior, please file an issue against the kubernetes/test-infra repository.

[Rustin170506]

I am working on this.

[Rustin170506]

I can reproduce it on the nightly build.

[Rustin170506]

Debugging note:
I tried this query:

use test;
CREATE TABLE temperature_data (temperature double);
CREATE TABLE humidity_data (humidity double);
CREATE TABLE weather_report (report_id double, report_date varchar(100));
INSERT INTO temperature_data VALUES (1.0);
INSERT INTO humidity_data VALUES (0.5);
INSERT INTO weather_report VALUES (2.0, 'test');
   SELECT
     EXISTS (
       SELECT
         FIRST_VALUE(temp_data.temperature) OVER weather_window AS first_temperature,
         MIN(report_data.report_id) OVER weather_window AS min_report_id
       FROM
         temperature_data AS temp_data
       WINDOW weather_window AS (
         PARTITION BY EXISTS (
           SELECT
             report_data.report_date AS report_date
           FROM
             humidity_data AS humidity_data
           WHERE temp_data.temperature >= humidity_data.humidity
         )
       )
     ) AS is_exist
   FROM
     weather_report AS report_data;

I found that if we remove the predicate WHERE temp_data.temperature >= humidity_data.humidity then it works well. Otherwise it panics.

It panic at:

// TryToGetChildProp will check if this sort property can be pushed or not.
// When a sort column will be replaced by scalar function, we refuse it.
// When a sort column will be replaced by a constant, we just remove it.
func (p *LogicalProjection) TryToGetChildProp(prop *property.PhysicalProperty) (*property.PhysicalProperty, bool) {
	newProp := prop.CloneEssentialFields()
	newCols := make([]property.SortItem, 0, len(prop.SortItems))
	for _, col := range prop.SortItems {
		idx := p.schema.ColumnIndex(col.Col)
		if idx == -1 {
			logutil.BgLogger().Info("column not found in schema", zap.String("column", col.Col.String()))
		}
		switch expr := p.Exprs[idx].(type) {
		case *expression.Column:
			newCols = append(newCols, property.SortItem{Col: expr, Desc: col.Desc})
		case *expression.ScalarFunction:
			return nil, false
		}
	}
	newProp.SortItems = newCols
	return newProp, true
}

We cannot find the sort item in the projection's schema. If we remove the predicate, we can find it correctly.

For example:

mysql> explain    SELECT
    ->      EXISTS (
    ->        SELECT
    ->          FIRST_VALUE(temp_data.temperature) OVER weather_window AS first_temperature,
    ->          MIN(report_data.report_id) OVER weather_window AS min_report_id
    ->        FROM
    ->          temperature_data AS temp_data
    ->        WINDOW weather_window AS (
    ->          PARTITION BY EXISTS (
    ->            SELECT
    ->              report_data.report_date AS report_date
    ->            FROM
    ->              humidity_data AS humidity_data
    ->          )
    ->        )
    ->      ) AS is_exist
    ->    FROM
    ->      weather_report AS report_data;
+--------------------------------+--------------+-----------+-------------------+-------------------------------------------------------------------------------------------------------------------+
| id                             | estRows      | task      | access object     | operator info                                                                                                     |
+--------------------------------+--------------+-----------+-------------------+-------------------------------------------------------------------------------------------------------------------+
| Projection_15                  | 10000.00     | root      |                   | Column#20                                                                                                         |
| └─Apply_17                     | 10000.00     | root      |                   | CARTESIAN left outer semi join                                                                                    |
|   ├─TableReader_19(Build)      | 10000.00     | root      |                   | data:TableFullScan_18                                                                                             |
|   │ └─TableFullScan_18         | 10000.00     | cop[tikv] | table:report_data | keep order:false, stats:pseudo                                                                                    |
|   └─Window_20(Probe)           | 100000000.00 | root      |                   | first_value(test.temperature_data.temperature)->Column#16, min(Column#15)->Column#17 over(partition by Column#14) |
|     └─Projection_21            | 100000000.00 | root      |                   | test.temperature_data.temperature, 0->Column#14, test.weather_report.report_id->Column#15                         |
|       └─TableReader_24         | 100000000.00 | root      |                   | data:TableFullScan_23                                                                                             |
|         └─TableFullScan_23     | 100000000.00 | cop[tikv] | table:temp_data   | keep order:false, stats:pseudo                                                                                    |
+--------------------------------+--------------+-----------+-------------------+-------------------------------------------------------------------------------------------------------------------+