cancel
Showing results for 
Search instead for 
Did you mean: 

HANA Calculation View: Right join returns wrong result?

roland_bouman
Contributor
0 Kudos

I created a calculation view CA_LEFT_JOIN of (category DIMENSION) like so:

The datasource is the DUMMY table and selects 1 row with 1 column, also named DUMMY, which contains the letter 'X'.

Two projections (left and right) are made on the datasource.

The "left" projection adds a calculated column "CC_LEFT", which simply selects the DUMMY column from the projection "base"; the "right" projection creates a calculated column "CC_RIGHT" which is the constant string literal 'Y'.

The join is "left outer" for left.CC_LEFT = rightCC_RIGHT.

If I look in the data preview then I get 1 row, with a 'X' for CC_LEFT, and null for CC_RIGHT. This is exactly what I expect.

Now, I have another view, CA_RIGHT_JOIN, which is similar to CA_LEFT_JOIN, but the join is now a right join; also, CC_RIGHT now selects the DUMMY column, and CC_LEFT now selects the string literal 'Y'

If I query this view, then I get also 1 row, both now both CC_LEFT and CC_RIGHT return the value 'X'. I would have expected. I expected CC_RIGHT to be 'X' and CC_LEFT to be NULL.

Can somebody explain why the CA_RIGHT_JOIN view behaves this way?

lbreddemann
Active Contributor
0 Kudos

Can you run an EXPLAIN PLAN on both queries and post it here?

roland_bouman
Contributor
0 Kudos

Hi Lars!

Thanks for chiming in. The explain plan for the left join view is:

OPERATOR_NAME

OPERATOR_DETAILSOPERATOR_PROPERTIESEXECUTION_ENGINEDATABASE_NAMESCHEMA_NAMETABLE_NAMETABLE_TYPETABLE_SIZEOUTPUT_SIZESUBTREE_COSTOPERATOR_IDPARENT_OPERATOR_IDLEVELPOSITIONHOSTPORTTIMESTAMPCONNECTION_ID ROW SEARCHTO_VARCHAR(DUMMY.DUMMY), NULL, 'Left', NULL?ROW?????10.00000332508681?11jbi-bid0031,003Sep 17, 2018 8:53:53.608 AM463,425 TABLE SCAN??ROW?SYSDUMMYROW TABLE110.0000000412121jbi-bid0031,003Sep 17, 2018 8:53:53.608 AM463,425

And for the right one, it's this:

OPERATOR_NAME OPERATOR_DETAILS OPERATOR_PROPERTIES EXECUTION_ENGINE DATABASE_NAME SCHEMA_NAME TABLE_NAME TABLE_TYPE TABLE_SIZE OUTPUT_SIZE SUBTREE_COST OPERATOR_ID PARENT_OPERATOR_ID LEVEL POSITION HOST PORT TIMESTAMP CONNECTION_ID ROW SEARCH TO_VARCHAR(DUMMY.DUMMY), TO_VARCHAR(DUMMY.DUMMY), NULL, 'Right' ? ROW ? ? ? ? ? 1 0.0000033250868 1 ? 1 1 jbi-bid00 31,003 Sep 17, 2018 8:55:59.035 AM TABLE SCAN ? ? ROW ? SYS DUMMY ROW TABLE 1 1 0.000000041 2 1 2 1 jbi-bid00 31,003 Sep 17, 2018 8:55:59.035 AM

They look pretty much the same to me, except for the "OPERATOR_DETAILS" of the ROW_SEARCH.

I hope this helps!

Roland,

roland_bouman
Contributor
0 Kudos

Hi Lars!

Thanks for chiming in. The explain plan for the left join view is:

For the right one, it's:

The only difference I can see is in the "OPERATOR_DETAILS" of the "ROW_SEARCH", but I don't really know how to interpret this.

I hope this helps!


Thanks in advance,

Roland.

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

What I wanted to see are the actual projection lists:

left outer
TO_VARCHAR(DUMMY.DUMMY), NULL                    , 'Left', NULL

right outer 
TO_VARCHAR(DUMMY.DUMMY), TO_VARCHAR(DUMMY.DUMMY), NULL, 'Right'

What I saw in my tests was that for the RIGHT OUTER JOIN, the mapping for both output columns (CC_RIGHT and CC_LEFT) was identical (TO_VARCHAR(DUMMY.DUMMY) ).

That's not he expected outcome and in my understanding not the correct one either (i.e. I believe this to be wrong).

Running the equivalent SQL query does not lead to this behavior. I've checked this with and without "Execute in SQL Engine" without any difference in the outcome.

In this case, I'd recommend opening a support incident for this.

roland_bouman
Contributor
0 Kudos

Lars,

Thanks! I'll see if I can find someone that has access to report support incidents. (I don't have it myself)