sql is not compiled properly

following sql is not properly compiled.

PHP Code:

       
        $itemsPfx = $this->db->dbprefix('invoice_items');
        $invPfx = $this->db->dbprefix('invoices');
        $this->db->select('name, ' . $this->db->dbprefix('invoices') . '.currency_symbol');
        $this->db->select_sum('amount * quantity', 'amount', FALSE);
    
        $this->db->select('SUM( ROUND((' . $itemsPfx . '.amount*' . $invPfx . '.tax1_rate/100 * ' . $itemsPfx . '.quantity) / 0.05 , 0) * 0.05) as tax1_collected', FALSE);
        $this->db->select('SUM( ROUND((' . $itemsPfx . '.amount*' . $invPfx . '.tax2_rate/100 * ' . $itemsPfx . '.quantity) / 0.05 , 0) * 0.05) as tax2_collected', FALSE);
        $this->db->select('SUM(ROUND((' . $itemsPfx . '.amount * ' .$invPfx . '.accounting_invoice_exchange_rate * ' . $itemsPfx . '.quantity + ((' . $itemsPfx . '.amount * ' . $invPfx . '.accounting_invoice_exchange_rate) *' . $itemsPfx . '.quantity * (' . $invPfx . '.tax1_rate/100 + ' . $invPfx . '.tax2_rate/100) * ' . $itemsPfx . '.taxable)) / 0.05, 0 ) * 0.05 ) AS accounting_total_with_tax', FALSE);
        
        $this->db->join('invoices', 'invoices.client_id = clients.id');
        $this->db->join('invoice_items', 'invoices.id = invoice_items.invoice_id');
        $this->db->where('dateIssued >= "' . $start_date . '" and dateIssued <= "' . $end_date . '"');
        $this->db->order_by($invPfx.'.currency_type,' . 'clients.name');
        $this->db->group_by('name');

        return $this->db->get('clients'); 


resulting in following sql

SELECT `name`, `bamboo_invoices`.`currency_symbol`, SUM(`amount` * quantity) AS amount, SUM( ROUND((bamboo_invoice_items.amount*bamboo_invoices.tax1_rate/100 * bamboo_invoice_items.quantity) / 0.05, 0) * 0.05) as tax1_collected, SUM( ROUND((bamboo_invoice_items.amount*bamboo_invoices.tax2_rate/100 * bamboo_invoice_items.quantity) / 0.05, 0) * 0.05) as tax2_collected, SUM(ROUND((bamboo_invoice_items.amount * bamboo_invoices.accounting_invoice_exchange_rate * bamboo_invoice_items.quantity + ((bamboo_invoice_items.amount * bamboo_invoices.accounting_invoice_exchange_rate) *bamboo_invoice_items.quantity * (bamboo_invoices.tax1_rate/100 + bamboo_invoices.tax2_rate/100) * bamboo_invoice_items.taxable)) / 0.05, `0` ) * 0.05 ) AS accounting_total_with_tax FROM (`bamboo_clients`) JOIN `bamboo_invoices` ON `bamboo_invoices`.`client_id` = `bamboo_clients`.`id` JOIN `bamboo_invoice_items` ON `bamboo_invoices`.`id` = `bamboo_invoice_items`.`invoice_id` WHERE `dateIssued` >= "2014-12-31" and dateIssued <= "2014-12-31" GROUP BY `name` ORDER BY `bamboo_invoices`.`currency_type`, `bamboo_clients`.`name`

The , 0 in the round function gets some quoted.

Code:

$this->db->select('SUM(ROUND((' . $itemsPfx . '.amount * ' .$invPfx . '.accounting_invoice_exchange_rate * ' . $itemsPfx . '.quantity + ((' . $itemsPfx . '.amount * ' . $invPfx . '.accounting_invoice_exchange_rate) *' . $itemsPfx . '.quantity * (' . $invPfx . '.tax1_rate/100 + ' . $invPfx . '.tax2_rate/100) * ' . $itemsPfx . '.taxable)) / 0.05, 0 ) * 0.05 ) AS accounting_total_with_tax', FALSE);

Hmmm - I count six opening parentheses, but seven closing parentheses.

