`SQL Error 1105` TiDB and MySQL behave differently (With Left Join) #9536

HankWang95 · 2019-03-04T06:45:27Z

Wrong SQL content:

SELECT  d.id, d.ctx, d.name, d.left_value, d.right_value, d.depth, d.leader_id, d.status, d.created_on, d.updated_on 
FROM `org_department` AS d 
LEFT JOIN `org_position` AS p ON p.department_id = d.id AND p.status = 1000 
LEFT JOIN `org_employee_position` AS ep ON ep.position_id = p.id AND ep.status = 1000 
WHERE (d.ctx = 1 AND (ep.user_id = 62 OR d.id = 20 OR d.id = 20) AND d.status = 1000)
GROUP BY d.id ORDER BY d.left_value

Error message content:

Error 1105:
SELECT d.id, d.ctx, d.name, d.left_value, d.right_value, d.depth, d.leader_id, d.status, d.created_on, d.updated_on
FROM org_department AS d
LEFT JOIN org_position AS p ON p.department_id = d.id AND p.status = 1000
LEFT JOIN org_employee_position AS ep ON ep.position_id = p.id AND ep.status = 1000
WHERE (d.ctx = 1 AND (ep.user_id = 62 OR d.id = 20 OR d.id = 20) AND d.status = 1000)
GROUP BY d.id ORDER BY d.left_value
[arguments: 1000, 1000, 1, 62, 20, 20, 1000]:
Can not find column ep.user_id in schema Column: [d.id,d.ctx,d.name,d.left_value,d.right_value,d.depth,d.leader_id,d.status,d.created_on,d.updated_on] Unique key: [[d.id],[d.id]]

My Fixed SQL:
SELECT ep.user_id, d.id, d.ctx, d.name, d.left_value, d.right_value, d.depth, d.leader_id, d.status, d.created_on, d.updated_on
......

The text was updated successfully, but these errors were encountered:

eurekaka · 2019-03-04T06:49:52Z

@HankWang95 Thanks for the report.

Could you please provide the schema of the tables involved in the query by using show create table xxx?
Do you mean after modifying the SQL as mentioned above, the error disappears?

Thanks.

HankWang95 · 2019-03-04T06:57:05Z

create SQL

CREATE TABLE `org_department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ctx` int(11) DEFAULT '0' COMMENT 'organization id',
  `name` varchar(128) DEFAULT NULL,
  `left_value` int(11) DEFAULT NULL,
  `right_value` int(11) DEFAULT NULL,
  `depth` int(11) DEFAULT NULL,
  `leader_id` bigint(20) DEFAULT NULL,
  `status` int(11) DEFAULT '1000',
  `created_on` datetime DEFAULT NULL,
  `updated_on` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `org_department_id_uindex` (`id`),
  KEY `org_department_leader_id_index` (`leader_id`),
  KEY `org_department_ctx_index` (`ctx`)
) 

CREATE TABLE `org_position` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ctx` int(11) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `left_value` int(11) DEFAULT NULL,
  `right_value` int(11) DEFAULT NULL,
  `depth` int(11) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `status` int(2) DEFAULT NULL,
  `created_on` datetime DEFAULT NULL,
  `updated_on` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `org_position_id_uindex` (`id`),
  KEY `org_position_department_id_index` (`department_id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8

