Wrong join generated by optimizer

[former_member199543]

2 tables and one very simple query, which joins these 2 tables based on ID and date fields. ID in Dimension table is needed to be modified by to_char(). If I modify the join and add to_char(), then optimizer deletes this join and only rest of the joins are sent to the database, which generates nonsense, of course.

I've attached the screen

[former_member187605]

to_char cannot be pushed down to the database. The two other conditions can.

That's why things go wrong.

if you want to achieve full join pushdown, do the data type mapping for table T_TEXT in a query transform, use a data_transfer transform to store the results in a temp table and join that table to F_TEXT.

[former_member199543]

Hello, Dirk!

Nice to see your comment !!!

Ok, let's skip push-downs for a while, I do not dream about them at current stage 🙂 , Optimizer should NOT drop the join from the query, this is what happens here. The main join from Primary key to foreign key is lost if I modify one part of the join.

Data Services simply do NOT use the very first join! only the joins with < > operators and one, the main one = is gone . try, you will get the same SQL construction.

[former_member187605]

DS does apply the first join condition. It first pushes down the two other condition to the database, pulls all results in memory and then and there applies the first one, too.

But why you don't get any results? Because your join condition is wrong. The DS to_char() function applied to an integer returns a string with leading blanks (the number depends on the format used) that does not match your varchar TAB_ID column, eg. '1' <> ' 1'.

Whenever you've got to compare a numeric value in a string to a genuine integer column, map the string using to_decimal() with a zero precision.

[former_member199543]

Hello!

many thanks!

Got it, then i understand, if it first compares all distinct date values and then apply the first condition too, then we will have to change this in code level, otherwise we will work with 'almost Cartesian data'. Frustration was because of the code I saw in SQL optimizer.

I do not see any result, because comparison takes hours, even if there is a column storage, because on DB level it compares only date fields. The only reason why conversion is there is because of non consolidated data structure, where ID in one system is stored as unsigned integer and in another as varchar, thus the conversion.. We cannot create new DB objects, therefore conversion was the only option, workarounds exists also, but i was frustrated of the SQL i saw.

IMHO such approach is not correct, but let it be so.

Mikel

[former_member199543]

Hello

This can be replicated with SQL Server, Sybase, have not tried with other db vendors. You can try

cast(T_TEXT.TAB_ID,'varchar(4)') , this is BODS syntax

[former_member442248]

Hi Mikel.

What is your source database?

I tried building the above scenario but got the below error. Please can you share screenshots of the DS canvas showing optimized query.

<Calling <to_char> with <1> parameters, but <2> are expected. Fix the call by passing in the correct number of parameters.>. (BODI-1116147)

Regards. S