| Bug #58073 | CONCAT and OR give incorrect query results | ||
|---|---|---|---|
| Submitted: | 9 Nov 2010 8:45 | Modified: | 13 Feb 2012 11:29 |
| Reporter: | Andrew Chapman | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S2 (Serious) |
| Version: | mysql-5.1-telco-7.1 | OS: | Linux (Centos5.4) |
| Assigned to: | Martin Skold | CPU Architecture: | Any |
| Tags: | 7.1.8-1.rhel5.i386 | ||
[9 Nov 2010 9:05]
Valeriy Kravchuk
Thank you for the problem report. Please, send the results of EXPLAIN for these queries.
[9 Nov 2010 23:38]
MySQL Verification Team
Could you please provide a table dump file (create table/insert statements)?. Thanks in advance.
[4 Feb 2011 17:40]
Jonas Oreland
could this be http://bugs.mysql.com/bug.php?id=53360 ? if so it's fixed in 7.1.9
[4 Feb 2011 18:36]
Andrew Chapman
I don't think this is the same as 53360 the fields with the concat and like are varchars whereas 53360 is for ENUMs and the fix (if I read it correctly) just disables pushdown for LIKE with ENUMs. It may be related though as the explain for this bug mentions pushdown conditions.
[13 Feb 2012 11:29]
Jon Stephens
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at http://www.mysql.com/downloads/
[13 Feb 2012 11:31]
Jon Stephens
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at http://www.mysql.com/downloads/
[13 Feb 2012 11:33]
Jon Stephens
Fixed in NDB 7.0+, documented in the NDB 7.0.29 and 7.1.18 changelogs as follows:
Queries using LIKE CONCAT(...) OR LIKE CONCAT(...) in the WHERE clause
on NDB tables returned incorrect results.
Closed.
[13 Feb 2012 11:33]
Jon Stephens
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at http://www.mysql.com/downloads/
[13 Feb 2012 11:35]
Jon Stephens
Fixed in NDB 7.0+, documented in the NDB 7.0.29 and 7.1.18 changelogs as follows:
Queries using LIKE CONCAT(...) OR LIKE CONCAT(...) in the WHERE clause
on NDB tables returned incorrect results.
Closed.
[13 Feb 2012 11:37]
Jon Stephens
Fixed in NDB 7.0+, documented in the NDB 7.0.29 and 7.1.18 changelogs as follows:
Queries using LIKE CONCAT(...) OR LIKE CONCAT(...) in the WHERE clause
on NDB tables returned incorrect results.
Closed.
Description: Queries with like, two CONCATs and an OR gives incorrect results. SELECT * FROM mytable WHERE problem LIKE CONCAT('%Text%') OR problem LIKE '%Text%'; 26 results SELECT * FROM mytable WHERE problem LIKE CONCAT('%Text%') OR problem LIKE CONCAT('%Text%'); 0 results It occurs on a 2 data node cluster using version 7.1.8-1 throughout (client, server, storage and management). I have tried the test queries on InnoDB and MySQL 5.0.37 and the bug doesn't happen there. I use this sort of construction quite often in prepared statements concat('%', ?, '%') so this bug is a bit worrying. How to repeat: These three queries should give the same results, but the third one give no results. The examples are somewhat artificial as I've reduced it to the minimum to reproduce the error. Originally it was querying the components of an address for partial matches. Create a simple table "mytable" with a varchar column "problem". SELECT * FROM mytable WHERE problem LIKE CONCAT('%Text%'); 26 results SELECT * FROM mytable WHERE problem LIKE CONCAT('%Text%') OR problem LIKE '%Text%'; 26 results SELECT * FROM mytable WHERE problem LIKE CONCAT('%Text%') OR problem LIKE CONCAT('%Text%'); 0 results I can have AND and CONCAT with no oddities. It only seems to happen with 2 or more CONCATs and one or more OR. I am not sure if it relevant, but the problem column has an index on it.