expr.startswith(...) should emit expr STARTING WITH ... on firebird

[jek]

[08:31](08:31)  <Crys_> SA emits "like 'str%'" for startswith. However FB doesn't use an index for LIKE 'str%'
[08:32](08:32)  <Crys_> I'd like to replace the op with Firebird's "STARTING WITH" statement.

[Mike Bayer]

we'd need a firebird guru to comment on the feasability of this. op("STARTING WITH") is an option for now.

[Lele Gaifax]

Maybe it's just because I'm not a guru :-) but I do not understand the posed question... or better, I do not understand why the ''feasibility'' is with FB instead more generally with SA.

OTOH, Firebird 2.1 does use indexes on LIKE conditions, when possible:

SQL> set planonly;
SQL> select * from a;

PLAN (A NATURAL)
SQL> select * from a where b='1';

PLAN (A INDEX (AB))
SQL> select * from a where b starting with '1';

PLAN (A INDEX (AB))
SQL> select * from a where b like '1%';

PLAN (A INDEX (AB))
SQL> select * from a where b like '%1';

PLAN (A NATURAL)

[Former user]

Under Firebird 2.0 LIKE doesn't use the index when the right hand argument is dynamic. The index is only used for a constant value but not for subselects or string concatenation. I don't have FB 2.1 on my machine. Could you test it on 2.1 for me, please?

create table test( a varchar(50) );

create index idx_test_a ON test(a); commit;

insert into test (a) values ('ab'); insert into test (a) values ('abc'); insert into test (a) values ('abd'); commit;

Preparing query: select * from test where a like (select a from test where a='ab') || 'c%' Field #01: TEST.A Alias:A Type:STRING(50) PLAN (TEST INDEX (IDX_TEST_A)) PLAN (TEST NATURAL)

Preparing query: select * from test where a starting with (select a from test where a='ab') || 'c' Field #01: TEST.A Alias:A Type:STRING(50) PLAN (TEST INDEX (IDX_TEST_A)) PLAN (TEST INDEX (IDX_TEST_A))

[Former user]

I forgot to mention: I'm using Firebird 2.0.4 and FlameRobin 0.8.6

Crys

[Former user]

Testdata

create table test(
    a varchar(50)
);

create index idx_test_a ON test(a); 
commit;

insert into test (a) values ('ab');
insert into test (a) values ('abc');
insert into test (a) values ('abcd'); 
commit;

Queries

select * from test where a like (select a from test where a='ab') || 'c%';
select * from test where a starting (select a from test where a='ab') || 'c';

[Lele Gaifax]

Ok, I confirm that a parametric LIKE does not use indexes, while STARTING WITH does, even under FB 2.1.

I tried to figure out where this could be changed, following the code path, but I miss how the dialect could bring some knowledge into the way SA compiles .startswith() (which AFAICT is in the _CompareMixin.startswith() near line 1382 of sql/expression.py).

[Mike Bayer]

we would have to shift the implementation of startswith/endswith/(contains?) to the base and dialect-level compilers. We have startswith/endswith operators so there's a clear path to this. The concatenation logic in sql/expressions.py would take place in the compiler.

[Lele Gaifax]

AFAICT the same problem affects also other engines, so it would be nice having a way to express the condition in a dialect-specific way. Postgres could take advantage by using BETWEEN :value AND :value||'zzz' for example...

[Mike Bayer]

Duplicate of #2470.