Wrong results from LIKE statement in MySQL procedure
If you are (as I was) facing a really weird issue using a LIKE statement in a MySQL procedure that returns wrong filters result maybe this post could help you.
Some background:
I was building a procedure that receives a project name (string) as the only input parameter and will extract data from TableB based on the records from TableA (resulting from the LIKE).
TableB has a log of phone calls that has been done, and TableA is a log of each calling batch.
Each row on TableA has a Log_ID field which is key on TableB.
Each batch has its customer name, so I needed to pull calls done for a specific bacth job (using LIKE).
For any reason I was getting a wrong number of rows matching the condition. Actually, it was running as if it had no WHERE clause.
Look at the tables structures:
- mysql> SHOW CREATE TABLE TableA \G
- *************************** 1. row ***************************
- Create Table: CREATE TABLE `TableA` (
- `Log_ID` mediumint(8) unsigned NOT NULL auto_increment,
- `Job_ID` int(10) unsigned NOT NULL default '0',
- `Batch_ID` mediumint(8) unsigned NOT NULL default '0',
- `StartDateTime` datetime NOT NULL,
- ...
- `CustomerName` varchar(255) collate latin1_general_ci default NULL,
- PRIMARY KEY (`Log_ID`),
- ...
- 1 row in set (0.20 sec)
- mysql> SHOW CREATE TABLE TableB \G
- *************************** 1. row ***************************
- Create Table: CREATE TABLE `TableB` (
- `Number` bigint(20) unsigned NOT NULL,
- `Date` date NOT NULL,
- `Time` time NOT NULL,
- ...
- `Log_ID` int(8) unsigned NOT NULL default '0',
- ...
- 1 row in set (0.18 sec)
Let's see the procedure:
- DECLARE ...
- DECLARE v_Log_ID BIGINT;
- DECLARE v_CustName VARCHAR(50) DEFAULT CONCAT('%',customername,'%');
- DECLARE crsr_results CURSOR FOR SELECT T.Log_ID FROM TableA T WHERE T.CustomerName LIKE v_CustName;
- ...
- OPEN crsr_results;
- WHILE Done = 0 DO
- FETCH crsr_results INTO v_Log_ID;
- INSERT IGNORE INTO dumpme SELECT B.Number FROM TableB B WHERE B.Log_ID = v_Log_ID;
- ...
- END WHILE;
- CLOSE crsr_results;
- ...
Bonus track: Another problem that I faced is how to prepare the variable to be used in the statement, because I can't issue a SET statement before declaring the cursor!
Ok, there you have it (I Googled a lot but nobody seems to be suffered this).
As you can see, the procedure is quite simple. It grabs from TableA the matching batch projects and with that Log_ID key gets the numbers from TableB and inserts them into a temporal table to be dumped after the cursor via SELECT INTO OUTFILE.
The problem appears when preparing the results for the cursor: that query ALWAYS returned the same number of rows that the whole table had. That means, no filter was applied.
So I ran an EXPLAIN to see what was happening:
- mysql> EXPLAIN SELECT Log_ID FROM TableA WHERE CustomerName LIKE '%AAA%';
- +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
- | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
- +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
- 1 row in set (0.22 sec)
After some research I found that that result from an EXPLAIN means NO FILTER AT ALL is being applied (so that, it returned all the time the total number of rows in the table).
Even worst, if you run the same query outside of a procedure you get the right results!
Then, after pulling some hair and many weird attempts I ended up that the problem was that the tables had no alias (in the SP above you're looking at the definitive code, with aliases).
So look now:
- mysql> EXPLAIN SELECT Log_ID FROM TableA T WHERE T.CustomerName LIKE '%AAA%';
- +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
- | 1 | SIMPLE | T | ALL | NULL | NULL | NULL | NULL | 10674 | Using where |
- +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
- 1 row in set (0.20 sec)
Ahh, much better now.
I've found a little information about this weird behavior and didn't found anything at all in the situation I faced it, so I hope this help someone.
Bonus track II: Finally I needed to dump the results from dumpme table to a text file. That's easy with INTO OUTFILE, but in my case I needed to put to the resulting file the same name that was used in the query.
That meant the need to concatenate the variable 'customername' with the path and the extension in this way:
- SET @filename = CONCAT('/tmp/',customername,'.txt');
- SELECT Number INTO OUTFILE filename FROM dumpme;
Unfortunately that didn't work. It was throwing an error.
So the solution was to use a prepared statement like this:
- SET @myvar = CONCAT('SELECT Number INTO OUTFILE ',"'/tmp/",customername,".txt'",' FROM dumpme') ;
- PREPARE stmt1 FROM @myvar;
- EXECUTE stmt1;
- DEALLOCATE PREPARE stmt1;
Happy Coding!
.: Pampa :.
Posts Calendar
Archives
- February 2012 (1)
- November 2010 (2)
- October 2010 (2)
- September 2010 (1)
- August 2010 (1)
- June 2010 (1)
- May 2010 (3)
- April 2010 (1)
Categories
Like this site?
Tell it to Google! :)