Regression between 6.35.1 and 6.35.2 - query stopped working.

[davidrac]

Issue Creation Checklist

• I understand that my issue will be automatically closed if I don't fill in the requested information
• I have read the contribution guidelines

Bug Description

The follwing query (generated by sequelize find with includes) does not produce the proper result. The deeply nested "Asset" does not contain an "Index" or a "Corporation" while in the previous version it did, and in the raw result of the query they exist.

Reproducible Example

Here is the link to the SSCCE for this issue:

This is the query that doesn't work:

SELECT "Asset"."id",
       "Asset"."name",
... (more asset fields) ...
       "Asset"."fixedAsset2Id",
       "assetPricingModels"."id" AS "_0",
       "assetPricingModels->assetPricings"."id" AS "_1",
       "assetPricingModels->assetPricings->heuristicComponents"."id" AS "_2",
       "%0"."id" AS "_3",
       "%1"."id" AS "_4",
       "%2"."id" AS "_5",
       "%3"."id" AS "_6",
       "%4"."id" AS "_7",
       "%5"."id" AS "_8",
       "%6"."id" AS "_9",
       "%6"."name" AS "_10",
       "%7"."id" AS "_11",
       "%8"."id" AS "_12",
       "%8"."name" AS "_13"
FROM "Assets" AS "Asset"
INNER JOIN "AssetPricingModels" AS "assetPricingModels" ON "Asset"."id" = "assetPricingModels"."assetId"
INNER JOIN "AssetPricings" AS "assetPricingModels->assetPricings" ON "assetPricingModels"."id" = "assetPricingModels->assetPricings"."assetPricingModelId"
INNER JOIN "HeuristicComponents" AS "assetPricingModels->assetPricings->heuristicComponents" ON "assetPricingModels->assetPricings"."id" = "assetPricingModels->assetPricings->heuristicComponents"."assetPricingId"
INNER JOIN "Heuristics" AS "%0" ON "assetPricingModels->assetPricings->heuristicComponents"."heuristicId" = "%0"."id"
AND "%0"."type" IN ('COVER_TAKING',
                    'COVER_BE_THE_FIRST',
                    'COVER_ALGO',
                    'COVER_STOCKS_BASKET',
                    'MULTI_ASSET_COVER')
AND "%0"."mode" = 'ENABLED'
LEFT OUTER JOIN "HeuristicComponents" AS "%1" ON "%0"."id" = "%1"."heuristicId"
LEFT OUTER JOIN "AssetPricings" AS "%2" ON "%1"."assetPricingId" = "%2"."id"
LEFT OUTER JOIN "AssetPricingModels" AS "%3" ON "%2"."assetPricingModelId" = "%3"."id"
LEFT OUTER JOIN "Assets" AS "%4" ON "%3"."assetId" = "%4"."id"
LEFT OUTER JOIN "Indices" AS "%5" ON "%4"."indexId" = "%5"."id"
LEFT OUTER JOIN "ExposureGroups" AS "%6" ON "%5"."exposureGroupId" = "%6"."id"
LEFT OUTER JOIN "Corporations" AS "%7" ON "%4"."corporationId" = "%7"."id"
LEFT OUTER JOIN "ExposureGroups" AS "%8" ON "%7"."exposureGroupId" = "%8"."id"
WHERE "Asset"."indexId" = '2508';

The produced query is exactly as in the previuos version, but in this version, the nested asset does not contain an index even though it is present in the result.

What do you expect to happen?

The marked line should return the index

  _(result)
    .flatMap(it => it.assetPricingModels)
    .flatMap(it => it.assetPricings)
    .flatMap(it => it.heuristicComponents)
    .map(it => it.heuristic)
    .flatMap(it => it.heuristicComponents)
    .map(it => it.assetPricing.assetPricingModel.asset)
    .map(it => it.index ?? it.corporation) // <== this should not return undefined
    .filter(({ id }) => id != indexOrCorporation.id)
    .map(it => it.exposureGroup)
    .uniqBy(({ id }) => id)
    .first();

What is actually happening?

The line above returns undefined and the code breaks

Environment

• Sequelize version: 6.35.2
• Node.js version: 18.12.1
• If TypeScript related: TypeScript version: N/A
• Database & Version: Postgres 14.3
• Connector library & Version: pg 8.11.3

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 will need guidance.
• No, I don't have the time, but my company or I are supporting Sequelize through donations on OpenCollective.
• No, I don't have the time, and I understand that I will need to wait until someone from the community or maintainers is interested in resolving my issue.

---

Indicate your interest in the resolution of this issue by adding the 👍 reaction. Comments such as "+1" will be removed.

[WikiRik]

@joker00777 can you take a look at this?

[joker00777]

@WikiRik will give it a look this week, also @davidrac can you share the sequelize query

[davidrac]

Asset.findAll({
    where: { indexId: index.id },
    include: {
      model: AssetPricingModel,
      as: 'assetPricingModels',
      required: true,
      attributes: ['id'],
      include: {
        model: AssetPricing,
        as: 'assetPricings',
        required: true,
        attributes: ['id'],
        include: {
          model: HeuristicComponent,
          as: 'heuristicComponents',
          required: true,
          attributes: ['id'],
          include: {
            model: Heuristic,
            as: 'heuristic',
            required: true,
            attributes: ['id'],
            where: { type: COVER_HEURISTIC_TYPES, mode: 'ENABLED' },
            include: {
              model: HeuristicComponent,
              as: 'heuristicComponents',
              attributes: ['id'],
              include: {
                model: AssetPricing,
                as: 'assetPricing',
                attributes: ['id'],
                include: {
                  model: AssetPricingModel,
                  as: 'assetPricingModel',
                  attributes: ['id'],
                  include: {
                    model: Asset,
                    as: 'asset',
                    attributes: ['id'],
                    include: [
                      {
                        model: Index,
                        as: 'index',
                        attributes: ['id'],
                        include: {
                          model: ExposureGroup,
                          as: 'exposureGroup',
                          attributes: ['id', 'name']
                        }
                      },
                      {
                        model: Corporation,
                        as: 'corporation',
                        attributes: ['id'],
                        include: {
                          model: ExposureGroup,
                          as: 'exposureGroup',
                          attributes: ['id', 'name']
                        }
                      }
                    ]
                  }
                }
              }
            }
          }
        }
      }
    },
    subQuery: false
  });

[davidrac]

Hi, any progress on that? Is there any information I can provide to help?