Thread: Inconsistent results from HEX values in predicates

Hello

I have a table, and I want to always supply predicates in a BETWEEN format.

If all data is to be returned, then the FROM and TO values of the
BETWEEN are set to low hex value, and high hex value.

Sometimes this is not returning rows, here's an example:

e5iso=# create table t1 (col1 character(3));
CREATE TABLE
e5iso=# insert into t1 values('AAA');
INSERT 0 1
e5iso=# insert into t1 values('000');
INSERT 0 1
e5iso=# insert into t1 values('   ');
INSERT 0 1
e5iso=# insert into t1 values(' x ');
INSERT 0 1
e5iso=# select  col1 from t1 where col1 BETWEEN E'\x01\x01\x01' AND
E'\xFF\xFF\xFF' ;
 col1
------
 AAA
 000
  x
(3 rows)

So where did my '   ' row go?

Am I missing something, are SPACES special in some way?

e5iso=# select * from t1 where col1 is null;
 col1
------
(0 rows)


All my rows hex values:

e5iso=# select encode(col1::bytea,'hex') from t1;
 encode
--------
 414141
 303030
 202020
 207820


The database is a LATIN9 database.

Help please!

Actually I find this returns my row:

select  col1 from t1 where col1 BETWEEN E'\x01\x01\x01' AND
E'\xFF\xFF\xFF' or col1=E'\x20';

Why does x'202020' match an equality condition on E'\x20' ?

Is there some auto trimming going on?

This is PostgreSQL 9.3.5 on Linux

On 14 November 2014 11:41, Gary Cowell <gary.cowell+pgsql@gmail.com> wrote:
> Hello
>
> I have a table, and I want to always supply predicates in a BETWEEN format.
>
> If all data is to be returned, then the FROM and TO values of the
> BETWEEN are set to low hex value, and high hex value.
>
> Sometimes this is not returning rows, here's an example:
>
> e5iso=# create table t1 (col1 character(3));
> CREATE TABLE
> e5iso=# insert into t1 values('AAA');
> INSERT 0 1
> e5iso=# insert into t1 values('000');
> INSERT 0 1
> e5iso=# insert into t1 values('   ');
> INSERT 0 1
> e5iso=# insert into t1 values(' x ');
> INSERT 0 1
> e5iso=# select  col1 from t1 where col1 BETWEEN E'\x01\x01\x01' AND
> E'\xFF\xFF\xFF' ;
>  col1
> ------
>  AAA
>  000
>   x
> (3 rows)
>
> So where did my '   ' row go?
>
> Am I missing something, are SPACES special in some way?
>
> e5iso=# select * from t1 where col1 is null;
>  col1
> ------
> (0 rows)
>
>
> All my rows hex values:
>
> e5iso=# select encode(col1::bytea,'hex') from t1;
>  encode
> --------
>  414141
>  303030
>  202020
>  207820
>
>
> The database is a LATIN9 database.
>
> Help please!

Gary Cowell <gary.cowell+pgsql@gmail.com> writes:
> e5iso=# create table t1 (col1 character(3));
> CREATE TABLE
> e5iso=# insert into t1 values('AAA');
> INSERT 0 1
> e5iso=# insert into t1 values('000');
> INSERT 0 1
> e5iso=# insert into t1 values('   ');
> INSERT 0 1
> e5iso=# insert into t1 values(' x ');
> INSERT 0 1
> e5iso=# select  col1 from t1 where col1 BETWEEN E'\x01\x01\x01' AND
> E'\xFF\xFF\xFF' ;
>  col1
> ------
>  AAA
>  000
>   x
> (3 rows)

> So where did my '   ' row go?

> Am I missing something, are SPACES special in some way?

In a char(N) column, certainly.  Read the manual.

You could dodge that by using varchar or text instead.  But you may have
another problem besides trailing blanks not being significant: you're
assuming that the sort order of text is pure bytewise, which would only
be true in C locale.  So this coding technique is fragile as can be
and I don't recommend it.

            regards, tom lane