Stared and stared and stared at the "is null"/"is not null" constraints and had to post to twitter to find the answer. Very annoying that I missed something so obvious, but I still don't know why it would munge the value of "parent_pid" and "child_pid" in the first query. (Update: Of course I know why it would munge that value. I need a drink).
mysql> select parent_pid, child_pid, title from clip left join pip_pip on clip.pid = child_pid and parent_pid is null; +------------+-----------+-------------------------------+ | parent_pid | child_pid | title | +------------+-----------+-------------------------------+ | NULL | NULL | Another clip | | NULL | NULL | Clip 2 with attached variants | | NULL | NULL | Clip with attached variants | +------------+-----------+-------------------------------+ 3 rows in set (0.00 sec) mysql> select parent_pid, child_pid, title from clip left join pip_pip on clip.pid = child_pid and parent_pid is not null; +------------+-----------+-------------------------------+ | parent_pid | child_pid | title | +------------+-----------+-------------------------------+ | NULL | NULL | Another clip | | NULL | NULL | Clip 2 with attached variants | | b00btc34 | b0001234 | Clip with attached variants | +------------+-----------+-------------------------------+ 3 rows in set (0.00 sec)
