Query returning incorrect results..

This message was edited 1 time. Last update was at 11 Aug 2008 07:30:42

Hi Mason,
We are testing GridSQL with Postgresplus. When I issue the below query I am getting incorrect result..

GridSQL -> select calledpartynumber from test_cdr
GridSQL -> where substr(calledpartynumber,1,2) not in ('11','12');
+-------------------+
| calledpartynumber |
+-------------------+
| 1194775588345 |
| 12456 |
+-------------------+
2 row(s).

Whereas, the below query also gives the same result..

GridSQL -> select calledpartynumber from test_cdr
GridSQL -> where substr(calledpartynumber,1,2) in ('11','12');
+-------------------+
| calledpartynumber |
+-------------------+
| 1194775588345 |
| 12456 |
+-------------------+
2 row(s).

And another is when giving limit and offset in a single query is resulting no rows even there are rows in table.

Thanks,
Venkat

Yikes, this is a bug!

I can see that when the condition is rebuilt, it is leaving off the all important "NOT". We will get this fixed right away.

I also double-checked IN and NOT IN for subqueries, and those are working properly.

Thanks,

Mason

I also wanted to add, that you can do the following in the meantime as a work-around, until this is fixed:

select calledpartynumber from test_cdr
where not substr(calledpartynumber,1,2) in ('11','12');

Note, I added NOT instead at the beginning of the condition.

Thanks,

Mason

This has been fixed and checked into CVS:

http://forums.enterprisedb.com/posts/list/0/1388.page#4936

Thanks,

Mason