none
SQLServer2016 JOIN DATETIME2(3) with DATETIME

    Question

  • Hi, 

    I'm getting unexpected results when joining between DATETIME2(3) column to DATETIME column with PK , in SQLServer 2016.

    The details are : 

    I have the following table: 

    CREATE TABLE DATETIME_TEST (
        [DATETIME] DATETIME   NOT NULL,
        [DATETIME2_3] DATETIME2(3));
    ALTER TABLE DATETIME_TEST ADD CONSTRAINT PK_DATETIME_TEST PRIMARY KEY ([DATETIME]);
    INSERT INTO DATETIME_TEST
      (  [DATETIME],[DATETIME2_3])
    VALUES
      ('20020202 02:02:02.000', '20020202 02:02:02.000' ), 
      ('20020202 02:02:02.003', '20020202 02:02:02.003' ), 
      ('20020202 02:02:02.007', '20020202 02:02:02.007' ),
      ('2019-04-28 07:23:29.447', '2019-04-28 07:23:29.447' )
    ;
    
    SELECT * FROM DATETIME_TEST WHERE  CONVERT(DATETIME2(3), [DATETIME]) = [DATETIME2_3]

    The results :

    DATETIME DATETIME2_3 2002-02-02 02:02:02.000 2002-02-02 02:02:02.000 2002-02-02 02:02:02.003 2002-02-02 02:02:02.003 2002-02-02 02:02:02.007 2002-02-02 02:02:02.007 2019-04-28 07:23:29.447 2019-04-28 07:23:29.447


    As you can see above,   the values are equal .

    SELECT		
    				a.DATETIME,
    				a.DATETIME2_3
    FROM	DATETIME_TEST	a
    INNER JOIN DATETIME_TEST b ON CONVERT(DATETIME2(3),a. [DATETIME]) = b.[DATETIME2_3]

    The results :

    DATETIME2_3 DATETIME 2002-02-02 02:02:02.000 2002-02-02 02:02:02.000


    although values are equal, I get only part of the rows. 

    But If I remove the PK or change compatibility level to COMPATIBILITY_LEVEL = 120  

    I get all rows as expected

    Is it a bug ? 

    Is there a better way to do this join.

    Note: I join to the same table only for the simplicity of the example in real life I join between 2 different tables.

    Tuesday, May 21, 2019 11:43 AM

All replies

  • You might be running into this issue with a 2016 behavior change. It is unfortunate the MS screwed everything with the changes to Connect but that thread at least covers the basics. 
    Tuesday, May 21, 2019 12:35 PM
  • I'm aware of the changes in the behaviour in SQL2016. 

    I don't want to change the compatibility level.

    The join gets different results when the table has a Primary Key. 

    If I do the same test without PK in the table, The join succeeds.

    I do not think existance of PK should influence on the number of rows in the resullt.

    Tuesday, May 21, 2019 1:20 PM
  • I agree that this is a bug. The join is definitely not returning the correct result. For the disbeliever, try adding OPTION (HASH JOIN) to the join with the PK in place and see what happens. A hint should not change the result.

    The plan for the join with the PK has a nested loop join with this Seek predicate:

    Seek Keys[1]: Prefix: [tempdb].[dbo].[DATETIME_TEST].DATETIME = Scalar Operator([tempdb].[dbo].[DATETIME_TEST].[DATETIME2_3] as [b].[DATETIME2_3])

    Note here that the explicit convert has been removed, and this filters out three of the rows. The operator also has a Predicate with the original condition, but that helps little.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, May 21, 2019 9:39 PM
  • Yes, You are right adding the hint OPTION (HASH JOIN)  does changes the results.

    Existance of PK or existance of hint doesn't need to change the results of a query.

    Where do I report a bug ?

    Wednesday, May 22, 2019 5:18 AM
  • You can file a bug on https://feedback.azure.com/forums/908035-sql-server. Note that filing a bug here is no guarantee that it will be fixed, although I think Microsoft takes incorrect-results bug seriously.

    If this is a blocking issue for you and you need a fix, you need to open a support case through your regular channel. To get a fix, you may have to argue from a business perspective why you need a fix. Else the support people may only help you with a workaround. Since this clearly a bug, a case should be of no cost for you, but it still likely they will ask for a credit card or similar initially.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, May 22, 2019 6:43 PM
  • We are working actively on fixing the issue. As soon as the fix is done, we will look at how to backport the fix to released versions of SQL Server.
    Thursday, June 13, 2019 6:20 PM