Welcome Guest, Not a member yet? Register   Sign In
Query builder count_all_results breaking change on group by statements
#1

Hi CI Forum,

I'm just copying what I've typed into the GH issue tracker on https://github.com/bcit-ci/CodeIgniter/issues/4842

I've just upgraded from CI 3.0.4 all the way to 3.1.0 with all the intermediary upgrade steps taken.
There is a specific query which works previously and breaks now (MySQL) when using count_all_results function.

In 3.0.4 , count_all_results compiled into the following query which works:

Code:
SELECT COUNT(*) AS numrows
FROM t_inventory sc
LEFT JOIN t_inventory_transaction it ON sc.item_id = it.item_id
LEFT JOIN t_site_location l ON l.location_id = it.whse_id
LEFT JOIN t_inventory_category ic ON ic.category_id = sc.item_category_id
LEFT JOIN t_inventory_group ig ON ig.group_id = ic.item_group
WHERE it.whse_id = '1'
GROUP BY sc.item_id, sc.item_name, sc.uom, sc.item_status, ic.category_name, ig.group_name, it.whse_id, l.location_name
In 3.1.0 , it creates the following query which resulted in MySQL Error due to duplicate column being returned in joined tables

Code:
SELECT COUNT(*) AS numrows FROM
( SELECT * FROM t_inventory sc
LEFT JOIN t_inventory_transaction it ON sc.item_id = it.item_id
LEFT JOIN t_site_location l ON l.location_id = it.whse_id
LEFT JOIN t_inventory_category ic ON ic.category_id = sc.item_category_id
LEFT JOIN t_inventory_group ig ON ig.group_id = ic.item_group
WHERE it.whse_id = '1'
GROUP BY sc.item_id, sc.item_name, sc.uom, sc.item_status, ic.category_name, ig.group_name, it.whse_id, l.location_name ) CI_count_all_results
SQL Error

Code:
Error Code: 1060. Duplicate column name 'item_id'
(item_id is in two tables)
Notice the difference is in the new version it creates a subquery, the reason for this is the change in count_all_results function :

In 3.0.4 - DB_query_builder.php , line 1382

Code:
$result = ($this->qb_distinct === TRUE OR ! empty($this->qb_orderby))
? $this->query($this->_count_string.$this->protect_identifiers('numrows')."\nFROM (\n".$this->_compile_select()."\n) CI_count_all_results")
: $this->query($this->_compile_select($this->_count_string.$this->protect_identifiers('numrows')));

In 3.1.0 - DB_query_builder.php , line 1398

Code:
$result = ($this->qb_distinct === TRUE OR ! empty($this->qb_groupby))
? $this->query($this->_count_string.$this->protect_identifiers('numrows')."\nFROM (\n".$this->_compile_select()."\n) CI_count_all_results")
: $this->query($this->_compile_select($this->_count_string.$this->protect_identifiers('numrows')));

So there is a new condition which checks for group by statements, and the condition is met (unlike the previous version).

A workaround currently we use is to revert that particular IF statement only to not check for group by statement.
Reply
#2

If this is a bug, it belongs on github and probably not here.
If it is a problem using the framework, then it belongs on here and *not* on github.
If you are not sure if it is a bug, it is better to raise it here, for community input.

It is not cool to cross-post, especially verbatim.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB