Grouping LedgerUnion: General error: 1111 Invalid use of group function

[romaninsh]

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:

sum(NULL) `due_net`, 

Normally sum() must have an argument. The aggregation is defined:

'due_net'=>'sum([])',

And this is defined in a UnionModel:

$this->addExpression('due_net', ['[total_net] * [pct]', 'type'=>'money']);

[romaninsh]

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`

[romaninsh]

I think culpit is here:

sum(
    (
      `derivedTable`.`total_net` * (
        1 - (2 / sum(`derivedTable`.`total_gross`)
        )
      )
    )
  ) `due_net`

[romaninsh]

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.

[romaninsh]

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.