Skip to Content
author's profile photo Former Member
Former Member

Error at inner join; order of tables makes a difference

When using inner joins, the order in which joined tables are introduced should be irrelevant. However, I'm faced with a query which will be executed correctly, but when I change the table order, the query would yield totally wrong records.

Illustration:

I have a table user2files which assigns certain files to certain users. There is NO record in the table that meets the following criteria

user_id = 410784 and file_id = 349658

Thus,

select * from user2files

where user2files.file_id = 349658 and user2files.user_id = 410784

yields an empty result set. So far, so good - correct!

BUT ... when I just add another table to be joined, suddenly I get result records:

// WRONG!! returns one record!

select * from files, user2files

where files.file_id = user2files.file_id

and user2files.file_id = 349658 and user2files.user_id = 410784

Strange - the additional table is linked via inner join, so if the table user2files does not return any records, the additional join to the table files should not change anything - there should be not a single record returned!

However, what it does return is "all rows of files matching the file_id 349658 and all columns of user2files, but empty, just as if I would have notated an outer join".

I tried other combinations. Some work, some are broken - apparently the order of mentioning the tables makes a difference (though it shouldn't). Here are all combinations I tried, the one mentioned above being top of the list:

// WRONG!! returns one record!

select * from files, user2files

where files.file_id = user2files.file_id

and user2files.file_id = 349658 and user2files.user_id = 410784

// works:

select * from user2files, files

where files.file_id = user2files.file_id

and user2files.file_id = 349658 and user2files.user_id = 410784

// works:

select * from user2files join files on user2files.file_id = files.file_id

where user2files.file_id = 349658 and user2files.user_id = 410784

// WRONG!! returns one record!

select * from files join user2files on user2files.file_id = files.file_id

where user2files.file_id = 349658 and user2files.user_id = 410784

// works:

select * from user2files join files on files.file_id = user2files.file_id

where user2files.file_id = 349658 and user2files.user_id = 410784

// WRONG!! returns one record!

select * from files join user2files on files.file_id = user2files.file_id

where user2files.file_id = 349658 and user2files.user_id = 410784

I suppose there has to be a problem with the query optimizer. Is this a known issue?

Version info:

MaxDB, Version 7.6.03.07, running on Windows Server 2003 family (WIN32)

Thank you,

Alex

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Jun 16, 2008 at 01:38 PM

    Hi Alexander,

    please make sure to use the most current available patch.

    For NON-SAP-customers this is currently MaxDB 7.6.03 Build 15.

    After using the current patch, please provide the execution plans both for the statements where the correct data is returned as well as for the failing ones.

    Also it would be a whole lot easier to reproduce the issue if we would know:

    - the DDL for the tables/indexes

    - the parametersetup of your DB

    - some testdata that allow to reproduce the issue.

    There are some known bugs related to Index-Only strategies and local predicates - we have to see what the DB is doing in your case to know which problem we have here.

    KR Lars

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.