possible problem with count (*) in ASE 15.5

[Former Member]

We have noticed that in rare occasions the count(*) on a stable table (in which there are not lots of inserts, updates, deletes) the count(*) returns a count which is a few records less than expected (instead of 1,412,444 which returned normally on a given table, we sometimes buit rarely  get 1,412,439 for example).

Has anyone else noticed such an anomaly?

A.N

[Former Member]

Thanks you Mark fro your approach to my query:

Here you are with what is exactly happening:

1- 3 times a day data is inserted into the table MYTBL from 3 vender files at 09:30 AM 11:30 AM and at 17:30 (In the files sent, there is a columns called acknowledgd, its value is always 9:30,11:30 or 17:30)

2- We run the following select at 18:00

SELECT acknowledgd, no_record=count(*) FROM MYTBLE where acknowledgd > convert(char(8),getdate(),112) group by brc_acknowledgd

We naturally obtain 3 rows:

Acknowledgd          no_record

----------------     ---------

2014/10/30 09:30      N1

2014/10/30 11:30      N2

2014/10/30 17:30      N3 

N1, N2 and N3 vary from one day to another.

Very often the above result corresponds to the real row numbers, but every now and then the third number is a few records less (N3 -3 to 4).

3- To verify the real numbers we rerun the same query again or run  following query at just at or after 6 PM:

SELECT acknowledgd FROM MYTBLE where acknowledgd > convert(char(8),getdate(),112) order by 1

Re-running the first query agin return teh correct number:

SELECT acknowledgd, no_record=count(*) FROM MYTBLE where acknowledgd > convert(char(8),getdate(),112) group by brc_acknowledgd

We naturally obtain 3 rows with correct numbers:

Acknowledgd          no_record

----------------     ---------

2014/10/30 09:30      N1

2014/10/30 11:30      N2

2014/10/30 17:30      N3 or N3 + 3 4

I find thsi rather odd, and have no explanation for my observation.

Hope that my explanation is clear enough. Thinking and expressing yourself in a language which is not your native one is often cumbersome; at least for me, it is like that in all 4 foreign languages that I know, sorry for my shortcoming.

[Mark_A_Parsons]

- first possible issue I see is the non-ANSI group by where the 'acknowledgd' column shows up in the SELECT list but the 'brc_acknowledgd' column shows up in the GROUP BY clause; while this type of query is 'legal' in ASE it can cause unwanted/unexpected results; also, it's not apparent (to me) from your post if these are 2 different columns or if you've made a cut-n-paste/edit error; if there are 2 different columns then I would want to investigate the data in these 2 columns to see if they are what you expected; though ideally I'd want to rewrite the query to be ANSI compliant and therefore easier to understand/verify the results, eg:

   select acknowledgd, no_record=count(*)

   from MYTBLE

   where acknowledgd > convert(char(8),getdate(),112)

   group by acknowledgd

- you've mentioned that you run the current query at 18:00 and get the wrong results, but if you run the same query again at/around 18:00 (I'm assuming slightly after 18:00??) then you get the correct results; are you really running this query twice in (relatively) quick succession and getting different results?

- your first post seems to indicate that inserts/updates/deletes could be running at the time; could the 17:30 file processing still be running when you run your 18:00 query (eg, processing running late due to larger data set, or late start, or unexpected delays/blocking)?  can you verify you don't have any other processing running at/near 18:00 that could be skewing your query results?

- do you *always* get the 'correct' results after running your query a 2nd time?

- is N3 the only count that ever comes up 'wrong', ie, N1/N2 are *always* correct? and does a subsequent run of the query always generate the 'correct' N3 value? if you answer 'yes' to both of these questions then I'm going to lean towards the following as a possible explanation: the 17:30 file processing is still running @ 18:00 thus leading to unexpected counts for N3 (ie, the expected 17:30 process counts), but a later run of the query provides a correct N3 value (because the 17:30 file processing has completed)

[Former Member]

Thanks again Mark, for spending time on my post. The name of column in the group by is just a typo. the group by in all cases is on the same column.

As for the possibility of the third run is not finished, when we run the group by, I can confirm that it is not the case, as we do the work in a set of autosys job in which the consition for the group by job is the (success) of the insert job. I wonder if when a insert statement is finished and committed and the script running the command has also finished, there can still be the remenant of insert going on. To be clear, let me explain the way that our treatment si done:

An autosys box starts at 9"30, 11:30, and 17:30, when it stars the following treatment is done:

1- Vender file is fetched Job1

2- the file is loaded into teh database and the insert is committed job2

3- Group by job runs and indicates how many rows are inserted since the first run job3

4- A job runs to compare the files and rows inserted and if there is a different of number of rows in teh files and what is inserted an alert is created. Job 4

In the chain of jobs, the start of J2 depends on the success of J1, j3 depends on success of j2 and J4 depends on the suvccess of J3.

To me what might happen here is one of the follwoing :

a- Either not all the inserted rows are committed when J4 starts, which should be impossible as the whole insert is one statement, no cursor of row by row insert is envolved.

b- count does not really count the row but calculates it in a particular algorithm

c- We are hitting a rare occuring bug of ASE 15.

Thanks again.

[Mark_A_Parsons]

- you've stated the summary query runs @ 18:00; you've also stated the summary query has a dependency on the data load process; what happens if the query/job runs @ 18:00 but the data load process is not yet complete?

- have the autosys admin verify that the job dependencies are properly defined and enforced

- verify the data load process is not kicking off a (sub)process in the background ... which would return control to autosys *before* the data load process actually completes

- check the logs for the data load process to see when it actually completes (eg, the N3-related job finishes @ 18:02); if this info is not available in the process log then it should be added

- make sure the database where MYTBLE resides is not configured with 'delayed commit' enabled; alternatively post the complete output of 'sp_helpdb <name_of_db_where_MYTBLE_resides>'