From a discussion on Freenode (and don’t feel bad if you don’t get it; I had to ask Sinisa and Monty to explain it to me. Sinisa explained how it worked, and Monty found a bug.)–<\/p>\n
INSERT INTO t VALUES (RAND() * 50); What would you expect from the following?<\/p>\n The first query:<\/p>\n is equivalent to:<\/p>\n Since the The second query:<\/p>\n Order all the rows by `a`. That’s roughly 20 rows of value ‘1’, then 20 rows of value ‘2’, then 20 rows of value ‘3’, etc. Take the row at offset 19. Assuming even distribution of our random data, your value should be ‘1’.<\/p>\n The third query:<\/p>\n The From a discussion on Freenode (and don’t feel bad if you don’t get it; I had to ask Sinisa and Monty to explain it to me. Sinisa explained how it worked, and Monty found a bug.)– USE test; DROP TABLE IF EXISTS `t`; CREATE TABLE `t` ( `a` int(11) DEFAULT NULL ); INSERT INTO t […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[6],"tags":[],"class_list":["post-123","post","type-post","status-publish","format-standard","hentry","category-gotchas"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/s2IBF1-123","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/123","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=123"}],"version-history":[{"count":6,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/123\/revisions"}],"predecessor-version":[{"id":165,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/123\/revisions\/165"}],"wp:attachment":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=123"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=123"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=123"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}USE test;
\nDROP TABLE IF EXISTS `t`;
\nCREATE TABLE `t` (
\n `a` int(11) DEFAULT NULL
\n);<\/p>\n
\nINSERT INTO t SELECT RAND() * 50 FROM t;<\/code>
\nRepeat that last INSERT a few times until you have about 1000 rows.<\/p>\nSELECT * FROM t WHERE false UNION SELECT * FROM t ORDER BY a LIMIT 19, 1;
\n(SELECT * FROM t WHERE false) UNION (SELECT * FROM t ORDER BY a LIMIT 19, 1);
\n(SELECT * FROM t WHERE false) UNION (SELECT * FROM t ORDER BY a) LIMIT 19, 1;<\/code><\/p>\n
\n– UNION<\/code> is equivalent to UNION DISTINCT<\/code>
\n– an ORDER BY<\/code> with no LIMIT<\/code> is optimized away in UNION<\/code>ed queries<\/p>\nSELECT * FROM t WHERE false UNION SELECT * FROM t ORDER BY a LIMIT 19, 1;<\/code><\/p>\n(SELECT * FROM t WHERE false UNION SELECT * FROM t) ORDER BY a LIMIT 19, 1;<\/code><\/p>\nUNION<\/code> is by default DISTINCT<\/code>, get each distinct value from the table, then order by `a`. Take the row at offset 19. Assuming even distribution of our random data, your value should be ’19’.<\/p>\n(SELECT * FROM t WHERE false) UNION (SELECT * FROM t ORDER BY a LIMIT 19, 1);<\/code><\/p>\n(SELECT * FROM t WHERE false) UNION (SELECT * FROM t ORDER BY a) LIMIT 19, 1;<\/code><\/p>\nORDER BY<\/code> is discarded. We get distinct values in random order. Take the row at offset 19. Since the values are in random order, the value is not predictable.<\/spoiler><\/p>\n","protected":false},"excerpt":{"rendered":"