Type casing for substituted double parameter

[Dmitry Katsubo]

Dear HSQL community,

I have noticed that in the following scenario the type discovery works in a non-consistent way from my point. I might be wrong of course.

Test script:

drop table if exists data_table;
create table data_table
(
    row_number              integer not null
);
insert into data_table values (0);
insert into data_table values (1);
insert into data_table values (4);
insert into data_table values (5);

Query 1 works as expected:

select distinct(round(row_number * 1.6)) as row_num from data_table order by row_num
[0, 2, 6, 8]

Query 2 treats the round() argument as integer. The parameter is bound to value Double.valueOf(1.6) so HSQL expression parser hopefully knows that round() argument is double:

select distinct(round(row_number * ?)) as row_num from data_table order by row_num
[0, 1, 4, 5]

Query 3 again works as expected:

select distinct(round(row_number * cast(? as double))) as row_num from data_table order by row_num
[0, 2, 6, 8]

I have tested query 2 on MySQL: it works as expected (results are equal to query 1).

[Fred Toussi]

The Query 2 statement is not correct. The type of the parameter is determined at compile time and is integer. MySQL has its peculiar ways, which are not often Standard conformant.

Use round (row_number  * cast(? as double)) as in Query 3 or alternatively, cast row_number to double.

[Dmitry Katsubo]

I have checked on MySQL that query (3) does not work, so I really can't find a "cross-DB" query. Any help is welcomed.

[Fred Toussi]

Try this or any representation of 1 as DOUBLE that MySQL might accept.

select distinct(round(row_number * 1E1 * ?) as row_num from data_table order by row_num

[Dmitry Katsubo]

Thanks for our reply. The hint you've suggested worked just fine.

You've mentioned that Query 2 is not correct. I would like to issue the bug for MySQL then. Do you have any reference or any specification, which mentions that?

You also said that the type of the parameter is determined at compile time. Do you mean that it is determined when the statement is prepared? More exactly, when statement "round(?)" is prepared, the argument is restricted to integer?

What about date functions, is the type restricted to date / time or timestamp? For example in statement "dateadd(?, 5, ?)" it is not possible to restrict the type of 2nd parameter. Correct me if I am wrong.

[Fred Toussi]

The parameter type is decided in the ROW_NUMBER * ? expression, which contains an INT. Compile time is the same as prepare time.

The argument for ROUND can be any number type, or some datetime types.

The possible types of the arguments to all functions are listed fully in the Guide. When a parameter is used, the default type is chosen unless a CAST is used.

[Dmitry Katsubo]

fredt, thanks for your reply.

The possible types of the arguments to all functions are listed fully in the Guide. When a parameter is used, the default type is chosen unless a CAST is used.

What is the default type for round() function? The documentation for it says:

The <num value expr> is of the DOUBLE type or DECIMAL type.

So what is the default: DOUBLE or DECIMAL? If parameter is always defaulting to integer, how then preparation of dateadd(?, 5, dateTimeCol) is done? (1st parameter should be varchar, expression engine can't assume it is integer).

Please, help me to find the corresponding section in SQL'92. The only thing I've found is general arithmetic rules in section 6.12 <numeric value expression>.

[Fred Toussi]

In your SELECT statement, the parameter is defaulting to integer because it is multiplied by an integer. If the parameter is used without multiplication it would take the default type, which is DECIMAL.

Determination of types of dynamic parameters is not  covered by SQL 92, but it is in SQL 2008.

To see which type is assigned to the parameters, you can use

EXPLAIN PLAN for select distinct(round(row_number * 1.6)) as row_num from data_table order by row_num

The parameters for DATAEADD or other functions default to a type appropriate for the function, which is not always integer.