how to debug a query that has valid syntax, executes, but returns no results?

Question

So I'm writing a fairly involved query with a half dozen joins, a dependent subquery for [greatest-n-per-group] purposes, grouping, etc. It is syntactically valid, but I've clearly made at least one mistake because it returns nothing.

In the past I've debugged valid queries that return nothing by removing joins, executing subqueries on their own, removing WHERE conditions, and removing grouping to see what I would get but so far this one has me stumped. Are there better tools or techniques to use for this sort of thing?

This particular query is for MySQL if it matters for any platform-specific tools.

Edit: was hoping for query-agnostic tips, but since you need to have a query in order to run EXPLAIN, and you need EXPLAIN output to learn about what it means, I suppose I'll have to volunteer the query I'm currently working on ;)

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

resulting in this EXPLAIN output:

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 trimmed the query down to the bare essentials for this question... basically all I need it to do is return 3 tracks per artist.

Answer 1

What you described as your process is how I would go about it. There is no substitute that I know of.

For your particular query, I would guess it has to do with the join to exp_weblog_titles whcih doesn't seem to be connected to any of the other tables in the query.

Answer 2

The subquery with LIMIT 3 looks highly suspicious to me.

I am no SQL standards expert, but I am tempted to say that this is the reason of error. Even though MySQL seems to allow you to compare 1 column (value) to multi-row set (you want to get 3 of them) -- I'd not try to do it unless I have no choice.

I don't believe that:

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

will do the right thing and perform necessary sub-joins (and this will essentially require just another join, between B and subquery).

I think that instead, MySQL tries to compare value to 3-rows rowset which is always false, and this is the reason why you dont get any rows.

You may want to try something like this (not sure it will work right away, you'll 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