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

Error with sequence of AND [CORE5502] #5771

Closed
firebird-automations opened this issue Mar 15, 2017 · 10 comments
Closed

Error with sequence of AND [CORE5502] #5771

firebird-automations opened this issue Mar 15, 2017 · 10 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Cassiano Baltazar da Silva (cassianobs)

It looks like the engine is evaluating the right hand side on an AND operator. Since the first clause is false for every record on this query, why is raising an exception?

I recive the follow error:
Undefined name.
Invalid offset parameter 0 to SUBSTRING. Only positive integers are allowed.

It's the sql that i running in EMPLOYEE.FDB:

select
*
from
COUNTRY CO
where
CO.COUNTRY = 'Germany' and
(
(position('-', 'test') > 0) and
(position('-', CO.CURRENCY) > 0) and
(nullif(trim(substring(CO.CURRENCY from position('-', CO.CURRENCY) for 10)), '') is not null) and
(nullif(trim(substring('test' from position('-', 'test') for 10)), '') is not null) and
(trim(substring(CO.CURRENCY from position('-', CO.CURRENCY) for 10)) = trim(substring('test' from position('-', 'test') for 10)))
)

@firebird-automations
Copy link
Collaborator Author

Modified by: Cassiano Baltazar da Silva (cassianobs)

description: When i run the follow sql i get the error

select
*
from
COUNTRY CO
where
CO.COUNTRY = 'Germany' and
(
(position('-', 'test') > 0) and
(position('-', CO.CURRENCY) > 0) and
(nullif(trim(substring(CO.CURRENCY from position('-', CO.CURRENCY) for 10)), '') is not null) and
(nullif(trim(substring('test' from position('-', 'test') for 10)), '') is not null) and
(trim(substring(CO.CURRENCY from position('-', CO.CURRENCY) for 10)) = trim(substring('test' from position('-', 'test') for 10)))
)

=>

It looks like the engine is evaluating the right hand side on an AND operator. Since the first clause is false for every record on this query, why is raising an exception?

I recive the follow error:
Undefined name.
Invalid offset parameter 0 to SUBSTRING. Only positive integers are allowed.

It's the sql that i running in EMPLOYEE.FDB:

select
*
from
COUNTRY CO
where
CO.COUNTRY = 'Germany' and
(
(position('-', 'test') > 0) and
(position('-', CO.CURRENCY) > 0) and
(nullif(trim(substring(CO.CURRENCY from position('-', CO.CURRENCY) for 10)), '') is not null) and
(nullif(trim(substring('test' from position('-', 'test') for 10)), '') is not null) and
(trim(substring(CO.CURRENCY from position('-', CO.CURRENCY) for 10)) = trim(substring('test' from position('-', 'test') for 10)))
)

@firebird-automations
Copy link
Collaborator Author

Modified by: Cassiano Baltazar da Silva (cassianobs)

Component: Engine [ 10000 ]

Component: ISQL [ 10003 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

Please improve the title of this issue. The problem is not actually with POSITION itself, so it is not very descriptive.

@firebird-automations
Copy link
Collaborator Author

Modified by: Cassiano Baltazar da Silva (cassianobs)

summary: Error with position on where => Error with sequence of AND

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

SQL is a declarative language, so evaluation order of predicates is not guaranteed. Moreover, it can vary from version to version. One should not rely on such side effects. IIRC, Firebird 3.0 was changed to use left-to-right approach by default, but this is just a basic rule that can be overriden by the optimizer.

@firebird-automations
Copy link
Collaborator Author

Commented by: Cassiano Baltazar da Silva (cassianobs)

Thanks for answer.

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Based on Dmitry's explanation, it seems appropriate for this issue to be marked as "Won't fix" since the engine is operating as expected.

@firebird-automations
Copy link
Collaborator Author

Commented by: Cassiano Baltazar da Silva (cassianobs)

How i do this?

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

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

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

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

No branches or pull requests

1 participant