0

Summary:

Results of CTE using OUTER APPLY return duplicates due to dirty nature of data and results of RegexFind to split out commingled key=value pairs.

When using DISTINCT on that CTE, the result is empty. Is this a bug in SQLServer 2014?

Tried:

  1. Even adding a layer on top of the CTE to implement DISTINCT has the same result.
  2. GROUP BY instead of DISTINCT has the same result.
  3. OVER PARTITION has the same result

NOTE: I was able to overcome this by exploiting submatchIDs from RegexFind, but this problem is concerning. I believe that OUTER APPLY messes up any attempt to get DISTINCT resultset.

Actual code that cannot be easily tested on SQL Fiddle or DBFiddle.uk due to insufficient permissions to implement the RegexFind function (on those I have loaded results of OUTER APPLY instead of raw input data):

USE M1_MA
--SELECT @@version -- Microsoft SQL Server 2014 (SP3-GDR) (KB5021037) - 12.0.6174.8 (X64)     Jan  4 2023 08:59:45     Copyright (c) Microsoft Corporation    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
declare  @key_value_pair_regex_pat nvarchar(max) = 
    
    '\b(jm[o|s]\w+)\s*=\s*((?:(?!(?:jmo|jms))[^=])*)(?=\s+(?:jmo|jms)|$)'
    
; WITH JobOperationsChangeLog as (
SELECT --DISTINCT 
keys.JobID  JobID
, keys.JobAssemblyID JobAssemblyID
, keys.JobOperationID   JobOperationID

, xagChangeDate ChangeDate, xagChangeUserID ChangeUserID, ChangeLogID 
--, xagTableName
, xagChangeType --[U]pdate [I]nsert, etc
, xagTableKeyValues
, xagTableOldValues
    , xagTableNewValues
FROM ChangeLog 
JOIN (
  SELECT distinct
  xagChangeLogID ChangeLogID, xagChangeDate ChangeDate

  , REPLACE(JobID.Value,'jmoJobID = ','') JobID
  , REPLACE(JobAssemblyID.Value,'jmoJobAssemblyID = ','') JobAssemblyID
  , REPLACE(JobOperationID.Value, 'jmoJobOperationID = ','') JobOperationID
  
  FROM ChangeLog

  OUTER APPLY M1_MA.dbo.RegexFind (
    @key_value_pair_regex_pat
    ,xagTableKeyValues
    ,1
    ,1) JobID 

  OUTER APPLY M1_MA.dbo.RegexFind (
    @key_value_pair_regex_pat
    ,xagTableKeyValues
    ,1
    ,1) JobAssemblyID 

  OUTER APPLY M1_MA.dbo.RegexFind (
    @key_value_pair_regex_pat
    ,xagTableKeyValues
    ,1
    ,1) JobOperationID 

    ) keys on ChangeLogID=xagChangeLogID and ChangeDate=xagChangeDate
    WHERE xagTableName='JobOperations'
    AND (Keys.JobID NOT LIKE 'jmo%' and Keys.JobOperationID NOT LIKE 'jmo%' and Keys.JobAssemblyID NOT LIKE 'jmo%') -- eliminate non-value rows
    AND xagChangeUserID <> 'SQLAGENT - TSQL JOBS'
)
, inserts_BASE as (
    SELECT  
    JobID, JobAssemblyID, JobOperationID
    ,  ChangeDate FirstDate
    ,  ChangeUserID 
    ,  ChangeLogID
    --,  old.Error , old.FirstIndex , old.Match_ID , old.Submatch_ID , old.SubmatchValue , old.[Value]
    ,  new.Match_ID     MatchID
    ,  new.FirstIndex   new_FirstIndex
    ,  new.Submatch_ID  new_SubmatchID  
    ,  new.[Value]      
    --,  new.SubmatchValue -- omit for distinct rows
    FirstValue
    FROM JobOperationsChangeLog
/*
    OUTER APPLY M1_MA.dbo.RegexFind (
        @key_value_pair_regex_pat
        ,xagTableOldValues
        ,1
        ,1) old 
*/
    OUTER APPLY M1_MA.dbo.RegexFind (
        @key_value_pair_regex_pat
        ,xagTableNewValues
        ,1
        ,1) new 
    
    WHERE xagChangeType='I'
    AND Match_ID NOT IN (1,2,3) -- eliminate rows matching key fields
)
, inserts_DISTINCT as (
    SELECT DISTINCT  -- returns 0 rows!
       JobID, JobAssemblyID, JobOperationID

    ,  FirstDate
    ,  ChangeUserID 
    ,  ChangeLogID
    ,  MatchID

    ,  FirstValue

    FROM inserts_BASE
)
, inserts_GROUP as (  -- returns 0 rows!
    SELECT          
        JobID, JobAssemblyID, JobOperationID
    ,  FirstDate
    ,  ChangeUserID 
    ,  ChangeLogID
    ,  MIN(  FirstValue  )
    FirstValue
    ,  MatchID
    --,  new_FirstIndex
    FROM inserts_BASE
    GROUP BY JobID, JobAssemblyID, JobOperationID, FirstDate, ChangeUserID, ChangeLogID, MatchID
)
, inserts_OVER_PARTITION as (  -- returns 0 rows!
    SELECT          
        JobID, JobAssemblyID, JobOperationID
    ,  FirstDate
    ,  ChangeUserID 
    ,  ChangeLogID
    ,  MatchID

    ,  FIRST_VALUE (FirstValue) OVER( 
                PARTITION BY JobID, JobAssemblyID, JobOperationID, FirstDate, ChangeUserID, ChangeLogID, MatchID
                ORDER BY ChangeLogID, MatchID
        )
    FirstValue
    --,  new_FirstIndex
    FROM inserts_BASE
)
, inserts_SUBMATCH as (
    SELECT  JobID, JobAssemblyID, JobOperationID
    , FirstDate, ChangeUserID, ChangeLogID
    --, MatchID , new_FirstIndex , new_SubmatchID
    , FirstValue
    FROM inserts_BASE
    WHERE ISNULL(new_SubmatchID , 1 ) = 1 
    -- ^ only way to yeild distinct partition over JobID, JobAssemblyID, JobOperationID, ChangeDate, ChangeUserID, ChangeLogID, MatchID
)
--SELECT * from JobOperationsChangeLog
--SELECT   * from inserts_BASE
--SELECT * from inserts_DISTINCT        -- does not work! returns zero rows!
--SELECT * from inserts_GROUP           -- does not work! returns zero rows!
--SELECT * from inserts_OVER_PARTITION  -- does not work! returns zero rows!
SELECT * from inserts_SUBMATCH          -- regex metadata exploit workaround

In the online tests, DISTINCT or GROUP BY do work, but there I am not able to implement the base CTE that uses OUTER APPLY on the function. Too many restrictions. So my example is inadequate to test the hypothesis.

Sample data

###Sample of raw input data### Pre-filtered change log data (I am not responsible for the terrible implementation of logging field changes):

SELECT xagChangeLogID, xagChangeType, xagChangeDate, xagChangeUserID
, xagTableKeyValues, xagTableOldValues , xagTableNewValues 
from ChangeLog     
WHERE xagTableName='JobOperations'
AND xagChangeUserID <> 'SQLAGENT - TSQL JOBS'
AND xagChangeType = 'I' 
order by xagChangeLogID
xagChangeLogID xagChangeType xagChangeDate xagChangeUserID xagTableKeyValues xagTableNewValues
499725 I 2023-05-02 15:03:40.647 ALIPLY jmoJobID = 27180-02-01 jmoJobAssemblyID = 0 jmoJobOperationID = 545 jmoJobID = 27180-02-01 jmoJobOperationID = 545 jmoOperationType = 1 jmoWorkCenterID = DRYIN jmoProcessID = UNDER jmoProcessShortDescription = Roofing Dry-In jmoProcessLongDescriptionRTF = {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Tahoma;}} \viewkind4\uc1\pard\f0\fs18 This part is for installation of all roof dry-in products and self-adhering membranes\par } jmoProcessLongDescriptionText = This part is for installation of all roof dry-in products and self-adhering membranes jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = MP jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoFirm = 1 jmoEstimatedProductionHours = 0.02 jmoMachinesToSchedule = 1 jmoCreatedBy = aliply jmoCreatedDate = May 2 2023 3:03PM
499810 I 2023-05-02 15:07:56.777 VICGEN jmoJobID = 29121-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 10 jmoJobID = 29121-01-01 jmoJobOperationID = 10 jmoOperationType = 1 jmoWorkCenterID = ENGIN jmoProcessID = PERMI jmoProcessShortDescription = Permitting jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = TD jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 8.00 jmoMachinesToSchedule = 1
499811 I 2023-05-02 15:07:56.777 VICGEN jmoJobID = 29121-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 20 jmoJobID = 29121-01-01 jmoJobOperationID = 20 jmoOperationType = 1 jmoWorkCenterID = BRAKE jmoProcessID = SMETA jmoProcessShortDescription = Sheet Metal Fab jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
499812 I 2023-05-02 15:07:56.790 VICGEN jmoJobID = 29121-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 30 jmoJobID = 29121-01-01 jmoJobOperationID = 30 jmoOperationType = 1 jmoWorkCenterID = TRUCK jmoProcessID = DELIV jmoProcessShortDescription = Delivery to Job jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 3.0000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 3.00 jmoMachinesToSchedule = 1
499813 I 2023-05-02 15:07:56.790 VICGEN jmoJobID = 29121-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 510 jmoJobID = 29121-01-01 jmoJobOperationID = 510 jmoOperationType = 1 jmoWorkCenterID = IWIND jmoProcessID = IWIND jmoProcessShortDescription = Window and Door Install jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 0.50 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
499814 I 2023-05-02 15:07:56.790 VICGEN jmoJobID = 29121-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 520 jmoJobID = 29121-01-01 jmoJobOperationID = 520 jmoOperationType = 1 jmoWorkCenterID = CRANE jmoProcessID = CRANE jmoProcessShortDescription = Crane Operator jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
502171 I 2023-05-03 07:36:28.000 LINHOC jmoJobID = 25140-04-01 jmoJobAssemblyID = 0 jmoJobOperationID = 535 jmoJobID = 25140-04-01 jmoJobOperationID = 535 jmoOperationType = 2 jmoAddedOperation = 1 jmoWorkCenterID = SUB jmoProcessID = 9FREI jmoProcessShortDescription = 905 - FREIGHT / POSTAGE jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 1.00 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoPartID = FREIGHT/POSTAGE jmoUnitOfMeasure = EA jmoSupplierOrganizationID = RBWAG jmoFirm = 1 jmoPurchaseOrderID = 31824 jmoEstimatedUnitCost = 10.40000 jmoCalculatedUnitCost = 10.40000 jmoStartDate = Aug 26 2022 12:00AM jmoDueDate = Feb 1 2023 12:00AM jmoMachinesToSchedule = 1 jmoCreatedBy = linhoc jmoCreatedDate = May 3 2023 7:36AM
502183 I 2023-05-03 07:54:05.963 PAMNEW jmoJobID = 29123-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 10 jmoJobID = 29123-01-01 jmoJobOperationID = 10 jmoOperationType = 1 jmoWorkCenterID = ENGIN jmoProcessID = PERMI jmoProcessShortDescription = Permitting jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = TD jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 8.00 jmoMachinesToSchedule = 1
502184 I 2023-05-03 07:54:05.963 PAMNEW jmoJobID = 29123-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 20 jmoJobID = 29123-01-01 jmoJobOperationID = 20 jmoOperationType = 1 jmoWorkCenterID = BRAKE jmoProcessID = SMETA jmoProcessShortDescription = Sheet Metal Fab jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
502185 I 2023-05-03 07:54:05.963 PAMNEW jmoJobID = 29123-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 30 jmoJobID = 29123-01-01 jmoJobOperationID = 30 jmoOperationType = 1 jmoWorkCenterID = TRUCK jmoProcessID = DELIV jmoProcessShortDescription = Delivery to Job jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 3.0000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 3.00 jmoMachinesToSchedule = 1
502186 I 2023-05-03 07:54:05.963 PAMNEW jmoJobID = 29123-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 510 jmoJobID = 29123-01-01 jmoJobOperationID = 510 jmoOperationType = 1 jmoWorkCenterID = IWIND jmoProcessID = IWIND jmoProcessShortDescription = Window and Door Install jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 0.50 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
502187 I 2023-05-03 07:54:05.963 PAMNEW jmoJobID = 29123-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 520 jmoJobID = 29123-01-01 jmoJobOperationID = 520 jmoOperationType = 1 jmoWorkCenterID = CRANE jmoProcessID = CRANE jmoProcessShortDescription = Crane Operator jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
502195 I 2023-05-03 07:55:47.807 PAMNEW jmoJobID = 29124-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 10 jmoJobID = 29124-01-01 jmoJobOperationID = 10 jmoOperationType = 1 jmoWorkCenterID = ENGIN jmoProcessID = PERMI jmoProcessShortDescription = Permitting jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = TD jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 8.00 jmoMachinesToSchedule = 1
502196 I 2023-05-03 07:55:47.807 PAMNEW jmoJobID = 29124-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 20 jmoJobID = 29124-01-01 jmoJobOperationID = 20 jmoOperationType = 1 jmoWorkCenterID = BRAKE jmoProcessID = SMETA jmoProcessShortDescription = Sheet Metal Fab jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
502197 I 2023-05-03 07:55:47.807 PAMNEW jmoJobID = 29124-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 30 jmoJobID = 29124-01-01 jmoJobOperationID = 30 jmoOperationType = 1 jmoWorkCenterID = TRUCK jmoProcessID = DELIV jmoProcessShortDescription = Delivery to Job jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 3.0000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 3.00 jmoMachinesToSchedule = 1
502198 I 2023-05-03 07:55:47.807 PAMNEW jmoJobID = 29124-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 510 jmoJobID = 29124-01-01 jmoJobOperationID = 510 jmoOperationType = 1 jmoWorkCenterID = IWIND jmoProcessID = IWIND jmoProcessShortDescription = Window and Door Install jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 0.50 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
502199 I 2023-05-03 07:55:47.807 PAMNEW jmoJobID = 29124-01-01 jmoJobAssemblyID = 0 jmoJobOperationID = 520 jmoJobID = 29124-01-01 jmoJobOperationID = 520 jmoOperationType = 1 jmoWorkCenterID = CRANE jmoProcessID = CRANE jmoProcessShortDescription = Crane Operator jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1

