Weird Issue with ColumnSet Comparison Analysis

Highlighted
One Star

Weird Issue with ColumnSet Comparison Analysis

Hi,

First, sorry about the length of this post. But there is no simpler way of clearly explaining the issue!

I have been facing a really weird issue with ColumnSet Comparison Analysis. First, let me clarify that my understanding is correct. The "where" condition for ColumnSet A is entered in the text box below "Left Columns" and "where" condition for ColumnSet B below "Right Columns" right? (as seen in attachment PK-FK_Analysis.JPG)
I am assuming that the answer to the above question is "Yes"!

In my analysis, I need to add a "where" condition only to the Table A (as seen in the above screen shot). I do that and leave the "where" condition for ColumnSet B blank.

Now I run the analysis, everything works normally.
When I re-run the analysis, I get the error seen in the attachment - PK-FK_Error.JPG

The error displays the SQL that TDQ tries to run. If we see this SQL, the "where" condition is swapped between ColumnSet A and ColumnSet B. i.e. now ColumnSet B has the "where" condition which was originally set under "Left Columns" in our analysis.

So I manually put the "where" condition under the "Right Columns" and run the analysis, now the analysis runs fine. If I try to re-run the analysis, I again get the same error.

Basically I am unable to run the analysis back-to-back with any of the settings.

Also, another issue what I faced is, the report which got generated is replicated twice. i.e. if I originally have 3 pages in my report, the same 3 pages get displayed as page 4, 5 and 6. However, I don't know whether this issue is related to the above.

Thanks!
Mythili
Employee

Re: Weird Issue with ColumnSet Comparison Analysis

that's unusual indeed.
Can you find in the Error log view the query generated the first time you run the analysis (when you set the where clauses correctly as shown in your screenshot)?
Then we will be able to compare it with the generated query in the error window.

The duplication of pages in the report may or may not be related to this error. I don't know yet.
One Star

Re: Weird Issue with ColumnSet Comparison Analysis

Sebastiao,

When I run the analysis the first time (with where clauses set as seen in my screen shot), no error log pops up. Ideally, I am assuming it should be running the query with the "where" condition against the correct table.

Also, when I run the same analysis on Unix, the report gets generated (with the duplication I mentioned earlier). Note that when run from the GUI, no report gets generated.
But the run shows the above error also.
PFA log of the analysis run from Unix. Hope this helps!

Thanks!
Mythili.S
One Star

Re: Weird Issue with ColumnSet Comparison Analysis

Sebastiao,

I am unable to view the the log file (notepad) attachment in my post above. Please let me know whether you are also unable to view it. I shall paste the log information directly in the post.

Thanks!
Mythili
Employee

Re: Weird Issue with ColumnSet Comparison Analysis

Mythili,

there is no attachment in your post. I think that the forum only allows some file types and has restrictions on the size too.
you may access to the error log and find the generated query by following this document: http://www.talendforge.org/wiki/doku.php?id=bugtracker#attach_the_error_log_to_the_bugtracker
One Star

Re: Weird Issue with ColumnSet Comparison Analysis

Sebastiao,

Find below log details for successful run and failed run.

Successful Run
--------------
eclipse.buildId=unknown
java.version=1.6.0_11
java.vendor=Sun Microsystems Inc.
BootLoader constants: OS=win32, ARCH=x86, WS=win32, NL=en_US
Command-line arguments: -os win32 -ws win32 -arch x86


Info
Fri Apr 29 05:12:00 VET 2011
2011-04-29 05:12:00,792 INFO org.talend.dq.analysis.ColumnAnalysisSqlExecutor -
Executing query:
SELECT COUNT(*) FROM "DQSMART"."TRD_VOL_DLY_SMRY_VW" A
LEFT JOIN (SELECT * FROM "DQSMART"."MAN_REF_LGL_ENTY_XREF_VW"
WHERE ( cob_dt between '25-APR-11' and '26-APR-11' )) B
ON ( (A."LGL_ENTY_KEY" = B."LGL_ENTY_KEY") ) WHERE (B."LGL_ENTY_KEY" IS NULL )


-----------------------------------------------------------------------------------------------------------------------------


Failed Run
--------------
eclipse.buildId=unknown
java.version=1.6.0_11
java.vendor=Sun Microsystems Inc.
BootLoader constants: OS=win32, ARCH=x86, WS=win32, NL=en_US
Command-line arguments: -os win32 -ws win32 -arch x86


Error
Fri Apr 29 05:12:01 VET 2011
2011-04-29 05:12:01,683 ERROR org.talend.dq.analysis.RowMatchingAnalysisExecutor
- java.sql.SQLException: ORA-00904: "COB_DT": invalid identifier

java.sql.SQLException: ORA-00904: "COB_DT": invalid identifier

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:112)
at oracle.jdbc.driver.T4CStatement.execute_for_describe(T4CStatement.java:351)
at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:896)
at oracle.jdbc.driver.T4CStatement.execute_maybe_describe(T4CStatement.java:383)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:986)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1516)
at org.talend.dq.analysis.ColumnAnalysisSqlExecutor.executeQuery(Unknown Source)
at org.talend.dq.analysis.RowMatchingAnalysisExecutor.executeQuery(Unknown Source)
at org.talend.dq.analysis.RowMatchingAnalysisExecutor.runAnalysis(Unknown Source)
at org.talend.dq.analysis.AnalysisExecutor.execute(Unknown Source)
at org.talend.dq.analysis.AnalysisExecutorSelector.executeAnalysis(Unknown Source)
at org.talend.dataprofiler.core.ui.action.actions.AnalysisExecutorThread.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

Thanks!
Mythili
Employee

Re: Weird Issue with ColumnSet Comparison Analysis

Mythili,

does COB_DT belong to "DQSMART"."TRD_VOL_DLY_SMRY_VW"?
If yes, then you must write A.COB_DT instead of COB_DT.

If this column belongs to the other table, then write B.COB_DT.
One Star

Re: Weird Issue with ColumnSet Comparison Analysis

Sebastiao,

Yes, COB_DT belongs to "DQSMART"."TRD_VOL_DLY_SMRY_VW".
I tried using the alias name as A.COB_DT (as stated above), but I still get the same error.
Did modifying the query as above fix the issue when your team tried it?

There is probably some issue with the way the query gets generated itself. Because the whole "where" clause gets shifted into the later half of the query.

Thanks!
Mythili.S
Employee

Re: Weird Issue with ColumnSet Comparison Analysis

Mythili,

we could not reproduce this issue yet. It seems to work on our side.
Can you declare a bug if you still have this issue, please?
And give us as much as possible information about your environment.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Enabling Data Governance

Learn how to enable Data Governance

Watch Now

The Definitive Guide to Government Data Quality

Take a peek at the definitive guide to Government Data Quality

Read