Description:
We have a query:
q1 =
SELECT ... FROM t1 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...
UNION
SELECT ... FROM t2 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...
UNION
SELECT ... FROM t3 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...
Running embedded SQL in PHP Flourish framework functions (buffered result) in a loop, I have different results when running a view and the exact same query defined in the view AND using a LIMIT in both cases. Query q1 consists of 3 selects, each doing an inner join. All 3 selects are put together with 2 unions. A view (let's call it someview) with q1 is created. When doing SELECT someview ORDER BY last_updated ASC LIMIT $i, 1000; I get a different result when doing either of the following:
1) q1 ORDER BY last_updated ASC LIMIT $i, 1000; (the selects between the unions are surrounded by parentheses () ). That is:
(SELECT ... FROM t1 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...)
UNION
(SELECT ... FROM t2 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...)
UNION
(SELECT ... FROM t3 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...)
ORDER BY last_updated ASC LIMIT $i, 1000
2) SELECT * FROM (q1) d ORDER BY d.last_updated ASC LIMIT $i, 1000; That is:
SELECT * FROM ((SELECT ... FROM t1 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...)
UNION
(SELECT ... FROM t2 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...)
UNION
(SELECT ... FROM t3 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...)) d
ORDER BY d.last_updated ASC LIMIT $i, 1000
I end up processing a lot less rows than in both of these cases than in the view. So the results from the view are correct, whereas the results of the same query NOT in the view are incorrect.
When not using the LIMIT, then I get the correct results, but in practice, I need to use the LIMIT.
It seems to me that the LIMIT start is not being remembered by MySQL when not used in a view.
How to repeat:
You should just create a table called orders with an order_id and some other columns. Then create 3 tables called t1, t2 and t3 (these hold transaction info). Each of them should have a unique ID that's also their PK, an FK (perhaps unofficial one) to order_id and a bunch of similar columns.
In PHP, do the following:
$i = 0;
while(1) {
$result = $db->query("one of three ways to: select from the view or query with a LIMIT %i, 1000", $i);
foreach($result AS $rows) {
// In practice, some processing is done with the rows prior to insert, but for our sake, just insert.
insert into some table the order_id, transaction_id concatenated with table name, etc...
}
$i += 1000;
}
Options for the first query:
1) View:
Before the first loop, create or replace the view:
CREATE OR REPLACE VIEW someview AS SELECT ... FROM t1 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...
UNION
SELECT ... FROM t2 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...
UNION
SELECT ... FROM t3 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...
In the first loop:
SELECT someview ORDER BY last_updated ASC LIMIT $i, 1000;
2. Selecting q1 first way (no view).
(SELECT ... FROM t1 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...)
UNION
(SELECT ... FROM t2 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...)
UNION
(SELECT ... FROM t3 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...)
ORDER BY last_updated ASC LIMIT $i, 1000
3. Selecting q1 second way (no view).
SELECT * FROM ((SELECT ... FROM t1 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...)
UNION
(SELECT ... FROM t2 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...)
UNION
(SELECT ... FROM t3 a INNER JOIN orders b ON a.order_id = b.order_id WHERE ...)) d
ORDER BY d.last_updated ASC LIMIT $i, 1000
Suggested fix:
I'm not sure what that would be, but it almost certainly has something to do with the LIMIT start not being remembered and/or the rows read previously not being "remembered". Logically, there is no reason why I shouldn't have the same results in 1, 2 and 3 (described in "How to repeat"). In fact, that's what SQL is supposed to do.