dont use table in subselect of mark read query to not fail on mysql, fixes #173

2 files changed, 26 insertions, 34 deletions

diff --git a/db/itemmapper.php b/db/itemmapper.php
index 11f917270..99b315111 100644
--- a/db/itemmapper.php
+++ b/db/itemmapper.php
@@ -117,14 +117,12 @@ class ItemMapper extends Mapper implements IMapper {
 	public function readAll($highestItemId, $userId) {
 		$sql = 'UPDATE `*PREFIX*news_items` ' . 
 			'SET `status` = `status` & ? ' .
-			'WHERE `id` IN (' .
-				'SELECT `items`.`id` FROM `*PREFIX*news_items` `items` ' .
-				'JOIN `*PREFIX*news_feeds` `feeds` ' .
-					'ON `feeds`.`id` = `items`.`feed_id` '.
-					'AND `items`.`id` <= ? ' .
-					'AND `feeds`.`user_id` = ? ' .
-				') ';
-		$params = array(~StatusFlag::UNREAD, $highestItemId, $userId);
+                        'WHERE `feed_id` IN (' .
+                                'SELECT `id` FROM `*PREFIX*news_feeds` ' .
+                                        'WHERE `user_id` = ? ' .
+                                ') '.
+                        'AND `id` <= ?';
+                $params = array(~StatusFlag::UNREAD, $userId, $highestItemId);
 		$this->execute($sql, $params);
 	}
 
@@ -132,15 +130,13 @@ class ItemMapper extends Mapper implements IMapper {
 	public function readFolder($folderId, $highestItemId, $userId) {
 		$sql = 'UPDATE `*PREFIX*news_items` ' . 
 			'SET `status` = `status` & ? ' .
-			'WHERE `id` IN (' .
-				'SELECT `items`.`id` FROM `*PREFIX*news_items` `items` ' .
-				'JOIN `*PREFIX*news_feeds` `feeds` ' .
-					'ON `feeds`.`id` = `items`.`feed_id` '.
-					'AND `feeds`.`folder_id` = ? ' .
-					'AND `items`.`id` <= ? ' .
-					'AND `feeds`.`user_id` = ? ' .
-				') ';
-		$params = array(~StatusFlag::UNREAD, $folderId, $highestItemId, $userId);
+                        'WHERE `feed_id` IN (' .
+                                'SELECT `id` FROM `*PREFIX*news_feeds` ' .
+                                        'WHERE `folder_id` = ? ' .
+                                        'AND `user_id` = ? ' .
+                                ') '.
+                        'AND `id` <= ?';
+                $params = array(~StatusFlag::UNREAD, $folderId, $userId, $highestItemId);
 		$this->execute($sql, $params);
 	}
 
diff --git a/tests/unit/db/ItemMapperTest.php b/tests/unit/db/ItemMapperTest.php
index 6d0de6459..5f2639979 100644
--- a/tests/unit/db/ItemMapperTest.php
+++ b/tests/unit/db/ItemMapperTest.php
@@ -128,14 +128,12 @@ class ItemMapperTest extends \OCA\AppFramework\Utility\MapperTestUtility {
 	public function testReadAll(){
 		$sql = 'UPDATE `*PREFIX*news_items` ' . 
 			'SET `status` = `status` & ? ' .
-			'WHERE `id` IN (' .
-				'SELECT `items`.`id` FROM `*PREFIX*news_items` `items` ' .
-				'JOIN `*PREFIX*news_feeds` `feeds` ' .
-					'ON `feeds`.`id` = `items`.`feed_id` '.
-					'AND `items`.`id` <= ? ' .
-					'AND `feeds`.`user_id` = ? ' .
-				') ';
-		$params = array(~StatusFlag::UNREAD, 3, $this->user);
+                        'WHERE `feed_id` IN (' .
+                                'SELECT `id` FROM `*PREFIX*news_feeds` ' .
+                                        'WHERE `user_id` = ? ' .
+                                ') '.
+                        'AND `id` <= ?';
+                $params = array(~StatusFlag::UNREAD, $this->user, 3);
 		$this->setMapperResult($sql, $params);
 		$this->mapper->readAll(3, $this->user);
 	}	
@@ -144,15 +142,13 @@ class ItemMapperTest extends \OCA\AppFramework\Utility\MapperTestUtility {
 	public function testReadFolder(){
 		$sql = 'UPDATE `*PREFIX*news_items` ' . 
 			'SET `status` = `status` & ? ' .
-			'WHERE `id` IN (' .
-				'SELECT `items`.`id` FROM `*PREFIX*news_items` `items` ' .
-				'JOIN `*PREFIX*news_feeds` `feeds` ' .
-					'ON `feeds`.`id` = `items`.`feed_id` '.
-					'AND `feeds`.`folder_id` = ? ' .
-					'AND `items`.`id` <= ? ' .
-					'AND `feeds`.`user_id` = ? ' .
-				') ';
-		$params = array(~StatusFlag::UNREAD, 3, 6, $this->user);
+                        'WHERE `feed_id` IN (' .
+                                'SELECT `id` FROM `*PREFIX*news_feeds` ' .
+                                        'WHERE `folder_id` = ? ' .
+                                        'AND `user_id` = ? ' .
+                                ') '.
+                        'AND `id` <= ?';
+                $params = array(~StatusFlag::UNREAD, 3, $this->user, 6);
 		$this->setMapperResult($sql, $params);
 		$this->mapper->readFolder(3, 6, $this->user);
 	}