Filters using IN operator

Just upgraded to TDP 5.0.7.106 and there seems to be an issue with how it handles IN filters.

First observation:
Previously I would select IN and key multiple values with a comma separating them.
FirstWhere

When I click OK TDP stops responding for about 18 seconds, as if it is having trouble building the SQL statement, then when it finishes I can see that it did not wrap each value of the IN list in single-quotes, it wrapped the whole list in one set of single-quotes.
FirstWhere1

Changing the comma separator to a semi-colon...and waiting another 18 seconds...causes TDP to format the values correctly.
SecondWhere

Second observation:
Importing a list of values for an IN filter from a text file has the same behavior...it wraps the entire list in one set of single-quotes.

1 Like

Wow, I am going to be following this one. I use IN filters quite a bit and the IN from a text file so this will definitely slow my down from upgrading from 5.0.6.97

Hi,

unfortunately it looks like this is a bug in our new implementation logic for IN filter. We had to change separator to semi-colon as there are users which using comma as a part of value. We will try to change this logic little bit more.

What is more important part for me is that 18 seconds freeze. Can you confirm to me this is also new in this version? Can you tell me few info about your database and SQL? What provider are you using? How big is your table? And how "big" is your SQL statement?

I will try to simulate it and we will create some task for fix.
Thanks

Filip

Yes, the long wait time after entering the filter criteria is a new occurrence. This is occurring on a simple, one-table query in an Oracle system. The query is returning 2 rows of 3 columns (no calculations) from our INVENTORY table. This table has 224 columns and 5.5MM rows. Resulting statement:

SELECT INVENTORY.PART_NUMBER,
INVENTORY.DATE_CREATION,
INVENTORY.FLAG_HAS_ATTRIBUTE_IMAGE
FROM DW_ORDER.INVENTORY INVENTORY
WHERE (INVENTORY.PART_NUMBER IN ('WMR-435', 'WMR-625'))

Hi, we finally found the issue of the performance problem and we will fix it in one of our next version (unfortunately not in version TDP 5.1 but in next one).
We created task for this item: TMB-1682

Filip,

Our team just discovered another detail related to this bug. When in a Query Builder and the SQL script is manually changed, the IN filters are being changed incorrectly by the software when the query is visualized...even if the IN filter was not a part of the modification.

Example:
Original script from the Query Builder.
The NOT IN filter on line 11 is correct.
I decide to change the year filter on line 12 to equal 2020.
I click the Visualize button.
The NOT IN filter on line 11 changes to an incorrect format.
Capture1

Capture3

1 Like

Can you verify it with TDP 5.1.2 ? We already fixed issue TMB-1682 and several other small items related to QB.
I am not able to reproduce the issue with your steps with our latest version.

I just upgraded to 5.1.2 and have some strange results...

  1. The first test failed.

  2. I closed that query and tried a different query, it succeeded.

  3. I closed the second query and recreated the first query...it succeeded.

Conclusion: I guess that this is fixed. No more 18-second freeze :slight_smile:

1 Like