Question Re: Auto convert and join in Technology Q&A

Auto convert and join

Chris26 — Mon, 11 Jul 2016 03:36:01 GMT

If you have a table with a varchar column containing different kinds of values (int, date, string, float) all converted to a string and you then want to join the int value to a different table. I'm getting errors like "Cannot convert "some date value" to a numeric".

Setup the tables:

CREATE TABLE "DBA"."TestData" ( "FieldName" VARCHAR(10) NULL, "TestData" VARCHAR(10) NULL ) IN "system"; INSERT INTO "DBA"."TestData" ("FieldName","TestData") VALUES('Id','1'); INSERT INTO "DBA"."TestData" ("FieldName","TestData") VALUES('OtherId','1'); INSERT INTO "DBA"."TestData" ("FieldName","TestData") VALUES('Date','2015-01-01'); INSERT INTO "DBA"."TestData" ("FieldName","TestData") VALUES('Text','Bla'); INSERT INTO "DBA"."TestData" ("FieldName","TestData") VALUES('Float','1.15') CREATE TABLE "DBA"."JoinId" ( "Id" INTEGER NULL ) IN "system"; INSERT INTO "DBA"."JoinId" ("Id") VALUES(1); INSERT INTO "DBA"."JoinId" ("Id") VALUES(2); CREATE FUNCTION "DBA"."IsIntegerValue1"(string VarChar(10)) returns bit begin declare isInt bit; if string = '1' then set isInt = 1 else set isInt = 0 endif; return isInt; end;

The result I am looking for:

The queries:

SQL 1:

select * from TestData left outer join JoinId on JoinId.Id = if TestData.Fieldname = 'Id' then TestData.TestData else null endif

SQL 2:

select * from TestData left outer join JoinId on TestData.Fieldname = 'Id' and JoinId.Id = if IsNumeric(TestData.TestData) = 1 then TestData.TestData else null endif

SQL 3:

select * from TestData left outer join JoinId on TestData.Fieldname = 'Id' and JoinId.Id = if IsNumeric(TestData.TestData) = 1 then TestData.TestData else null endif

SQL 4:

select * from TestData left outer join JoinId on TestData.Fieldname = 'Id' and Id = if IsIntegerValue1(TestData) = 1 then TestData else null endif

Result 1, 2, 3 and 4:

Cursor not in a valid state SQLCODE=-853, ODBC 3 State="24000" Cannot convert '2015-01-01' to a numeric SQLCODE=-157, ODBC 3 State="07006"

SQL 5:

select * from TestData left outer join JoinId on TestData.Fieldname = 'Id' and JoinId.Id = if IsNumeric(TestData.TestData) = 1 and IsDate(TestData.TestData) = 0 then TestData.TestData else null endif

Result 5: This doesn't crash but it doesn't give the result I am looking for (as expected because IsDate('1') returns true)

My questions:

Why are SQL 1, 2, 3 and 4 crashing and not SQL 5?
How can I get the desired result?

Extra note: In the actual case I'm using this for I could really use the available PK/FK indexes for performance reasons and I would rather not create additional indexes like "cast(JoinId.Id as varchar)" as suggested in Volkers answer.

Tested with ASA 16.0.0.2283

Re: Auto convert and join

VolkerBarth — Mon, 11 Jul 2016 03:47:07 GMT

What version do you use?

With 12.0.1.4403, all 5 queries do succeed and do return the desired result (although queries 2 and 3 look similar to me...).

That being said, in general when the query engine seems to try to do undesired casts and fails, I'd usually try to adapt the "comparison goal" accordingly, such as

select *
   from TestData
      left outer join JoinId
         on TestData.Fieldname = 'Id'
         and cast(JoinId.Id as varchar) = TestData.TestData

That would even work if you would attempt to join over a different row that has no int value, such as TestData.Fieldname = 'Date'.

Re: Auto convert and join

Chris26 — Mon, 11 Jul 2016 04:15:34 GMT

Tested with ASA 16.0.0.2283

