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-automations opened this issue Jan 17, 2007 · 21 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

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-automations
Copy link
Collaborator Author

Commented by: @pcisar

Reproducible test case.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Attachment: CRE_DB.SQL.txt [ 10230 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE1897 [ CORE1897 ]

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue is duplicated by CORE1915 [ CORE1915 ]

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Fix Version: 3.0.2 [ 10785 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

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-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Interesting. I'll take a look.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Missing alias in the plan output should be fixed now.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE5596 [ CORE5596 ]

@firebird-automations
Copy link
Collaborator Author

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