Fix MySQL commitNewEntries (#8223)

I just realised that `commitNewEntries()` was not sorting articles properly before insertion in database when using MySQL: Articles were not sorted by publication date as expected from the temporary table before insertion in the final table. MySQL was not picking the correct field, so fixed with an explicit alias.
Discovered because I did some tests with MySQL in https://github.com/FreshRSS/FreshRSS/pull/6957
At the same time, I did the same change for PostgreSQL and SQLite although those were not affected.

3 files changed, 44 insertions, 42 deletions

diff --git a/app/Models/EntryDAO.php b/app/Models/EntryDAO.php
index 0acc2c510..572c9054d 100644
--- a/app/Models/EntryDAO.php
+++ b/app/Models/EntryDAO.php
@@ -248,18 +248,18 @@ SQL;
 
 	public function commitNewEntries(): bool {
 		$sql = <<<'SQL'
-SET @rank=(SELECT MAX(id) - COUNT(*) FROM `_entrytmp`);
-
-INSERT IGNORE INTO `_entry` (
-	id, guid, title, author, content_bin, link, date, `lastSeen`,
-	hash, is_read, is_favorite, id_feed, tags, attributes
-)
-SELECT @rank:=@rank+1 AS id, guid, title, author, content_bin, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes
-FROM `_entrytmp`
-ORDER BY date, id;
-
-DELETE FROM `_entrytmp` WHERE id <= @rank;
-SQL;
+			SET @rank=(SELECT MAX(id) - COUNT(*) FROM `_entrytmp`);
+
+			INSERT IGNORE INTO `_entry` (
+				id, guid, title, author, content_bin, link, date, `lastSeen`,
+				hash, is_read, is_favorite, id_feed, tags, attributes
+			)
+			SELECT @rank:=@rank+1 AS id, guid, title, author, content_bin, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes
+			FROM `_entrytmp` etmp
+			ORDER BY etmp.date, etmp.id;
+
+			DELETE FROM `_entrytmp` WHERE id <= @rank;
+			SQL;
 		$hadTransaction = $this->pdo->inTransaction();
 		if (!$hadTransaction) {
 			$this->pdo->beginTransaction();
diff --git a/app/Models/EntryDAOPGSQL.php b/app/Models/EntryDAOPGSQL.php
index 91068919d..05850632f 100644
--- a/app/Models/EntryDAOPGSQL.php
+++ b/app/Models/EntryDAOPGSQL.php
@@ -90,22 +90,24 @@ class FreshRSS_EntryDAOPGSQL extends FreshRSS_EntryDAOSQLite {
 	#[\Override]
 	public function commitNewEntries(): bool {
 		//TODO: Update to PostgreSQL 9.5+ syntax with ON CONFLICT DO NOTHING
-		$sql = 'DO $$
-DECLARE
-maxrank bigint := (SELECT MAX(id) FROM `_entrytmp`);
-rank bigint := (SELECT maxrank - COUNT(*) FROM `_entrytmp`);
-BEGIN
-	INSERT INTO `_entry`
-		(id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes)
-		(SELECT rank + row_number() OVER(ORDER BY date, id) AS id, guid, title, author, content,
-			link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes
-			FROM `_entrytmp` AS etmp
-			WHERE NOT EXISTS (
-				SELECT 1 FROM `_entry` AS ereal
-				WHERE (etmp.id = ereal.id) OR (etmp.id_feed = ereal.id_feed AND etmp.guid = ereal.guid))
-			ORDER BY date, id);
-	DELETE FROM `_entrytmp` WHERE id <= maxrank;
-END $$;';
+		$sql = <<<'SQL'
+			DO $$
+			DECLARE
+			maxrank bigint := (SELECT MAX(id) FROM `_entrytmp`);
+			rank bigint := (SELECT maxrank - COUNT(*) FROM `_entrytmp`);
+			BEGIN
+				INSERT INTO `_entry`
+					(id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes)
+					(SELECT rank + row_number() OVER(ORDER BY etmp.date, etmp.id) AS id, guid, title, author, content,
+						link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes
+						FROM `_entrytmp` AS etmp
+						WHERE NOT EXISTS (
+							SELECT 1 FROM `_entry` AS ereal
+							WHERE (etmp.id = ereal.id) OR (etmp.id_feed = ereal.id_feed AND etmp.guid = ereal.guid))
+						ORDER BY etmp.date, etmp.id);
+				DELETE FROM `_entrytmp` WHERE id <= maxrank;
+			END $$;
+			SQL;
 		$hadTransaction = $this->pdo->inTransaction();
 		if (!$hadTransaction) {
 			$this->pdo->beginTransaction();
diff --git a/app/Models/EntryDAOSQLite.php b/app/Models/EntryDAOSQLite.php
index c272272b0..4ae4d3579 100644
--- a/app/Models/EntryDAOSQLite.php
+++ b/app/Models/EntryDAOSQLite.php
@@ -81,20 +81,20 @@ class FreshRSS_EntryDAOSQLite extends FreshRSS_EntryDAO {
 	#[\Override]
 	public function commitNewEntries(): bool {
 		$sql = <<<'SQL'
-DROP TABLE IF EXISTS `tmp`;
-CREATE TEMP TABLE `tmp` AS
-	SELECT id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes
-	FROM `_entrytmp`
-	ORDER BY date, id;
-INSERT OR IGNORE INTO `_entry`
-	(id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes)
-	SELECT rowid + (SELECT MAX(id) - COUNT(*) FROM `tmp`) AS id,
-	guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes
-	FROM `tmp`
-	ORDER BY date, id;
-DELETE FROM `_entrytmp` WHERE id <= (SELECT MAX(id) FROM `tmp`);
-DROP TABLE IF EXISTS `tmp`;
-SQL;
+			DROP TABLE IF EXISTS `tmp`;
+			CREATE TEMP TABLE `tmp` AS
+				SELECT id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes
+				FROM `_entrytmp`
+				ORDER BY date, id;
+			INSERT OR IGNORE INTO `_entry`
+				(id, guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes)
+				SELECT rowid + (SELECT MAX(id) - COUNT(*) FROM `tmp`) AS id,
+				guid, title, author, content, link, date, `lastSeen`, hash, is_read, is_favorite, id_feed, tags, attributes
+				FROM `tmp` t
+				ORDER BY t.date, t.id;
+			DELETE FROM `_entrytmp` WHERE id <= (SELECT MAX(id) FROM `tmp`);
+			DROP TABLE IF EXISTS `tmp`;
+			SQL;
 		$hadTransaction = $this->pdo->inTransaction();
 		if (!$hadTransaction) {
 			$this->pdo->beginTransaction();