Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Length of INPUT parameter in SQLDA depends on whether it is enclosed in COALESCE() or no. [CORE6187] #6432

Open
firebird-automations opened this issue Nov 13, 2019 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

Is related to CORE6108

Consider following script:

set bail on;
set echo on;
shell del c:\temp\tmp4test.tmp 2>nul;

set names win1251;
create database 'localhost:c:\temp\tmp4test.tmp' user sysdba password 'masterkey';
show version;

create domain dm_txt as varchar(24);
create table phones(prefix dm_txt);
commit;
set planonly;
set sqlda_display on;
select 1 from phones p where coalesce(right(p.prefix, 3), '') = ? ;
select 1 from phones p where coalesce(right(p.prefix, 3), '') = coalesce( ? , '') ;
set sqlda_display off;
set planonly;
commit;
drop database;

It will produce :
1) on WI-V3.0.5.33166 (date of snapshot: 05-sep-2019):

INPUT message field count: 1
01: sqltype: 448 VARYING Nullable scale: 0 subtype: 0 len: 24 charset: 52 WIN1251 ------ NOTE: LEN=24 (expected)
. . .
INPUT message field count: 1
01: sqltype: 452 TEXT Nullable scale: 0 subtype: 0 len: 0 charset: 52 WIN1251 -------------- NOTE: LEN=0 (why ?)

(the same on WI-T4.0.0.1646, date: 09-nov-2019)

2) on WI-V3.0.5.33162 (date of snapshot: 30-aug-2019)

INPUT message field count: 1
01: sqltype: 448 VARYING Nullable scale: 0 subtype: 0 len: 24 charset: 52 WIN1251
. . .
INPUT message field count: 1
01: sqltype: 448 VARYING Nullable scale: 0 subtype: 0 len: 24 charset: 52 WIN1251 -- OK, expected

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

The bevaior matches v2.5, which has no CORE6108 problem while v3 before fix for CORE6108 was of course a problem. Look:

select 1 from phones p where 1 = coalesce(?, '');

2.5: TEXT, len=0
3.0 before CORE6108: VARYING, len=11
3.0 after CORE6108: TEXT, len=0

---------

select 1 from phones p where coalesce(right(p.prefix, 3), '') = ? ;

2.5: VARYING, len=24
3.0 before CORE6108: VARYING, len=24
3.0 after CORE6108: VARYING, len=24

---------

select 1 from phones p where coalesce(right(p.prefix, 3), '') = coalesce( ? , '') ;

2.5: TEXT, len=0
3.0 before CORE6108: VARYING, len=24
3.0 after CORE6108: TEXT, len=0

---------

If someone disagree, then how parameters are mapped should be completelly specified when there are parameters in both sides of an expression.

Also note that when we see "1 = coalesce(?, '')", there is nothing in the expression that binds the parameter to 1. The parameter should be resolved as a coalesce(?, '') alone, which is what the expression is IMO.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Adriano, basically I agree that the parameter inside the COALESCE should be derived from the neighbor argument values. A few comments though:

1) Should it be a COALESCE-only feature or something more generic? What about arbitrary expressions?
2) Describing the parameter as CHAR(0) is absolutely useless in practice. I believe some hardcoded default (e.g. VARCHAR(100)) should be used in this case. And it would resolve this ticket.
3) What are we going to do for A = COALESCE(?,?), describe both as defaults or use typeof(A) or raise an error?

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is related to CORE6108 [ CORE6108 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

> 1) Should it be a COALESCE-only feature or something more generic? What about arbitrary expressions?

It's difficult to say in general. Even this simple expression throws error:

SQL> select 1 + ? from rdb$database;
Statement failed, SQLSTATE = HY004
Dynamic SQL Error
-SQL error code = -804
-Data type unknown

But I believe there is more expressions that tries "smart" logic with parameters that should be more simple, as they guess things incorrectly.

> 2) Describing the parameter as CHAR(0) is absolutely useless in practice. I believe some hardcoded default (e.g. VARCHAR(100)) should be used in this case. And it would resolve this ticket.

Why 100? And COALESCE(?, ' ') will be CHAR(1) ? :)

The CHAR(0) here is more logical, or maybe SQL_NULL.

I believe sometimes user needs to use casts to make things the way he can, as we can't guess correctly all cases.

> 3) What are we going to do for A = COALESCE(?,?), describe both as defaults or use typeof(A) or raise an error?

As now, Data type unknown.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

1) Speaking about arbitrary expressions, I don't think it's doable at all. Consider for example "<date> = ? + <number>" and "<number> = ? - <date>" where the parameter should be described as date in both cases. The logic seems too complex to cover all possible cases properly.

2) The problem is that you cannot pass anything other than NULL / empty string to such a parameter and this is hardly the case everyone uses in the real world. Of course COALESCE(?, ' ') is not much better ;-) But I suspect the last parameter of COALESCE (if specified as a constant) is always going to be (by its "default" nature) shorther than the other arguments and in 99% of cases it gonna be an empty string. So the current solution may limit the practical usage of COALESCE.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Same thing happens even with a single parameter, for example CHAR_LENGTH(?). How can we guess?

Also imagine a FORMAT (printf-like) function which receives format string and parameters, but the result length cannot be guessed.

We have text-blobs but they are slow.

We have varchars but when declaring various with max-length, it will use a single and continuous block of memory.

Maybe we need something better for variable string of long (undertermined) size in the API? Or maybe improve blobs for such cases?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants