{"action":"created","issue":{"url":"https://api.github.com/repos/bcit-ci/CodeIgniter/issues/4647","repository_url":"https://api.github.com/repos/bcit-ci/CodeIgniter","labels_url":"https://api.github.com/repos/bcit-ci/CodeIgniter/issues/4647/labels{/name}","comments_url":"https://api.github.com/repos/bcit-ci/CodeIgniter/issues/4647/comments","events_url":"https://api.github.com/repos/bcit-ci/CodeIgniter/issues/4647/events","html_url":"https://github.com/bcit-ci/CodeIgniter/issues/4647","id":156866251,"number":4647,"title":"count_all_results produces incorrect results","user":{"login":"blasto333","id":2765230,"avatar_url":"https://avatars.githubusercontent.com/u/2765230?v=3","gravatar_id":"","url":"https://api.github.com/users/blasto333","html_url":"https://github.com/blasto333","followers_url":"https://api.github.com/users/blasto333/followers","following_url":"https://api.github.com/users/blasto333/following{/other_user}","gists_url":"https://api.github.com/users/blasto333/gists{/gist_id}","starred_url":"https://api.github.com/users/blasto333/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/blasto333/subscriptions","organizations_url":"https://api.github.com/users/blasto333/orgs","repos_url":"https://api.github.com/users/blasto333/repos","events_url":"https://api.github.com/users/blasto333/events{/privacy}","received_events_url":"https://api.github.com/users/blasto333/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/bcit-ci/CodeIgniter/labels/Bug","name":"Bug","color":"eb2420"}],"state":"closed","locked":false,"assignee":null,"milestone":{"url":"https://api.github.com/repos/bcit-ci/CodeIgniter/milestones/11","html_url":"https://github.com/bcit-ci/CodeIgniter/milestones/3.0.7","labels_url":"https://api.github.com/repos/bcit-ci/CodeIgniter/milestones/11/labels","id":1677677,"number":11,"title":"3.0.7","description":"","creator":{"login":"narfbg","id":1058011,"avatar_url":"https://avatars.githubusercontent.com/u/1058011?v=3","gravatar_id":"","url":"https://api.github.com/users/narfbg","html_url":"https://github.com/narfbg","followers_url":"https://api.github.com/users/narfbg/followers","following_url":"https://api.github.com/users/narfbg/following{/other_user}","gists_url":"https://api.github.com/users/narfbg/gists{/gist_id}","starred_url":"https://api.github.com/users/narfbg/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/narfbg/subscriptions","organizations_url":"https://api.github.com/users/narfbg/orgs","repos_url":"https://api.github.com/users/narfbg/repos","events_url":"https://api.github.com/users/narfbg/events{/privacy}","received_events_url":"https://api.github.com/users/narfbg/received_events","type":"User","site_admin":false},"open_issues":1,"closed_issues":10,"state":"open","created_at":"2016-04-01T09:05:49Z","updated_at":"2016-05-26T07:23:24Z","due_on":null,"closed_at":null},"comments":1,"created_at":"2016-05-25T22:39:23Z","updated_at":"2016-05-26T07:23:24Z","closed_at":"2016-05-26T07:23:24Z","body":"count_all_results is returning the wrong value in many cases in CI 3.0.6\r\n\r\nQUERY BUILDER:\r\n\r\n\t\t$this->db->select('items.item_id, items.name, categories.id as category_id,categories.name as category,location, company_name, item_number,size, product_id, \r\n\t\tIFNULL('.$this->db->dbprefix('location_items').'.cost_price, '.$this->db->dbprefix('items').'.cost_price) as cost_price, \r\n\t\tIFNULL('.$this->db->dbprefix('location_items').'.unit_price, '.$this->db->dbprefix('items').'.unit_price) as unit_price,\r\n\t\tSUM(quantity) as quantity, \r\n\t\tIFNULL('.$this->db->dbprefix('location_items').'.reorder_level, '.$this->db->dbprefix('items').'.reorder_level) as reorder_level, \r\n\t\tdescription', FALSE);\r\n\t\t$this->db->from('items');\r\n\t\t$this->db->join('suppliers', 'items.supplier_id = suppliers.person_id', 'left outer');\r\n\t\t$this->db->join('categories', 'items.category_id = categories.id', 'left outer');\r\n\t\t$this->db->join('location_items', 'location_items.item_id = items.item_id and location_id IN('.$location_ids_string.')', 'left');\r\n\t\t$this->db->where('items.deleted', 0);\r\n\t\t$this->db->group_by('items.item_id');\r\n\t\t\r\n\t\tif ($this->params['supplier'] != -1)\r\n\t\t{\r\n\t\t\t$this->db->where('suppliers.person_id', $this->params['supplier']);\r\n\t\t}\r\n\t\t\r\n\t\tif ($this->params['category_id'] != -1)\r\n\t\t{\r\n\t\t\t$this->db->where_in('categories.id', $category_ids);\r\n\t\t}\r\n\t\t\r\n\t\tif ($this->params['inventory'] == 'in_stock')\r\n\t\t{\r\n\t\t\t$this->db->where('quantity > 0');\r\n\t\t}\r\n\t\t\r\n\t\tif ($this->params['inventory'] == 'out_of_stock')\r\n\t\t{\r\n\t\t\t$this->db->where('quantity <= 0');\r\n\t\t}\r\n\t\t\r\n\t\t$this->db->where('is_service !=', 1);\r\n\t\t$this->db->order_by('items.name');\r\n\t\t\r\n\t\t$ret = $this->db->count_all_results();\r\n\t\t\r\n\t\techo $this->db->last_query();\r\n\r\n\r\n3.0.4 (Returns 38) (Correct)\r\n\r\nSELECT COUNT(*) AS `numrows` FROM ( SELECT phppos_items.item_id, phppos_items.name, phppos_categories.id as category_id, phppos_categories.name as category, location, company_name, item_number, size, product_id, IFNULL(phppos_location_items.cost_price, phppos_items.cost_price) as cost_price, IFNULL(phppos_location_items.unit_price, phppos_items.unit_price) as unit_price, SUM(quantity) as quantity, IFNULL(phppos_location_items.reorder_level, phppos_items.reorder_level) as reorder_level, description FROM `phppos_items` LEFT OUTER JOIN `phppos_suppliers` ON `phppos_items`.`supplier_id` = `phppos_suppliers`.`person_id` LEFT OUTER JOIN `phppos_categories` ON `phppos_items`.`category_id` = `phppos_categories`.`id` LEFT JOIN `phppos_location_items` ON `phppos_location_items`.`item_id` = `phppos_items`.`item_id` and `location_id` IN(1) WHERE `phppos_items`.`deleted` =0 AND `is_service` != 1 GROUP BY `phppos_items`.`item_id` ORDER BY `phppos_items`.`name` ) CI_count_all_results\r\n\r\n3.0.6 (Returns 1) (Incorrect)\r\n`SELECT COUNT(*) AS `numrows` FROM `phppos_items` LEFT OUTER JOIN `phppos_suppliers` ON `phppos_items`.`supplier_id` = `phppos_suppliers`.`person_id` LEFT OUTER JOIN `phppos_categories` ON `phppos_items`.`category_id` = `phppos_categories`.`id` LEFT JOIN `phppos_location_items` ON `phppos_location_items`.`item_id` = `phppos_items`.`item_id` and `location_id` IN(1) WHERE `phppos_items`.`deleted` =0 AND `is_service` != 1 GROUP BY `phppos_items`.`item_id``"},"comment":{"url":"https://api.github.com/repos/bcit-ci/CodeIgniter/issues/comments/221796660","html_url":"https://github.com/bcit-ci/CodeIgniter/issues/4647#issuecomment-221796660","issue_url":"https://api.github.com/repos/bcit-ci/CodeIgniter/issues/4647","id":221796660,"user":{"login":"narfbg","id":1058011,"avatar_url":"https://avatars.githubusercontent.com/u/1058011?v=3","gravatar_id":"","url":"https://api.github.com/users/narfbg","html_url":"https://github.com/narfbg","followers_url":"https://api.github.com/users/narfbg/followers","following_url":"https://api.github.com/users/narfbg/following{/other_user}","gists_url":"https://api.github.com/users/narfbg/gists{/gist_id}","starred_url":"https://api.github.com/users/narfbg/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/narfbg/subscriptions","organizations_url":"https://api.github.com/users/narfbg/orgs","repos_url":"https://api.github.com/users/narfbg/repos","events_url":"https://api.github.com/users/narfbg/events{/privacy}","received_events_url":"https://api.github.com/users/narfbg/received_events","type":"User","site_admin":false},"created_at":"2016-05-26T07:23:24Z","updated_at":"2016-05-26T07:23:24Z","body":"Patch is referenced above.\r\n\r\nBut ... aside from the fact (evident by your other submissions) that you're always pushing the Query Builder to its limits while it is made for way simpler queries, I should ask you to put more effort into identifying problems before reporting them.\r\n\r\nIt is of course better for me to have as much information as possible, but it's also counter-productive when most of it is irrelevant and half of the work is noise-cancelling. Filtering that out would've shown you that 3.0.4 is also affected by this bug (because it is caused by GROUP BY), but another one (ORDER BY related) is simply masking it in your huge query."}}
{"actor":{"display_login":"narfbg"}}