+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Development (https://forum.codeigniter.com/forumdisplay.php?fid=6)
+--- Forum: CodeIgniter 2.x (https://forum.codeigniter.com/forumdisplay.php?fid=18)
+--- Thread: sql is not compiled properly (/showthread.php?tid=646)
sql is not compiled properly - cperrot - 12-31-2014
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.
RE: sql is not compiled properly - jlp - 12-31-2014
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.
RE: sql is not compiled properly - cperrot - 12-31-2014
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.
RE: sql is not compiled properly - mwhitney - 01-07-2015
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?
RE: sql is not compiled properly - cperrot - 01-08-2015
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.
(01-07-2015, 12:28 PM)mwhitney Wrote: 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?
RE: sql is not compiled properly - cperrot - 01-08-2015
I am still waiting from someone at Codeigniter to acknowledge that this is a bug.
(01-08-2015, 12:52 AM)cperrot Wrote: 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.
(01-07-2015, 12:28 PM)mwhitney Wrote: 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?
RE: sql is not compiled properly - mwhitney - 01-08-2015
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.
RE: sql is not compiled properly - zepernick - 01-14-2015
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
RE: sql is not compiled properly - CroNiX - 01-21-2015
(01-14-2015, 06:56 AM)zepernick Wrote: 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 FALSEAdditionally you can disable protect_identifiers for a single query:
Code:
$this->db->_protect_identifiers = FALSE;
$this->db->query(some complex query);RE: sql is not compiled properly - cperrot - 04-18-2015
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);
}