Skip to content

Nested include causes wrong sql on postgres when quoteIdentifiers is false #6351

@topikachu

Description

@topikachu

What you are doing?

Post a minimal code sample that reproduces the issue, including models and associations

test('nested include (left outer join)', function () {
      var User = Support.sequelize.define('User', {
          name: DataTypes.STRING,
          age: DataTypes.INTEGER
        },
        {
          freezeTableName: true
        });
      var Post = Support.sequelize.define('Post', {
          title: DataTypes.STRING
        },
        {
          freezeTableName: true
        });
      var Comment = Support.sequelize.define('Comment', {
          title: DataTypes.STRING
        },
        {
          freezeTableName: true
        });

      User.Posts = User.hasMany(Post, {foreignKey: 'user_id'});
      Post.Comments = Post.hasMany(Comment, {foreignKey: 'post_id'});

      expectsql(sql.selectQuery('User', {
        attributes: ['name', 'age'],
        include: Model.$validateIncludedElements({
          include: [{
            attributes: ['title'],
            association: User.Posts,
            include: [
              {
                model: Comment
              }
            ]
          }],
          model: User
        }).include,
        model: User
      }, User), {
        default: 'SELECT [User].[name], [User].[age], [Posts].[id] AS [Posts.id], [Posts].[title] AS [Posts.title], [Posts.Comments].[id] AS [Posts.Comments.id], [Posts.Comments].[title] AS [Posts.Comments.title], [Posts.Comments].[createdAt] AS [Posts.Comments.createdAt], [Posts.Comments].[updatedAt] AS [Posts.Comments.updatedAt], [Posts.Comments].[post_id] AS [Posts.Comments.post_id] FROM [User] AS [User] LEFT OUTER JOIN [Post] AS [Posts] ON [User].[id] = [Posts].[user_id] LEFT OUTER JOIN [Comment] AS [Posts.Comments] ON [Posts].[id] = [Posts.Comments].[post_id];',
        postgres: 'SELECT User.name, User.age, Posts.id AS "Posts.id", Posts.title AS "Posts.title", "Posts.Comments".id AS "Posts.Comments.id", "Posts.Comments".title AS "Posts.Comments.title", "Posts.Comments".createdAt AS "Posts.Comments.createdAt", "Posts.Comments".updatedAt AS "Posts.Comments.updatedAt", "Posts.Comments".post_id AS "Posts.Comments.post_id" FROM User AS User LEFT OUTER JOIN Post AS Posts ON User.id = Posts.user_id LEFT OUTER JOIN Comment AS "Posts.Comments" ON Posts.id = "Posts.Comments".post_id;'
      });
    });

  });

What do you expect to happen?

The sql should generated as expecedt

What is actually happening?

SELECT 
User.name, 
User.age, Posts.id AS "Posts.id", 
Posts.title AS "Posts.title", 
Posts.Comments.id AS "Posts.Comments.id", #wrong
Posts.Comments.title AS "Posts.Comments.title",  #wrong
Posts.Comments.createdAt AS "Posts.Comments.createdAt", #wrong
Posts.Comments.updatedAt AS "Posts.Comments.updatedAt", #wrong
Posts.Comments.post_id AS "Posts.Comments.post_id" #wrong
FROM User AS User 
  LEFT OUTER JOIN Post AS Posts 
    ON User.id = Posts.user_id 
  LEFT OUTER JOIN Comment AS Posts.Comments #wrong
    ON Posts.id = Posts.Comments.post_id; 

The nested table alias has no quote.

Dialect: postgres
Database version: any
Sequelize version: 3.23.6

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions