How to correctly join INFORMATION_SCHEMA.ALIASES with TYPE_INFO to detect the alias return type?

[Lukas Eder]

Hello,

Check out this simple alias:

-----------------------------------------
CREATE ALIAS show AS $$
String showme(String value)
{
  return value;
}
$$;
-----------------------------------------

When created, it can be found in the INFORMATION_SCHEMA.ALIASES table:

-----------------------------------------
select * from information_schema.function_aliases where alias_name = 'SHOW';
-----------------------------------------

Its result data type is 12, which stands for JDBC's VARCHAR type code. When I select that data type in type_info, however, I get two results:

-----------------------------------------
select type_name from information_schema.type_info where data_type = 12;

-- returns
VARCHAR
VARCHAR_IGNORECASE
-----------------------------------------

I guess, the ALIASES view should also contain a type_name field, to unambiguously reference a type_info entry...? Or how can it be done? The following query shows that three data_types are actually ambiguously defined, with H2-specific data type "overloads":

-----------------------------------------
select i1.type_name, i1.data_type , i2.c as "duplicates"
from information_schema.type_info i1
join (
 select data_type , count(*)  c
 from information_schema.type_info
 group by data_type
 having count(*) > 1
) i2 on i1.data_type = i2.data_type;

-- returns
TYPE_NAME          DATA_TYPE   duplicates  
VARCHAR            12          2
VARCHAR_IGNORECASE 12          2
BIGINT             -5          2
IDENTITY           -5          2
BINARY             -2          2
UUID               -2          2
-----------------------------------------

Cheers
Lukas

[Lukas Eder]

Note, I've found the semantics of TYPE_INFO.POS to be useful in my use-case. It seems that I can probably ignore types with POS != 0. However, that doesn't seem to be the correct way to handle this ambiguity (especially, since I didn't find any documentation about POS)...

[Lukas Eder]

Hello guys. Any ideas on this topic?

Thanks
Lukas

[Thomas Mueller]

Hi,

Note, I've found the semantics of TYPE_INFO.POS to be useful in my use-case. It seems that I can probably ignore types with POS != 0. However, that doesn't seem to be the correct way to handle this ambiguity (especially, since I didn't find any documentation about POS)...

DatabaseMetaData.getTypeInfo is supposed to return the best match first. The table TYPE_INFO is used internally in this method, and used 'order by data_type, pos'. So POS = 0 is the best match, but really this is database specific.

I will add a new column TYPE_NAME to INFORMATION_SCHEMA.FUNCTION_ALIASES.

Regards,

Thomas

[Lukas Eder]

Hi Thomas,

OK, thank you very much!

Cheers

Lukas