Query returning incorrect results: datatype char8, where clause '=', 'IN'

[David Hill]

This query not returning correct results:
  select count(*) from test_table where test_field = "MCARD";

Where this query was:
  select count(*) from test_table1 where test_field like "MCARD";

Problem happens on datatype char-8, but not on char-9.

[David Hill (dhill)]

Problem occurs right after the initial import is performed.
Query can start working after updates/inserts are made to the table.

[wweeks (wweeks)]

The problem exists for char(3), char(5), char(6), and char(7) and for varchar(1) through varchar(7) columns. This will be fixed in the 1.5.4 patch release.

The bug is in cpimport where the extent map min and max values are getting set incorrectly for certain values.

A work arounds are available:
1. Use char(4) instead of char(3) or varchar(2-4).
     Use char(8) instead of char(5-7) and varchar(5-8).
     The bytes stored on disk will be the same with this work around.
or...
2. Use the editem utility to clear the min and max values for the column. This option has a very quick run time, likely sub second. Here is a Linux command that will do this for the test_table.test_field column described in the example.
/usr/local/Calpont/bin/editem -c `idbmysql calpontsys -e "select objectid from syscolumn where columnname='test_field' and tablename='test_table';" | grep -v objectid`
or...
3. Update the column setting the value to itself. This may be a good solution if you have a few thousand rows in the table. #2 will be much faster if you are dealing with a large number of rows.
Ex:
update test_table set test_field=test_field;

[wweeks (wweeks)]

Correction to my prior comment. The problem also exists for char(4) and char(8) data types, so work around #1 is not an option.

Work arounds #2 and #3 are available.
2. Use the editem utility to clear the min and max values for the column. This option has a very quick run time, likely sub second. Here is a Linux command that will do this for the test_table.test_field column described in the example.
/usr/local/Calpont/bin/editem -c `idbmysql calpontsys -e "select objectid from syscolumn where columnname='test_field' and tablename='test_table';" | grep -v objectid`
or...
3. Update the column setting the value to itself. This may be a good solution if you have a few thousand rows in the table. #2 will be much faster if you are dealing with a large number of rows.
Ex:
update test_table set test_field=test_field;

[wweeks (wweeks)]

Resolved in 1.5.4. The extent map min and max values are set correctly by cpimport with the fix.