Author Topic: (Microsoft) SQL wierdness  (Read 574 times)

iddu

  • Are we there yet?
(Microsoft) SQL wierdness
« on: August 06, 2019, 02:35:33 pm »
Left join query not returing *field content* of outer joined table...

select *
from ACO_CCaseContact as CCC
left outer join ACO_Organisation as O on CCC.OrganisationID = O.OrganisationID
where CCC.SID = 2 and CCC.Case_ID = 'EXP-0200860' and CCC.CIdx = 8000

returns 1 row, but the fields O.* are all null (i.e. thinks join could not be constructed).

OrganisationID can both be proved by dump of field values to be equivalent value  of "0x4141414141465246545532526B346E4641796E5743436B6F553733667445" (char field in hex)

No, it's not use of O.* in where clause causing implict inner join/filtering of row, unless I'm missing something.

Any clues?
I'd offer you some moral support - but I have questionable morals.

FifeingEejit

  • Not Small just Far Away at the back
Re: (Microsoft) SQL wierdness
« Reply #1 on: August 06, 2019, 02:44:03 pm »
Are both OrganizationId fields the same data type?
Is there a foreign key constraint on CCC.OrganizationId for O.OrganizationId?
Whats the execution plan looking like?

iddu

  • Are we there yet?
Re: (Microsoft) SQL wierdness
« Reply #2 on: August 06, 2019, 02:56:26 pm »
Yes
No
As to be expected - nothing anomolous jumps out.

Just doesn't seem to believe the relevant value can be found in any row of O. - so 'correctly' fails the join, whilst reviewing the O. table content singularly does show existence.

I'd offer you some moral support - but I have questionable morals.

iddu

  • Are we there yet?
Re: (Microsoft) SQL wierdness
« Reply #3 on: August 06, 2019, 02:57:50 pm »
Relevant CheckDB  already run w/o issues, so apparantly DB content in good nick...
I'd offer you some moral support - but I have questionable morals.

FifeingEejit

  • Not Small just Far Away at the back
Re: (Microsoft) SQL wierdness
« Reply #4 on: August 06, 2019, 03:27:24 pm »
Stating the obvious: The lack of foreign key constraint allows for the potential of the data to be different while apparently not on visual inspection.

What's the data type?
If it's VARCHAR what's the Collition of both columns?

It's sounding like it's one of those situations where the difference is so blindingly obvious that you can't see it for the glare, or ridiculously pernickety.


I'm currently lost in ORACLEland...
Seriously lost, no one ever thought about how they were managing these databases and certainly not before merging them... and now we're trying to shift them to a version 12 from a mess of 10s and 8s...
Currently riding the Circular dependency roundabout

Re: (Microsoft) SQL wierdness
« Reply #5 on: August 06, 2019, 03:30:41 pm »
Don't you usually get an error if you try and equate columns of different collation?

iddu

  • Are we there yet?
Re: (Microsoft) SQL wierdness
« Reply #6 on: August 06, 2019, 04:17:51 pm »
Collation both fields is SQL_Latin1_General_CP1_CI_AS, and yes, you'd (generally) get an error on mismatch

> It's sounding like it's one of those situations where the difference is so blindingly obvious that you can't see it for the glare, or ridiculously pernickety.

Yeerrrz - I've already tried explaining it to the cat, for the DOH! moment.
I'd offer you some moral support - but I have questionable morals.

iddu

  • Are we there yet?
Re: (Microsoft) SQL wierdness
« Reply #7 on: August 06, 2019, 04:18:41 pm »
char(88) on both sides, singular field usage as PKey in O table.
I'd offer you some moral support - but I have questionable morals.

iddu

  • Are we there yet?
Re: (Microsoft) SQL wierdness
« Reply #8 on: August 06, 2019, 05:45:43 pm »
Grrrr...

FFS.   :facepalm: :facepalm: :facepalm:

Pad one field with LF.  Pad other with space. Equal in the JOIN? - Are they bollocks. 
Can you see it in grid / via cast to varbinary - No.  Gotta use [sys].[fn_varbintohexstr](...)

Gonna be a severe LART'ing tomorrow.
I'd offer you some moral support - but I have questionable morals.

Re: (Microsoft) SQL wierdness
« Reply #9 on: August 06, 2019, 05:52:25 pm »
Remember a similar thing back in 90s on something I was working on. Field in one file padded with spaces, field in other file padded with nulls. Looked the same viewed in text mode. Not so in hex mode. Something staring you in the face when the record matching didn't work...

FifeingEejit

  • Not Small just Far Away at the back
Re: (Microsoft) SQL wierdness
« Reply #10 on: August 06, 2019, 09:46:41 pm »
Grrrr...

FFS.   :facepalm: :facepalm: :facepalm:

Pad one field with LF.  Pad other with space. Equal in the JOIN? - Are they bollocks. 
Can you see it in grid / via cast to varbinary - No.  Gotta use [sys].[fn_varbintohexstr](...)

Gonna be a severe LART'ing tomorrow.

Doh...

Re: (Microsoft) SQL wierdness
« Reply #11 on: August 06, 2019, 10:20:27 pm »
Ugh. Whitespace. Nuff said.