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

"value STARTING WITH field" fails when using indices [CORE233] #564

Closed
firebird-automations opened this issue May 12, 2003 · 5 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: askln (askln)

SFID: 736318#⁠
Submitted By: askln

(Tested on Firebird 1.5 RC2 Windows SS)

Requests like these look bizzare at first, but they are
useful for a number of applications (esecially in the
absence of CONNECTED BY), and the workaround
shown below obviously leads to a performance hit.

If index is either not defined or manually excluded from
plan, the results are correct. Else it seems that only the
rows having field equal to a value are returned.

This code

RECREATE TABLE t (
id INTEGER NOT NULL PRIMARY KEY,
s VARCHAR(50)
);
CREATE INDEX idx ON t(s);

INSERT INTO t VALUES (1, 'ab');
INSERT INTO t VALUES (2, 'aa');
INSERT INTO t VALUES (3, 'aaa');

SELECT id FROM t WHERE 'aaa' STARTING WITH s;
SELECT id FROM t WHERE 'aaa' STARTING WITH s
PLAN (t NATURAL);

gives

      ID

============
3

      ID

============
2
3

this is possibly related with bug #⁠735720

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

Date: 2003-05-12 09:50
Sender: dimitr
Logged In: YES
user_id=61270

Helen, I think you're wrong here. The correct syntax
is <value> LIKE <value> or <value> STARTING WITH
<value>, when <value> may be a column (but it's
not necessary). But for both mentioned clauses the
optimizer sucks if their normal form is reverted, i.e.
an expression is compared with a column. This
incorrect behaviour takes place only when some
index is available and can be used for a column.

So it's definitely a bug (although I was sure it was
already logged in our tracker).

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

Date: 2003-05-12 09:37
Sender: askln
Logged In: YES
user_id=757561

No! I *DO* understand the difference between
SELECT id FROM t WHERE s STARTING WITH 'aaa'
and
SELECT id FROM t WHERE 'aaa' STARTING WITH s
and I explicitly wanted the latter, that is, to select all rows
where the value of s is the prefix of the string 'aaa'.
I have anticipated possible confusion -- hence the second
paragraph of my report ;) Sorry if I was not clear enough, but
IS a bug -- in any case, the query result shoult NOT depend
on the usage of the index.
If this syntax is to be disallowed -- well, so be it, though it will
be a pity. As I have already stated it is quite useful on some
occasions.

@firebird-automations
Copy link
Collaborator Author

Commented by: Alice F. Bird (firebirds)

Date: 2003-05-12 08:29
Sender: helebor
Logged In: YES
user_id=60469

Of course! Your clause asks the engine to find any row
whose value of s starts with 'aaa'. Only one row matches. I
think you're quite lucky to get this much from wrong syntax.

You wouldn't have this problem if you used the STARTING
WITH syntax *properly*, i.e.
where <col_ref> starting with <constant string>

So your search should be:
SELECT id FROM t WHERE s STARTING WITH 'aaa' ;

Please don't use the tracker for things that aren't bugs.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Workflow: jira [ 10257 ] => Firebird [ 14489 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

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

2 participants