sequelize: MSSQL query fails with The column 'Id' was specified multiple times for 'Channel' starting with Sequelize 5.18.4

Issue Description

MS SQL query fails with the following error:

The column 'Id' was specified multiple times for 'Channel'.

This issue started with the version 5.18.4. Same code works perfectly fine with versions below, even with the version 5.18.3.

What are you doing?

I have two tables - Channel and Track. One Channel can have multiple tracks, one Track belongs to one Channel.

When trying to select a Channel with its Tracks like this:

const channel = await Channel.findOne({
    where: { token: 'channel-token' },
    include: [{ model: Track, as: 'tracks' }]
});

the query fails with the error:

The column 'Id' was specified multiple times for 'Channel'.

This error started from the version 5.18.4, earlier versions are not affected.

This is the full, minimal code to reproduce the issue (except the config and the database):

const settings = require('./lib/settings');
const Sequelize = require('sequelize');
const config = settings.database;

function channelModel(sequelize, DataTypes) {
    return sequelize.define(
        'Channel',
        {
            id: {
                type: DataTypes.INTEGER,
                field: 'Id',
                primaryKey: true,
                autoIncrement: true,
                allowNull: false
            },
            token: {
                type: DataTypes.STRING(64),
                field: 'Token',
                allowNull: false
            }
        },
        {
            timestamps: false,
            freezeTableName: true,
            tableName: 'Channel'
        }
    );
}

function trackModel(sequelize, DataTypes) {
    return sequelize.define(
        'Track',
        {
            id: {
                type: DataTypes.INTEGER,
                field: 'Id',
                primaryKey: true,
                autoIncrement: true,
                allowNull: false
            },
            token: {
                type: DataTypes.STRING(64),
                field: 'Token',
                allowNull: false
            },
            channelId: {
                type: DataTypes.INTEGER,
                field: 'ChannelId',
                allowNull: false
            }
        },
        {
            timestamps: false,
            freezeTableName: true,
            tableName: 'Track'
        }
    );
}

const db = new Sequelize(config.database, config.user, config.password, config);
channelModel(db, Sequelize.DataTypes);
trackModel(db, Sequelize.DataTypes);

const { Channel, Track } = db.models;

Channel.hasMany(Track, { foreignKey: 'channelId', sourceKey: 'id', as: 'tracks' });
Track.belongsTo(Channel, { foreignKey: 'channelId', sourceKey: 'id', as: 'channel' });

(async () => {
    try {
        const channel = await Channel.findOne({
            where: { token: 'channel-token' },
            include: [{ model: Track, as: 'tracks' }]
        });
        console.log(channel.get({ plain: true }));
    } catch (err) {
        console.error('OPS', err);
    } finally {
        db.close();
    }
})();

What do you expect to happen?

I would expect the code not the fail with the exception, but rather to provide the data.

The query executed against the DB in version 5.18.3 was:

