StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied! <p>This appears to be a bug in MySQL, about which I have <a href="http://bugs.mysql.com/bug.php?id=70466" rel="nofollow">filed a report</a>. I have narrowed it to the following test case, which one would expect to return a single record (but it does not):</p> <pre class="lang-sql prettyprint-override"><code>CREATE TABLE t (x INT NULL); -- table with nullable column INSERT INTO t VALUES (0); -- but non null data SELECT a.x -- select our nullable column FROM t a, (SELECT NULL) b -- joining it with anything at all WHERE EXISTS ( -- but filter on a subquery SELECT * FROM (SELECT NULL) c -- doesn't really matter what HAVING a.x IS NOT NULL -- provided there is some correlated condition -- on our nullable column in the HAVING clause ) ORDER BY RAND() -- then perform a filesort on the outer query </code></pre> <p>See it on <a href="http://sqlfiddle.com/#!2/8d013/1/0" rel="nofollow">sqlfiddle</a>.</p> <p>In your case, you can do a number of things to fix this:</p> <ol> <li><p>Avoid the correlated subquery by rewriting as a join:</p> <pre class="lang-sql prettyprint-override"><code>SELECT * FROM people AS p LEFT JOIN (people_stages AS s NATURAL JOIN ( SELECT person_id, MAX(created) created FROM people_stages GROUP BY person_id ) t) ON s.person_id = p.id ORDER BY p.last_name </code></pre></li> <li><p>If you want to keep the correlated subquery (which can generally yield poor performance but is often easier to understand), use <code>WHERE</code> instead of <code>HAVING</code>:</p> <pre class="lang-sql prettyprint-override"><code>SELECT * FROM people AS p LEFT JOIN people_stages AS s ON s.person_id = p.id WHERE s.created = ( SELECT MAX(created) FROM people_stages WHERE person_id = s.person_id ) ORDER BY p.last_name </code></pre></li> <li><p>If you're unable to change the query, you should find that making the <code>people_stages.person_id</code> column non-nullable will get around the problem:</p> <pre class="lang-sql prettyprint-override"><code>ALTER TABLE people_stages MODIFY person_id BIGINT UNSIGNED NOT NULL </code></pre> <p>It seems that having an index on that column (which would be required to effect a foreign key constraint) may also help:</p> <pre class="lang-sql prettyprint-override"><code>ALTER TABLE people_stages ADD FOREIGN KEY (person_id) REFERENCES people (id) </code></pre></li> <li><p>Alternatively one could remove <code>people_stages.person_id</code> from the select list, or adjust the data model/indexing/query strategy to avoid a filesort (may not be practical in this case, but I mention them here for completeness).</p></li> </ol>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload