Different Result Query When Use Mysql And Mariadb

Here is my problem: My database have table Book, Post. Each book has many post Table posts has field 'book_id', that is foreign key reference table Book primary key (id). This is

Solution 1:

You can try the below queries which does the job of getting the last post from each book

select
b.id,
b.name,
p.content,
p.published_date
from book b 
join post p on p.book_id = b.id
left join post p1 on p1.book_id = p.book_id and p1.published_date > p.published_date
where p1.id isnull;

OR

select 
b.id,
b.name,
p.content,
p.published_date
from book b 
join post p on p.book_id = b.id
wherenotexists(select1from post p1 
  where p.book_id = p1.book_id
  and p1.published_date > p.published_date
)

DEMO

Solution 2:

Try this:

SELECT b.*, p.*  
FROM my_book_store.books AS b 
INNER JOIN posts p ON b.id = p.book_id
INNER JOIN (SELECT p.book_id, MAX(p.published_date) published_date
            FROM posts p 
            WHERE posts.published = 1AND posts.published_date <= NOW() 
            GROUPBY p.book_id
          ) AS p1 ON p.book_id = p1.book_id AND p.published_date = p1.published_date 
GROUPBY b.id 
ORDERBY p.published_date DESC 
LIMIT 100

Solution 3:

The problem seems to be that you're only grouping by Book.id but select a lot of other non-aggregated values, so actual query results depend on the execution plan the optimizer came up with. See also

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. [...] However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group.

THE SERVER IS FREE TO CHOOSE ANY VALUE from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause.

Different result query when use mysql and mariadb