How to debug a query with valid syntax, executes, but does not return any results?

Question

Thus, I am writing a rather complex query with half a dozen joins, a dependent subquery for the purposes of [largest n-per-group], grouping, etc. This is syntactically fair, but I clearly made at least one mistake, because it returns nothing.

In the past, I debugged valid queries that didn’t return anything, removing joins, doing subqueries myself, removing WHERE clauses, and deleting the grouping to see what I get, but so far it has puzzled me a lot. Are there any better tools or methods for this kind of thing?

This particular query is for MySQL, if it matters to any platform-oriented tools.

Edit : I was hoping for advice from query agnostics, but since you need EXPLAIN to run the query and you need EXPLAIN output to find out what this means, I suppose I should voluntarily complete the query I'm working on now;)

SELECT artist.entry_id AS artist_id, GROUP_CONCAT(tracks.title ORDER BY tracks.entry_date DESC SEPARATOR ',') AS recent_songs FROM exp_favorites AS fav JOIN exp_weblog_titles AS artist ON fav.entry_id = artist.entry_id JOIN exp_weblog_titles AS tracks ON tracks.entry_id = ( SELECT t.entry_id FROM exp_weblog_titles AS t JOIN exp_relationships AS r1 ON r1.rel_parent_id = t.entry_id WHERE t.weblog_id = 3 AND t.entry_date < UNIX_TIMESTAMP() AND t.status = 'open' AND r1.rel_child_id = artist.entry_id -- this line relates the subquery to the outside world ORDER BY t.entry_date DESC LIMIT 3 -- I want 3 tracks per artist ) WHERE artist.weblog_id = 14 AND fav.member_id = 1 GROUP BY artist.entry_id LIMIT 5 

which will cause EXPLAIN to exit:

 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY fav ALL 293485 Using where; Using temporary; Using filesort 1 PRIMARY artist eq_ref PRIMARY,weblog_id PRIMARY 4 db.fav.entry_id 1 Using where 1 PRIMARY tracks eq_ref PRIMARY PRIMARY 4 func 1 Using where 2 DEPENDENT SUBQUERY r1 ref rel_parent_id,rel_child_id rel_child_id 4 db.artist.entry_id 5 Using where; Using temporary; Using filesort 2 DEPENDENT SUBQUERY t eq_ref PRIMARY,weblog_id,status,entry_date PRIMARY 4 db.r1.rel_parent_id 1 Using where 

I cut the request to the easiest for this question ... basically all I need is to return 3 tracks for each artist.

Answer 1

The subquery with LIMIT 3 looks very suspicious to me.

I am not an expert on SQL standards, but I am tempted to say that this is the cause of the error. Despite the fact that MySQL seems to allow you to compare 1 column (value) with many rows (you want to get 3 of them) - I would not try to do this if I had no choice.

I do not believe that:

 A join B on b.column = (select some-multi-records-sub-query) 

will do the right job and do the necessary sub-joins (and it just needs a different connection between B and the subquery).

I think that instead, MySQL is trying to compare a value with a 3-line rowset, which is always false, and that is why you are not getting any rows.

You can try something like this (not sure if this will work right away, you will have to debug it, but I think the idea is clear):

 exp_weblog_titles AS artist ON fav.entry_id = artist.entry_id JOIN exp_relationships AS r1 ON r1.rel_child_id = artist.entry_id join ( SELECT t.entry_id as entry_id FROM exp_weblog_titles AS t WHERE r1.rel_parent_id = t.entry_id t.weblog_id = 3 AND t.entry_date < UNIX_TIMESTAMP() AND t.status = 'open' ORDER BY t.entry_date DESC LIMIT 3 -- I want 3 tracks per artist ) as t2 exp_weblog_titles AS tracks ON tracks.entry_id = t2.entry_id 

Answer 2

What you described as your process is how I will deal with it. I do not know a single replacement.

In your particular query, I assume this is due to joining exp_weblog_titles, which does not seem to be related to any of the other tables in the query.