maria-discuss team mailing list archive

["Karl E. Jorgensen" <karl@xxxxxxxxxxxxxxxx>]

Hi

On Thu, Apr 28, 2016 at 05:50:42PM +0200, Tristan AURIOL wrote:
> Hi everyone,
> 
> I have a big problem with my application.
> My application execute this request :
> 
> INSERT INTO `mydb`.`service_request` (  `id_z`,  `id_app`,  `request_time`, 
> `priority` )
> (  SELECT sc.`id_z`,         sc.`id_app`,         NOW(),         0
>    FROM `mydb`.`service_connection` sc
>    JOIN `mydb`.`service` s ON ( s.`id_service` = sc.`id_service` )
>    WHERE 1=1  
>    AND sc.`id_app` = _utf8'afr'  
>    AND s.`status` = 'available'  
>    AND (  sc.`last_synchronized_time` < SUBDATE( NOW(), INTERVAL 600 SECOND ) 
> OR sc.`last_synchronized_time` IS NULL )  
>    AND sc.`nb_errors` < 2 
>    AND sc.`id_z` NOT IN (
>              SELECT sr.`id_z`
>              FROM `mydb`.`service_request` sr
>              WHERE sr.`id_app` = _utf8'afr'
>              AND sr.`status` IN ( 'waiting thu', 'waiting sez', 'in progress' )
>            )
>  ) ;
> 
> This request works well into MySQL 5.6.25-1~dotdeb+7.1 but when I run into
> 10.1.11-MariaDB-1~jessie-log I have this error:
> ERROR 1242 (21000): Subquery returns more than 1 row
> 
> Do you have an idea?

One thing that springs to mind is the brackets...

  INSERT INTO table(column,column)
  ( SELECT ...)

seems to imply that you only expect one row? Syntactially, the
bracketed select can be interpreted as a single tuple...  It is
arguably ambiguous...

I'd be inclined to try it without the brackets around the SELECT...:

  INSERT INTO table(column,column)
  SELECT ...

Hope this helps
--
Karl E. Jorgensen