none
left join to null result in query yields non-null column value--why?!? RRS feed

  • Question

  • Table1:

    Table2:

    Query3:

    Query3 returns:

    Query4:

    Query4 returns:

    Question: why, if the PK for the joined row of the Query3 result is null, does Query3.stuff3 return a non-null value?

    Sunday, April 2, 2017 3:54 PM

Answers

  • Microsoft does understand that database developers very reasonably expect an outer join to be implemented correctly, don't they? How long has this issue been identified, and when does Microsoft anticipate resolution? This is a VERY basic, fundamental operation to have such a glaring bug. The workaround of including a Where clause to exclude the null FK row is equivalent to saying "don't use an outer join because it doesn't work correctly. Use an inner join instead." They are not equivalent!

    Paul

    • Marked as answer by Dick Watson Monday, April 3, 2017 8:32 PM
    Monday, April 3, 2017 8:05 PM

All replies

  • Hi Dick,

    Is  "[pk] Is Null"  interpreted as Boolean?

    Imb.

    Sunday, April 2, 2017 4:24 PM
  • Apparently. It returns 0 or -1. I only put it there to demonstrate that we are joining to nothing in
    Query3. You can take it out and still get the same "ressult" for Query3.stuff3
    Sunday, April 2, 2017 4:27 PM
  • Interesting. This is one example where I think Access SQL is incorrect. Compare with SQL Server 2016:


    -Tom. Microsoft Access MVP

    Sunday, April 2, 2017 5:39 PM
  • Hello,

    This issue does exist. To work around, we may add WHERE condition to exclude NULL value. Besides, I suggest you submit a feedback on Access User Voice site:https://access.uservoice.com/

    Thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 3, 2017 8:17 AM
    Moderator
  • Microsoft does understand that database developers very reasonably expect an outer join to be implemented correctly, don't they? How long has this issue been identified, and when does Microsoft anticipate resolution? This is a VERY basic, fundamental operation to have such a glaring bug. The workaround of including a Where clause to exclude the null FK row is equivalent to saying "don't use an outer join because it doesn't work correctly. Use an inner join instead." They are not equivalent!

    Paul

    • Marked as answer by Dick Watson Monday, April 3, 2017 8:32 PM
    Monday, April 3, 2017 8:05 PM
  • It's not clear that, in the case of Access, they do understand this. And telling users to use their votes for at User Voice for bug fixes instead of the routinely rejected suggestions for basic improvements like a 1990's era SQL editor just adds insult to injury.
    Monday, April 3, 2017 8:35 PM
  • Sometimes Access makes me crazy. I just ran into this same issue in a *completely* different context. Racked my brain for an hour. Tried to create a standalone re-creation case. Failed for some reason. Took the many parts and pieces from where I was having the issue and copied them to a second database. Successively simplified them down to a minimal testcase so I could understand what/where, exactly, was the failure. Came to the forum to search/post. Found my own thread from six months ago.

    Annoying, adj: finding the same stupid Access bug for the second time in six months and not remembering you'd gone down this sewer pipe before.


    • Edited by Dick Watson Saturday, October 7, 2017 5:22 PM
    Saturday, October 7, 2017 5:21 PM