query failed with "runtime error: index out of range"

[zhaojunliRain]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.
   SELECT sum((salary/(select sum(salary) from (SELECT
   e.employee_id as employee_id,
   e.job_id as job_id,
   e.manager_id as manager_id,
   e.department_id as department_id,
   d.location_id as location_id,
   l.country_id as country_id,
   e.first_name as first_name,
   e.last_name as last_name,
   e.phone_number as phone_number,
   e.hire_date as hire_date,
   e.salary as salary,
   e.commission_pct as commission_pct,
   CONCAT(CONCAT(f.first_name, ' '), f.last_name) as manager_name,
   d.department_name as department_name,
   j.job_title as job_title,
   l.city as city,
   l.state_province as state_province,
   c.country_name as country_name,
   r.region_name as region_name,
   j.min_salary as job_min_salary,
   j.max_salary as job_max_salary,
   e.longitude as longitude,
   e.latitude as latitude,
   e.indexno as indexno
   FROM
   a_ivt_employees e,
   a_ivt_employees f,
   a_ivt_departments d,
   a_ivt_jobs j,
   a_ivt_locations l,
   a_ivt_countries c,
   a_ivt_regions r
   WHERE
   e.department_id = d.department_id
   AND d.location_id = l.location_id
   AND l.country_id = c.country_id
   AND c.region_id = r.region_id
   AND j.job_id = e.job_id
   AND e.manager_id = f.employee_id
   UNION ALL
   SELECT
   e.employee_id as employee_id,
   e.job_id as job_id,
   e.manager_id as manager_id,
   e.department_id as department_id,
   d.location_id as location_id,
   l.country_id as country_id,
   e.first_name as first_name,
   e.last_name as last_name,
   e.phone_number as phone_number,
   e.hire_date as hire_date,
   e.salary as salary,
   e.commission_pct as commission_pct,
   null as manager_name,
   d.department_name as department_name,
   j.job_title as job_title,
   l.city as city,
   l.state_province as state_province,
   c.country_name as country_name,
   r.region_name as region_name,
   j.min_salary as job_min_salary,
   j.max_salary as job_max_salary,
   e.longitude as longitude,
   e.latitude as latitude,
   e.indexno as indexno
   FROM
   a_ivt_employees e,
   a_ivt_departments d,
   a_ivt_jobs j,
   a_ivt_locations l,
   a_ivt_countries c,
   a_ivt_regions r
   WHERE
   e.department_id = d.department_id
   AND d.location_id = l.location_id
   AND l.country_id = c.country_id
   AND c.region_id = r.region_id
   AND j.job_id = e.job_id
   AND e.manager_id is null) tmp_1187211921))) AS c0__sum, job_title AS job_title__group FROM (SELECT
   e.employee_id as employee_id,
   e.job_id as job_id,
   e.manager_id as manager_id,
   e.department_id as department_id,
   d.location_id as location_id,
   l.country_id as country_id,
   e.first_name as first_name,
   e.last_name as last_name,
   e.phone_number as phone_number,
   e.hire_date as hire_date,
   e.salary as salary,
   e.commission_pct as commission_pct,
   CONCAT(CONCAT(f.first_name, ' '), f.last_name) as manager_name,
   d.department_name as department_name,
   j.job_title as job_title,
   l.city as city,
   l.state_province as state_province,
   c.country_name as country_name,
   r.region_name as region_name,
   j.min_salary as job_min_salary,
   j.max_salary as job_max_salary,
   e.longitude as longitude,
   e.latitude as latitude,
   e.indexno as indexno
   FROM
   a_ivt_employees e,
   a_ivt_employees f,
   a_ivt_departments d,
   a_ivt_jobs j,
   a_ivt_locations l,
   a_ivt_countries c,
   a_ivt_regions r
   WHERE
   e.department_id = d.department_id
   AND d.location_id = l.location_id
   AND l.country_id = c.country_id
   AND c.region_id = r.region_id
   AND j.job_id = e.job_id
   AND e.manager_id = f.employee_id
   UNION ALL
   SELECT
   e.employee_id as employee_id,
   e.job_id as job_id,
   e.manager_id as manager_id,
   e.department_id as department_id,
   d.location_id as location_id,
   l.country_id as country_id,
   e.first_name as first_name,
   e.last_name as last_name,
   e.phone_number as phone_number,
   e.hire_date as hire_date,
   e.salary as salary,
   e.commission_pct as commission_pct,
   null as manager_name,
   d.department_name as department_name,
   j.job_title as job_title,
   l.city as city,
   l.state_province as state_province,
   c.country_name as country_name,
   r.region_name as region_name,
   j.min_salary as job_min_salary,
   j.max_salary as job_max_salary,
   e.longitude as longitude,
   e.latitude as latitude,
   e.indexno as indexno
   FROM
   a_ivt_employees e,
   a_ivt_departments d,
   a_ivt_jobs j,
   a_ivt_locations l,
   a_ivt_countries c,
   a_ivt_regions r
   WHERE
   e.department_id = d.department_id
   AND d.location_id = l.location_id
   AND l.country_id = c.country_id
   AND c.region_id = r.region_id
   AND j.job_id = e.job_id
   AND e.manager_id is null) tmp_1187211921 WHERE city IN ('Oxford') GROUP BY job_title LIMIT 1000

2. What did you expect to see?
   I expect this sql run successfully. It works on mysql.

3. What did you see instead?
   It failed with error: 执行sql失败，原因 runtime error: index out of range

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   / # ./tidb-server -V
   Release Version: v1.1.0-beta-48-g86af180
   Git Commit Hash: 86af180
   Git Commit Branch: master
   UTC Build Time: 2018-03-06 03:56:21
   GoVersion: go version go1.10 linux/amd64
   TiKV Min Version: 1.1.0-dev.2

[zz-jason]

@zhaojunliRain Thanks for your feed back! Could you please provide us the these materials to help us locate the problem:

1. tidb log
2. create table statement

[zhaojunliRain]

I do not know how to find tidb log. You can just create these tables and insert some values, then use a db client to run the SQL I pasted.
CREATE TABLE a_ivt_countries (country_id CHAR(2) NOT NULL, country_name VARCHAR(40), region_id NUMERIC);

CREATE TABLE a_ivt_regions (region_id NUMERIC NOT NULL, region_name VARCHAR(25));

CREATE TABLE a_ivt_locations (location_id NUMERIC(4) NOT NULL, street_address VARCHAR(40), postal_code VARCHAR(12), city VARCHAR(30) NOT NULL, state_province VARCHAR(25), country_id CHAR(2));

CREATE TABLE a_ivt_jobs (job_id VARCHAR(10) NOT NULL, job_title VARCHAR(35) NOT NULL, min_salary NUMERIC(6), max_salary VARCHAR(20));

CREATE TABLE a_ivt_departments (department_id NUMERIC(4) NOT NULL, department_name VARCHAR(30) NOT NULL, manager_id VARCHAR(10), location_id NUMERIC(4));

CREATE TABLE a_ivt_employees (employee_id NUMERIC(6) NOT NULL, first_name VARCHAR(20), last_name VARCHAR(25) NOT NULL, email VARCHAR(25) NOT NULL, phone_number VARCHAR(20), hire_date VARCHAR(20) NOT NULL, job_id VARCHAR(10) NOT NULL, salary NUMERIC(8,2), commission_pct NUMERIC(2,2), manager_id NUMERIC(6), department_id NUMERIC(4), longitude NUMERIC(15,12), latitude NUMERIC(15,12), indexno NUMERIC(2));

[zimulala]

@zhaojunliRain
You can do the operation of ps -ef|grep tidb to find the path of TiDB log. Then you can do the operation of grep "index out of range" path/tidb.log -C 50.

[zhaojunliRain]

I am using pingcap/tidb image to run tidb, and I can not find tidb.log in my container. Here is the snapshot from my db client.

[zz-jason]

@zhaojunliRain we can not reproduce this problem in our master branch, could you please try our master version to check whether this problem still exists ?

[bugme1024]

I meet the same problem too.

run the long sql with joins;

Release Version: v2.1.17
Git Commit Hash: 603f0b9
Git Branch: HEAD
UTC Build Time: 2019-09-11 11:49:06
GoVersion: go version go1.12 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false

[bugme1024]

[err="runtime error: index out of range\ngithub.com/pingcap/tidb/executor.(*HashJoinExec).finishOuterFetcher\n\t/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/executor/join.go:343\ngithub.com/pingcap/tidb/util.WithRecovery.func1\n\t/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/util/misc.go:73\nruntime.gopanic\n\t/usr/local/go/src/runtime/panic.go:522\nruntime.panicindex\n\t/usr/local/go/src/runtime/panic.go:44\ngithub.com/pingcap/tidb/util/chunk.(*Chunk).AppendPartialRow\n\t/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/util/chunk/chunk.go:304\ngithub.com/pingcap/tidb/util/chunk.(*Chunk).AppendRow\n\t/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/util/chunk/chunk.go:290\ngithub.com/pingcap/tidb/executor.(*TopNExec).Next\n\t/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/executor/sort.go:258\ngithub.com/pingcap/tidb/executor.Next\n\t/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/executor/executor.go:186\ngithub.com/pingcap/tidb/executor.(*ProjectionExec).unParallelExecute\n\t/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/executor/projection.go:176\ngithub.com/pingcap/tidb/executor.(*ProjectionExec).Next\n\t/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/executor/projection.go:163\ngithub.com/pingcap/tidb/executor.Next\n\t/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/executor/executor.go:186\ngithub.com/pingcap/tidb/executor.(*HashJoinExec).fetchOuterChunks\n\t/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/executor/join.go:206\ngithub.com/pingcap/tidb/executor.(*HashJoinExec).fetchOuterAndProbeHashTable.func1\n\t/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/executor/join.go:327\ngithub.com/pingcap/tidb/util.WithRecovery\n\t/home/jenkins/workspace/release_tidb_2.1/go/src/github.com/pingcap/tidb/util/misc.go:81\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1337"]

[zz-jason]

@lysu PTAL