If you have a complicated query with many tables that doesn't return any
rows, you should use the following procedure to find out what is wrong
with your query:
Test the query with
EXPLAIN and check if you can find something that is
Select only those fields that are used in the
Remove one table at a time from the query until it returns some rows.
If the tables are big, it's a good idea to use
LIMIT 10 with the query.
SELECT for the column that should have matched a row,
against the table that was last removed from the query.
If you are comparing
DOUBLE columns with numbers that
have decimals, you can't use
=! This problem is common in most
computer languages because floating point values are not exact values.
mysql> SELECT * FROM table_name WHERE float_column=3.5;
mysql> SELECT * FROM table_name WHERE float_column between 3.45 and 3.55;
In most cases, changing the
FLOAT to a
DOUBLE will fix this!
If you still can't find out what's wrong, create a minimal test that can
be run with
mysql test < query.sql that shows your problems.
You can create a test file with
mysqldump --quick database tables > query.sql. Take the file up in a editor, remove some insert lines (if there are
too many of these) and add your select statement last in the file.
Test that you still have your problem by doing:
shell> mysqladmin create test2
shell> mysql test2 < query.sql
Post the test file using
mysqlbug to email@example.com.