sequelize: Nested include with limit and order generates incorrect SQL

What are you doing?

The code in line 39 (return User.findAll( ...) is to find top 25 users and their Posts, then order by users createdAt time

'use strict'

const Sequelize = require('sequelize')

const sequelize = new Sequelize('postgres://user@localhost:5432/test', {
  logging: console.log
})

const User = sequelize.define('User', {
  id: {
    type: Sequelize.UUID,
    primaryKey: true
  },
  username: Sequelize.STRING,
  password: Sequelize.STRING,
  createdAt: Sequelize.DATE,
  updatedAt: Sequelize.DATE
})
const Post = sequelize.define('Post', {
  id: {
    type: Sequelize.UUID,
    primaryKey: true
  },
  title: Sequelize.STRING,
  content: Sequelize.STRING,
  ownerId: Sequelize.UUID,
  createdAt: Sequelize.DATE,
  updatedAt: Sequelize.DATE
})

User.hasMany(Post, {foreignKey: 'ownerId', as: 'ownedPost'})
Post.belongsTo(User, {foreignKey: 'ownerId', as: 'owner'})

sequelize.authenticate()
  .then(() => {
    return sequelize.sync()
  })
  .then(() => {
    return User.findAll({
      limit: 25,
      include: ['ownedPost'],
      order: ['createdAt']
    }).then((result) => {
      console.log(result)
      return sequelize.close()
    })
  })

What do you expect to happen?

expect order both in subquery and main query. it works correctly in sequelize <= 4.8.3

SELECT "User".*, "ownedPost"."id" AS "ownedPost.id", "ownedPost"."title" AS "ownedPost.title", "ownedPost"."content" AS "ownedPost.content", "ownedPost"."ownerId" AS "ownedPost.ownerId", "ownedPost"."createdAt" AS "ownedPost.createdAt", "ownedPost"."updatedAt" AS "ownedPost.updatedAt" 
FROM (
    SELECT "User"."id", "User"."username", "User"."password", "User"."createdAt", "User"."updatedAt" FROM "Users" AS "User" ORDER BY "User"."createdAt" LIMIT 25) AS "User" 
LEFT OUTER JOIN "Posts" AS "ownedPost" ON "User"."id" = "ownedPost"."ownerId"
ORDER BY "User"."createdAt"

What is actually happening?

but in sequelize 4.8.4, in main query isn’t order users createdAt, therefore the result is incorrect. maybe it’s caused by https://github.com/sequelize/sequelize/pull/8278

SELECT "User".*, "ownedPost"."id" AS "ownedPost.id", "ownedPost"."title" AS "ownedPost.title", "ownedPost"."content" AS "ownedPost.content", "ownedPost"."ownerId" AS "ownedPost.ownerId", "ownedPost"."createdAt" AS "ownedPost.createdAt", "ownedPost"."updatedAt" AS "ownedPost.updatedAt" 
FROM (
  SELECT "User"."id", "User"."username", "User"."password", "User"."createdAt", "User"."updatedAt" 
  FROM "Users" AS "User" 
  ORDER BY "User"."createdAt" LIMIT 25) AS "User" 
LEFT OUTER JOIN "Posts" AS "ownedPost" ON "User"."id" = "ownedPost"."ownerId";

Dialect: postgres Dialect version: pg:7.3.0, pg-hstore: 2.3.2 Database version: 9.6.5 Sequelize version: 4.8.4 Tested with master branch: YES