[MCOL-260] update two tables with a subquery fails Created: 2016-08-03  Updated: 2016-11-21  Resolved: 2016-11-21

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.0.1
Fix Version/s: 1.0.5

Type: Bug Priority: Minor
Reporter: David Hall (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

Centos 6.5, any Linux

Issue Links:
Duplicate
is duplicated by MCOL-288 test001 vtabledmlcol2col.sql fails Closed
Sprint: 2016-20, 2016-21, 2016-22

 Description   

Consider the following using the dml database:

update orders, customer set c_comment = o_clerk, c_acctbal=c_acctbal+o_totalprice where c_custkey = o_orderkey and o_orderkey in (select l_orderkey from lineitem where l_orderkey < 100 order by l_orderkey, l_linenumber);

The result is:
ERROR 1815 (HY000): Internal error: IDB-2004: Cannot connect to ExeMgr

The error is misleading, as connection to ExeMgr is not the problem. That's the error spit out by getSelectPlan() in ha_calpont_execplan.cpp when a ... exception is caught. The real problem is that the subquery is being sent in with the regular table list, but with a NULL name. The name is used without test. That's all fine, but it shouldn't be there at all. It belongs elsewhere. Ignoring the entry leads to other problems.

We need to figure out how it used to work and then find a fix.

The query is from test005 and is in working_dml/misc/vtabledmlcol2col.sql

 Comments   
Comment by Andrew Hutchings (Inactive) [ 2016-10-20 ]

The table it is tripping up on is an unnamed temporary table with the alias <subquery2>. This query generates a similar execution plan with the temporary able but works fine:

select * from orders, customer where c_custkey = o_orderkey and o_orderkey in (select l_orderkey from lineitem where l_orderkey < 100 order by l_orderkey, l_linenumber);

I'll try and figure out how that is processed in the select query so the update subquery processing can do something similar.

Comment by Andrew Hutchings (Inactive) [ 2016-10-20 ]

The difference is the semijoin optimization flag. We hard turn it off for select but not for update

Comment by Andrew Hutchings (Inactive) [ 2016-10-20 ]

Commit ready. Set to stalled state until develop branch is unfrozen

Comment by Daniel Lee (Inactive) [ 2016-11-21 ]

Build verified: 1.0.5-1

Name : mariadb-columnstore-platform
Version : 1.0.5
Release : 1
Architecture: x86_64
Install Date: Mon 21 Nov 2016 05:18:03 PM CST
Group : Applications/Databases
Size : 9990104

Reproduced the issue in 1.0.4-1 and verified it has been fixed in 1.0.5-1.

MariaDB [tpch1c]> update orders, customer set c_comment = o_clerk, c_acctbal=c_acctbal+o_totalprice where c_custkey = o_orderkey and o_orderkey in (select l_orderkey from lineitem where l_orderkey < 100 order by l_orderkey, l_linenumber);
Query OK, 27 rows affected (0.29 sec)
Rows matched: 0 Changed: 0 Warnings: 0

MariaDB [tpch1c]> select distinct l_orderkey from lineitem where l_orderkey < 100 order by l_orderkey
-> ;
------------

l_orderkey

------------

1
2
3
4
5
6
7
32
33
34
35
36
37
38
39
64
65
66
67
68
69
70
71
96
97
98
99

------------
27 rows in set (0.12 sec)

Generated at Thu Feb 08 02:19:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.