Open romaninsh opened 7 years ago
Cleaned up the query:
select
`derivedTable`.`id`,
`derivedTable`.`contact_id`,
`derivedTable`.`date`,
sum(`derivedTable`.`total_gross`) `total_gross`,
`derivedTable`.`total_net`,
sum(
(
`derivedTable`.`total_net` * (
1 - (2 / sum(`derivedTable`.`total_gross`)
)
)
)
) `due_net`
from
(
(
select
`dochead`.`id` `id`,
`dochead`.`contractor_from` `contact_id`,
`dochead`.`doc_date` `date`,
sum(`dochead`.`total_gross`) `total_gross`,
`dochead`.`total_net` `total_net`,
sum(NULL) `due_net`
from
`dochead`
inner join `invoice` as `_i` on `_i`.`dochead_id` = `dochead`.`id`
where
`dochead`.`doc_type` = 'invoice'
and `dochead`.`contractor_to` = 8
and `dochead`.`deleted` = 'N'
and `dochead`.`system_id` = 9
and `dochead`.`doc_date` <= '2017-04-18'
group by
`dochead`.`contractor_from`
)
) `derivedTable`
group by
`contact_id`
I think culpit is here:
sum(
(
`derivedTable`.`total_net` * (
1 - (2 / sum(`derivedTable`.`total_gross`)
)
)
)
) `due_net`
changing it to 1 - (2 / derivedTable
.total_gross
) woks.
So problem is when grouping using UnionLedger it replaces columns with aggregates, but some other columns may rely on the original values.
Idea to reproduce this issue:
$m->addField('type');
$m->addField('x');
$m->addExpression('x2', '[x] * 2');
$m->groupBy('type', [
'x'=>'sum([])',
'x2=>'sum([])',
]);
This would cause both x
and x2
to be replaced with aggregate fields, but during select x2
relies on x
causing query:
select sum( sum(x) * 2)
which should be
select sum( (x) * 2 )
Current work-around not to use same names for the aggregate fields.
Normally UnionModel can be grouped using a built-in method already. Even though the query seems fine, it produces the following error:
error: "SQLSTATE[HY000]: General error: 1111 Invalid use of group function" (further reported in #8)
It appears that generated SQL query is invalid containing:
Normally sum() must have an argument. The aggregation is defined:
And this is defined in a UnionModel: