SQL query finds nothing, but returns 1 row with all nulls

Question

I have this query:

select problems.problem_id , creator_member_id , problem_title , problem_description , sum( vote ) as totalVotes , DAYOFMONTH(problem_date) , DAYNAME(problem_date) , YEAR(problem_date) , MONTH(problem_date) , first_name , last_name , email , small_thumb , mid_thumb 
    from problems 
        left join problem_votes 
            on problems.problem_id = problem_votes.problem_id 
        left join users 
            on problems.creator_member_id = users.user_id 
        left join member_photo 
            on problems.creator_member_id = member_photo.member_id 
    where problems.problem_id = 1;

It matches nothing, and returns this:

-+--------------------+---------------------+------------+-----------+-------+-------------+-----------+
| problem_id | creator_member_id | problem_title | problem_description | totalVotes | DAYOFMONTH(problem_date) | DAYNAME(problem_date) | YEAR(problem_date) | MONTH(problem_date) | first_name | last_name | email | small_thumb | mid_thumb |
+------------+-------------------+---------------+---------------------+------------+--------------------------+-----------------------+--------------------+---------------------+------------+-----------+-------+-------------+-----------+
|       NULL |              NULL | NULL          | NULL                |       NULL |                     NULL | NULL                  |               NULL |                NULL | NULL       | NULL      | NULL  | NULL        | NULL      |
+------------+-------------------+---------------+---------------------+------------+--------------------------+-----------------------+--------------------+---------------------+------------+-----------+-------+-------------+-----------+

But I wonder why it returns anything at all? Is there something wrong with the schema? Or the query?

I am checking whether it returns nothing, and trying to return 404 page, but the system thinks there is 1 returned row, so it confuses my application.

Anything I might be doing wrong here?

Thanks!!

Can't see how it would return anything since you're explicitly forcing it to return only rows where problems.problem_id = 1. — Marc B, Jan 04 '12 at 16:36
I believe this has something to do with using aggregate functions like `sum` without a `group by` clause. Adding a `group by` clause may fix it, or you may also need to change your criteria to `having` instead of `where`. (Or I may be totally wrong.) — grossvogel, Jan 04 '12 at 16:45
I think it has to do something with the sum( vote ) as totalVotes in the select clause — GeekedOut, Jan 04 '12 at 16:47
@grossvogel yes you are right. When I take out that clause, it returns empty as expected. But the problem is that I need it to count still, lol - any way to get around this issue? — GeekedOut, Jan 04 '12 at 16:47
@GeekedOut: Try adding `GROUP BY problems.problem_id, creator_member_id, problem_title, problem_description, problem_date, first_name, last_name, email, small_thumb ,mid_thumb` at the end of the query. My usual practice is that every column that is in `select` but not aggregated (like `vote`) must be in the `group by` clause. (see the answer here: http://stackoverflow.com/questions/1244169/mysql-1140-mixing-of-group-columns) — grossvogel, Jan 04 '12 at 16:51
@GeekedOut - That wouldn't help you - you don't know if the given id should be present, or is simply bad input data. So you _shouldn't_ count the 'votes' as 0 - the proper answer is `null` (or perhaps a 'not found exception). If the id is coming from somewhere else, what happened to your referential integrity? — Clockwork-Muse, Jan 04 '12 at 16:54
@grossvogel that seems to have worked. If you want, post it as an answer and I'll accept it :) Thank you! — GeekedOut, Jan 04 '12 at 16:57
@X-Zero good point regarding the referential integrity. In this case the db setup isn't optimal, so if there is nothing matched on problem_id then there isn't much to return in that page anyway. — GeekedOut, Jan 04 '12 at 16:58

score 3 · Accepted Answer · edited May 23 '17 at 11:55

3

Try adding the following clause to the end of your query:

GROUP BY problems.problem_id, creator_member_id, problem_title, 
problem_description, problem_date, first_name, last_name, 
email, small_thumb ,mid_thumb

Best practice is that every column that is selected but not aggregated (like vote) must be in the group by clause, though MYSQL does not enforce this. Instead, it behaves strangely and confuses people. (See this answer for a better explanation.)

edited May 23 '17 at 11:55

Community

1
1

answered Jan 04 '12 at 17:04

grossvogel

6,694
1
25
36

This was my problem exactly. Coming from a PostgreSQL background, I was used to the DB not letting me be sloppy... – Nostradamnit Dec 06 '12 at 13:06

giammin · Answer 2 · 2012-01-04T17:16:01.780

The problem is due to

sum( vote ) as totalVotes

If you want to use an aggregate functions as SUM to group the result-set you have to use a statement as GROUP BY.

And if you want to filter that result you should use HAVING

http://database-programmer.blogspot.com/2008/04/group-by-having-sum-avg-and-count.html

In your case i think this should work:

SELECT problems.problem_id , creator_member_id , problem_title , problem_description , SUM( vote ) AS totalVotes , 
DAYOFMONTH(problem_date) , DAYNAME(problem_date) , YEAR(problem_date) , MONTH(problem_date) , first_name , last_name , email , small_thumb , mid_thumb 
FROM problems 
    LEFT JOIN problem_votes 
        ON problems.problem_id = problem_votes.problem_id 
    LEFT JOIN users 
        ON problems.creator_member_id = users.user_id 
    LEFT JOIN member_photo 
        ON problems.creator_member_id = member_photo.member_id
 WHERE problems.problem_id = 1; 
GROUP BY problems.problem_id , creator_member_id , problem_title , problem_description , problem_date , first_name , last_name , email , small_thumb , mid_thumb

score -1 · Answer 3 · answered Jan 04 '12 at 16:35

-1

First, make sure you do not have a row that with all NULL

Then: The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

Try change it to INNER JOIN

answered Jan 04 '12 at 16:35

Eric Yin

8,737
19
77
118

1

The WHERE clause is filtering `problems.problem_id`, which is a value in the left most table in the joins. Yet the results show NULL for `problems.problem_id`. Changing to INNER JOIN should have no effect here. I suspect the issue is outside of the SQL itself. – MatBailie Jan 04 '12 at 16:37

SQL query finds nothing, but returns 1 row with all nulls

3 Answers3