+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Development (https://forum.codeigniter.com/forumdisplay.php?fid=6)
+--- Forum: Issues (https://forum.codeigniter.com/forumdisplay.php?fid=19)
+--- Thread: Query builder add extra '(' and ')' caused error in mysql 8 (/showthread.php?tid=74977)
Query builder add extra '(' and ')' caused error in mysql 8 - StevieJS - 12-03-2019
I'm using CI 3.1.11 with mysql 5.7 and it is working ok. However, I'm testing upgrading my database to mysql 8 and some query generated by query builder returned error. After checking the error message, I noticed query builder added extra '(' & ')' to my first subquery "... FROM ((SELECT... ) a) INNER JOIN..." and caused the error in mysql 8 (mysql 5.7 seems to ignore these extras '(' ')' and no error). What should I do to fix this? Is it CI bug or mysql 8 bug?
My Code:
$this->db->select("b.item_code,b.item_name,c.cat_name,a.sale_qty,a.saler_qty,a.net_qty");
$this->db->from(" ( SELECT a.wa_id,a.item_id,a.sale_qty,COALESCE(b.saler_qty,0) as saler_qty,(a.sale_qty - COALESCE(b.saler_qty,0)) AS net_qty
FROM (SELECT b.wa_id,d.item_id,SUM(d.qty)as sale_qty FROM tbl_bill a
INNER JOIN tbl_sale b ON b.sale_id = a.sale_id
INNER JOIN tbl_bill_detail d ON d.bill_id = a.bill_id
{$join}
{$where} {$w_bill} {$w_cust} {$created_by}
GROUP BY b.wa_id,d.item_id) a
LEFT JOIN (SELECT c.wa_id,b.item_id,SUM(b.qty)as saler_qty
FROM tbl_bill a
INNER JOIN tbl_sale_item_return c on c.bill_id = a.bill_id
INNER JOIN tbl_sale_item_return_details b ON b.sale_ireturn_id = c.sale_ireturn_id
{$join}
{$where2} {$w_bill} {$w_cust}
GROUP BY c.wa_id,b.item_id)b ON b.wa_id = a.wa_id AND b.item_id = a.item_id) a");
$this->db->join("tbl_item b","b.item_id = a.item_id","INNER");
$this->db->join("tbl_item_category c","c.cat_id = b.cat_id","INNER");
$this->db->order_by("c.cat_name,b.item_code","ASC");
$q = $this->db->get();
Error Message:
Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') INNER JOIN tbl_item b ON b.item_id = a.item_id INNER JOIN `tbl_ite' at line 15
SELECT b.item_code, b.item_name, c.cat_name, a.sale_qty, a.saler_qty, a.net_qty FROM ((SELECT a.wa_id, a.item_id, a.sale_qty, COALESCE(b.saler_qty, 0) as saler_qty, (a.sale_qty - COALESCE(b.saler_qty, 0)) AS net_qty FROM (SELECT b.wa_id, d.item_id, SUM(d.qty)as sale_qty FROM tbl_bill a INNER JOIN tbl_sale b ON b.sale_id = a.sale_id INNER JOIN tbl_bill_detail d ON d.bill_id = a.bill_id INNER JOIN tbl_bill_outlet_1 e ON e.bill_id = a.bill_id WHERE a.eff_date BETWEEN '2019-10-01' AND '2019-12-03' GROUP BY b.wa_id, d.item_id) a LEFT JOIN (SELECT c.wa_id, b.item_id, SUM(b.qty)as saler_qty FROM tbl_bill a INNER JOIN tbl_sale_item_return c on c.bill_id = a.bill_id INNER JOIN tbl_sale_item_return_details b ON b.sale_ireturn_id = c.sale_ireturn_id INNER JOIN tbl_bill_outlet_1 e ON e.bill_id = a.bill_id WHERE c.tran_date BETWEEN '2019-10-01' AND '2019-12-03' GROUP BY c.wa_id, b.item_id)b ON b.wa_id = a.wa_id AND b.item_id = a.item_id) a) INNER JOIN tbl_item b ON b.item_id = a.item_id INNER JOIN tbl_item_category c ON c.cat_id = b.cat_id ORDER BY c.cat_name ASC, b.item_code ASC
Correct Query:
SELECT b.item_code, b.item_name, c.cat_name, a.sale_qty, a.saler_qty, a.net_qty FROM (SELECT a.wa_id, a.item_id, a.sale_qty, COALESCE(b.saler_qty, 0) as saler_qty, (a.sale_qty - COALESCE(b.saler_qty, 0)) AS net_qty FROM (SELECT b.wa_id, d.item_id, SUM(d.qty)as sale_qty FROM tbl_bill a INNER JOIN tbl_sale b ON b.sale_id = a.sale_id INNER JOIN tbl_bill_detail d ON d.bill_id = a.bill_id INNER JOIN tbl_bill_outlet_1 e ON e.bill_id = a.bill_id WHERE a.eff_date BETWEEN '2019-10-01' AND '2019-12-03' GROUP BY b.wa_id, d.item_id) a LEFT JOIN (SELECT c.wa_id, b.item_id, SUM(b.qty)as saler_qty FROM tbl_bill a INNER JOIN tbl_sale_item_return c on c.bill_id = a.bill_id INNER JOIN tbl_sale_item_return_details b ON b.sale_ireturn_id = c.sale_ireturn_id INNER JOIN tbl_bill_outlet_1 e ON e.bill_id = a.bill_id WHERE c.tran_date BETWEEN '2019-10-01' AND '2019-12-03' GROUP BY c.wa_id, b.item_id)b ON b.wa_id = a.wa_id AND b.item_id = a.item_id) a INNER JOIN tbl_item b ON b.item_id = a.item_id INNER JOIN tbl_item_category c ON c.cat_id = b.cat_id ORDER BY c.cat_name ASC, b.item_code ASC