Query results on greenplum/Postgres boolean variable

[BijZuiderduin]

I query a Greenplum/Postgres database on a Boolean variable with a similar query in two different ways. Functionaly they are the same but I get different results. Can anybody explain this and perhaps help me with a solution?

• Functionally I need the two different ways in the application as a before and after check
• Unfortunately I cannot access the database directly. 

• Example 1 directly on the greenplum table

create table countdirect as 

"before" as method length=15, 

count(*) as countdirect 

from lib.table

where booleanfield;

• Example 2 in 2 steps via SAS table

create table temp_table (compress=yes) as select

base.c_custom_documentid as id ,

from lib.table base 

where booleanfield ;

create table countindirect as 

"after" as method length=15, 

count(*) as countindirect 

from temp_table;

Remarks:

1. ‘booleanfield’ is a Greenplum/Postgres Boolean variable

2. Running in SAS DI and SAS EG both examples deliver matching results

3. Running in LSF the results of Example 1 and 2 are different

4. When running from LSF the results of the first example differ per run 

5. The result of Example 2 are constant and seem correct

The results per run:

	before	after
Day 1 run 1	1.285.585	1.287.365
Day 1 run 2	1.285.521	1.287.365
Day 2 run 1	1.285.586	1.287.365
Day 2 run 2	1.285.521	1.287.365

[s_lassen]

I assume the by "LSF" you mean either the IBM Load Sharing Facility, or the similar named facility from Platfor Computing (or maybe the two are the same?). I am not sure, but I think that what happens is that when querying in LSF, some records are not read, possibly because they are locked by other users, as the result from the first run is always smaller than the result from the second run.

What to do about it? Sorry, no idea.

[BijZuiderduin]

Thx for your reaction. I think the 2 LSF's are indeed the same.
Regarding your suggestion that records may be locked using the first example: this does not happen using the second example; to me this seems to indicate another cause.