Issue Details (XML | Word | Printable)

Key: CORE-1095
Type: Improvement Improvement
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Pavel Cisar
Votes: 5
Watchers: 3
Operations

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

Support BETWEEN predicate for select expressions

Created: 17/Jan/07 01:56 PM   Updated: 22/Nov/17 05:50 AM
Component/s: Engine
Affects Version/s: 1.5.2, 1.5.3, 2.0.0
Fix Version/s: 3.0.2, 4.0 Alpha 1

File Attachments: 1. Text File CRE_DB.SQL.txt (3 kB)

Environment: confirmed on Windows, but it should be platform independent.
Issue Links:
Duplicate
 
Relate
 

QA Status: Done successfully
Test Details: See also CORE-5596


 Description  « Hide
View that uses select expressions from selectable stored procedure fails to execute with error:
Unsuccessful execution caused by an unavailable resource.
Unsupported field type specified in BETWEEN predicate.

for select from view that uses BETWEEN predicate applied to select expression. See attached script for reproducible test case.
It executes correctly when equivalent construct X >= val1 and X <= val2 is used.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Pavel Cisar added a comment - 17/Jan/07 01:57 PM
Reproducible test case.

Pavel Cisar made changes - 17/Jan/07 01:57 PM
Field Original Value New Value
Attachment CRE_DB.SQL.txt [ 10230 ]
Dmitry Yemanov added a comment - 17/Jan/07 02:46 PM
This is a known limitation. Problem is not a procedure but a subquery. They're not supported in BETWEEN.

Pavel Cisar made changes - 18/Jan/07 09:41 AM
Summary Problem with BETWEEN predicate applied on select expression in view Support BETWEEN predicate for select expressions
Issue Type Bug [ 1 ] Improvement [ 4 ]
Pavel Cisar made changes - 28/Jan/08 01:23 PM
Workflow jira [ 11508 ] Firebird [ 15497 ]
Dmitry Yemanov made changes - 10/Apr/08 02:43 PM
Assignee Dmitry Yemanov [ dimitr ]
Dmitry Yemanov made changes - 13/May/08 10:34 AM
Link This issue is duplicated by CORE-1897 [ CORE-1897 ]
Karol Bieniaszewski added a comment - 16/May/08 04:41 AM
But this queries work (not operator)

select
*
from
RDB$DATABASE R
WHERE
(SELECT COUNT(*) FROM RDB$DATABASE) NOT BETWEEN 1 AND 5

############################################################

select
*
from
RDB$DATABASE R
WHERE
NOT ((SELECT COUNT(*) FROM RDB$DATABASE) BETWEEN 1 AND 5) <<<<-- i do not know why heare work but without "not " do not work

with not operator between is acceptable

Dmitry Yemanov added a comment - 16/May/08 04:52 AM
It's the problem with the optimizer. It attempts to convert BETWEEN into two predicates: greater-than and less-than. If subquery is involved, this attempt fails due to some internal reasons. But such a conversion is not done for NOT BETWEEN, thus it just works "as is".

Dmitry Yemanov made changes - 26/May/08 11:20 PM
Link This issue is duplicated by CORE-1915 [ CORE-1915 ]
Khmelevskiy Alexandr added a comment - 21/Oct/10 01:06 PM
Ubuntu 10.04, Firebird 1.5.6, 2.1.3, 2.5.0 Classic Server 32-bit
For this query:
select * from rdb$database where strlen((select '1' from rdb$database)) between 0 and 2

I have received such error:
Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements.
feature is not supported.

This queries works fine:
select * from rdb$database where strlen('1') between 1 and 2
select * from rdb$database where strlen((select '1' from rdb$database)) = 1
select * from rdb$database where strlen((select '1' from rdb$database)) not between 0 and 2

This error for the same reason (problem with the optimizer in between)? But if so, why differs error's text? Or, may be, this is bug?

Dmitry Yemanov added a comment - 21/Oct/10 03:56 PM
The reason is the same. Different error is also okay, although I'd agree better consistency would be welcome.

Kjell Rilbe added a comment - 01/Dec/16 03:36 PM
This one just hit me on version 2.5. Has it been addressen in 3.0? If not, are there any plans?

Possible workaround is to modify subquery to not select the actual value, but a flag that indicates if the actual value is within the interval or not.

So, instead of this:
...where (select count(*) from SomeTable) between 1 and 5
you could write:
...where (select case when count(*) between 1 and 5 then 1 end from SomeTable) = 1


Dmitry Yemanov made changes - 05/Jan/17 07:27 PM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 4.0 Alpha 1 [ 10731 ]
Resolution Fixed [ 1 ]
Dmitry Yemanov made changes - 07/Jan/17 12:48 PM
Fix Version/s 3.0.2 [ 10785 ]
Pavel Zotov made changes - 19/Jan/17 05:54 AM
Status Resolved [ 5 ] Resolved [ 5 ]
QA Status Done successfully
Pavel Zotov added a comment - 19/Jan/17 05:57 AM - edited
set planonly;

select 1 from rdb$database r_out where ((select count(*) from rdb$database r_chk) not between ? and ?);
select 2 from rdb$database r_out where not ((select count(*) from rdb$database r_chk) between ? and ?);
select 3 from rdb$database r_out where ((select count(*) from rdb$database r_chk) between ? and ?);


Output for 1st and 2nd will be:

PLAN (R_CHK NATURAL)
PLAN (R_CHK NATURAL)
PLAN (R_OUT NATURAL)

Output for 3rd:
PLAN (R_CHK NATURAL)
PLAN (RDB$DATABASE NATURAL) <<<< ??
PLAN (R_OUT NATURAL)

Why 'RDB$DATABASE' is shown in 3rd plan ? Every source is aliased in this query (as in 1st and 2nd).

Dmitry Yemanov made changes - 19/Jan/17 06:17 AM
Comment [ See my comment above re. NOT BETWEEN (dated 16-May-2008). ]
Dmitry Yemanov added a comment - 19/Jan/17 06:17 AM
Interesting. I'll take a look.

Dmitry Yemanov added a comment - 28/Jan/17 08:52 AM
Missing alias in the plan output should be fixed now.

Dmitry Yemanov made changes - 21/Nov/17 12:22 PM
Link This issue relate to CORE-5596 [ CORE-5596 ]
Pavel Zotov made changes - 22/Nov/17 05:50 AM
Status Resolved [ 5 ] Resolved [ 5 ]
Test Details See also CORE-5596