Forum Discussion

mheggie's avatar
mheggie
Frequent Visitor
6 months ago
Solved

Fabric Warehouse Incorrect Query Results

I'm hoping to get some guidance from the group on where to go with this.  I have 2 queries that run in sequence that read from my Fabric data warehouse.  The queries are different and should yield different results but when they run, they actually return the same data.  

 

Query #1:
DECLARE @StartingDate DATE = DATEADD(YEAR, -10, DATETRUNC(year, GETDATE()));

select * from rpt.vw_DimGLAccount
where [GL Account Key] in (SELECT F.ExpenseGLAccountKey
                    FROM gold.FactPayrollHistory F
                         JOIN gold.DimDate D ON F.PeriodEndDateKey = D.DateKey
                    WHERE D.CalendarDate >= @StartingDate);
 
Returns 2365 rows as it should

Query #2:
DECLARE @StartingDate DATE = DATEADD(YEAR, -10, DATETRUNC(year, GETDATE()));

select * from rpt.vw_DimGLAccount
where [GL Account Key] in (SELECT F.LiabilityGLAccountKey
                    FROM gold.FactPayrollHistory F
                         JOIN gold.DimDate D ON F.PeriodEndDateKey = D.DateKey
                    WHERE D.CalendarDate >= @StartingDate);
 
Should return 67 rows but returns the same 2365 rows as the first query.
 
If I run these in reverse order, when the second query returns the same results as the first one run.
 
It looks like the DW optimizer thinks these 2 queries are the same.  It doesn't recognize that the the queries have different column names in the subquery.
 
I have found a way to work around this specific instance by restructuring one of the queries so it is distinctly different from the other.  However, I can't guarantee that similar queries like above won't happen.  This feels like a bug to me.
 
Have you seen this?  Does anyone have any guidance on how things like this can be reported to Microsoft?  
 
Any thoughts / guidance you can provide is greatly appreciated!
data warehouse
  • deborshi_nag's avatar
    deborshi_nag
    6 months ago

    yeah, best to raise a ticket with product support - use the menu above Support > Product Support to log the new support case. Please make it clear in the ticket that removing the variable StartingDate resolves it; and the issue seems to be happening quite recently. 

     

    If my workaround helped please mark it as a solution, so that others in this situation can take advantage of this workaround! 

     

6 Replies

  • deborshi_nag's avatar
    deborshi_nag
    Super User
    6 months ago

    Hello mheggie 

     

    It is a very strange behavious indeed! Can you try using a WITH clause instead of a IN clause and let me know if you see any difference in the behaviour? Here's one of your query with a WITH clause. 

     

    -- Derive the starting date: first day of the current year minus 10 years
DECLARE @StartingDate DATE = DATEADD(YEAR, -10, DATETRUNC(year, GETDATE()));

WITH PayrollGL AS (
    SELECT DISTINCT F.ExpenseGLAccountKey
    FROM gold.FactPayrollHistory AS F
    JOIN gold.DimDate AS D
      ON F.PeriodEndDateKey = D.DateKey
    WHERE D.CalendarDate >= @StartingDate
)
SELECT a.*
FROM rpt.vw_DimGLAccount AS a
JOIN PayrollGL AS p
  ON a.[GL Account Key] = p.ExpenseGLAccountKey;

     

    • mheggie's avatar
      mheggie
      Frequent Visitor
      6 months ago

      Thanks for your suggestion. 

       

      I tested with your query for both expense and liability and they both return what is expected.  I had already worked around this by replacing @StartingDate in the where clause with the expression I was using to calculate it and it works.

       

      select * from rpt.vw_DimGLAccount
      where [GL Account Key] in (SELECT F.ExpenseGLAccountKey
                          FROM gold.FactPayrollHistory F
                               JOIN gold.DimDate D ON F.PeriodEndDateKey = D.DateKey
                          WHERE D.CalendarDate >= DATEADD(YEAR, -10, DATETRUNC(year, GETDATE())));
       
      There are definitely some workarounds for this which is why it feels like a bug to me. 
       
      The process that uses this has been running  successfully in production since May 2025.  This issue just appeared in the last few weeks.
      • deborshi_nag's avatar
        deborshi_nag
        Super User
        6 months ago

        yeah, best to raise a ticket with product support - use the menu above Support > Product Support to log the new support case. Please make it clear in the ticket that removing the variable StartingDate resolves it; and the issue seems to be happening quite recently. 

         

        If my workaround helped please mark it as a solution, so that others in this situation can take advantage of this workaround! 

         

  • stoic-harsh's avatar
    stoic-harsh
    Super User
    6 months ago

    Hi mheggie,

    Before concluding it an engine bug or caching issue, I would like to first think purely from a data/query perspective. If you are only looking at row counts as validation, it is possible that even if there are only 67 distinct liability keys,  [GL Account Key] in rpt.vw_DimGLAccount may contain 2,365 rows matching these 67 keys (given, no DISTINCT is being applied in the outer query).

    Another reason could be that [LiabilityGLAccountKey] and [ExpenseGLAccountKey] contain identical values, or one is a subset of other, over the filtered date range.

    Could you first verify this, by looking at the actual result sets, rather than relying solely on row counts?

    • mheggie's avatar
      mheggie
      Frequent Visitor
      6 months ago

      Thanks for the feedback.  I have evaluated the results along with the counts.  What I've shared in my post is accurate.  I should get 2365 rows returned for one query and 67 for the other.  LiabilityGLAccountKey and ExpenseGLAccountKey values are not identical and are not a subset of one another.  Liabilities post to a different place in the GL than expenses.

       

      If I change the "select *" in each query to a "select count(*)", the numbers returned are correct.  When I then change the "count(*)" back to "*", it returns the same result for both.

       

      Also ... the process with this logic has been running successfully in production since May 2025.  It is only in the last few weeks that this has been a problem.  I've tested this with other keys that reference the same dimension and can consistently re-create the issue.

       

      When the second query runs, it notes that it is using cached results.