Internal error during join: invalid input (revision 102 - partitioned tables)

[Former Member]

Hi folks,

We're facing the following error after upgrading our Hana instance to revision 102:

"Column store error: search table error: [23057] Internal error during join : invalid input"

while trying to execute a hdbprocedure (called by an XSJS service) that makes a query similar to this:

SELECT COL_1,

              COL_2,

             ...

  FROM TABLE_1 T1

INNER JOIN TABLE_2 T2 ON T1.COL_A  = T2.COL_X

                                     AND T1.COL_B  = T2.COL_Y

                                     ...

                                     AND T1.DATE_1 = IFNULL(T2.DATE_2, T2.DATE_3)

WHERE T2.DATE_3 = P_DATE;

These tables are partitioned by Hash on level 1 and Range by MONTH(DATE_3) on level 2 for Data Pruning.

If we undo the partitioning, the execution completes successfully. Also, if we execute this query manually (outside the proc), it brings data successfully.

We already checked the data and none of these date columns have invalid data. The weird part is that it was working pretty fine when we were using rev 85.

Any ideas?

Thanks,

[glen_spalding]

hello, did you ever get this fixed ?

[Former Member]

We're trying to create a text search page using the SinA library and get the same error.

We modified the demo text search page which came with the SinA library. This page freestyle searches through the columns of an attribute view.This view joined several tables, using inner and left joins. The SinA page uses a XSJS service to fire a query on the attribute view.

On our lower HANA environments attribute view's tables are all within the same partition and the SinA search page works fine. However, from the acceptance environment onward, the tables are distributed among multiple partitions and we get the same error as Andre:
Server responded with an error message: code 200 search table error: [23057] Internal error during join: invalid input

Has anybody, in the mean while, been able to solve this issue?

[Former Member]

I have opened a ticket with SAP and we're still working on it. Anyway, have you tried changing/including the WHERE clause as I described above on your query? This is far from being a solution but fixed temporarily some of our scenarios here.

[Former Member]

Hi Andre,

Did you get any feedback from SAP? We are seeing the same problem on Rev 102.00.

Regards,

Paul.

[Bojan-lv-85]

on SPS10 I would check this note which is brand new:

2269861 - Possible Wrong Results during JOIN Queries

to verify that you are facing exactly this issue you need to browse through the indexserver tracefile search for "invalid input" and check whether as a consequence "inconsistent reduction" is logged.

If not, please log an incident with SAP and make sure the following information is provided:

- hana revision

- exact query which is failing

- error reported on application side

- indexserver trace (ideally HANA full system info dump available)

BR, Bojan

[Former Member]

We had a simpler situation where we fixed this same error. It was a query similar to this:

SELECT ...

  FROM TABLE_1 T1

INNER JOIN TABLE_2 T2 ON T1.COL_A  = T2.COL_X

                                          ...

                                    AND T1.DT_COL = T2.DT_COL

INNER JOIN TABLE_3 T3 ON T1.COL_A  = T3.COL_Y

                                          ...

                                    AND T1.DT_COL = T3.DT_COL

INNER JOIN TABLE_4 T4 ON T1.COL_A  = T4.COL_Z

                                          ...

                                    AND T1.DT_COL = T4.DT_COL

WHERE T2.DT_COL = P_DT_COL;

and, as we're facing this errors only with partitioned tables (whose partitions are made by RANGE upon DT_COL values), I forced to filter by these columns on all the tables inner joined:

SELECT ...

  FROM TABLE_1 T1

INNER JOIN TABLE_2 T2 ON T1.COL_A  = T2.COL_X

                                          ...

                                    AND T1.DT_COL = T2.DT_COL

INNER JOIN TABLE_3 T3 ON T1.COL_A  = T3.COL_Y

                                          ...

                                    AND T1.DT_COL = T3.DT_COL

INNER JOIN TABLE_4 T4 ON T1.COL_A  = T4.COL_Z

                                          ...

                                    AND T1.DT_COL = T4.DT_COL

WHERE T1.DT_COL = :P_DT_COL

      AND T2.DT_COL = :P_DT_COL

      AND T3.DT_COL = :P_DT_COL

      AND T4.DT_COL = :P_DT_COL;

In theory, this is unnecessary but fixed this error on SQL parser used by procedures. Unfortunately, we have other cases where this same handling didn't fix our problem.

[Former Member]

Hi Andre,

Did you try appending a semicolon to input parameter. I mean P_DATE should be :P_DATE.

Regards,

Anil

[Former Member]

Thanks Anil for your thought. I tried with colon in the variable and it didn't work either.

[Bojan-lv-85]

Hi Andre,

I would suggest to report this in a support incident, as rev 102 is in play, we need to take a closer look

BR, Bojan

[Former Member]

Bojan Dujic wrote:

Hi Andre,

I would suggest to report this in a support incident, as rev 102 is in play, we need to take a closer look

BR, Bojan

Thanks Bojan. I will see how to proceed with this report to the support.