I have a join that works but seems hacky because it orders the subquery and uses group by to filter everything but the top subquery result. Without doing so, the cartesian product of s and print_issues.pub_date would be returned.

FROM print_issues, 
  ( select pub_date, page_size 
    from print_page_size, 
    where effective_date < print_issues.pub_date 
    order by effective_date desc
  ) as s 
WHERE s.pub_date = print_issues.pub_date 
group by print_issues.pub_date 
order by print_issues.pub_date desc

The query is an artifact of the schema of print_page_size. That table has the columns page_size and effective-date. New entries with a greater effective_date supersede old ones. The goal of the query is to unite the print_issue table with the page_size in effect when each issue was published.

Sample Data:

TABLE: print_page_size
effective_date      page_size
2014-01-01          100
2017-05-01          105

TABLE: print_issues
pub_date      x
2017-04-26    "Random"
2017-05-02    "Data"

print_issues.pub_date          print_issues.x    s.page_size
2017-04-26                     "Random"          100
2017-05-02                     "Data"            105      

My questions: Is there a problem with this approach? Is there a more direct way to to achieve the desired result?

  • 2
    What is the query supposed to return? I suggest you add sample data and expected output. – ypercubeᵀᴹ Jun 15 '17 at 17:14
  • If there could be multiple page_size values for a given pub_date in print_page_size, you're picking one of them at random. Same holds true for multiple possible values of print_issues.x. – RDFozz Jun 15 '17 at 17:19
  • @ypercubeᵀᴹ I've added sample data and output – AndrewE Jun 15 '17 at 17:51
  • @RDFozz That's what I was worried about. However when I use the query it always picks the top resulting page size (the one I want) and discards the subsequent results. If it was non-deterministic, shouldn't the 2017-05-02 result above sometimes return 100 and other times 105? I've been looking at the documentation but I have found the behavior of group by in this situation. – AndrewE Jun 15 '17 at 17:55

Your query is non-deterministic as it abuses GROUP BY.

It may give incorrect/unexpected results in any version of MySQL, depending on the execution plan and available indexes. You may not have seen such behaviour because it hasn't happened yet (you were just lucky or the indexes and size of the table have up to now lead to plans that give correct results) or it happened but you didn't notice.

There are versions (MariaDB 5.3+) where similar queries - not sure about this one but similar - will almost always give incorrect results due to some optimizations that remove the redundant ORDER BY in the subquery.

There are versions of MySQL (5.7) where you'll get an error if you try the query - exactly because it might give inconsistent results. In that version, you'll have to change the default settings (and get the wrong non-deterministic behaviour) or you'll have to rewrite/correct the query.

In short, don't use it.

I would write the query like this. Much simpler, deterministic and probably more efficient as there is no GROUP BY and doesn't join the pi table twice:

         ( SELECT   pps.page_size
           FROM     print_page_size AS pps
           WHERE    pps.effective_date < pi.pub_date
           ORDER BY pps.effective_date DESC
           LIMIT    1
         ) AS page_size
         print_issues AS pi
         pub_date ;              -- or whatever

An index on print_page_size (effective_date, pagesize) would be further help.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.