[Maybe Bug] - some query can't work on subquery - V3.0.

[imiskolee]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

  select
  restaurant.party_groups.order_id,
  max(restaurant.party_groups.id  ) as id
  from
  restaurant.party_groups
  left join database_name.stores on stores.id = party_groups.store_id
  where restaurant.party_groups.store_id in
  (select case when stores.id <=> null then k.id else stores.id end as  id
  from (select stores.id,stores.parent_id from database_name.stores where  stores.id = 36359 )k
  left join database_name.stores on stores.parent_id = k.parent_id)

output

  ERROR 1105 (HY000): Can't find column database_name.stores.id in schema Column: [database_name.stores.id,database_name.stores.parent_id] Unique key: [[database_name.stores.id]]

2. What did you expect to see?

the subquery will returns a id list.

3. What did you see instead?

  ERROR 1105 (HY000): Can't find column database_name.stores.id in schema Column: [database_name.stores.id,database_name.stores.parent_id] Unique key: [[database_name.stores.id]]

its worked on version 2.1.

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

MySQL [bindo]> select tidb_version();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.0-beta-163-g76e1e5851
Git Commit Hash: 76e1e58511885109c2dc12554a32dbb3e5d47c08
Git Branch: master
UTC Build Time: 2019-03-07 07:59:51
GoVersion: go version go1.11.2 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |

schema

stores:

id int
store_id int

[imiskolee]

I think it's related to

tidb/planner/core/rule_column_pruning.go

Line 33 in d094a0f

func getUsedList(usedCols []*expression.Column, schema *expression.Schema) ([]bool, error) {

[alivxxx]

@imiskolee Thanks for your feedback! It should be a same problem with #9536.

[bash99]

@imiskolee Thanks for your feedback! It should be a same problem with #9536.

Hi, @lamxTyler , we are also hit by this bug in 3.0 GA version.
But the workaround in that issue don't works,

We have to back to MySQL right now.

[bash99]

@lamxTyler
Our SQL and Table schema is below

CREATE TABLE test_parallel_rule_check_item_relation (
id bigint(20) NOT NULL AUTO_INCREMENT,
parallel_rule_id bigint(20) DEFAULT NULL,
check_item_code varchar(50) DEFAULT NULL,
status int(11) DEFAULT '0',
create_time timestamp DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_parallel_rule_id_status (parallel_rule_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE test_check_item (
id bigint(20) NOT NULL AUTO_INCREMENT,
code varchar(50) DEFAULT NULL,
name varchar(100) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY uidx_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE test_parallel_rule (
id bigint(20) NOT NULL AUTO_INCREMENT,
parallel_rule_name varchar(100) DEFAULT NULL ,
create_id int(11) DEFAULT NULL,
create_time timestamp DEFAULT CURRENT_TIMESTAMP,
update_id int(11) DEFAULT NULL,
update_time timestamp DEFAULT CURRENT_TIMESTAMP,
is_effective int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_parallel_rule_name_effective (parallel_rule_name,is_effective),
KEY idx_is_effective (is_effective)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

SELECT
count()
FROM
(
SELECT
count()
FROM
(
SELECT
pr.*, group_concat(ci.NAME) XNAME
FROM
test_parallel_rule pr
LEFT JOIN test_parallel_rule_check_item_relation prcir ON pr.id = prcir.parallel_rule_id
LEFT JOIN test_check_item ci ON prcir.check_item_code = ci.CODE
WHERE
prcir.STATUS = 0
GROUP BY
pr.id
) temp
WHERE
temp.XNAME LIKE "%1%"
GROUP BY
temp.update_time DESC
) tmp_count;

Add ci.name to sql don't make a wordaround

SELECT
count()
FROM
(
SELECT
count()
FROM
(
SELECT
ci.name, pr.*, group_concat(ci.NAME) XNAME
FROM
test_parallel_rule pr
LEFT JOIN test_parallel_rule_check_item_relation prcir ON pr.id = prcir.parallel_rule_id
LEFT JOIN test_check_item ci ON prcir.check_item_code = ci.CODE
WHERE
prcir.STATUS = 0
GROUP BY
pr.id
) temp
WHERE
temp.XNAME LIKE "%1%"
GROUP BY
temp.update_time DESC
) tmp_count

[ghost]

I am going to close this as a duplicate of #9536 (which has been fixed). Please feel free to re-open if you have any further questions. Thanks!