[MCOL-391] cursors in stored procedures over columnstore tables not supported Created: 2016-11-07  Updated: 2021-01-15  Resolved: 2021-01-15

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.0.4
Fix Version/s: 5.5.1

Type: New Feature Priority: Major
Reporter: David Thompson (Inactive) Assignee: Todd Stoffel (Inactive)
Resolution: Fixed Votes: 9
Labels: None

Epic Link: ColumnStore Compatibility Improvements

 Description   

You can create a stored procedure that creates cursors over column store tables, however the stored procedure will fail to execute with the following error:
ERROR 1178 (42000): The storage engine for the table doesn't support This stored procedure syntax is not supported by Columnstore in this version

This was also unsupported in InfiniDB.

Couple cases to verify / repro.

Basic table setup:

create table t1(i int) engine=columnstore;
 
insert into t1 values (1), (2);

Insert select:

delimiter //
 
create or replace procedure p()  begin create table tmp(i int) engine=memory; insert into tmp(i) select sum(i) from t1; select * from tmp; drop table tmp; end;//
 
call p//
 
ERROR 1178 (42000): The storage engine for the table doesn't support This stored procedure syntax is not supported by Columnstore in this version

cursor case:

create or replace procedure q()  begin declare done int default false; declare x int; declare c1 cursor for select i from t1; declare continue handler for not found set done = true; open c1; rl:loop fetch c1 into x; if done then leave rl; end if; end loop; close c1; end;//
 
 call q//
 
ERROR 1178 (42000): The storage engine for the table doesn't support This stored procedure syntax is not supported by Columnstore in this version

In both cases, making table t1 an innodb table will result in the call invocation completing.

 Comments   
Comment by dic (Inactive) [ 2017-06-28 ]

This issue is the same on MariaDB Columnstore 1.0.9:
ERROR 1178 (42000): The storage engine for the table doesn't support This stored procedure syntax is not supported by Columnstore in this version

Comment by David Thompson (Inactive) [ 2017-06-28 ]

nguyen while not a great workaround one thing you can do if you need to do something like this in a stored proc is to create a temp table, insert select the data you need in there and then use a cursor over that. Let me know if that helps.

Comment by dic (Inactive) [ 2017-06-28 ]

Dear Thompson,

Your solution is good, if I create a temp table use InnoDB engine. However, in this case, I want to test stored procedure on Columnstore engine.

Comment by David Thompson (Inactive) [ 2017-06-28 ]

yes, you are correct. I thought i had a workaround for this previously when filing this but i didn't save it or maybe i remembered incorrectly. Creating a temp table from a cs query does work as a standalone statement so not sure if this could work for you.

Note that joining a temp table with a columnstore table is not possible because cross engine join uses a secondary connection and so it is not visible there.

Comment by Michael Golisch [ 2017-09-21 ]

we face the same problem.
We basicaly want to move data from our staging area to the productive schema/database using a procedure like this :

https://gist.github.com/mgolisch/4b48620e715fd8f7ead3831a0fdab408

Is there any ETA/Resolution Plan for this?
And in the meantime:
Is there any workarounds that dont involve copying all data to a innodb table?
And even then , would that work at all? From what i understand it seem to be a problem to do insert into select from a columnstore table, so how would i get the data into a innodb table from within the stored procedure in the first place.

Comment by David Thompson (Inactive) [ 2017-09-21 ]

mgolisch thanks for providing an example. Your cursor example is relatively simple, couldn't you achieve the same with an insert into select sql statement? If you run autocommit on then internally it would be converted to cpimport for speed, even with transactions this should be faster too.

Back to this bug we will get to it and the increasing vote count helps us prioritize it higher.

Comment by Michael Golisch [ 2017-09-22 ]

David Thompsonl thank you for your answer.
The problem is not the cursor but rather the insert into select statement we try to execute from within the stored procedure.
The actual meta table contains alot more statements like that(for copying data, altering the detsination tables and other stuff)
Yes we can execute those statements on the mysql commandline just fine but this is basicaly about getting our data update procedure in shape before we go live with our new app.
We realy would like to have that sorted out, as the manual data update scenario seems like a bad idea.
Not sure this is the correct place for discussing stuff like that.
Maybe i should open a post on your google group and ask for ideas on how to do this instead untill this issue is resolved.

Comment by David Thompson (Inactive) [ 2017-09-22 ]

Yes, that might a be a good idea, it's possible someone else might have a creative solution out there. With 1.1 (in beta) we are also introducing a native write api so if you are open to writing in c+, python, or java you could move your logic out to an external language? At this stage we only have c+ and a work in progress version of the python binding but that will improve over the next few weeks.

Unfortunately this jira is not an easy enhancement.

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