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
|