A.5.6 Solving Problems with No Matching Rows

If you have a complicated query that has many tables and that doesn't return any rows, you should use the following procedure to find out what is wrong with your query:

1. Test the query with EXPLAIN and check if you can find something that is obviously wrong. See section 5.2.1 EXPLAIN Syntax (Get Information About a SELECT).
2. Select only those fields that are used in the WHERE clause.
3. 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.
4. Do a SELECT for the column that should have matched a row against the table that was last removed from the query.
5. If you are comparing FLOAT or 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. In most cases, changing the FLOAT to a DOUBLE will fix this. See section A.5.7 Problems with Floating-Point Comparison.
6. If you still can't figure 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. Open the file in an editor, remove some insert lines (if there are too many of these), and add your select statement at the end of 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 mysql@lists.mysql.com.

User Comments

Add your own comment.