--source include/have_innodb.inc

--source include/have_query_cache.inc

--enable_connect_log

SET @query_cache_size.saved = @@query_cache_size;

SET GLOBAL query_cache_size = 1024*1024;

DROP DATABASE IF EXISTS `db-db`;

CREATE DATABASE `db-db`;

USE `db-db`;

--connect (con2,localhost,root,,db-db)

create table t1 (id int primary key) ENGINE=InnoDB;

create table t2 (

  id int primary key, 

  t1_id int, 

  constraint t2_fk foreign key (t1_id) references t1 (id) on delete cascade

) ENGINE=InnoDB;

insert into t1 values (100);

insert into t2 values (1,100);

select * from t2;

--connection default

delete from t1 where id=100;

select * from t2;

select SQL_NO_CACHE * from t2;

DROP DATABASE `db-db`;

SET GLOBAL query_cache_size = @query_cache_size.saved;

delete from t1 where id=100;

select * from t2;

id	t1_id

1	100

select SQL_NO_CACHE * from t2;

id	t1_id

revno: 4514

revision-id: nisha.gopalakrishnan@oracle.com-20121031062656-4advnrgfltkxsu4y

parent: nuno.carvalho@oracle.com-20121030162613-vvq9dnc1hbh3pmlq

committer: Nisha Gopalakrishnan <nisha.gopalakrishnan@oracle.com>

branch nick: mysql-5.6-13919851

timestamp: Wed 2012-10-31 11:56:56 +0530

message:

  BUG#13919851:QUERY CACHE NOT USED WHEN TABLE NAME CONTAINS

               DOLLAR SIGN AND ENGINE IS INNODB

  Analysis

  --------

  The query cache is not used when the database name or the table

  name contains special characters and the engine used is INNODB.

  Ex: CREATE TABLE 'db-test'.'query$table'(fld int) ENGINE=INNODB;

  While storing the query, the query cache interface checks with

  the storage engine module to determine whether the query can be

  cached. INNODB uses the canonical format

  ('db@002dtest/query@0024table') as the key to find the

  corresponding table object.

  The query cache interface passes the key which is a string of

  the format 'databasename/0tablename' in a non-canonical format

  ('db-test/0query$table'). Hence the hash value generated is

  different and the table object is not found.

  So when the query cache interface checks with storage engine

  to determine whether the query can be cached, the storage

  engine INNODB does not find the corresponding table object.

  Hence the query is not cached.

  Fix

  ---

  a) The query cache interface now passes the normalized path to

  the table in its canonical form to the storage engines.

  b) The piece of code in the routine

  'innobase_query_caching_of_table_permitted' used to normalize the

  table name is replaced with an existing macro 'normalize_table_name'

  which does the same.

  c) THE NDB handler code which used to perform the conversion of the

  key to canonical format has been removed. This is because the query

  cache interface now passes the canonical format of the key to the

  storage engines.