CREATE TABLE `org_employee_position` (
  `hotel_id` int(11) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `position_id` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `created_on` datetime DEFAULT NULL,
  `updated_on` datetime DEFAULT NULL,
  UNIQUE KEY `org_employee_position_pk` (`hotel_id`,`user_id`,`position_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Do you mean after modifying the SQL as mentioned above, the error disappears?
re:
Yes

eurekaka · 2019-03-04T07:40:59Z

@HankWang95 thanks, we will fix this later.

Here is the minimal repro case:

mysql> SELECT  d.id FROM `org_department` AS d LEFT JOIN `org_employee_position` AS ep ON ep.position_id = d.id WHERE (ep.user_id = 62 OR d.id = 20) GROUP BY d.id;
ERROR 1105 (HY000): Can't find column ep.user_id in schema Column: [d.id] Unique key: [[d.id],[d.id]]

Looks like we have problems in column pruning.

HankWang95 · 2019-03-04T07:48:29Z

Please tell me when the bug was fixed . Thanks bro.

eurekaka · 2019-03-04T07:48:37Z

@erjiaqing Do you have interest to fix this issue?

eurekaka · 2019-03-04T07:49:38Z

@HankWang95 sure.

HankWang95 · 2019-03-04T07:49:52Z

Okey, can you tell me which file should be fix?

eurekaka · 2019-03-04T07:51:54Z

@HankWang95 I guess it should be in planner/core/rule_column_pruning.go, but I just took a brief glance at the error stack, and don't verify it yet. If you have interest to fix it also, feel free to contribute. 😃

HankWang95 · 2019-03-04T07:56:03Z

well, I'll try it, but I can't guarantee to fix it.😄

eurekaka · 2019-03-04T07:58:45Z

@HankWang95 that will be fine.

erjiaqing · 2019-03-05T09:16:42Z

@HankWang95 thanks, we will fix this later.

Here is the minimal repro case:
mysql> SELECT  d.id FROM `org_department` AS d LEFT JOIN `org_employee_position` AS ep ON ep.position_id = d.id WHERE (ep.user_id = 62 OR d.id = 20) GROUP BY d.id;
ERROR 1105 (HY000): Can't find column ep.user_id in schema Column: [d.id] Unique key: [[d.id],[d.id]]
Looks like we have problems in column pruning.

I printed the LogicalPlan after each optimize at Line 133,

tidb/planner/core/optimizer.go

Lines 121 to 136 in 63b49c9

    
           func logicalOptimize(flag uint64, logic LogicalPlan) (LogicalPlan, error) { 
        
           	var err error 
        
           	for i, rule := range optRuleList { 
        
           		// The order of flags is same as the order of optRule in the list. 
        
           		// We use a bitmask to record which opt rules should be used. If the i-th bit is 1, it means we should 
        
           		// apply i-th optimizing rule. 
        
           		if flag&(1<<uint(i)) == 0 { 
        
           			continue 
        
           		} 
        
           		logic, err = rule.optimize(logic) 
        
           		if err != nil { 
        
           			return nil, errors.Trace(err) 
        
           		} 
        
           	} 
        
           	return logic, errors.Trace(err) 
        
           }

And get the following tree, After *core.outerJoinEliminator Logic Plan:

*core.LogicalProjection(Column: [d.id] Unique key: [[d.id]])[
  *core.LogicalAggregation(Column: [d.id] Unique key: [[d.id]])[
    *core.LogicalSelection(Column: [d.id] Unique key: [[d.id],[d.id]])[
      *core.DataSource(Column: [d.id] Unique key: [[d.id],[d.id]])[]
    ]
  ]
]

Seems we have done something wrong with outer join elimination.

lzmhhh123 · 2019-03-05T12:00:51Z

It looks like we should record all the columns of schema between the Agg node and join node rather than only record the Agg columns.

…pingcap#9536)

imiskolee · 2019-03-08T09:53:12Z

@erjiaqing Are u still working on this? I just found one commit on u repo before 3 days.

erjiaqing · 2019-03-08T13:38:10Z

@erjiaqing Are u still working on this? I just found one commit on u repo before 3 days.

It is on my todo list but I found it is a bit difficult to solve and might took a few weeks.

imiskolee · 2019-03-10T16:03:48Z

@erjiaqing thanks for you work, but I am blocked the bug. Maybe we can fix it this week? and what's thing I can help ?

eurekaka · 2019-03-11T02:11:18Z

@imiskolee the code change is more extensive than the initial expectation, so it would take some time to fix this bug. You can use the workaround mentioned in the description of the issue to solve this problem temporarily.

imiskolee · 2019-03-11T13:41:18Z

@eurekaka thanks your work! the temp solution is work for me also.

wwar · 2020-04-10T23:54:38Z

This was fixed in #11160

Proof:

tidb> SELECT  d.id FROM `org_department` AS d LEFT JOIN `org_employee_position` AS ep ON ep.position_id = d.id WHERE (ep.user_id = 62 OR d.id = 20) GROUP BY d.id;
Empty set (0.00 sec)

tidb> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-183-g57db6cec7
Git Commit Hash: 57db6cec7ffad78e74b6ac6c67a2bfe9a6718d17
Git Branch: master
UTC Build Time: 2020-04-04 08:06:18
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

eurekaka added the type/bug The issue is confirmed as a bug. label Mar 4, 2019

erjiaqing mentioned this issue Mar 5, 2019

planner: check isParentColsAllFromOuterTable before eliminate an outer join (#9536) #9566

Closed

erjiaqing added a commit to erjiaqing/tidb that referenced this issue Mar 6, 2019

planner: record all the columns of schema when doing join elimination (…

c41b9a6

…pingcap#9536)

alivxxx mentioned this issue Mar 8, 2019

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

Closed

lzmhhh123 mentioned this issue Jul 11, 2019

planner: fix bugs and make it more effective in outer join elimination rule. #11160

Merged

zz-jason closed this as completed Apr 11, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

`SQL Error 1105` TiDB and MySQL behave differently (With Left Join) #9536

`SQL Error 1105` TiDB and MySQL behave differently (With Left Join) #9536

HankWang95 commented Mar 4, 2019

eurekaka commented Mar 4, 2019

HankWang95 commented Mar 4, 2019

eurekaka commented Mar 4, 2019 •

edited

Loading

HankWang95 commented Mar 4, 2019

eurekaka commented Mar 4, 2019

eurekaka commented Mar 4, 2019

HankWang95 commented Mar 4, 2019

eurekaka commented Mar 4, 2019 •

edited

Loading

HankWang95 commented Mar 4, 2019

eurekaka commented Mar 4, 2019

erjiaqing commented Mar 5, 2019

lzmhhh123 commented Mar 5, 2019

imiskolee commented Mar 8, 2019

erjiaqing commented Mar 8, 2019

imiskolee commented Mar 10, 2019

eurekaka commented Mar 11, 2019

imiskolee commented Mar 11, 2019

wwar commented Apr 10, 2020

SQL Error 1105 TiDB and MySQL behave differently (With Left Join) #9536

SQL Error 1105 TiDB and MySQL behave differently (With Left Join) #9536

Comments

HankWang95 commented Mar 4, 2019

eurekaka commented Mar 4, 2019

HankWang95 commented Mar 4, 2019

eurekaka commented Mar 4, 2019 • edited Loading

HankWang95 commented Mar 4, 2019

eurekaka commented Mar 4, 2019

eurekaka commented Mar 4, 2019

HankWang95 commented Mar 4, 2019

eurekaka commented Mar 4, 2019 • edited Loading

HankWang95 commented Mar 4, 2019

eurekaka commented Mar 4, 2019

erjiaqing commented Mar 5, 2019

lzmhhh123 commented Mar 5, 2019

imiskolee commented Mar 8, 2019

erjiaqing commented Mar 8, 2019

imiskolee commented Mar 10, 2019

eurekaka commented Mar 11, 2019

imiskolee commented Mar 11, 2019

wwar commented Apr 10, 2020

`SQL Error 1105` TiDB and MySQL behave differently (With Left Join) #9536

`SQL Error 1105` TiDB and MySQL behave differently (With Left Join) #9536

eurekaka commented Mar 4, 2019 •

edited

Loading

eurekaka commented Mar 4, 2019 •

edited

Loading