MySQL bites my ass... again.

Having got all my data shovelled into MySQL, I foolishly decided that I'd like some of it back. I'd carefully normalised all my tables, so in order to retrieve it I needed to join across several tables plus I needed some subqueries in the select field list to do some lookups, so it ended up being a reasonably complex query - something along the lines of:

select
    a1.col1, c1.col2
    (select b1.col2 from b b1 where b1.col1 = a1.col2),
    (select b2.col2 from b b2 where b2.col1 = a1.col3)
from
    a a1 join b b3 on a1.col1 = b3.col1
    join c c1 on a1.col4 = c1.col1
order by
    b3.col4

Which worked just fine - right up until the rows where the value in a1.col3 was NULL, when instead of the second subselect returning the value NULL it returned the value looked up in the subselect on the previous line of the query!!!!! I've tried to create a smaller reproducible test case without success, and I really don't have the time to chase it down. Removing the third join on the b table makes the problem go away, but then I lose the required ordering - gah!

I can reformulate the query to remove the subselects by the crafty use of a left outer join, which will probably be more efficient anyway, but it should work as is. Bah.

I hates software!

Categories : Solaris, Tech, Work


Re: MySQL bites my ass... again.

Have you considered having a look at PostgreSQL then, since MySQL is not being friendly to you?

Re: MySQL bites my ass... again.

Yes, I did look at Postgres, but it too has it's problems - see my previous post on the subject.