SELECT with sequential counter does not accept LEFT JOIN

Question

Because by making a SELECT that returns a Sequence number doesn’t work if you have LEFT JOIN?

SELECT @ROW_NUMBER:=@ROW_NUMBER+1 AS ROW_NUMBER, 
       P.PEDIDOID
FROM PEDIDO AS P, 
     (SELECT @ROW_NUMBER:=0) AS T 
LEFT JOIN PEDIDOPRODUTO AS PP ON PP.PEDIDOID = P.PEDIDOID 
ORDER BY P.PEDIDOID DESC;

Answer 1

The reason for the error is in mysql manual:

INNER JOIN and , (comma) are semantically equivalent in the Absence of a Join condition: Both Produce a Cartesian product between the specified Tables (that is, each and Every Row in the first table is joined to each and Every Row in the Second table).

However, the precedence of the comma Operator is Less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other Join types when there is a Join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur.

(...)

Now JOIN has Higher precedence, so the Expression is Interpreted as (t1, (t2 JOIN t3)).

That is, mixing an implicit Join (with comma) with other explicit joins (INNER, LEFT, etc.) may cause problems as the comma precedence is less than that of explicit joins.

In the case of your query, the joins are interpreted as (P, (T LEFT JOIN PP ON...)), and that doesn’t make sense, because his condition in the ON does not associate T to PP, and yes P to PP.

The solution is simple, just use an explicit Join:

SELECT @ROW_NUMBER:=@ROW_NUMBER+1 AS ROW_NUMBER, 
       P.PEDIDOID
FROM PEDIDO AS P 
     INNER JOIN (SELECT @ROW_NUMBER:=0) AS T 
     LEFT JOIN PEDIDOPRODUTO AS PP ON PP.PEDIDOID = P.PEDIDOID 
ORDER BY P.PEDIDOID DESC;