SELECT [Channel].*, [tracks].[Id] AS [tracks.id], [tracks].[Token] AS [tracks.token], [tracks].[ChannelId] AS [tracks.channelId] FROM (SELECT [Channel].[Id] AS [id], [Channel].[Token] AS [token] FROM [Channel] AS [Channel] WHERE [Channel].[Token] = N'audinovinky' ORDER BY [Channel].[Id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) AS [Channel] LEFT OUTER JOIN [Track] AS [tracks] ON [Channel].[id] = [tracks].[ChannelId];

Or actually formatted to make it bit more readable:

SELECT [Channel].*,
       [tracks].[Id] AS [tracks.id],
       [tracks].[Token] AS [tracks.token],
       [tracks].[ChannelId] AS [tracks.channelId]
FROM
  (SELECT [Channel].[Id] AS [id],
          [Channel].[Token] AS [token]
   FROM [Channel] AS [Channel]
   WHERE [Channel].[Token] = N'audinovinky'
   ORDER BY [Channel].[Id]
   OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) AS [Channel]
LEFT OUTER JOIN [Track] AS [tracks] ON [Channel].[id] = [tracks].[ChannelId];

What is actually happening?

Invalid query is executed:

SELECT [Channel].*, [tracks].[Id] AS [tracks.id], [tracks].[Token] AS [tracks.token], [tracks].[ChannelId] AS [tracks.channelId] FROM (SELECT [Channel].[Id] AS [id], [Channel].[Token] AS [token], [Channel].[Id] FROM [Channel] AS [Channel] WHERE [Channel].[Token] = N'audinovinky' ORDER BY [Channel].[Id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) AS [Channel] LEFT OUTER JOIN [Track] AS [tracks] ON [Channel].[id] = [tracks].[ChannelId];

Or again bit formatted. The bold line is what is new with version 5.18.4 and that is causing an error:

SELECT [Channel].*,
       [tracks].[Id] AS [tracks.id],
       [tracks].[Token] AS [tracks.token],
       [tracks].[ChannelId] AS [tracks.channelId]
FROM
  (SELECT [Channel].[Id] AS [id],
          [Channel].[Token] AS [token],
          [Channel].[Id]
   FROM [Channel] AS [Channel]
   WHERE [Channel].[Token] = N'audinovinky'
   ORDER BY [Channel].[Id]
   OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) AS [Channel]
LEFT OUTER JOIN [Track] AS [tracks] ON [Channel].[id] = [tracks].[ChannelId];

As can be seen from the query, the Id column is indeed selected twice.

The error occurs, this is logged to the console:

OPS { SequelizeDatabaseError: The column 'Id' was specified multiple times for 'Channel'.
    at Query.formatError (/path/to/my/project/node_modules/sequelize/lib/dialects/mssql/query.js:314:12)
    at Request.connection.lib.Request [as userCallback] (/path/to/my/project/node_modules/sequelize/lib/dialects/mssql/query.js:74:23)
    at Request.callback (/path/to/my/project/node_modules/tedious/lib/request.js:40:14)
    at Connection.endOfMessageMarkerReceived (/path/to/my/project/node_modules/tedious/lib/connection.js:2273:20)
    at Connection.dispatchEvent (/path/to/my/project/node_modules/tedious/lib/connection.js:1220:36)
    at Parser.tokenStreamParser.on (/path/to/my/project/node_modules/tedious/lib/connection.js:1023:14)
    at Parser.emit (events.js:182:13)
    at Parser.parser.on.token (/path/to/my/project/node_modules/tedious/lib/token/token-stream-parser.js:27:14)
    at Parser.emit (events.js:182:13)
    at addChunk (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:297:12)
    at readableAddChunk (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:279:11)
    at Parser.Readable.push (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:240:10)
    at Parser.Transform.push (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_transform.js:139:32)
    at Parser.afterTransform (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_transform.js:88:10)
    at Parser._transform (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:41:7)
    at Parser.Transform._read (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_transform.js:177:10)
    at Parser.Transform._write (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_transform.js:164:83)
    at doWrite (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_writable.js:405:139)
    at writeOrBuffer (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_writable.js:394:5)
    at Parser.Writable.write (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_writable.js:303:11)
    at Parser.addEndOfMessageMarker (/path/to/my/project/node_modules/tedious/lib/token/token-stream-parser.js:45:24)
    at Connection.message (/path/to/my/project/node_modules/tedious/lib/connection.js:2262:32)
  name: 'SequelizeDatabaseError',
  parent:
   { RequestError: The column 'Id' was specified multiple times for 'Channel'.
       at Parser.tokenStreamParser.on.token (/path/to/my/project/node_modules/tedious/lib/connection.js:788:27)
       at Parser.emit (events.js:182:13)
       at Parser.parser.on.token (/path/to/my/project/node_modules/tedious/lib/token/token-stream-parser.js:27:14)
       at Parser.emit (events.js:182:13)
       at addChunk (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:297:12)
       at readableAddChunk (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:279:11)
       at Parser.Readable.push (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:240:10)
       at Parser.Transform.push (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_transform.js:139:32)
       at doneParsing (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:80:14)
       at token (/path/to/my/project/node_modules/tedious/lib/token/infoerror-token-parser.js:48:5)
       at call.lineNumber (/path/to/my/project/node_modules/tedious/lib/token/infoerror-token-parser.js:13:19)
       at awaitData (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:179:7)
       at Parser.awaitData (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:103:7)
       at Parser.readUInt32LE (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:176:10)
       at parser.readBVarChar.procName (/path/to/my/project/node_modules/tedious/lib/token/infoerror-token-parser.js:12:90)
       at readBuffer.data (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:316:9)
       at awaitData (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:308:7)
       at Parser.awaitData (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:103:7)
       at Parser.readBuffer (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:305:10)
       at readUInt8.length (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:315:12)
       at awaitData (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:123:7)
       at Parser.awaitData (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:103:7)
     message:
      'The column \'Id\' was specified multiple times for \'Channel\'.',
     code: 'EREQUEST',
     number: 8156,
     state: 1,
     class: 16,
     serverName: 'SRVNAME',
     procName: '',
     lineNumber: 1,
     sql:
      'SELECT [Channel].*, [tracks].[Id] AS [tracks.id], [tracks].[Token] AS [tracks.token], [tracks].[ChannelId] AS [tracks.channelId] FROM (SELECT [Channel].[Id] AS [id], [Channel].[Token] AS [token], [Channel].[Id] FROM [Channel] AS [Channel] WHERE [Channel].[Token] = N\'channel-token\' ORDER BY [Channel].[Id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) AS [Channel] LEFT OUTER JOIN [Track] AS [tracks] ON [Channel].[id] = [tracks].[ChannelId];',
     parameters: undefined },
  original:
   { RequestError: The column 'Id' was specified multiple times for 'Channel'.
       at Parser.tokenStreamParser.on.token (/path/to/my/project/node_modules/tedious/lib/connection.js:788:27)
       at Parser.emit (events.js:182:13)
       at Parser.parser.on.token (/path/to/my/project/node_modules/tedious/lib/token/token-stream-parser.js:27:14)
       at Parser.emit (events.js:182:13)
       at addChunk (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:297:12)
       at readableAddChunk (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:279:11)
       at Parser.Readable.push (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_readable.js:240:10)
       at Parser.Transform.push (/path/to/my/project/node_modules/tedious/node_modules/readable-stream/lib/_stream_transform.js:139:32)
       at doneParsing (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:80:14)
       at token (/path/to/my/project/node_modules/tedious/lib/token/infoerror-token-parser.js:48:5)
       at call.lineNumber (/path/to/my/project/node_modules/tedious/lib/token/infoerror-token-parser.js:13:19)
       at awaitData (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:179:7)
       at Parser.awaitData (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:103:7)
       at Parser.readUInt32LE (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:176:10)
       at parser.readBVarChar.procName (/path/to/my/project/node_modules/tedious/lib/token/infoerror-token-parser.js:12:90)
       at readBuffer.data (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:316:9)
       at awaitData (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:308:7)
       at Parser.awaitData (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:103:7)
       at Parser.readBuffer (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:305:10)
       at readUInt8.length (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:315:12)
       at awaitData (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:123:7)
       at Parser.awaitData (/path/to/my/project/node_modules/tedious/lib/token/stream-parser.js:103:7)
     message:
      'The column \'Id\' was specified multiple times for \'Channel\'.',
     code: 'EREQUEST',
     number: 8156,
     state: 1,
     class: 16,
     serverName: 'SRVNAME',
     procName: '',
     lineNumber: 1,
     sql:
      'SELECT [Channel].*, [tracks].[Id] AS [tracks.id], [tracks].[Token] AS [tracks.token], [tracks].[ChannelId] AS [tracks.channelId] FROM (SELECT [Channel].[Id] AS [id], [Channel].[Token] AS [token], [Channel].[Id] FROM [Channel] AS [Channel] WHERE [Channel].[Token] = N\'channel-token\' ORDER BY [Channel].[Id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) AS [Channel] LEFT OUTER JOIN [Track] AS [tracks] ON [Channel].[id] = [tracks].[ChannelId];',
     parameters: undefined },
  sql:
   'SELECT [Channel].*, [tracks].[Id] AS [tracks.id], [tracks].[Token] AS [tracks.token], [tracks].[ChannelId] AS [tracks.channelId] FROM (SELECT [Channel].[Id] AS [id], [Channel].[Token] AS [token], [Channel].[Id] FROM [Channel] AS [Channel] WHERE [Channel].[Token] = N\'channel-token\' ORDER BY [Channel].[Id] OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) AS [Channel] LEFT OUTER JOIN [Track] AS [tracks] ON [Channel].[id] = [tracks].[ChannelId];',
  parameters: undefined }

Additional context

The query runs against the MS SQL server.

Environment

  • Sequelize version: 5.18.4 and above
  • Node.js version: v10.14.1
  • Operating System: MacOS Mojave 10.14.7

Issue Template Checklist

How does this problem relate to dialects?

  • I think this problem happens regardless of the dialect.
  • I think this problem happens only for the following dialect(s):
  • I don’t know, I was using mssql, with connector library tedious version 6.4.0 and MS SQL database version 12.0.5223

Would you be willing to resolve this issue by submitting a Pull Request?

  • Yes, I have the time and I know how to start.
  • Yes, I have the time but I don’t know how to start, I would need guidance.
  • No, I don’t have the time, although I believe I could do it if I had the time…
  • No, I don’t have the time and I wouldn’t even know how to start.

About this issue

  • Original URL
  • State: closed
  • Created 5 years ago
  • Reactions: 4
  • Comments: 28 (10 by maintainers)

Most upvoted comments

The weird thing is they continue to update the main branch. Last update was two days ago and last release just two weeks ago. Why no love for this issue? 😦

+4
rconstantine on Apr 6, 2021

This issue is very annoying for mssql users, we were stuck at version 5.18.3 for a long time, we had to create a fork since there is no action here.

It would be awesome if someone could help us merge @DragonRaider5’s PR https://github.com/sequelize/sequelize/pull/12801.

+4
danielfsousa on Apr 2, 2021

@DragonRaider5 I am running into this issue as well with my application, I have used the solution you came up with in your PR and have confirmed that is is working. Is there anything else we can do to get this PR merged?

+3
thisbechristian on Mar 26, 2021

Hey all,

according to my debugging, this problem occurs, because even though in the join clauses the aliased name is used, the not aliased name is added to the selected attributes list for sub queries.

For example an association referencing id on

const User = sequelize.define('User', {
  id: {
    field: 'Id',
    type: Sequelize.INTEGER,
    autoIncrement: true,
    primaryKey: true 
 }
})

would (when using a sub query) result in

SELECT [User].* FROM (
  SELECT [User].[Id] AS [id], [User].[Id]
  FROM [User]
) AS [User]
  LEFT OUTER JOIN [Task] ON [User].[id] = [Task].[OwnerId]

At this point the MSSQL Server complains The column 'Id' was specified multiple times for 'User'. - but only for the primary key colum [User].[Id]. Doing the same thing with other columns does not raise an error. I’d say this is a bug in MSSQL, however the selection of the unaliased [User].[Id] is redundant anyways, which means we can circumvent the issue in sequelize.

Here’s the code, which adds the unaliased column to the attributes: https://github.com/sequelize/sequelize/blob/c3ec6c507f4b7115455834bda052806d29a5132c/lib/dialects/abstract/query-generator.js#L1683-L1692

You can see the joinOn expression uses attrLeft while the push to subqueryAttributes uses fieldLeft without possibly employing an alias. I’ll provide a PR for this.

+2
marcesengel on Oct 31, 2020

Pinging @papb @Keimeno @sdepold @janmeier - please run the CI Pipelines on #12801 to get this merged. It have been nearly 2 years now 😅

+1
marcesengel on Sep 30, 2021

@rconstantine it’s a bug, I fixed it in my PR #12801 - but sadly is no action there.

+1
marcesengel on Jan 21, 2021

I came across the same error, any updates?

+1
victorcabeceira on Jul 15, 2020
Read more comments on GitHub
← sequelize: MSSQL: Implicit conversion causes table scan -- Prepending N to strings precludes index use on varchar columns
sequelize: MySQL Connection Error Handling →