James Parry
Project Lead

the sql works. there are 7 opening and 7 closing parentheses.

I upgraded from 1.7.x to 2.2 and realised that the sql which used to work suddenly doesn't in CI 2.2.
If I remove the ,0 the sql is compiled properly.

You probably need to change each of your select('SUM(...' statements to select_sum('...' statements. The first thing the select method does is explode the input on the comma, so the round functions are being handled in 2 parts. The amazing thing here is that only the last zero is quoted.

EDIT: Something odd I just noticed... Does removing the space between the zero and the closing paren in the last select statement prevent the zero from being quoted in the output?

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide

SELECT `name`, `bamboo_invoices`.`currency_symbol`, SUM(`amount` * quantity) AS amount, SUM( ROUND((bamboo_invoice_items.amount*bamboo_invoices.tax1_rate/100 * bamboo_invoice_items.quantity) / 0.05, 0) * 0.05) as tax1_collected, SUM( ROUND((bamboo_invoice_items.amount*bamboo_invoices.tax2_rate/100 * bamboo_invoice_items.quantity) / 0.05, 0) * 0.05) as tax2_collected, SUM(ROUND((bamboo_invoice_items.amount * bamboo_invoices.accounting_invoice_exchange_rate * bamboo_invoice_items.quantity + ((bamboo_invoice_items.amount * bamboo_invoices.accounting_invoice_exchange_rate) *bamboo_invoice_items.quantity * (bamboo_invoices.tax1_rate/100 + bamboo_invoices.tax2_rate/100) * bamboo_invoice_items.taxable)) / 0.05, `0)` * 0.05 ) AS accounting_total_with_tax FROM (`bamboo_clients`) JOIN `bamboo_invoices` ON `bamboo_invoices`.`client_id` = `bamboo_clients`.`id` JOIN `bamboo_invoice_items` ON `bamboo_invoices`.`id` = `bamboo_invoice_items`.`invoice_id` WHERE `dateIssued` >= "2014-01-01" and dateIssued <= "2015-01-08" GROUP BY `name` ORDER BY `bamboo_invoices`.`currency_type`, `bamboo_clients`.`name`

As requested I closed the parent parenthesis right after the zero without space and the quote gets placed after the parenthesis

The first round in the select is perfectly fine and the second Round gets the quotes.

You probably need to change each of your select('SUM(...' statements to select_sum('...' statements. The first thing the select method does is explode the input on the comma, so the round functions are being handled in 2 parts. The amazing thing here is that only the last zero is quoted.

EDIT: Something odd I just noticed... Does removing the space between the zero and the closing paren in the last select statement prevent the zero from being quoted in the output?

I am still waiting from someone at Codeigniter to acknowledge that this is a bug.

SELECT `name`, `bamboo_invoices`.`currency_symbol`, SUM(`amount` * quantity) AS amount, SUM( ROUND((bamboo_invoice_items.amount*bamboo_invoices.tax1_rate/100 * bamboo_invoice_items.quantity) / 0.05, 0) * 0.05) as tax1_collected, SUM( ROUND((bamboo_invoice_items.amount*bamboo_invoices.tax2_rate/100 * bamboo_invoice_items.quantity) / 0.05, 0) * 0.05) as tax2_collected, SUM(ROUND((bamboo_invoice_items.amount * bamboo_invoices.accounting_invoice_exchange_rate * bamboo_invoice_items.quantity + ((bamboo_invoice_items.amount * bamboo_invoices.accounting_invoice_exchange_rate) *bamboo_invoice_items.quantity * (bamboo_invoices.tax1_rate/100 + bamboo_invoices.tax2_rate/100) * bamboo_invoice_items.taxable)) / 0.05, `0)` * 0.05 ) AS accounting_total_with_tax FROM (`bamboo_clients`) JOIN `bamboo_invoices` ON `bamboo_invoices`.`client_id` = `bamboo_clients`.`id` JOIN `bamboo_invoice_items` ON `bamboo_invoices`.`id` = `bamboo_invoice_items`.`invoice_id` WHERE `dateIssued` >= "2014-01-01" and dateIssued <= "2015-01-08" GROUP BY `name` ORDER BY `bamboo_invoices`.`currency_type`, `bamboo_clients`.`name`

As requested I closed the parent parenthesis right after the zero without space and the quote gets placed after the parenthesis

The first round in the select is perfectly fine and the second Round gets the quotes.

You probably need to change each of your select('SUM(...' statements to select_sum('...' statements. The first thing the select method does is explode the input on the comma, so the round functions are being handled in 2 parts. The amazing thing here is that only the last zero is quoted.

EDIT: Something odd I just noticed... Does removing the space between the zero and the closing paren in the last select statement prevent the zero from being quoted in the output?

I think I've tracked down the problem in the code. In DB_active_rec.php, the _max_min_avg_sum() method (used by select_sum()) sets $this->ar_select[], but doesn't set an associated $this->ar_no_escape[] value. So, when _compile_select() goes through $this->ar_select[] and checks $this->ar_no_escape[] for the associated keys, it will escape the wrong statements if select_sum() is used before a select('...', false) statement.

In this case, since select_sum() was used once, the last part of the last select() statement gets escaped. Of course, that doesn't address whatever leads to 0 or 0) getting escaped in the first place, but since false was passed as the second argument to select(), the expected behavior was that CI was not going to attempt to escape anything in that statement.

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide

I have also seen incorrect ticks placed in SQL statements when using expressions such as this one in the query:

concat(IFNULL(i.item_unit_size, ''), ' ', IFNULL(i.unittype_id, ' '))

It will place the ticks around the ifnull functions.

For now I have just shut the feature off in the config.

$db['default']['_protect_identifiers']= FALSE

I have also seen incorrect ticks placed in SQL statements when using expressions such as this one in the query:

concat(IFNULL(i.item_unit_size, ''), ' ', IFNULL(i.unittype_id, ' '))

It will place the ticks around the ifnull functions.

For now I have just shut the feature off in the config.

$db['default']['_protect_identifiers']= FALSE

You can also tell it to not protect identifiers on a select.

Code:

$this->db
  ->select('field1, field2') //will select as normal
  ->select('concat(IFNULL(i.item_unit_size, ''), ' ', IFNULL(i.unittype_id, ' '))', FALSE) //won't protect identifiers for this one as the 2nd parameter is set to FALSE

Additionally you can disable protect_identifiers for a single query:

Code:

$this->db->_protect_identifiers = FALSE;
$this->db->query(some complex query);

After some digging I found the problem. It is a bug.

The function select tokenizes all variables based on comma delimiter

PHP Code:

public function select($select = '*', $escape = NULL)
 {
 if (is_string($select))
 {
 $select = explode(',', $select);
 } 


well this is all fine until you put in

PHP Code:

(SELECT SUM(' . $itemsPfx . '.amount * ' . $itemsPfx . '.quantity + ROUND((' . $itemsPfx . '.amount * ' . $itemsPfx . '.quantity * (' . $invPfx . '.tax1_rate/100 + ' . $invPfx . '.tax2_rate/100) * ' . $itemsPfx . '.taxable), 2)) FROM ' . $itemsPfx . ' WHERE ' . $itemsPfx . '.invoice_id=' . $invoice_id . ') AS total_with_tax', $escape) 


it starts tokenizing the round function where comma is the delimiter for the ROUND function

for now I am circumventing the problem by passing in ; as the ROUND function delimiter

PHP Code:

(SELECT SUM(' . $itemsPfx . '.amount * ' . $itemsPfx . '.quantity + ROUND((' . $itemsPfx . '.amount * ' . $itemsPfx . '.quantity * (' . $invPfx . '.tax1_rate/100 + ' . $invPfx . '.tax2_rate/100) * ' . $itemsPfx . '.taxable); 2)) FROM ' . $itemsPfx . ' WHERE ' . $itemsPfx . '.invoice_id=' . $invoice_id . ') AS total_with_tax', $escape) 


and in DB_query_builder.php I change the ; back into ,

PHP Code:

protected function _compile_select($select_override = FALSE)
 {
                //change back ; to ,
                foreach ($this->qb_select as $key => $val)
                {     
                    $this->qb_select[$key] = str_replace(";",",", $val);
                }