This Query is being generated by Django ORM using RawSQL:

SELECT `productos`.`codigo_barras`, (
            articulos.costo_us * (1 + articulos.iva_coef)
        INNER JOIN (
          , MAX(encargosProveedor.fecha_entrega)
                    articulos, encargosProveedor_listado_articulos, encargosProveedor, itemArticulosProveedor
           = itemArticulosProveedor.articulos_id AND
           = encargosProveedor_listado_articulos.encargosproveedor_id
                GROUP BY
        AS ultimos ON =
) AS `ultimo_precio` FROM `productos`

It's giving an error

1242 - Subquery returns more than 1 row

This is the result of the subquery

| id | MAX(encargosProveedor.fecha_entrega) |
|  1 |              2019-04-17              |
|  3 |              2019-04-17              |

I read the MYSQL documentation but i can't understand why is there a problem with returning two rows. I've tried a lot of alternatives.

Where is the problem?

2 Answers

The Impaler On

Subqueries included as columns of a SELECT statement are called "scalar subqueries". A scalar subquery should be able to produce zero or one row only since its value (the scalar) will be placed in the returned row of the result set of the query, where there's room for one value only. Therefore, if a subquery returns more than a single row, it cannot be used directly as a SELECT column.

One option is to force it to produce one row at most, maybe using an aggregation function such as MAX(), MIN(), COUNT(), etc.

Another option is to join the subquery as a "table expression", where there are no restriction on the number of returned rows.

Nick On

Too long for a comment.

It's not the

SELECT, MAX(encargosProveedor.fecha_entrega) 
FROM ...

subquery that's the problem. As that is part of a JOIN expression it is allowed to return more than one row. However, since that returns more than one row, the surrounding subquery:

SELECT articulos.costo_us * (1 + articulos.iva_coef)
FROM articulos
INNER JOIN (SELECT, MAX(encargosProveedor.fecha_entrega)
            FROM ...)

will also return more than one row.

You need to figure out a way to prevent the outer subquery returning more than one row even when the inner one does, possibly by using aggregation functions such as MIN or MAX. Alternatively, you need to find a way to distinguish between rows in the inner subquery that have the same MAX(encargosProveedor.fecha_entrega) value (perhaps ordering by another value with a LIMIT 1) so that query only returns one row.