Bug #55016 Doesn't search but insert in query cache queries leading one-line comment
Submitted: 6 Jul 2010 4:29 Modified: 6 Jul 2010 9:09
Reporter: Oleg Tsarev Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Query Cache Severity:S5 (Performance)
Version:Any OS:Any
Assigned to:

[6 Jul 2010 4:29] Oleg Tsarev
Description:
Simple test case:
show status like "Qcache_queries_in_cache";
Variable_name   Value
Qcache_queries_in_cache 1
show status like "Qcache_inserts";
Variable_name   Value
Qcache_inserts  1
show status like "Qcache_hits";
Variable_name   Value
Qcache_hits     2
# with comment first
select * from t1;
a
1
2
3
# with comment first
select * from t1;
a
1
2
3
show status like "Qcache_queries_in_cache";
Variable_name   Value
Qcache_queries_in_cache 2
show status like "Qcache_inserts";
Variable_name   Value
Qcache_inserts  2
show status like "Qcache_hits";
Variable_name   Value
Qcache_hits     2

As you, comment like: "# with comment first
select * from t1;" insert to cache, but doesn't search in it.

How to repeat:
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
# with comment first
select * from t1;
# with comment first
select * from t1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
[6 Jul 2010 4:38] Valerii Kravchuk
What exact version of MySQL serverr, x.y.z, and on what OS do you use?

I do not see the problem with recent 5.1.49 on Mac OS X, for example:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> show status like "Qcache_queries_in_cache";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 0     |
+-------------------------+-------+
1 row in set (0.03 sec)

mysql> show status like "Qcache_inserts";
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Qcache_inserts | 0     |
+----------------+-------+
1 row in set (0.00 sec)

mysql> show status like "Qcache_hits";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> # with comment first
mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.02 sec)

mysql> # with comment first
mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> show status like "Qcache_queries_in_cache";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> show status like "Qcache_inserts";
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Qcache_inserts | 1     |
+----------------+-------+
1 row in set (0.01 sec)

mysql> show status like "Qcache_hits";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> # with comment first
mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> show status like "Qcache_queries_in_cache";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Qcache_queries_in_cache | 1     |
+-------------------------+-------+
1 row in set (0.00 sec)

mysql> show status like "Qcache_inserts";
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Qcache_inserts | 1     |
+----------------+-------+
1 row in set (0.00 sec)

mysql> show status like "Qcache_hits";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 2     |
+---------------+-------+
1 row in set (0.00 sec)
[6 Jul 2010 4:43] Oleg Tsarev
I use 5.1 version. But i check source code, nothing changes in 5.5.

* file sql_cache.cc
* function int Query_cache::send_result_to_client(THD *thd, char *sql, uint query_length)
    /*                                                                                                                                        
      Skip '(' characters in queries like following:                                                                                          
      (select a from t1) union (select a from t1);                                                                                            
    */
    while (sql[i]=='(')
      i++;

    /*                                                                                                                                        
      Test if the query is a SELECT                                                                                                           
      (pre-space is removed in dispatch_command).                                                                                             
                                                                                                                                              
      First '/' looks like comment before command it is not                                                                                   
      frequently appeared in real life, consequently we can                                                                                   
      check all such queries, too.                                                                                                            
    */
    if ((my_toupper(system_charset_info, sql[i])     != 'S' ||
         my_toupper(system_charset_info, sql[i + 1]) != 'E' ||
         my_toupper(system_charset_info, sql[i + 2]) != 'L') &&
        sql[i] != '/')
    {
      DBUG_PRINT("qcache", ("The statement is not a SELECT; Not cached"));
      goto err;
    }
    if (query_length > 20 && has_no_cache_directive(&sql[i+6]))
    {
      /*                                                                                                                                      
        We do not increase 'refused' statistics here since it will be done                                                                    
        later when the query is parsed.                                                                                                       
      */
      DBUG_PRINT("qcache", ("The statement has a SQL_NO_CACHE directive"));
      goto err;
    }

This is LOL.
I also can fill new bugs, to queries like:
"( SELECT * from t1 )"
"(() SELECT * from t1 )"
so one.
[6 Jul 2010 5:18] Oleg Tsarev
Also, Valeriy:

Your mysql client strip comments befoire send to server.
Please run mysql as "msyql -c..."

Thank you, Oleg.
[6 Jul 2010 7:04] Valerii Kravchuk
This is a duplicate of bug #40986 actually.
[6 Jul 2010 8:41] Oleg Tsarev
No, this is NOT duplicate.

Bug what you linked coverage only C-style comments /* ... */. As you can see in source code, only this comments work correctly.

My example work with one-line comments:

"# one-line comments (doesn't work)
select..."

"-- one-line comments (doesn't work)
select..."

"/* C-style comment (does wor!k)
select..."
[6 Jul 2010 9:01] Valerii Kravchuk
Description of bug #40986 starts with:

"SELECT statements with leading line comments (beginning with '#' or '--') are still not
cached correctly by the Query Cache."

So, it is about one-line comments. /* */ processing was fixed long time ago.
[6 Jul 2010 9:09] Oleg Tsarev
Valeriy,

Thank you, i understand.