[MCOL-800] INSERT...SELECT in a prepared statement falls back to vtable disable Created: 2017-07-03  Updated: 2020-08-25  Resolved: 2017-07-26

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.0.9, 1.1.0
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: Andrew Hutchings (Inactive) Assignee: Andrew Hutchings (Inactive)
Resolution: Not a Bug Votes: 0
Labels: None

Sprint: 2017-14, 2017-15

 Description   

For the prepared statement section of vtable sql_parse we check for SQLCOM_SELECT but not SQLCOM_INSERT_SELECT. This means that prepared statements with INSERT...SELECT fall through to disable vtable.

Test:

create table tc1 (a int) engine=innodb;
 
insert into tc1 values (1),(2),(3);
 
create table tc2 (a int) engine=columnstore;
 
set @psql="insert into tc2 select * from tc1";
 
prepare run_sql from @psql;
 
execute run_sql;



 Comments   
Comment by Andrew Hutchings (Inactive) [ 2017-07-11 ]

Pull request for 1.0 and 1.1 open.

For QA:
This is a performance issue due to not using vtable for INSERT...SELECT inside a prepared statement. This should now use cpimport internally instead of single row inserts. There should be a performance improvement when there is a large number of rows.

Comment by Daniel Lee (Inactive) [ 2017-07-14 ]

Build tested: Github source for 1.0.10 and 1.1.0

1.0.10-1

[root@localhost mariadb-columnstore-server]# git show
commit 435972e50ee33911ce39696ce101d1cd23ed9c2b
Merge: b1d1ca1 5d3fcfe
Author: David.Hall <david.hall@mariadb.com>
Date: Wed Jul 12 13:07:55 2017 -0500

[root@localhost mariadb-columnstore-engine]# git show
commit 3501c1a17a920ee765c6255c5a5fd8c64fed7c8e
Author: david hill <david.hill@mariadb.com>
Date: Wed Jul 12 09:52:28 2017 -0500

1.1.0-1

[root@localhost mariadb-columnstore-server]# git show
commit 8e07495da650d922c4d1f3f09d77382168132b11
Merge: 80e57a8 c27e1e5
Author: David.Hall <david.hall@mariadb.com>
Date: Wed Jul 12 13:07:42 2017 -0500

[root@localhost mariadb-columnstore-engine]# git show
commit d1386928dcaf1d9acc92ab11e2415c808c75dd49
Author: david hill <david.hill@mariadb.com>
Date: Thu Jul 13 11:20:08 2017 -0500

Test performed:

1. create orders and orders1 tables
2. cpimport 10gb source into orders1.
3. execute the following statements

set @psql="insert into orders1 select * from orders";
prepare run_sql from @psql;
execute run_sql;

insert_select timing for 10gb ORDERS table

1.0.9-1 88.34 sec
1.1.0-1 85.44 seconds
1.0.10-1 89.37 sec

The timings were virtually the same among all three releases. I disabled infinidb_use_import_for_batchinsert and the same test took 444.63 seconds, about 5 times longer.

Comment by Andrew Hutchings (Inactive) [ 2017-07-17 ]

It turns out this is a non-issue but the code doesn't hurt being there as it will stop the parser re-entry.

Comment by Andrew Hutchings (Inactive) [ 2017-07-26 ]

Closing this due to previous comment.

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