Sample of inserts with fields split out, contains duplicate records

SELECT JobID, JobAssemblyID, JobOperationID, ChangeDate, ChangeUserID
, xagChangeLogID , xagTableNewValues
from JobOperationsChangeLog 
where xagChangeType = 'I'
order by xagChangeLogID
JobID JobAssemblyID JobOperationID ChangeDate ChangeUserID xagChangeLogID xagTableNewValues
27180-02-01 0 545 2023-05-02 15:03:40.647 ALIPLY 499725 jmoJobID = 27180-02-01 jmoJobOperationID = 545 jmoOperationType = 1 jmoWorkCenterID = DRYIN jmoProcessID = UNDER jmoProcessShortDescription = Roofing Dry-In jmoProcessLongDescriptionRTF = {\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fcharset0 Tahoma;}} \viewkind4\uc1\pard\f0\fs18 This part is for installation of all roof dry-in products and self-adhering membranes\par } jmoProcessLongDescriptionText = This part is for installation of all roof dry-in products and self-adhering membranes jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = MP jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoFirm = 1 jmoEstimatedProductionHours = 0.02 jmoMachinesToSchedule = 1 jmoCreatedBy = aliply jmoCreatedDate = May 2 2023 3:03PM
29121-01-01 0 10 2023-05-02 15:07:56.777 VICGEN 499810 jmoJobID = 29121-01-01 jmoJobOperationID = 10 jmoOperationType = 1 jmoWorkCenterID = ENGIN jmoProcessID = PERMI jmoProcessShortDescription = Permitting jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = TD jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 8.00 jmoMachinesToSchedule = 1
29121-01-01 0 20 2023-05-02 15:07:56.777 VICGEN 499811 jmoJobID = 29121-01-01 jmoJobOperationID = 20 jmoOperationType = 1 jmoWorkCenterID = BRAKE jmoProcessID = SMETA jmoProcessShortDescription = Sheet Metal Fab jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
29121-01-01 0 30 2023-05-02 15:07:56.790 VICGEN 499812 jmoJobID = 29121-01-01 jmoJobOperationID = 30 jmoOperationType = 1 jmoWorkCenterID = TRUCK jmoProcessID = DELIV jmoProcessShortDescription = Delivery to Job jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 3.0000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 3.00 jmoMachinesToSchedule = 1
29121-01-01 0 510 2023-05-02 15:07:56.790 VICGEN 499813 jmoJobID = 29121-01-01 jmoJobOperationID = 510 jmoOperationType = 1 jmoWorkCenterID = IWIND jmoProcessID = IWIND jmoProcessShortDescription = Window and Door Install jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 0.50 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
29121-01-01 0 520 2023-05-02 15:07:56.790 VICGEN 499814 jmoJobID = 29121-01-01 jmoJobOperationID = 520 jmoOperationType = 1 jmoWorkCenterID = CRANE jmoProcessID = CRANE jmoProcessShortDescription = Crane Operator jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
25140-04-01 0 535 2023-05-03 07:36:28.000 LINHOC 502171 jmoJobID = 25140-04-01 jmoJobOperationID = 535 jmoOperationType = 2 jmoAddedOperation = 1 jmoWorkCenterID = SUB jmoProcessID = 9FREI jmoProcessShortDescription = 905 - FREIGHT / POSTAGE jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 1.00 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoPartID = FREIGHT/POSTAGE jmoUnitOfMeasure = EA jmoSupplierOrganizationID = RBWAG jmoFirm = 1 jmoPurchaseOrderID = 31824 jmoEstimatedUnitCost = 10.40000 jmoCalculatedUnitCost = 10.40000 jmoStartDate = Aug 26 2022 12:00AM jmoDueDate = Feb 1 2023 12:00AM jmoMachinesToSchedule = 1 jmoCreatedBy = linhoc jmoCreatedDate = May 3 2023 7:36AM
29123-01-01 0 10 2023-05-03 07:54:05.963 PAMNEW 502183 jmoJobID = 29123-01-01 jmoJobOperationID = 10 jmoOperationType = 1 jmoWorkCenterID = ENGIN jmoProcessID = PERMI jmoProcessShortDescription = Permitting jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = TD jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 8.00 jmoMachinesToSchedule = 1
29123-01-01 0 20 2023-05-03 07:54:05.963 PAMNEW 502184 jmoJobID = 29123-01-01 jmoJobOperationID = 20 jmoOperationType = 1 jmoWorkCenterID = BRAKE jmoProcessID = SMETA jmoProcessShortDescription = Sheet Metal Fab jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
29123-01-01 0 30 2023-05-03 07:54:05.963 PAMNEW 502185 jmoJobID = 29123-01-01 jmoJobOperationID = 30 jmoOperationType = 1 jmoWorkCenterID = TRUCK jmoProcessID = DELIV jmoProcessShortDescription = Delivery to Job jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 3.0000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 3.00 jmoMachinesToSchedule = 1
29123-01-01 0 510 2023-05-03 07:54:05.963 PAMNEW 502186 jmoJobID = 29123-01-01 jmoJobOperationID = 510 jmoOperationType = 1 jmoWorkCenterID = IWIND jmoProcessID = IWIND jmoProcessShortDescription = Window and Door Install jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 0.50 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
29123-01-01 0 520 2023-05-03 07:54:05.963 PAMNEW 502187 jmoJobID = 29123-01-01 jmoJobOperationID = 520 jmoOperationType = 1 jmoWorkCenterID = CRANE jmoProcessID = CRANE jmoProcessShortDescription = Crane Operator jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
29124-01-01 0 10 2023-05-03 07:55:47.807 PAMNEW 502195 jmoJobID = 29124-01-01 jmoJobOperationID = 10 jmoOperationType = 1 jmoWorkCenterID = ENGIN jmoProcessID = PERMI jmoProcessShortDescription = Permitting jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 1.0000 jmoStandardFactor = TD jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 8.00 jmoMachinesToSchedule = 1
29124-01-01 0 20 2023-05-03 07:55:47.807 PAMNEW 502196 jmoJobID = 29124-01-01 jmoJobOperationID = 20 jmoOperationType = 1 jmoWorkCenterID = BRAKE jmoProcessID = SMETA jmoProcessShortDescription = Sheet Metal Fab jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
29124-01-01 0 30 2023-05-03 07:55:47.807 PAMNEW 502197 jmoJobID = 29124-01-01 jmoJobOperationID = 30 jmoOperationType = 1 jmoWorkCenterID = TRUCK jmoProcessID = DELIV jmoProcessShortDescription = Delivery to Job jmoQuantityPerAssembly = 1.000000 jmoProductionStandard = 3.0000 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoEstimatedProductionHours = 3.00 jmoMachinesToSchedule = 1
29124-01-01 0 510 2023-05-03 07:55:47.807 PAMNEW 502198 jmoJobID = 29124-01-01 jmoJobOperationID = 510 jmoOperationType = 1 jmoWorkCenterID = IWIND jmoProcessID = IWIND jmoProcessShortDescription = Window and Door Install jmoQuantityPerAssembly = 1.000000 jmoSetupHours = 0.50 jmoStandardFactor = TH jmoSetupRate = 25.00 jmoProductionRate = 25.00 jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1
29124-01-01 0 520 2023-05-03 07:55:47.807 PAMNEW 502199 jmoJobID = 29124-01-01 jmoJobOperationID = 520 jmoOperationType = 1 jmoWorkCenterID = CRANE jmoProcessID = CRANE jmoProcessShortDescription = Crane Operator jmoQuantityPerAssembly = 1.000000 jmoStandardFactor = MP jmoOperationQuantity = 1.00000 jmoMachineType = 1 jmoUnitOfMeasure = EA jmoFirm = 1 jmoMachinesToSchedule = 1

