Use of *= Join Operator in Generated SQL Server Query

[godonnell_ip]

As you will know, the *= join operator is not support in SQL server versions 90 and above and yet I find that it is being included in generated queries by SmartClient Version: v11.1p_2020-06-06/PowerEdition Deployment (built 2020-06-06). For example:

SELECT
   *
FROM
   (
      SELECT
         *,
         ROW_NUMBER() OVER (
      ORDER BY
         ItemID, ShipmentContainerID) AS rowID
      FROM
         (
            SELECT
               TOP 100 PERCENT [sw].CO_ASGMT_CTORD_GRP.orderLineKey,
               [sw].CO_ASGMT_CTORD_GRP.ID_ORD AS CustomerOrderID,
               [sw].CO_ASGMT_CTORD_GRP.IC_OR_LTM AS CustomerOrderLineItemSequenceNumber,
               [sw].CO_ASGMT_CTORD_GRP.ID_CTORD_GRP AS CustomerOrderGroupID,
               [sw].CO_ASGMT_CTORD_GRP.QU_QTY AS CustomerOrderProductAssignmentQuantity,
               [sw].CO_ASGMT_CTORD_GRP.productLineAssignmentKey,
               a0.ID_ITM AS ItemID,
               a0.DE_ITM AS Description,
               a0.QU_ORD_CNT AS OrderedItemQuantity,
               a0.QU_FLF_CNT AS FulfilledItemQuantity,
               CuOrderGroupID_CuOrPrGroup.CD_TYP_CTORD_GRP AS CustomerOrderProductGroupTypeCode,
               CuOrderGroupID_CuOrPrGroup.Inventory,
               (
                  SELECT
                     sum([TR_ITM_RTL_SHP].QU_ITM_SHP) AS Quantity
                  FROM
                     [sw].[CO_CTORD_GRP] CuOrderGroupID_CuOrPrGroup,
                     [sw].[CO_UOM] a5,
                     [sw].[TR_ITM_RTL_SHP],
                     [sw].[TR_SHP_CT_ORD] a7,
                     [sw].[DO_CTOLTM_PDT] a0,
                     [sw].[AS_CT_ORD_SHPMT_CNR] a6,
                     [sw].[LO_LCN_INV] a3,
                     [sw].[LO_LCN] a4,
                     [sw].[CO_TYP_CTORD_GRP] a8
                  WHERE
                     [sw].CO_ASGMT_CTORD_GRP.orderLineKey = a0.orderLineKey
                     AND [sw].CO_ASGMT_CTORD_GRP.ID_CTORD_GRP * = CuOrderGroupID_CuOrPrGroup.ID_CTORD_GRP
                     AND [sw].CO_ASGMT_CTORD_GRP.productLineAssignmentKey * = [TR_ITM_RTL_SHP].productLineAssignmentKey
                     AND CuOrderGroupID_CuOrPrGroup.Inventory * = a3.ID_LCN
                     AND a3.ID_LCN * = a4.ID_LCN
                     AND a4.LU_UOM_SZ * = a5.CD_UOM
                     AND a5.CD_UOM * = a6.CD_UOM_LNR
                     AND a5.CD_UOM * = a6.CD_UOM_WT
                     AND CuOrderGroupID_CuOrPrGroup.ID_CTORD_GRP * = a7.ID_CTORD_GRP
                     AND CuOrderGroupID_CuOrPrGroup.CD_TYP_CTORD_GRP * = a8.CD_TYP_CTORD_GRP
               )
               AS Quantity,
               a6.ID_CT_ORD_SHPMT_CNR AS ShipmentContainerID,
               a7.ID_SHP AS ShipmentID,
               a8.NM_TYP_CTORD_GRP AS _lvm_CustomerOrderGroupID
            FROM
               [sw].CO_ASGMT_CTORD_GRP
               JOIN
                  [sw].[DO_CTOLTM_PDT] a0
                  ON [sw].CO_ASGMT_CTORD_GRP.orderLineKey = a0.orderLineKey
               LEFT OUTER JOIN
                  [sw].[CO_CTORD_GRP] CuOrderGroupID_CuOrPrGroup
                  ON [sw].CO_ASGMT_CTORD_GRP.ID_CTORD_GRP = CuOrderGroupID_CuOrPrGroup.ID_CTORD_GRP
               LEFT OUTER JOIN
                  [sw].[LO_LCN_INV] a3
                  ON CuOrderGroupID_CuOrPrGroup.Inventory = a3.ID_LCN
               LEFT OUTER JOIN
                  [sw].[LO_LCN] a4
                  ON a3.ID_LCN = a4.ID_LCN
               LEFT OUTER JOIN
                  [sw].[CO_UOM] a5
                  ON a4.LU_UOM_SZ = a5.CD_UOM
               LEFT OUTER JOIN
                  [sw].[AS_CT_ORD_SHPMT_CNR] a6
                  ON a5.CD_UOM = a6.CD_UOM_LNR
                  AND a5.CD_UOM = a6.CD_UOM_WT
               LEFT OUTER JOIN
                  [sw].[TR_SHP_CT_ORD] a7
                  ON CuOrderGroupID_CuOrPrGroup.ID_CTORD_GRP = a7.ID_CTORD_GRP
               LEFT OUTER JOIN
                  [sw].[CO_TYP_CTORD_GRP] a8
                  ON CuOrderGroupID_CuOrPrGroup.CD_TYP_CTORD_GRP = a8.CD_TYP_CTORD_GRP
            WHERE
               (
                  a7.ID_SHP = 411
                  AND a7.ID_SHP IS NOT NULL
               )
            ORDER BY
               ItemID,
               ShipmentContainerID
         )
         x
   )
   y
WHERE
   y.rowID BETWEEN 1 AND 1000

The problem is evident in the subselect generated for the Quantity field, which is defined with an includeSummaryFunction attribute:

        <field includeFrom="CustomerOrderShipmentItem.Quantity" includeSummaryFunction="sum"/>

Without that attribute all works fine (although differently, as would be expected).

I should mention that I have the following property set in server.properties:

sql.useAnsiJoins=true

Is this by design, or has it perhaps been changed in later versions of SmartGWT? I'm happy to provide more details if you believe this to be something you wish to investigate further.

[Isomorphic]

Apologies for the long wait. This is not intended and needs to be fixed. We are working on this and will follow up soon.

[Isomorphic]

This is fixed now and is available for download in the latest 11.1 nightly build. Ansi joins usage in sub-selects is controlled via same global setting you mentioned or individual DataSource.useAnsiJoins attribute.