Re: select fails on indexed varchars.

Hi Tom,

> >> So it does.  Okay, what was the complete test case again?
> >> I'm afraid I didn't save your original message because I wrote it off
> >> as a known problem ...
>
> > Here it is:
>
> >         links=# create table foo ( a char(25) );
> >         CREATE
> >         links=# create index foodx on foo (a);
> >         CREATE
> >         links=# insert into foo values ('Test/Test');
> >         INSERT 29689 1
> >         links=# select * from foo;
> >                      a
> >         ---------------------------
> >          Test/Test
> >         (1 row)
>
> >         links=# select * from foo where a like 'Test/%';
> >          a
> >         ---
> >         (0 rows)
>
> How odd.  I get 'Test/Test' from the last select, under both 7.0.2
> and current sources, when using C locale.  The query certainly looks
> like the kind that would suffer from the LIKE-optimization problem in
> non-C locales ... but we seem to have established that you've gotten
> the postmaster switched into C locale.
>
> What does EXPLAIN VERBOSE select * from foo where a like 'Test/%';
> show?

Here's a cut and paste, not sure if there is a nicer way to output it:

links=# EXPLAIN VERBOSE select * from foo where a like 'Test/%';
NOTICE:  QUERY DUMP:

{ INDEXSCAN :startup_cost 0.00 :total_cost 8.14 :rows 10 :width 12 :state <> :qptargetlist ({ TARGETENTRY :resdom {
RESDOM:resno 1 :restype 1042 :restypmod 29 :resname a :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr {
VAR:varno 1 :varattno 1 :vartype 1042 :vartypmod 29  :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual ({ EXPR
:typeOid16  :opType op :oper { OPER :opno 1211 :opid 850 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype
1042:vartypmod 29  :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 25 :constlen -1 :constisnull false
:constvalue 10 [ 10 0 0 0 84 101 115 116 47 37 ]  :constbyval false })}) :lefttree <> :righttree <> :extprm () :locprm
():initplan <> :nprm 0  :scanrelid 1 :indxid ( 18825) :indxqual (({ EXPR :typeOid 16  :opType op :oper { OPER :opno
1061:opid 1052 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 29  :varlevelsup 0
:varnoold1 :varoattno 1} { CONST :consttype 1042 :constlen -1 :constisnull false :constvalue  9 [ 9 0 0 0 84 101 115
11647 ]  :constbyval false })} { EXPR :typeOid 16  :opType op :oper { OPER :opno 1058 :opid 1049 :opresulttype 16 }
:args({ VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 29  :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST
:consttype1042 :constlen -1 :constisnull false :constvalue  9 [ 9 0 0 0 84 101 115 116 48 ]  :constbyval false })}))
:indxqualorig(({ EXPR :typeOid 16  :opType op :oper { OPER :opno 1061 :opid 1052 :opresulttype 16 } :args ({ VAR :varno
1:varattno 1 :vartype 1042 :vartypmod 29  :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 1042 :constlen -1
:constisnullfalse :constvalue  9 [ 9 0 0 0 84 101 115 116 47 ]  :constbyval false })} { EXPR :typeOid 16  :opType op
:oper{ OPER :opno 1058 :opid 1049 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 29
:varlevelsup0 :varnoold 1 :varoattno 1} { CONST :consttype 1042 :constlen -1 :constisnull false :constvalue  9 [ 9 0 0
084 101 115 116 48 ]  :constbyval false })})) :ind 
NOTICE:  QUERY PLAN:

Index Scan using foodx on foo  (cost=0.00..8.14 rows=10 width=12)

EXPLAIN
links=#

Cheers,

Alex