1. Mike Bayer
  2. sqlalchemy

Issues

Issue #1168 duplicate

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

jason kirtland
created an issue
[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.

Comments (10)

  1. 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)
    
  2. Anonymous

    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))

  3. Anonymous

    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';
    
  4. 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).

  5. Mike Bayer repo owner

    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.

  6. 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...

  7. Log in to comment