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
Comments
Commented by: @pcisar Reproducible test case. |
Modified by: @pcisarAttachment: CRE_DB.SQL.txt [ 10230 ] |
Commented by: @dyemanov This is a known limitation. Problem is not a procedure but a subquery. They're not supported in BETWEEN. |
Modified by: @pcisarissuetype: Bug [ 1 ] => Improvement [ 4 ] summary: Problem with BETWEEN predicate applied on select expression in view => Support BETWEEN predicate for select expressions |
Modified by: @pcisarWorkflow: jira [ 11508 ] => Firebird [ 15497 ] |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Commented by: @livius2 But this queries work (not operator) select ############################################################ select with not operator between is acceptable |
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". |
Commented by: Khmelevskiy Alexandr (khmelevskiy) Ubuntu 10.04, Firebird 1.5.6, 2.1.3, 2.5.0 Classic Server 32-bit I have received such error: This queries works fine: 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? |
Commented by: @dyemanov The reason is the same. Different error is also okay, although I'd agree better consistency would be welcome. |
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: |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 4.0 Alpha 1 [ 10731 ] |
Modified by: @dyemanovFix Version: 3.0.2 [ 10785 ] |
Commented by: @pavel-zotov set planonly; select 1 from rdb$database r_out where ((select count(*) from rdb$database r_chk) not between ? and ?); Output for 1st and 2nd will be: PLAN (R_CHK NATURAL) Output for 3rd: Why 'RDB$DATABASE' is shown in 3rd plan ? Every source is aliased in this query (as in 1st and 2nd). |
Commented by: @dyemanov Interesting. I'll take a look. |
Commented by: @dyemanov Missing alias in the plan output should be fixed now. |
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
The text was updated successfully, but these errors were encountered: