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

Support BETWEEN predicate for select expressions [CORE1095] #1517

Closed
firebird-issue-importer opened this issue Jan 17, 2007 · 21 comments
Closed

Support BETWEEN predicate for select expressions [CORE1095] #1517

firebird-issue-importer opened this issue Jan 17, 2007 · 21 comments

Comments

@firebird-issue-importer

Submitted by: @pcisar

Is duplicated by CORE1897
Is duplicated by CORE1915
Relate to CORE5596

Attachments:
CRE_DB.SQL.txt

Votes: 5

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.

Commits: 50d3041 191f700

====== Test Details ======

See also CORE5596

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 17, 2007

Commented by: @pcisar

Reproducible test case.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 17, 2007

Modified by: @pcisar

Attachment: CRE_DB.SQL.txt [ 10230 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 17, 2007

Commented by: @dyemanov

This is a known limitation. Problem is not a procedure but a subquery. They're not supported in BETWEEN.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 18, 2007

Modified by: @pcisar

issuetype: Bug [ 1 ] => Improvement [ 4 ]

summary: Problem with BETWEEN predicate applied on select expression in view => Support BETWEEN predicate for select expressions

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 28, 2008

Modified by: @pcisar

Workflow: jira [ 11508 ] => Firebird [ 15497 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Apr 10, 2008

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 13, 2008

Modified by: @dyemanov

Link: This issue is duplicated by CORE1897 [ CORE1897 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 16, 2008

Commented by: @livius2

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 16, 2008

Commented by: @dyemanov

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".

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented May 27, 2008

Modified by: @dyemanov

Link: This issue is duplicated by CORE1915 [ CORE1915 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 21, 2010

Commented by: Khmelevskiy Alexandr (khmelevskiy)

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?

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Oct 21, 2010

Commented by: @dyemanov

The reason is the same. Different error is also okay, although I'd agree better consistency would be welcome.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 1, 2016

Commented by: Kjell Rilbe (kjellrilbe)

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 5, 2017

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 7, 2017

Modified by: @dyemanov

Fix Version: 3.0.2 [ 10785 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2017

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2017

Commented by: @pavel-zotov

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).

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 19, 2017

Commented by: @dyemanov

Interesting. I'll take a look.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Jan 28, 2017

Commented by: @dyemanov

Missing alias in the plan output should be fixed now.

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 21, 2017

Modified by: @dyemanov

Link: This issue relate to CORE5596 [ CORE5596 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 22, 2017

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

Test Details: See also CORE5596

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