0

I would like to use comma delimited ID string as an input into the same query.

i.e:

Set @IDs := '';

Select @IDs := CAST(GROUP_CONCAT(DISTINCT CAST(`id` as CHAR)) AS Char) AS 'ID String',
(
 Select FORMAT(Sum(`Quantity`),0)
 from 'My Table'
 Where `ID` IN (@IDs)
 ) As 'Quantity'

From `My Table`

The actual query is much more complicated, but what I need to achieve is to assign comma delimited ID string to variable and use that string in sub-query withing the IN clause.

The above is not working. I would appreciate any feedback.

Thank you!

user1431213
  • 31
  • 8

1 Answers1

0

What MySQL sees is a string value. It doesn't interpret that value as a list of values, even if the string contains commas.

Let's explain this by way of an example:

 SET @bar = 'fee,fi,fo,fum';
 SELECT * FROM mytable t WHERE t.foo IN (@bar) ;

That's equivalent to 

 SELECT * FROM mytable t WHERE t.foo = 'fee,fi,fo,fum'

MySQL is going to see @bar as a single value. It doesn't matter that it contains one or more commas, it's just a string literal; it isn't interpreted as SQL text. Those commas are just characters in a string.

One workaround to this limitation is to use "dynamic SQL", which would allow you to execute an arbitrary string as a SQL statement. You can construct/concatenate together whatever text you want into a variable, and then execute the contents of the variable as if it were a SQL statement (within a MySQL stored procedure. Your use case would require multiple statements. But be very careful with this approach, because this can potentially open the code to some nefarious SQL Injection vulnerabilities.) I'm not going to give an example, because I don't see that the problem you are trying to solve warrants that level of complexity.

It looks like the query in the question is attempting to return a result equivalent to this:

SELECT GROUP_CONCAT(DISTINCT t.id) AS `ID String`
     , FORMAT(SUM(t.quantity),0)   AS `Quantity`
  FROM `My Table` t
 WHERE t.id IS NOT NULL

Be aware that there's a limit on the length of the string returned by the GROUP_CONCAT function (the setting of the group_concat_max_len variable, and the max_allowed_packet variable). If the limit is exceeded, MySQL doesn't issue any error or warning, it completes successfully, returning a string truncated to the maximum allowed length.

It's not at all clear why a subquery would be needed, or why you'd need to check if value of the id column is one of the values included in the "list" of ids returned by GROUP_CONCAT function. (Perhaps there is something in the specification that I'm not understanding.)

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Thank you Spencer. Very detailed answer. I used Dynamic SQL, prepared statements in stored procedure and everything works just fine. Query execution dropped from around 9 seconds to just below 1. Edited group_concat_max_len and max_allowed_packet variable just in case as well. as for the safety, procedure in called from within C# code which is completely managed, so I don;t foresee any risk of SQL injection. Thanks again. – user1431213 Oct 23 '14 at 13:51