kvesteri/sqlalchemy-continuum — pull request #82 by dtheodor (2014-11-11 00:06:38+00:00)
Title: One-to-many query fix

Hey,

The query produced for one-to-many versioned relationship produces unexpected results when the versioned 'many' items start to spread over different transaction ids: You may get back different versions of the same child item.

Original query for getting the tags of article with id 3 and transaction id 5:
```sql
SELECT *
FROM tags_version
WHERE article_id = 3
AND operation_type != 2
AND transaction_id IN (
SELECT max(transaction_id)
FROM tags_version
WHERE transaction_id <= 5
GROUP BY id
)
```
The subquery returns a tuple of max transactions per tag, but the IN clause there is satisfied as long as there is *any* tag whose max version is the current tag's version, not only tags with the same id as the current tag (current as in the current tag in the loop of the sql query).

The right query would use a tuple of (transaction_id, id) in the IN clause...
```sql
SELECT *
FROM tags_version
WHERE article_id = 3
AND operation_type != 2
AND (transaction_id, id) IN (
SELECT max(transaction_id), id
FROM tags_version
WHERE transaction_id <= 5
GROUP BY id
)
```

...but since is not valid sqlite the following one with `EXISTS` is, which I implemented in this patch
```sql
SELECT tags_version.*
FROM tags_version
WHERE tags_version.article_id = 3
AND tags_version.operation_type != 2
AND EXISTS (
SELECT 1
FROM tags_version as tags_version_2
WHERE tags_version_2.transaction_id <= 5
AND tags_version_2.id = tags_version.id
GROUP BY tags_version_2.id
HAVING max(tags_version_2.transaction_id) =
tags_version.transaction_id
)
```

There's also one with a `JOIN` that's probably better, but this would require more code refactoring.
```sql
SELECT tags_version.*
FROM tags_version
JOIN (
SELECT max(transaction_id) as max_tx, id
FROM tags_version
WHERE transaction_id <= 5
GROUP BY id
) max_txs
ON max_txs.max_tx = tags_version.transaction_id,
max_txs.id = tags_version.id
WHERE tags_version.article_id = 3
AND tags_version.operation_type != 2
```