#1  
Old 26th July 2017, 10:46 PM
Paul Toms
 
Posts: n/a
Default SQL bug?

Tables and SQL statement are in the zip.

Problem:

Hetype.nx1 has 936 record in it. One of these records ( primary key 608)
has a NULL value Hetype.Category value.
If you INNER JOIN the Category.nx1 table you would expect 935 records
returned.
If you add a meaningless WHERE that matches ALL records you would also
expect 935 records returned.
But if you also LEFT OUTER JOIN Similars.nx1 AND have a meaningless
WHERE you get 933 records return, with primary key records 947 and 948
excluded!!

Remove the LEFT OUTER JOIN Similars.nx1 and the problem goes away.
OR
put a value Category for primary key 608 and the problem goes away
OR
lose the WHERE and the problem goes away

So something not right here.

Paul


Attached Files
File Type: zip SQL_Problem.zip (87.4 KB, 0 views)
  #2  
Old 27th July 2017, 02:09 AM
Will Owyong
 
Posts: n/a
Default Re: SQL bug?

On 26/07/2017 9:46 PM, Paul Toms wrote:
> Tables and SQL statement are in the zip.
>
> Problem:
>
> Hetype.nx1 has 936 record in it. One of these records ( primary key 608)
> has a NULL value Hetype.Category value.
> If you INNER JOIN the Category.nx1 table you would expect 935 records
> returned.
> If you add a meaningless WHERE that matches ALL records you would also
> expect 935 records returned.
> But if you also LEFT OUTER JOIN Similars.nx1 AND have a meaningless
> WHERE you get 933 records return, with primary key records 947 and 948
> excluded!!
>
> Remove the LEFT OUTER JOIN Similars.nx1 and the problem goes away.
> OR
> put a value Category for primary key 608 and the problem goes away
> OR
> lose the WHERE and the problem goes away
>
> So something not right here.
>
> Paul
>


Hi Paul,

I've found if I wrap your WHERE condition

T.EquipmentType < 5

like so

COALESCE(T.EquipmentType,0) < 5
or
CAST(T.EquipmentType AS BYTE) < 5

it returns the expected results, so at least there's a workaround.

Trying a few of the other fields with different field types yield
similar abnormal results, each time I needed to use COALESCE() or CAST()
on the field (as above) in order to return the expected result.

Byte, BCD, and Double data types might be triggering something odd
within the SQL engine which the CAST() and COALESCE() functions help
correct.

--
Regards,
Will Owyong
(Newcastle & Parramatta
NSW, AUSTRALIA | GMT+10)


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT +11. The time now is 06:38 AM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.