Associations multiple join conditions bug

[naxels]

Description of the Issue

When using Jailer 12.2 Data Browser and setting up an Association with multiple Join conditions, in the Desktop tab
the multiple conditions are ignored, while the SQL Console applies the multiple conditions.

Steps to Reproduce the Issue

Using Oracle:
Setup table: MTL_SYSTEM_ITEMS_B
Setup table: MTL_ITEM_CATEGORIES

Create Association:
From A: MTL_SYSTEM_ITEMS_B
Type: associates
To B: MTL_ITEM_CATEGORIES
Join condition: A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID AND A.ORGANIZATION_ID = B.ORGANIZATION_ID
Cardinality: 1:n
Name: msi-mic

Expected Behavior

In the Desktop tab, I expected the same to happen as on the SQL Console: show only 13 records.

Actual Behavior

The Desktop tab shows 500+ records because the AND A.ORGANIZATION_ID = B.ORGANIZATION_ID is ignored
it however does show the full condition when hovering over the 'on'

Debug Information

macOS: 12.2
Jailer 12.2

Thank you for creating and maintaining this wonderful tool!

[Wisser]

Thanks for the report!

Unfortunately, I cannot reproduce the problem. Would it be possible for you to restore the constellation seen in the screenshot and press the "Reload Rows" button in the "MTL_ITEM_CATEGORIES" window? (The green arrow to the right of the "Where" input field).

This logs the reload SQL statements into the "sql.log" file in the jailer directory. It would be very helpful for me to be able to view this log.

[naxels]

Attached the log
i've done a restart of the program and cleaned the log first.

sql for Github.log

It's interesting to see that there are also errors:
java.sql.SQLException: ORA-31603: object "MTL_ITEM_CATEGORIES" of type TABLE not found in schema "Naxels"
As all of the tables in my schema are synonyms (I have a read only user account with only access to tables listed in my schema)

Thanks for looking into this!

[Wisser]

Thanks for the log file!

A first impression:
This is the last statement in log (formatted):

Select
     S.A0,
     S.A1,
     ...
     S.A10,
     S.A11,
     S.A12,
     S.A13,
     S.A14
From
     (
          Select
               A.INVENTORY_ITEM_ID as A0,
               A.ORGANIZATION_ID as A1,
               A.CATEGORY_SET_ID as A2,
               ...
               A.ROWID as A14,
               row_number() over(
                    order by
                         -1
               ) as RN
          From
               MTL_SYSTEM_ITEMS_B B
               join MTL_ITEM_CATEGORIES A on B.INVENTORY_ITEM_ID = A.INVENTORY_ITEM_ID AND B.ORGANIZATION_ID = A.ORGANIZATION_ID
          Where
               (B.INVENTORY_ITEM_ID = 4240080)
     ) S
Where
     S.RN <= 5000

which results in 585 rows.
Is the displayed result still wrong?
The join condition is completely taken into account.

[Wisser]

The ORA-31603 messages are harmless. An attempt is made to generate the DDL for the tables to be displayed in the metadata view. This obviously does not work for synonyms.

[naxels]

interesting..

I think it should have also taken ORGANIZATION_ID into account since that is setup in the association
and there is an active filter on the ORGANIZATION_ID (8734) in the MTL_SYSTEM_ITEMS_B table.

The join statement:
join MTL_ITEM_CATEGORIES A on B.INVENTORY_ITEM_ID = A.INVENTORY_ITEM_ID AND B.ORGANIZATION_ID = A.ORGANIZATION_ID
is correct though, it's just that I expected the Where to come along for the ride

[naxels]

However I get the idea that what is going on is that the association sets up the JOIN, but doesn't carry over the WHERE?

I had hoped/expected that clicking on Related Rows, would carry over all the Association keys/filters set (as seen in the screenshot)

[Wisser]

Yes, the association-condition is part of the JOIN.
Does the 585 rows result still contain ORGANIZATION_IDs which differ from that of the MTL_SYSTEM_ITEMS_B with INVENTORY_ITEM_ID = 4240080?

[naxels]

Yes, it still contains different ORGANIZATION_IDs.

The actual count (as the SQL screenshot shows) should be 13 rows when applying both the INVENTORY_ITEM_ID and ORGANIZATION_ID filters.

Funny that the SQL Console got it right straight away (right click on MTL_ITEM_CATEGORIES and selecting SQL Console) while the Desktop tab doesn't

[Wisser]

I think I now know what is going wrong here.
Since MTL_SYSTEM_ITEMS_B is a synonym, the primary key cannot be recognized automatically and you have set it as "INVENTORY_ITEM_ID" manually.
But this key is probably not unique!
You can check this by using the menu item "Check Primary Key" under "Model".

As you can see in the generated statement, only this key is used to establish the connection with the MTL_ITEM_CATEGORIES table. Not however any filters. This makes no difference as long as the key is unique.

So be careful when defining keys manually. Keys must be unique, otherwise there will be problems in other places (e.g. subsetting). That's why there is this check tool.

Since you are working with Oracle, you do not necessarily need a key for each table for the browser, since ROWID can be used here.

[naxels]

While working on analysing the tables, indeed no primary keys were found in most tables and i've set them manually, INVENTORY_ITEM_ID is unique in this table, but not set as primary key in the DB (set as constraint).

So you are saying it is not possible to have multiple associations work except if the primary key(s) are those associations?

[Wisser]

I meant that if you manually set a column or columns as "Primary Key" in the data model editor, then this key must be unique.

What does this query return:
Select INVENTORY_ITEM_ID from MTL_SYSTEM_ITEMS_B Group by INVENTORY_ITEM_ID having count(*) > 1
and
Select * from MTL_SYSTEM_ITEMS_B Where INVENTORY_ITEM_ID is null
?

[naxels]

Aaah, I'm sorry, I misunderstood as I was still thinking about the associations and the joins with the filters.

I've now set the primary key in Jailer on the MTL_SYSTEM_ITEMS_B to INVENTORY_ITEM_ID and ORGANIZATION_ID fields and now the Related Rows feature works for MTL_ITEM_CATEGORIES (correctly shows 13 records).

and you are correct that INVENTORY_ITEM_ID is not unique on that table.

Didn't know I could set multiple Primary Keys on a table! So doing that now, it works :)

Thanks for your help!