CODEWITHSUNDEEP
mysqldjangodatabasesubquerydjango-orm
Hernan
Hernan

Reputation: 1148

Why can't this subquery return more than one row?

This Query is being generated by Django ORM using RawSQL:

SELECT `productos`.`codigo_barras`, (
        SELECT
            articulos.costo_us * (1 + articulos.iva_coef)
        FROM
            articulos
        INNER JOIN (
                SELECT 
                    articulos.id, MAX(encargosProveedor.fecha_entrega)
                FROM
                    articulos, encargosProveedor_listado_articulos, encargosProveedor, itemArticulosProveedor
                WHERE
                    articulos.id = itemArticulosProveedor.articulos_id AND
                    encargosProveedor.id = encargosProveedor_listado_articulos.encargosproveedor_id
                GROUP BY
                    articulos.producto_id
            )
        AS ultimos ON articulos.id = ultimos.id
) 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?

Upvotes: 0

Views: 2011

Answers (2)

Nick
Nick

Reputation: 147146

Too long for a comment.

It's not the 

SELECT articulos.id, 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 articulos.id, 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.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48770

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.

Upvotes: 1

Related Questions