Find with Include with a where clause generates wrong SQL

[Americas]

I am trying to do a find with a nested model with a where clause, as per the example below.

var CD = sequelize.define("CD", {
    id    : {
        type         : Sequelize.UUID,
        field        : "a",
        defaultValue : Sequelize.UUIDV4,
        primaryKey   : true,
    },
    extra : {
        type  : Sequelize.JSONB,
        field : "ctrd_extra",
    },
}, {
    tableName    : "test1",
    name         : {
        singular : "cD",
        plural   : "cDs",
    },
});

var CQ = sequelize.define("CQ", {
    a : {
        type      : Sequelize.INTEGER,
        field     : "a",
        allowNull : false,
    },
    b     : {
        type       : Sequelize.STRING,
        field      : "b",
        primaryKey : true,
        allowNull  : false,
    },
}, {
    tableName    : "test2",
    paranoid     : false,
    name         : {
        singular : "cQ",
        plural   : "cQs",
    },
});
CQ.removeAttribute("id");

CD.hasMany(CQ, {foreignKey : "c"});
CQ.belongsTo(CD, { foreignKey : "c", primaryKey : true });

sequelize
.sync({force: true})
.then(function() {
    return CD.create({})
})
.then(function (cd) {
    return cd.createCQ({ a : 1, b : "bbb" });
})
.then(function () {
    return CD.find({
        include : [
            {
                model    : CQ,
                where    : { b : "bbb" },
                required : true,
            },
        ],
    });
})
.then(function getQuantities(result) {
    console.log(result);
});

This generates the following SQL for the select, which is wrong because of line 16 : WHERE "CD"."id" = "cQs"."c". "CD"."id" does not exist at that level, it should be "CD"."a".

 SELECT "CD".*,
        "cQs"."a"          AS "cQs.a",
        "cQs"."b"          AS "cQs.b",
        "cQs"."created_at" AS "cQs.created_at",
        "cQs"."updated_at" AS "cQs.updated_at",
        "cQs"."c"          AS "cQs.c" 
   FROM (     SELECT "CD"."a"          AS "id",
                    "CD"."ctrd_extra" AS "extra",
                    "CD"."created_at",
                    "CD"."updated_at",
                    "CD"."deleted_at" 
               FROM "test1" AS "CD" 
              WHERE "CD"."deleted_at" IS NULL 
                AND (     SELECT "c" 
                            FROM "test2" AS "cQs" 
                           WHERE "CD"."id" = "cQs"."c" 
                             AND "cQs"."b" = 'bbb' 
                           LIMIT 1 
                    ) IS NOT NULL 
              LIMIT 1
        ) AS "CD" 
  INNER 
   JOIN "test2" AS "cQs" 
     ON "CD"."id" = "cQs"."c" 
    AND "cQs"."b" = 'bbb';

Throws the Error: Unhandled rejection SequelizeDatabaseError: column CD.id does not exist.

I am using postgres 9.4 and sequelize 2.1.3.

[Americas]

I have also found that the offending code is in "lib/dialects/abstract/query-generator.js" starting in line 1175 where it says:

// Filter statement
// Used by both join and where

[janmeier]

Try adding C with primary key true to the model definition instead. That way sequelize will know what your primary key is and not try to add an id

While you can remove the id column after the model has been defined , it is bound to break sooner or later :-)

[Americas]

The model CD has an id column defined. The problem is that in the inner query that column is referenced as the field, which is "a", and in the outer query the field has been renamed to the model's name "id".

As such, using the same filter statement for the join and the where is what is breaking the SQL.

 SELECT "CD".*,
        "cQs"."a"          AS "cQs.a",
        "cQs"."b"          AS "cQs.b",
        "cQs"."created_at" AS "cQs.created_at",
        "cQs"."updated_at" AS "cQs.updated_at",
        "cQs"."c"          AS "cQs.c" 
   FROM ( --INNER QUERY START - inside here there is no "CD"."id"
              SELECT "CD"."a"          AS "id", --only "CD"."a"
                    "CD"."ctrd_extra" AS "extra",
                    "CD"."created_at",
                    "CD"."updated_at",
                    "CD"."deleted_at" 
               FROM "test1" AS "CD" 
              WHERE "CD"."deleted_at" IS NULL 
                AND (     SELECT "c" 
                            FROM "test2" AS "cQs" 
                           WHERE "CD"."id" = "cQs"."c" --so this column does not exist here
                             AND "cQs"."b" = 'bbb' 
                           LIMIT 1 
                    ) IS NOT NULL 
              LIMIT 1
        ) AS "CD" 
  INNER 
   JOIN "test2" AS "cQs" 
     ON "CD"."id" = "cQs"."c" --OUTER QUERY reference, in here the column does exist
    AND "cQs"."b" = 'bbb';

