Bug #68904 View and Select Result Set Mismatch
Submitted: 9 Apr 2013 21:00 Modified: 9 May 2013 23:58
Reporter: Ben Brown Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.5.24 OS:Windows (WAMP/Apache 2.4.2)
Assigned to: CPU Architecture:Any

[9 Apr 2013 21:00] Ben Brown
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.
[9 Apr 2013 21:01] Ben Brown
In "How to Repeat", I forgot to add a break in the loop. This code would go right below the query in the while loop:

if(rows->countReturnedRows() < 1) {
    break;
}
[9 Apr 2013 23:58] MySQL Verification Team
Thank you for the bug report. Please try version 5.5.30 if the issue still is observed please provide the complete test case (create statements, insert data, actual results and expected ones). Thanks.
[10 May 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".