Welcome Guest, Not a member yet? Register   Sign In
sql is not compiled properly
#1

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.
Reply
#2

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
Reply
#3

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.
Reply
#4

(This post was last modified: 01-07-2015, 12:38 PM by mwhitney.)

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?
Reply
#5

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?
Reply
#6

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?
Reply
#7

(This post was last modified: 01-08-2015, 09:39 AM by mwhitney.)

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.
Reply
#8

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
Reply
#9

(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 FALSE

Additionally you can disable protect_identifiers for a single query:
Code:
$this->db->_protect_identifiers = FALSE;
$this->db->query(some complex query);
Reply
#10

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);
 
               
Reply




Theme © iAndrew 2016 - Forum software by © MyBB