Auto convert and join

[Chris26]

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:

FieldName | TestData   | Id
-----------------------------
Id        | 1          | 1
OtherId   | 1          | NULL
Date      | 2015-01-01 | NULL
Text      | Bla        | NULL
Float     | 1.15       | NULL

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

[Former Member]

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.

[VolkerBarth]

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?

[VolkerBarth]

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'.

[Chris26]

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)".

[VolkerBarth]

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

[Chris26]

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.

[VolkerBarth]

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

[Chris26]

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?

[VolkerBarth]

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...

[Chris26]

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.

[Chris26]

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)