Automatic type conversion to string

[philippefbertrand]

In the example below, column ver_ts is passed to a builtin function that expects a string argument. This results in a casting error

Could not execute statement. Cannot convert timestamp to a long binary SQLCODE=-157, ODBC 3 State="07006"

I would have expected that since all date constants are specified as strings that there would have been automatic conversion of date/time types to string.

From SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - SQL Reference » SQL data types :

All date constants are specified as strings. The string is automatically converted to a date before use.

1. Why does SA try to convert the timestamp value into a long binary instead of string?
2. Can one extract the raw binary form of a timestamp (8 bytes) / DATE / TIME?

Example:

CREATE TABLE T3(
 pk1    INTEGER   NOT NULL,
 c1     INTEGER   NOT NULL,
 ver_ts TIMESTAMP NOT NULL DEFAULT TIMESTAMP,
 PRIMARY KEY(pk1));

INSERT INTO T3(pk1,c1) VALUES (1, 1);

INSERT INTO T3(pk1,c1) VALUES (2, 2);

INSERT INTO T3(pk1,c1) VALUES (3, 3);

SELECT TOP 31  HASH( t1."ver_ts", 'SHA256' ), t1."pk1", t1."c1", t1."ver_ts" 
  FROM "dba"."T3" t1
 ORDER BY t1."pk1";

[Breck_Carter]

Caveat: I'm not a REAL lawyer, but I did stay at a Holiday Inn Express last night...

First of all, you are passing a column reference to HASH, not a string literal, so all discussions of string literals are moot.

The help does say that HASH expects a "string-expression" as the first argument, but it goes on to say that it is case sensitive which might lead one to believe that the actual string datatype being used is not VARCHAR but BINARY.

What, BINARY is not a string? Don't let those BINARYs hear you say that! "Varchar bigot!" 🙂

Apparently, you cannot convert a TIMESTAMP to BINARY, not directly using CAST, or indirectly.

But, you can convert TIMESTAMP to VARCHAR, and VARCHAR to BINARY, so the solution to your problem require a single extra CAST...

SELECT TOP 31  HASH( CAST ( t1."ver_ts" AS VARCHAR ), 'SHA256' ), t1."pk1", t1."c1", t1."ver_ts" 
  FROM "dba"."T3" t1
 ORDER BY t1."pk1";

HASH(cast(t1.ver_ts as varchar),'SHA256')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                pk1          c1 ver_ts                  
-----------------------------------------------------------------
1113c8fe0133a10ef41fa4c7c3d6128fe8d34f4613d0bbb2c601583a7a448a8b                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           1            1  2013-05-23 15:09:57.788 
65af62522183bb2f0065b3b8fd7b2f781080a8cc71591213f5d190d8d689bc5e                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           2            2  2013-05-23 15:09:57.793 
ec500e888819c569afad991831730d4dec195d8bbf7990036ac9871af3efb605                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           3            3  2013-05-23 15:09:57.801 
(3 rows)
Execution time: 0.302 seconds

[philippefbertrand]

It is the help's assertion that HASH's first argument is a string that made me post the question. Yes, there are lot's of ways to cast TIMESTAMP/DATE/TIME/etc to a string first. In my particular usage I prefer DATEFORMAT() because I can isolate myself from strange settings of *_format option.

[Breck_Carter]

Well, it IS a string :)

[MarkCulp]

1) The builtin HASH function's first parameter is a long binary - not a long varchar - and therefore the query execution engine attempts to find a rule that will convert the timestamp into a long binary. There isn't one and hence the error that is generated. Note that there is a rule that will convert a char type (char, varchar, long varchar) to binary so you can cast the timestamp explicitly to a char type and the select statement will succeed. Example:

SELECT TOP 31  HASH( cast( t1."ver_ts" as varchar(250) ), 'SHA256' ), t1."pk1", t1."c1", t1."ver_ts" 
  FROM "dba"."T3" t1
 ORDER BY t1."pk1";

2) Within the server I do not believe that you can get the binary representation of the timestamp. Note however that within an ESQL or ODBC (may other APIs) client you can fetch the timestamp as binary and you get a 14 byte TIMESTAMP_STRUCT structure.

[Breck_Carter]

Like so many many many many many builtin functions, the Help does not explicitly define the data type of the arguments, and there is NO WAY ON EARTH anyone below Beta class can determine what those data types are... except by random experimentation. I have ranted about this before, and I will rant again, whenever the opportunity arises.

HASH is a particularly egregious example. It does give EXACT data types for the return values, but they are wrong... or at least one of them is wrong: The help says SELECT EXPRTYPE ( 'SELECT HASH ( ''ASDF'', ''SHA256'' )', 1 ); will say VARCHAR(64) but in fact it returns varchar(32767).

As for the first argument to HASH, it simply says "string-expression" which is less than helpful.

Torches! Pitchforks! Call out the villagers! 🙂

[MarkCulp]

I understand your grief... as I feel it too. 😞

I've discussed this with the doc team in the past, and I will do so again.

[Breck_Carter]

I think it will need more than the Doc team's willingness... THEY have no way to discover the data types of builtin functions, do they?

Personally, I believe that ALL builtin functions should be exposed in SYSPROCEDURE.

[Breck_Carter]

...but that's just a pipedream because the data types for some can vary at runtime.

[MarkCulp]

Ok, I discussed this again with the doc team...

A number of years ago the doc team did work with a couple of developers to improve the information provided in the docs about what types are expected as input parameters and what type(s) can be expected as output. This took several man-months of effort. The result was better documentation than what it was before but the rules for inputs and outputs is... well... let's just say 'complicated'.

I have added a task to my list to see if there is anything we can do about this issue.

[Breck_Carter]

Let me guess... some (many?) of these functions do not exist as separate objects with fixed interfaces, but are compiled as inline code... or something like that.