SELECT in PL expressions' is not supported by MemSQL

Help Procedural SQL
1

Hi, I am trying to do something similar to this here:

delimiter //
do 
begin
  if not exists (select 1 from module where moduleid = 1) then
    select 1 as ok;
  end if;
end //
delimiter ;

and it gives me the following error:
SELECT in PL expressions’ is not supported by MemSQL

Am I understanding correctly that this is not possible? There are of course some workarounds to achieve the desired result but will this be supported later? And/or do you have a preferred workaround method for this?

1 Like
2

Try ECHO SELECT to return the desired rowset to the console or a client app.

3

Hi Hanson, I forgot the echo select to output the rowset but the problem is not there its in the following line:

  if not exists (select 1 from module where moduleid = 1) then

this is the reason for the SELECT in PL expressions’ is not supported by MemSQL error and that was the issue that I was asking about. We are currently migrating a lot of procedure code to SingleStore and we use this a lot to check for the existence of a row in a table before doing some work and therefore I’m asking if this is possible or not with some other workaround that is the best one, also the best performance one.

4

Oh, I see. the most local and direct thing you can do is to use the SCALAR() function.

e.g.,

drop table if exists t;
create table t(a int);
insert t values(100);

delimiter //

do begin  
if scalar("select a from t", query(a int)) = 100 then 
  echo select "true";
else 
  echo select "false";
end if;
end
//
delimiter ;

/* result 

+------+
| true |
+------+
| true |
+------+
*/

You could also factor it out onto another line, like:

delimiter //
do 
declare f int;
begin  
select a into f from t;
if f then
  echo select "true";
else 
  echo select "false";
end if;
end
//
delimiter ;

I’ll make a note of this as a feature request. What database software are you migrating from?

5

I see in your original example you had a NOT EXISTS before the query. SCALAR and SELECT INTO varName require a result to be returned or they will error.

So you could instead look for a zero count result like this:

delimiter //
do 
declare f int; 
begin
  select count(*) into f from t where a = 200;
  if f = 0 then
    echo select 1 as ok;
  end if;
end //
delimiter ;
6

Hi Hanson, thanks a lot for the info, this is a better approach than the one that I was using.

I’m migrating from MSSQL but still in POC phase, our application has over 100.000 lines of SQL code and I’m also getting backup from SingleStore to help me out but trying also to post questions here to help the community :slight_smile:

1 Like