In grouping nested query, the clause for ordering is invalid.

Expected behavior

I need to sum up the article of a contest on steemit. I need to search the last record of each group. In the inner nesting I plan to sort the records in descending order of the field "timestamp", and in the outer nesting I plan to group the records by "permlink". According to my expectations, I should get the recent record of each group.

Actual behavior

In order to test, I wrote four queries to compare. The first is a simple query:

SELECT * FROM sbds_tx_comments WHERE author =  "tvb" AND permlink NOT LIKE  're%'AND TIMESTAMP >  '2018-01-24 00:00:00'

The second is a simple query with ordering:

SELECT * FROM sbds_tx_comments WHERE author =  "tvb" AND permlink NOT LIKE  're%'AND TIMESTAMP >  '2018-01-24 00:00:00' ORDER BY TIMESTAMP DESC

The third is a nested query:

SELECT permlink, TIMESTAMP FROM ( SELECT * FROM sbds_tx_comments WHERE author =  "tvb" AND permlink NOT LIKE  're%' AND TIMESTAMP >  '2018-01-24 00:00:00' ORDER BY TIMESTAMP DESC)tmp GROUP BY permlink

The fourth is another nested query:

SELECT permlink, TIMESTAMP FROM ( SELECT * FROM sbds_tx_comments WHERE author =  "tvb" AND permlink NOT LIKE  're%' AND TIMESTAMP >  '2018-01-24 00:00:00' ORDER BY TIMESTAMP ASC)tmp GROUP BY permlink

How to reproduce

I will provide the screenshot for the results of these queries. The result of the first query is as below:

According to the picture, in the records whose "permlink" is equal to "laodr-or", the "timestamp" of the first record is "2018-01-25 09:53:42". The record is the first in the physical order of the table.

---

The result of the second query is as below:

In the picture, we can see that, in the records whose "permlink" is equal to "laodr-or", the timestamp" of the first record is "2018-01-26 05:15:57". That means the last modified version of the link "laodr-or" is at "2018-01-26 05:15:57".

---

Then let me show the result of the thrid query:

Aft
er grouping, the result should show the last record of each group, whose timestamp is max. But in the screenshot, we can see ,the first record whose "link" is "laodr-or" is at "2018-01-25 09:53:42", which is the same as the first query, not the second!

---

Let's watch the fourth:

The result is the same as the first and the third one.

---

In conclusion, in nested query, grouping after sorting, the ordering is invalid. In each group, the result the first record of the physical order of the table.

Chrome version 63 64bit
Win 10 64 bit
(Both of them are uninfluential in the bug.)

I tested the bug with phpMyAdmin - 2.11.9.2. Of course the bug still exists when I use php to connect the SBDS. And I have exported a few data to my local mysql database, there isn't the bug.

---

Posted on Utopian.io - Rewarding Open Source Contributors

---