Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

AR query cache corrupting query #2004

Closed
mikerh opened this Issue · 5 comments

3 participants

@mikerh

Please see this thread http://codeigniter.com/forums/viewthread/229833/

I have looked through the forum and I don't see the answer to my problem. The issue is pretty simple. I script my query in CI and what it delivers to mysql is different.

If i am missing something please point me in the right direction.

@ganiutomo

You have entered a wrong query. There is no closing parentheses on the second "SELECT"

@mikerh

It's there. This is the query as I have it in CI

$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
",FALSE);

This is what mysql is getting when it runs.

 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, ') '))

You can see that it's eating part of the query.

If I don't use query cache...the query runs fine...it doesn't get corrupted.

@narfbg
Owner

No, it's not there.

@mikerh

Ok...yes sorry was pasting the wrong code but the issue still remains.

Here is the query run in mysql and the output

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,' - ',tablea.b_number),CONCAT(' (',tablea.b_status,') '))
END
FROM tablea
WHERE tablea.c_number = tableb.c_number)
FROM 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 tab |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1-PPPPP  | 11111 - F-99999 (Open) ,22222 - F-99999 (Closed) ,55555 - F-88888 (Open)                                                                                                                                                                                         |
| 2-PPPPP  | 33333 - F-99999 (Closed) ,44444 - F-99999 (Closed)                                                                                                                                                                                                               |
| 3-PPPPP  | NO A_NUMBER                                                                                                                                                                                                                                                          |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Here is the query in CI with query cache on

$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 resulting query sent to mysql...notice the difference in output.

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 tablea
 WHERE tablea.c_number = tableb.c_number)
FROM (`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 tablea
 WHERE tablea.c_number = tableb.c_ |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1-PPPPP  | 11111) ,22222) ,55555)                                                                                                                                                                                                                                               |
| 2-PPPPP  | 33333) ,44444)                                                                                                                                                                                                                                                       |
| 3-PPPPP  | NO A_NUMBER                                                                                                                                                                                                                                                          |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

And here is query in CI with query cache turned off

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

and the result which is good.

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)
FROM (`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, ') '))
 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1-PPPPP  | 11111 - F-99999 (Open) ,22222 - F-99999 (Closed) ,55555 - F-88888 (Open)                                                                                                                                                                                         |
| 2-PPPPP  | 33333 - F-99999 (Closed) ,44444 - F-99999 (Closed)                                                                                                                                                                                                               |
| 3-PPPPP  | NO A_NUMBER                                                                                                                                                                                                                                                          |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
@narfbg narfbg referenced this issue from a commit
@narfbg narfbg Fix issues #751 and #2004 eae17d1
@narfbg
Owner

Fixed, see the above commit.

@narfbg narfbg closed this
@nonchip nonchip referenced this issue from a commit in nonchip/CodeIgniter
@narfbg narfbg Fix issues #751 and #2004 418cf2d
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.