• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Query builder add extra '(' and ')' caused error in mysql 8

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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.