Welcome Guest, Not a member yet? Register   Sign In
Active Record query cache eating queries. Looks like BUG
#1

[eluser]mikerh[/eluser]
Query cache is eating complex sub queries in select().

Here is DB info.

tableA
Code:
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
Code:
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

Code:
$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

Code:
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`)

#2

[eluser]jmadsen[/eluser]
what is it exactly you are trying to point out?
#3

[eluser]mikerh[/eluser]
[quote author="jmadsen" date="1353120345"]what is it exactly you are trying to point out?[/quote]

Sorry...your blindness must be affecting you.

Here is the query as I have it in CI.

Code:
$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();

Here is the query that CI is sending to mysql

Code:
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`)

If I don't use query cache the query is fine.

Really? You can't see what's in plain English there...did you even try to re-create the issue before replying?
#4

[eluser]CroNiX[/eluser]
Your answer is in the user guide. Didn't you read it before posting?

This has also been asked and answered many times on the forums. Did you try searching before posting?

We can play that game too, but it won't help you get very far now will it...
#5

[eluser]jmadsen[/eluser]
[quote author="mikerh" date="1353121995"]did you even try to re-create the issue before replying?
[/quote]

No, I didn't. I'm here as a volunteer.

I have no interest in spending time trying to recreate an issue that the poster can't even explain clearly. Obviously there is some difference in your query - tell us what it is we should be looking for when you report the bug.

And after acting like such a complete dickhead, don't expect anyone in these forums to make much effort helping you in the future.
#6

[eluser]mikerh[/eluser]
Fixed

https://github.com/EllisLab/CodeIgniter/issues/2004

https://github.com/EllisLab/CodeIgniter/...2dc8386ecf

Nice turnaround.

Thanks to those who actually looked into the issue...good thing it was "in the user guide" :roll:





Theme © iAndrew 2016 - Forum software by © MyBB