Not able retieve records even though data is available in the table

Hi,

We are using GridSQL 1.1.0.9 with postgresplus standard server.
We are having problem in getting records even though data is available in the table.

GridSQL -> select count(*) from test where served_number='779294936';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row(s).

GridSQL -> select served_number from test where served_number like '%779294936%';
+---------------+
| served_number |
+---------------+
| 779294936 |
| 779294936 |
| 779294936 |
| 779294936 |
| 779294936 |
+---------------+
5 row(s).

GridSQL -> select served_number,length(served_number) from test where served_number like '%779294936%';
+------------------------+
| served_number | length |
+------------------------+
| 779294936 | 9 |
| 779294936 | 9 |
| 779294936 | 9 |
| 779294936 | 9 |
| 779294936 | 9 |
+------------------------+

It is working fine if we use trim,but is not required as the above query shows all the records are having same length.

If we create a temp table with create table test1 as select served_number from test;
select count(*) from test1 where served_number='779294936';
+----------+
| count(*) |
+----------+
| 5 |
+----------+

Then it is working fine.
What can be the reson for this problem?

Thanks
Sri

Hi Sri,

May be your table is partitioned by served_number and partitioning got corrupted.
Queries with conditions like column=constant are optimized and run only on single node, matching the constant.
Queries with LIKE runs on all nodes.

I would recommend to reload data your table:

COPY table TO file
TRUNCATE table
COPY table FROM file

Andrei

Is served_number a character field then? Are there any leading or trailing spaces?

Mason