I should have added that in the actual case I'm using this for I could really use indexes for performance reasons and I would rather not create an index on "cast(JoinId.Id as varchar)".

Re: Auto convert and join

VolkerBarth — Mon, 11 Jul 2016 04:55:18 GMT

Ah, I see, with 16.0.0.2270 I do the errors for queries 1-4, too.

However, for me query 5 the result seems fitting.

Here's another attempt by building a derived query just with the "comparable rows" and then join that with JoinId. That may or may not help in your real case, that's difficult to tell without knowing that case...

select TestData.*, JoinId.*
   from TestData
      left join
         (select * from TestData where Fieldname = 'Id') TestDataWithId
          on TestData.FieldName = TestDataWithId.FieldName
      left outer join JoinId
         on TestDataWithId.TestData = JoinId.Id

Re: Auto convert and join

Chris26 — Mon, 11 Jul 2016 05:47:33 GMT

In the result for query 5 I get the "Id" column contains only "NULL" values. Where I need a value "1" in that column for the record with "FieldName" = "Id".

Your attempt looks promising. I would need to see if that leads to acceptable results (if it's not too slow)

However I hope that someone from SAP will react on this, and explain what is happening here and why this seems to work with version 12 and not with 16.

Re: Auto convert and join

VolkerBarth — Mon, 11 Jul 2016 07:59:12 GMT

FWIW, with 16.0.0.2270, query 5 returns the desired result set for me:

Re: Auto convert and join

Chris26 — Mon, 11 Jul 2016 06:35:30 GMT

Thats strange... In that first row I'm getting Id = NULL

If I try

select IsNumeric('1') as IsNumeric, IsDate('1') as IsDate

The result is

IsNumeric | IsDate ------------------ 1 | 1

Then I'm guessing IsDate('1') is returning 0 for you?

Re: Auto convert and join

VolkerBarth — Mon, 11 Jul 2016 06:52:52 GMT

Yes, I'm getting

1,0

here. Note, I'm running with German locale but with default date_format = 'YYYY-MM-DD' and default date_order = 'YMD'.

According, the following cast fails with SQLCODE -157:

select cast('1' as date)

So that difference seems to explain the different query results...

Re: Auto convert and join

Chris26 — Mon, 11 Jul 2016 07:42:01 GMT

I'm indeed running with different settings: date_format = 'DD-MM-YYYY' and date_order = 'DMY'. The result for cast('1' as date) then is '01-07-2016'.

If I change them back to the default, I get the same results as you.

Re: Auto convert and join

Chris26 — Mon, 11 Jul 2016 07:49:44 GMT

This also means that if i choose 10 as the value for TestData in the first row we would have seen the same behaviour (1-1-1000 vs 10-07-2016)

Re: Auto convert and join

Former Member — Mon, 11 Jul 2016 17:53:25 GMT

While a little late to the thread . . . A simpler rewrite might be this one:

select * from TestData left outer join JoinId on CAST(JoinId.Id as varchar(10)) = if TestData.Fieldname = 'Id' then TestData.TestData else null endif;

The CASTing (or alternatively a Convert()) of JoinId.Id to a character string avoids the implicit/automatic conversion that is exposing this issue.

I'm assuming 12.0.x did something similar when evaluating the original predicate.

Both types of automatic conversion are feasible here, ie:

- automatically converting a numeric term to character in a character expression - automatically converting a character term to a numeric in a numeric expression

and since this predicate 'could operate' either way (as a numeric comparison or a character comparison) there seems to be no preferred bias to either.

I'm not quite certain if this is exactly a bug or not. Treating all such comparisons as numeric comparisons whenever possible/feasible can be significantly more efficient than assuming every such expression to be a character string comparison.

Re: Auto convert and join

VolkerBarth — Tue, 12 Jul 2016 02:36:41 GMT

Yes, the explicit cast in the JOIN condition is probably the simplest rewrite, see the suggestion in my answer...

However, as Christian has stated, the cast may prevent the usage of an index on the according column JoinId.Id here, right?