Query cache is eating complex sub queries in select().
Here is DB info.
tableA
DROP TABLE IF EXISTS `tablea`;
CREATE TABLE `tablea` (
`a_number` int(11) DEFAULT NULL,
`b_number` varchar(255) DEFAULT NULL,
`c_number` varchar(255) DEFAULT NULL,
`b_status` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `tablea` VALUES ('11111', 'F-99999', '1-PPPPP', 'Open');
INSERT INTO `tablea` VALUES ('22222', 'F-99999', '1-PPPPP', 'Closed');
INSERT INTO `tablea` VALUES ('33333', 'F-99999', '2-PPPPP', 'Closed');
INSERT INTO `tablea` VALUES ('44444', 'F-99999', '2-PPPPP', 'Closed');
INSERT INTO `tablea` VALUES ('55555', 'F-88888', '1-PPPPP', 'Open');
INSERT INTO `tablea` VALUES (null, null, '3-PPPPP', null);
tableB
DROP TABLE IF EXISTS `tableb`;
CREATE TABLE `tableb` (
`c_number` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `tableb` VALUES ('1-PPPPP');
INSERT INTO `tableb` VALUES ('2-PPPPP');
INSERT INTO `tableb` VALUES ('3-PPPPP');
Here is the query in CI
$this->db->start_cache();
$this->db->select("
tableb.c_number,
(SELECT
CASE
WHEN GROUP_CONCAT(CONCAT(tablea.a_number,' - ',tablea.b_number),CONCAT(' (',tablea.b_status,') ')) IS NULL
THEN 'NO A_NUMBER'
ELSE GROUP_CONCAT(CONCAT(tablea.a_number,' - ',tablea.b_number),CONCAT(' (',tablea.b_status,') '))
END
FROM tablea
WHERE tablea.c_number = tableb.c_number)
",FALSE);
$this->db->from('tableb');
$this->db->stop_cache();
$this->db->get();
This is what is getting sent to MySQL
SELECT tableb.c_number,
(SELECT
CASE
WHEN GROUP_CONCAT(CONCAT(tablea.a_number, ' - ', tablea.b_number), CONCAT(' (', tablea.b_status, ') ')) IS NULL
THEN 'NO A_NUMBER'
ELSE GROUP_CONCAT(CONCAT(tablea.a_number, ') '))
END
FROM (`tableb`)
