Subscribe to this thread
Home - General / All posts - Checking outer joins in Manifold 9
tjhb

8,280 post(s)
#11-Oct-18 08:46

Dan has found some unexpected query results which we have puzzled over together and discussed this afternoon. We think there may be a bug in the execution of outer joins in Manifold 9 (9.0.168.3).

The attached test project contains two tables, SUPPLIERS and ORDERS (in Manifold 8 format to help comparison).

When the project is opened in Manifold 9, the following query using LEFT JOIN gives the same results as if INNER JOIN had been used, or as if a WHERE filter had been used instead of the second join condition. As far as we can see, it should not. Instead, every row from the left table should be returned at least once.

--SQL9 

SELECT 

    s.[supplier_id], s.[supplier_country]

    o.[order_id] 

FROM 

    [SUPPLIERS] AS s 

    LEFT JOIN 

    --INNER JOIN 

    [ORDERS] AS o 

    ON s.[supplier_id] = o.[supplier_id] 

    AND s.[supplier_country] = 'New Zealand' 

    --WHERE s.[supplier_country] = 'New Zealand' 

Dan has compared the results of analogous queries in Manifold 8, Oracle, SQL Server and PostgreSQL. Their results all seem to be correct.

Is this a bug, or are we missing something? Unless it's obvious, we'll file a report.

Attachments:
Test outer join.map

rk
276 post(s)
#11-Oct-18 09:59

Instead, every row from the left table should be returned at least once.

No, only every row from left table that satisfies s.[supplier_country]= 'New Zealand'

If you insert another 'New Zealand' supplier and no orders for it, then you will see that supplier and ORDER_ID as NULL.

Hard to believe it is different in other systems

Attachments:
supplier8.png

tjhb

8,280 post(s)
#11-Oct-18 10:14

No. That would be the result of the first join condition only, plus a WHERE filter.

An outer join preserves all rows from LEFT, RIGHT or both (FULL) tables. One or both tables have immunity from the join conditions in that they are returned (singly, against NULL), even if not matched.

It is not that the first join condition is the true one, then any others are merely filters applied before or after. That is the role of WHERE.

Join conditions jointly control the join--that is, which rows are matched, left to right. Which rows from left and right are returned is controlled by the type of join, and, if it is present, by WHERE.

I should add, my first instinct Riivo was the same as yours. But I was wrong, Dan is right.

adamw


8,139 post(s)
#11-Oct-18 10:46

Tim (and Dan), you are right.

This is a bug in 9.

Very short demo why: if we change the condition in ON to FALSE, we should be getting all rows from SUPPLIERS with NULLs for fields from ORDERS. That's the nature of the outer join. And we are getting that if the condition is FALSE - but losing records from SUPPLIERS prematurely if the condition is a more complex (was: composite, corrected) one like in the first post.

We will fix it. (And extend the unit tests, etc.)

Thanks a lot for the post.

adamw


8,139 post(s)
#11-Oct-18 11:05

A few more words:

This is a bug, yes, but thankfully it's not something that is lurking around waiting to hit you. What happens here is that the intent of the join is pretty specific and somewhat unusual. It's not 'give me all suppliers from New Zealand and then let's see which ones have orders' - that would put 'from New Zealand' into WHERE either before or at most after the join, and keep the matching in the join. No, the intent is 'let's get all suppliers and, by the way, also match suppliers - just from New Zealand - to orders while we are at it'. When you write something specific and somewhat unusual like that, you notice when it does not do what you want. Which is good.

But we will fix it, absolutely, and extend the tests to cover this and related cases more thoroughly. Apologies for the bug.

rk
276 post(s)
#11-Oct-18 11:34

I did not know that.

I now found one source to describe this, but I have never seen it described before.

danb


1,656 post(s)
#11-Oct-18 18:50

I now found one source to describe this, but I have never seen it described before.

This is why it caught my attention initially. I just couldn't work out which result was correct. Tim and I discussed it at length and once we realized why the Oracle etc. result had to be the correct one, it suddenly seemed obvious. This conclusion took a while to reach however.

Many thanks for confirming this Adam and even better to know that it will soon be gone.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

Dimitri

5,045 post(s)
#12-Oct-18 06:26

Great find! Thank you Dan and Tim for your sharp eyes. This will be fixed immediately.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.