Sample of filtered data with key fields split out

SELECT  * from inserts
JobID JobAssemblyID JobOperationID FirstDate ChangeUserID xagChangeLogID Match_ID FirstIndex FirstValue
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 4 68 jmoWorkCenterID = TRUCK
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 4 68 jmoWorkCenterID = TRUCK
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 5 92 jmoProcessID = DELIV
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 6 113 jmoProcessShortDescription = Delivery to Job
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 6 113 jmoProcessShortDescription = Delivery to Job
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 7 158 jmoQuantityPerAssembly = 1.000000
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 8 192 jmoProductionStandard = 3.0000
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 8 192 jmoProductionStandard = 3.0000
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 9 223 jmoStandardFactor = TH
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 10 246 jmoSetupRate = 25.00
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 10 246 jmoSetupRate = 25.00
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 11 267 jmoProductionRate = 25.00
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 12 293 jmoOperationQuantity = 1.00000
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 12 293 jmoOperationQuantity = 1.00000

sample of de-duplicated result via submatch exploit

(other methods such as DISTINCT yeild empty set)

SELECT * from inserts_SUBMATCH
JobID JobAssemblyID JobOperationID FirstDate ChangeUserID ChangeLogID FirstValue
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoWorkCenterID = TRUCK
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoProcessID = DELIV
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoProcessShortDescription = Delivery to Job
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoQuantityPerAssembly = 1.000000
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoProductionStandard = 3.0000
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoStandardFactor = TH
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoSetupRate = 25.00
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoProductionRate = 25.00
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoOperationQuantity = 1.00000
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoMachineType = 1
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoUnitOfMeasure = EA
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoFirm = 1
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoEstimatedProductionHours = 3.00
29127-01-01 0 30 2023-05-03 08:12:11.583 PAMNEW 502294 jmoMachinesToSchedule = 1
29127-01-01 0 510 2023-05-03 08:12:11.583 PAMNEW 502295 jmoWorkCenterID = IWIND
29127-01-01 0 510 2023-05-03 08:12:11.583 PAMNEW 502295 jmoProcessID = IWIND
29127-01-01 0 510 2023-05-03 08:12:11.583 PAMNEW 502295 jmoProcessShortDescription = Window and Door Install
29127-01-01 0 510 2023-05-03 08:12:11.583 PAMNEW 502295 jmoQuantityPerAssembly = 1.000000
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
Joseph Shirk
  • 45
  • 5
  • Doesn't make sense. `DISTINCT` can't make 0 rows, it's effectively a glorified `GROUP BY all_columns`. Perhaps you have a `WHERE` somewhere that is doing something you didn't expect, maybe the `NOT IN` with nullable columns etc? – Charlieface May 04 '23 at 23:01
  • inserts_BASE returns results. Adding DISTINCT to it returns zero rows. Moreover, the second layer on top of inserts_BASE, that is, inserts_DISTINCT , returns zero rows. There is no additional WHERE added there. – Joseph Shirk May 05 '23 at 12:00
  • Sorry not possible, there is no way `DISTINCT` can return zero rows if it starts off with more than zero. The only other thing I can think of is that there is something else non-deterministic, such as a `TOP` with a non-deterministic `ORDER BY` – Charlieface May 05 '23 at 12:20
  • The code is right there to read. – Joseph Shirk May 05 '23 at 12:37
  • I think I will have to make a toy version of this problem that does not involve the use of RegexFind. I'm open to suggestions on how to reproduce this on the fiddle sites. – Joseph Shirk May 05 '23 at 12:48
  • I didn't DV, but I think "unclear" might be the answer, it lacks sufficient debugging info. We have no way of replicating your problem, and all we can see is that it *does* work, on the DBFiddle. We don't even have your CLR functions' code. I can't tell you anything other than "what you describe is impossible, so there must be something else you're not telling us". It's not possible that `inserts_BASE` returns rows, and `inserts_DISTINCT` returns zero rows, when all `inserts_DISTINCT` does is `DISTINCT`. There is simply no way that can happen, unless something undeterministic is going on. – Charlieface May 05 '23 at 12:48
  • RegexFind function (not CLR) is right here in second block: https://dbfiddle.uk/BBL3QWra?hide=16 – Joseph Shirk May 05 '23 at 12:52
  • That's not the code, just the output. You have `AND (Keys.JobID NOT LIKE 'jmo%' and Keys.JobOperationID NOT LIKE 'jmo%' and Keys.JobAssemblyID NOT LIKE 'jmo%')` and also `WHERE xagChangeType='I' AND Match_ID NOT IN (1,2,3)` which don't handle possible nulls if there are any. – Charlieface May 05 '23 at 12:56
  • It's hidden by default, since there are not sufficient permissions to create the function. https://dbfiddle.uk/BBL3QWra I'm working on a re-write that does not involve creating a function (that can't be run on the fiddle sites) that also works for compatibility level 120 (i.e. without function STRING_SPLIT() - I can't bump my DB up to level 130. – Joseph Shirk May 05 '23 at 15:42
  • What's going on here is something vicious. The split function has a sequence of match IDs that reflect the set of key-values in the string, yet I want to eliminate some of those that are junk in the WHERE clause. But somehow the query engine optimizes in such a way that DISTINCT will cause a different numbering of match ID. So the logic is self-contradicting. It does not seem to matter whether the DISTINCT is applied in a later stage. I have come up with a better strategy that avoids this, at least for a known set or number of key-value pairs (to follow): – Joseph Shirk May 05 '23 at 18:56
  • This works, as long as you don't expect to list an unknown number of pairs. These are named specifically for the desired key-value pairs and no quasi-duplicates are produced. ... FROM ChangeLog /* only works because key order is fixed ; */ CROSS APPLY dbo.fn_split_string(xagTableKeyValues,'jmo') JOB CROSS APPLY dbo.fn_split_string(xagTableKeyValues,'jmo') JOBASSEMBLY CROSS APPLY dbo.fn_split_string(xagTableKeyValues,'jmo') JOBOPERATION WHERE xagTableName='JobOperations' AND (JOB.id=2 AND JOBASSEMBLY.id=3 AND JOBOPERATION.id=4) – Joseph Shirk May 05 '23 at 18:58
  • 1
    If you want to use `STRING_SPLIT` you can use a function that calls it which is defined in another database. You can even put it in `master` and call it everywhere, see https://stackoverflow.com/a/69928018/14868997 – Charlieface May 06 '23 at 21:37

0 Answers0