SitePoint Sponsor

User Tag List

Results 1 to 1 of 1
  1. #1
    SitePoint Zealot
    Join Date
    May 2003
    Location
    Sarasota, FL
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query with subquery variably returns empty result set

    I have a rather uncomplicated query that will randomly returning an empty result set even though I know there are valid records.

    Here's the query:
    Code:
    mysql> SELECT p . * , a.username, a.location FROM photos p LEFT JOIN accounts a ON p.account = a.id WHERE p.id = ( SELECT photo FROM photos_photo_categories WHERE photo_category = '2b5458a0-02ad-4a8b-aaa5-686f6d3f5598' ORDER BY RAND( ) LIMIT 0 , 1 );
    Empty set (0.01 sec)
    
    mysql> SELECT p . * , a.username, a.location FROM photos p LEFT JOIN accounts a ON p.account = a.id WHERE p.id = ( SELECT photo FROM photos_photo_categories WHERE photo_category = '2b5458a0-02ad-4a8b-aaa5-686f6d3f5598' ORDER BY RAND( ) LIMIT 0 , 1 );
    +--------------------------------------+---------------------+---------------------+-----------+---------------------+--------+--------------------------------------+------------+---------+----------+------------+-------------------+----------+----------+
    | id                                   | date_added          | date_updated        | is_active | microtime_added     | review | account                              | vote_count | vote_up | category | file_img   | file_original_img | username | location |
    +--------------------------------------+---------------------+---------------------+-----------+---------------------+--------+--------------------------------------+------------+---------+----------+------------+-------------------+----------+----------+
    | 90b8ea9d-bc47-44ad-b07a-84b54480f872 | 2008-05-07 11:29:32 | 2008-05-07 11:29:32 |         1 | 1210174172.56600020 |      1 | 3993d270-b70f-46e8-b0e5-72a78f4ad258 |          0 |       0 |          | 001_26.jpg | 001.JPG           | myuser   | asdf, as |
    +--------------------------------------+---------------------+---------------------+-----------+---------------------+--------+--------------------------------------+------------+---------+----------+------------+-------------------+----------+----------+
    1 row in set (0.01 sec)
    
    mysql> SELECT p . * , a.username, a.location FROM photos p LEFT JOIN accounts a ON p.account = a.id WHERE p.id = ( SELECT photo FROM photos_photo_categories WHERE photo_category = '2b5458a0-02ad-4a8b-aaa5-686f6d3f5598' ORDER BY RAND( ) LIMIT 0 , 1 );
    +--------------------------------------+---------------------+---------------------+-----------+---------------------+--------+--------------------------------------+------------+---------+----------+------------+-------------------+----------+----------+
    | id                                   | date_added          | date_updated        | is_active | microtime_added     | review | account                              | vote_count | vote_up | category | file_img   | file_original_img | username | location |
    +--------------------------------------+---------------------+---------------------+-----------+---------------------+--------+--------------------------------------+------------+---------+----------+------------+-------------------+----------+----------+
    | 1bb24e86-d5eb-41fe-b6fd-28c07dea37b5 | 2008-05-07 11:28:44 | 2008-05-07 11:28:44 |         1 | 1210174124.63900020 |      1 | 3993d270-b70f-46e8-b0e5-72a78f4ad258 |          0 |       0 |          | 001_25.jpg | 001.JPG           | myuser   | asdf, as |
    +--------------------------------------+---------------------+---------------------+-----------+---------------------+--------+--------------------------------------+------------+---------+----------+------------+-------------------+----------+----------+
    1 row in set (0.01 sec)
    
    mysql> SELECT p . * , a.username, a.location FROM photos p LEFT JOIN accounts a ON p.account = a.id WHERE p.id = ( SELECT photo FROM photos_photo_categories WHERE photo_category = '2b5458a0-02ad-4a8b-aaa5-686f6d3f5598' ORDER BY RAND( ) LIMIT 0 , 1 );
    +--------------------------------------+---------------------+---------------------+-----------+---------------------+--------+--------------------------------------+------------+---------+----------+------------+-------------------+----------+----------+
    | id                                   | date_added          | date_updated        | is_active | microtime_added     | review | account                              | vote_count | vote_up | category | file_img   | file_original_img | username | location |
    +--------------------------------------+---------------------+---------------------+-----------+---------------------+--------+--------------------------------------+------------+---------+----------+------------+-------------------+----------+----------+
    | 1bb24e86-d5eb-41fe-b6fd-28c07dea37b5 | 2008-05-07 11:28:44 | 2008-05-07 11:28:44 |         1 | 1210174124.63900020 |      1 | 3993d270-b70f-46e8-b0e5-72a78f4ad258 |          0 |       0 |          | 001_25.jpg | 001.JPG           | myuser   | asdf, as |
    | 90b8ea9d-bc47-44ad-b07a-84b54480f872 | 2008-05-07 11:29:32 | 2008-05-07 11:29:32 |         1 | 1210174172.56600020 |      1 | 3993d270-b70f-46e8-b0e5-72a78f4ad258 |          0 |       0 |          | 001_26.jpg | 001.JPG           | myuser   | asdf, as |
    +--------------------------------------+---------------------+---------------------+-----------+---------------------+--------+--------------------------------------+------------+---------+----------+------------+-------------------+----------+----------+
    2 rows in set (0.01 sec)
    You can see that running the same query multiple alternately returns one of the two photos in the photos_photo_categories table (see below) or an empty result set. I even got it to return BOTH rows a few times after running the query many times over!!?

    photos_photo_categories has only 4 entries:
    Code:
    mysql> SELECT * FROM photos_photo_categories;
    +--------------------------------------+--------------------------------------+
    | photo                                | photo_category                       |
    +--------------------------------------+--------------------------------------+
    | 1bb24e86-d5eb-41fe-b6fd-28c07dea37b5 | 17a85d8e-62e0-4ede-b5d5-bb30bdb145d6 |
    | 1bb24e86-d5eb-41fe-b6fd-28c07dea37b5 | 2b5458a0-02ad-4a8b-aaa5-686f6d3f5598 |
    | 90b8ea9d-bc47-44ad-b07a-84b54480f872 | 17a85d8e-62e0-4ede-b5d5-bb30bdb145d6 |
    | 90b8ea9d-bc47-44ad-b07a-84b54480f872 | 2b5458a0-02ad-4a8b-aaa5-686f6d3f5598 |
    +--------------------------------------+--------------------------------------+
    4 rows in set (0.00 sec)
    As you can see, the table contains only two photos, both with the same two photo_category's.

    Here are the results of selecting the photos manually:
    Code:
    mysql> SELECT * FROM photos WHERE id IN ('1bb24e86-d5eb-41fe-b6fd-28c07dea37b5','90b8ea9d-bc47-44ad-b07a-84b54480f872');
    +--------------------------------------+---------------------+---------------------+-----------+---------------------+--------+--------------------------------------+------------+---------+----------+------------+-------------------+
    | id                                   | date_added          | date_updated        | is_active | microtime_added     | review | account                              | vote_count | vote_up | category | file_img   | file_original_img |
    +--------------------------------------+---------------------+---------------------+-----------+---------------------+--------+--------------------------------------+------------+---------+----------+------------+-------------------+
    | 1bb24e86-d5eb-41fe-b6fd-28c07dea37b5 | 2008-05-07 11:28:44 | 2008-05-07 11:28:44 |         1 | 1210174124.63900020 |      1 | 3993d270-b70f-46e8-b0e5-72a78f4ad258 |          0 |       0 |          | 001_25.jpg | 001.JPG           |
    | 90b8ea9d-bc47-44ad-b07a-84b54480f872 | 2008-05-07 11:29:32 | 2008-05-07 11:29:32 |         1 | 1210174172.56600020 |      1 | 3993d270-b70f-46e8-b0e5-72a78f4ad258 |          0 |       0 |          | 001_26.jpg | 001.JPG           |
    +--------------------------------------+---------------------+---------------------+-----------+---------------------+--------+--------------------------------------+------------+---------+----------+------------+-------------------+
    2 rows in set (0.01 sec)
    As you can see, the photos are valid records. Yet when I run the original query multiple times it will often return the emtpy result. What is happening?

    I'm attaching the relevant SQL for the table.
    Attached Files Attached Files
    Chris Bloom
    Web Application Developer


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •