Put simply, I'm getting one single row all columns NULL. I should not get a row at all.

I'm trying to get the next "round" in a competition. I'm joining it to a roundgames table which lists all the games for each round in it. I then join that also to the games table.
I'm then getting the kickoff time greater than now.

There's isn't a round with games in the future so I should return an empty row but I'm getting one with NULL in each column.

Why?

SELECT r.*, MIN(g.gameId) FROM `rounds` r
                JOIN `roundgames` rg ON r.roundId = rg.roundId
                JOIN `games` g ON rg.gameId = g.gameId
                
                WHERE `competitionId` = 65 AND g.kickOff > now()

    What's the difference between an "empty row" and a row with NULL in each column? What should an empty row contain? Since you're using aggregate functions, you will get a row back to contain the result of the aggregation.

    I'm actually surprised that even runs, seeing as you're using an aggregate function without a GROUP BY clause to do any aggregation. Is every column in the rounds table functionally dependent on games.gameId or is MySQL being sloppy about aggregation again?

    Weedpacket or is MySQL being sloppy

    Yeah, I think MySQL assumes grouping or something, unlike PostgreSQL requiring you explicitly do the grouping.

        sneakyimp
        So (assuming it means grouping on all columns), there would be one aggregated row for each distinct primary key value in the table (if the primary key is among the columns being selected)?

        Something also mentioned on that page:

        If there are no matching rows, MIN() returns NULL.

        Which would explain why there is a result.

        Probably what's really wanted here is just an ORDER BY clause followed by limiting the result to one row (so that if there are no rows at all then there are no rows returned; instead of constructing a row to contain the results of functions on empty aggregates).

            Write a Reply...