Issue Details (XML | Word | Printable)

Key: CORE-6187
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Pavel Zotov
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Length of INPUT parameter in SQLDA depends on whether it is enclosed in COALESCE() or no.

Created: 12/Nov/19 10:05 PM   Updated: 24/Dec/19 10:35 AM
Component/s: None
Affects Version/s: None
Fix Version/s: None

Issue Links:
Relate
 

QA Status: No test


 Description  « Hide
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
=====



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 22/Nov/19 04:12 PM
The bevaior matches v2.5, which has no CORE-6108 problem while v3 before fix for CORE-6108 was of course a problem. Look:

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

2.5: TEXT, len=0
3.0 before CORE-6108: VARYING, len=11
3.0 after CORE-6108: TEXT, len=0

---------

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

2.5: VARYING, len=24
3.0 before CORE-6108: VARYING, len=24
3.0 after CORE-6108: VARYING, len=24

---------

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

2.5: TEXT, len=0
3.0 before CORE-6108: VARYING, len=24
3.0 after CORE-6108: 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.

Dmitry Yemanov added a comment - 17/Dec/19 08:33 AM - edited
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?

Adriano dos Santos Fernandes added a comment - 20/Dec/19 03:18 PM
> 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.

Dmitry Yemanov added a comment - 24/Dec/19 06:45 AM
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.
 

Adriano dos Santos Fernandes added a comment - 24/Dec/19 10:35 AM
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?