[mickhansen]

Appears to be a bug. Would be great if you can verify whether this persists on the latest version.

[Americas]

Switched to version 3.2.0 and it still has the same error. Here is the whole log.

Executing (default): DROP TABLE IF EXISTS "test2" CASCADE;
Executing (default): DROP TABLE IF EXISTS "test1" CASCADE;
Executing (default): DROP TABLE IF EXISTS "test1" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "test1" ("cd_a" UUID , "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, "deleted_at" TIMESTAMP WITH TIME ZONE, PRIMARY KEY ("cd_a"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) AND t.relkind = 'r' and t.relname = 'test1' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): DROP TABLE IF EXISTS "test2" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "test2" ("cq_a" VARCHAR(255) NOT NULL , "created_at" TIMESTAMP WITH TIME ZONE NOT NULL, "updated_at" TIMESTAMP WITH TIME ZONE NOT NULL, "b" UUID REFERENCES "test1" ("cd_a") ON DELETE SET NULL ON UPDATE CASCADE, PRIMARY KEY ("cq_a"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) AND t.relkind = 'r' and t.relname = 'test2' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "test1" ("cd_a","updated_at","created_at") VALUES ('bc7cc5c4-9fea-4186-93f1-307ca449aea1','2015-06-16 10:19:15.165 +00:00','2015-06-16 10:19:15.165 +00:00') RETURNING *;
Executing (default): INSERT INTO "test2" ("cq_a","b","updated_at","created_at") VALUES ('bbb','bc7cc5c4-9fea-4186-93f1-307ca449aea1','2015-06-16 10:19:15.195 +00:00','2015-06-16 10:19:15.195 +00:00') RETURNING *;
Executing (default): SELECT "CD".*, "cQs"."cq_a" AS "cQs.a", "cQs"."created_at" AS "cQs.created_at", "cQs"."updated_at" AS "cQs.updated_at", "cQs"."b" AS "cQs.b" FROM (SELECT "CD"."cd_a" AS "id", "CD"."created_at", "CD"."updated_at", "CD"."deleted_at" FROM "test1" AS "CD" WHERE "CD"."deleted_at" IS NULL AND ( SELECT "b" FROM "test2" AS "cQs" WHERE "CD"."id" = "cQs"."b" AND "cQs"."cq_a" = 'bbb' LIMIT 1 ) IS NOT NULL LIMIT 1) AS "CD" INNER JOIN "test2" AS "cQs" ON "CD"."id" = "cQs"."b" AND "cQs"."cq_a" = 'bbb';
Unhandled rejection SequelizeDatabaseError: column CD.id does not exist
    at Query.formatError (/home/prl/Projects/testseq/node_modules/sequelize/lib/dialects/postgres/query.js:420:14)
    at null.<anonymous> (/home/prl/Projects/testseq/node_modules/sequelize/lib/dialects/postgres/query.js:104:19)
    at emitOne (events.js:77:13)
    at emit (events.js:166:7)
    at Query.handleError (/home/prl/Projects/testseq/node_modules/pg/lib/query.js:99:8)
    at null.<anonymous> (/home/prl/Projects/testseq/node_modules/pg/lib/client.js:166:26)
    at emitOne (events.js:77:13)
    at emit (events.js:166:7)
    at Socket.<anonymous> (/home/prl/Projects/testseq/node_modules/pg/lib/connection.js:109:12)
    at emitOne (events.js:77:13)
    at Socket.emit (events.js:166:7)
    at readableAddChunk (_stream_readable.js:145:16)
    at Socket.Readable.push (_stream_readable.js:109:10)
    at TCP.onread (net.js:509:20)