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
Comments
Modified by: @dyemanovassignee: Adriano dos Santos Fernandes [ asfernandes ] |
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 --------- select 1 from phones p where coalesce(right(p.prefix, 3), '') = ? ; 2.5: VARYING, len=24 --------- select 1 from phones p where coalesce(right(p.prefix, 3), '') = coalesce( ? , '') ; 2.5: 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. |
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? |
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; 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. |
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. |
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? |
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
The text was updated successfully, but these errors were encountered: