Forum Discussion
SQL Server Query returns different results from SQL Server 2014 to SQL Server 2019
We are in the processes of migrating to SQL Server 2019 from SQL Server 2014. Our regression testing revealed an issue with 2019.
We are executing a query that involves 4 CTE's, some of which use partitioning. When we run the query in 2019 we get a different result than we do in 2016. In some cases there is a date field in the final selection which comes from a CTE, yet that date does not exist in any of the CTE's.
We did find a work around that seems to work. Both these query hints seem to give us the right results
OPTION(USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_120'));
level_130 and level_140 also work. Level_150 does not
OPTION(RECOMPILE, USE HINT('DISALLOW_BATCH_MODE'));
We use hundreds of queries in this SSIS project so adding the hint would be very difficult and we don't want to change the compatibility level of the database, but might be OK changes batch mode.
Is this a known issue? Was it fixed in another CU? We are using Version 15.0.4261.1 (CU 18) standard edition 64 bit.
- Jeff_ModenCopper ContributorYou should be making a call to Microsoft Support on this one because it sounds like a bug.
- olafhelperBronze Contributor
Is this a known issue?
jahill , how to say, you didn't provide any line of code or example to reproduce the behaviour.
- jahillCopper ContributorI've been told it's ok to post the query.Here is the SQL query that is causing the issue:
-- first, use windowing to find which events are related to each other. If the start_date is > max end_date of the window (all records that came before), then you are starting a new grouping, so is_start = 1
with related as
(
select *, case when start_date <= max(coalesce(end_date, '3000-01-01')) over (partition by oamt_barcode order by start_date rows between unbounded preceding and 1 preceding) then 0 else 1 end as is_start
from work.cmms_time_slice_events
where start_date is not null -- don't consider bad data quality records
and oamt_barcode is not null -- only oamt. Would do again for faults
and event_type_id in (36, 37, 38, 39, 40, 41, 44, 50, 52, 55, 56, 57) -- LR, CT, PR, etc. including uncat_delay
),
-- next, set a cluster_id for each row to relate them to others in the same group (overlapping)
clusters as
(
select oamt_barcode, start_date, end_date, sum(is_start) over (order by oamt_barcode rows unbounded preceding) as cluster_id -- sum works here because they are all 1s or 0s, and each cluster only has 1 start.
from related
),
-- build a single record to define periods where there are things happening. Any remaining gaps within the task boundaries will be uncat delay.
has_maint_drivers as
(
select oamt_barcode, cluster_id, min(start_date) as start_date, max(end_date) as end_date
from clusters
group by oamt_barcode, cluster_id
),
-- invert to find the gaps. This does *NOT* find the gaps between task/fault start and earliest maintenance driver, nor between the last maintenance driver and task/fault complete, which will need to be counted
inverse as
(
select oamt_barcode, end_date as start_date, min(start_date) over(partition by oamt_barcode order by start_date rows between 1 following and 2 following) as end_date
from has_maint_drivers
)
-- need to update this step to include the misses from the last step
select 'UNCAT_OAMT_GAP_' + i.oamt_barcode +'_' + convert(varchar,i.start_date,120) as remarks
, i.oamt_barcode as source_id
,44 as event_type_id
,tr.tail_number
,i.oamt_barcode
,tr.parent_fault_barcode as fault_barcode
,i.start_date
,i.end_date
,'DND' as accountability
from inverse i
left join source.cmms_task_relationships tr on i.oamt_barcode = tr.barcode
left join source.cmms_faults f on f.barcode = tr.parent_fault_barcode
where i.end_date is not null and i.end_date <> i.start_date
and (f.deferred_date not between i.start_date and i.end_date or f.barcode is null or f.deferred_date is null)
and tr.element_type = 'OAMT' and tr.is_current = 1 and coalesce(f.is_current,1) = 1
order by oamt_barcode, remarks, start_date,end_date
Here are the results of the query (only the date fields which is the issue:
Good Results Bad Results
start_date end_date start_date end_date
2018-06-22 01:02:00 2018-06-22 13:04:25 2018-06-21 23:35:40 2018-06-21 23:03:48
2018-06-22 17:57:00 2018-06-22 19:19:00 2018-06-22 13:52:13 2018-06-22 13:05:10
2018-06-22 19:49:00 2018-06-22 19:55:00 2018-06-22 19:49:00 2018-06-22 19:55:00
2018-06-23 03:00:00 2018-06-25 10:18:00 2018-06-22 23:25:00 2018-06-22 21:00:00
2018-06-25 10:36:00 2018-06-25 15:24:00 2018-06-25 10:36:00 2018-06-25 15:24:00
2018-06-25 15:30:00 2018-06-27 14:31:00 2018-06-25 15:30:00 2018-06-27 14:31:00- olafhelperBronze Contributor
jahill , we still have no chance to reproduce the reported behaviour, because we don't have your database.
Create a srcipt with tables+data+query, where we would be able with to reproduce it.