PDO with mysql user-defined variable returns null

Question

why does this query returns $row['percentualeScarico'] = null (other calculated fields totaleLiquidato, totaleIncarichiRicevuti, totaleIncarichiEvasi, are ok) ?

SELECT 
    SUM(importoLiquidato) as totaleLiquidato,
    @totaleIncarichiRicevuti := COUNT(*) as totaleIncarichiRicevuti,
    @totaleIncarichiEvasi := SUM(if(dataRestituzione IS NOT null,1,0)) as totaleIncarichiEvasi,
    TRUNCATE((@totaleIncarichiEvasi/@totaleIncarichiRicevuti)*100,2) as percentualeScarico
FROM incarico
LEFT JOIN compagnia ON incarico.idCompagnia = compagnia.id
LEFT JOIN evento ON incarico.idTipoEvento = evento.id
WHERE (dataIncarico BETWEEN :daDataIncarico AND :aDataIncarico)

$stmt = $this->conn->prepare( $query )

[here I do the bindings]

$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);

If I try to execute the same query in phpstorm sql console, everything is ok.

Thank you

Alex

why don't you just select @totaleIncarichiEvasi and@totaleIncarichiRicevuti and see what's wrong with them? why don't you post a code where these variables are set? — Your Common Sense
– Your Common Sense, Commented Jul 13, 2016 at 15:58
did you check if @totaleIncarichiRicevuti is non-zero? If it's zero, you get a div-by-zero and the result becomes null. — Marc B
– Marc B, Commented Jul 13, 2016 at 15:58
@YourCommonSense @totaleIncarichiEvasi and @totaleIncarichiRicevuti are correctly fetched — alzambo
– alzambo, Commented Jul 13, 2016 at 16:40
it won't be at the end of the query, but you're doing aggregate functions, and those results are not available until after ALL rows have been processed. mysql cannot time travel, so when you do your division, that var is undefined and null. — Marc B
– Marc B, Commented Jul 13, 2016 at 16:42

Uueerdo · Accepted Answer · 2016-07-13 16:32:08Z

1

How session variables are handled by MySQL can be less than intuitive at times. I recommend not using them across clauses (i.e. not in SELECT and WHERE, or HAVING, etc... in the same query for example) and not when dealing with grouping/aggregation.

My guess is that MySQL is calculating TRUNCATE((@totaleIncarichiEvasi/@totaleIncarichiRicevuti)*100,2) before the aggregations are assigned to those variables. Normally, selections are processed from "left to right", but since that calculation on it's own involves no aggregation, it is likely processed along with other non-aggregated (and non-grouped) fields, before the aggregations using first value "found".

If you want to avoid the duplication of the aggregate calculations, the simplest way I've found is to make the original query a singular subquery and use the aggregation results in the outer query, in this manner:

SELECT q.*, [calculation using aggregate field(s)]
FROM ([original query]) AS q

answered Jul 13, 2016 at 16:32

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

alzambo Over a year ago

Thanks, that's what i've done:

SELECT   totaleLiquidato,   totaleIncarichiRicevuti,   totaleIncarichiEvasi,   TRUNCATE((totaleIncarichiEvasi / totaleIncarichiRicevuti)*100,2) as percentualeScarico FROM (     SELECT       SUM(importoLiquidato) as totaleLiquidato,       COUNT(*) as totaleIncarichiRicevuti,       SUM(if(dataRestituzione IS NOT null,1,0)) as totaleIncarichiEvasi     FROM incarico       LEFT JOIN compagnia ON incarico.idCompagnia = compagnia.id       LEFT JOIN evento ON incarico.idTipoEvento = evento.id) as subquery

Collectives™ on Stack Overflow

PDO with mysql user-defined variable returns null

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related