Query returns incorrect results unless max_query_tasks option set to 1

[Former Member]

On SQL Anywhere 12.01 I have a query that returns incorrect results unless I set the max_query_tasks option to 1. I am wondering if I am missing something in the way I have structured the query or if perhaps this is a bug.

My base query gives me what appears to be the correct result set

select location_number, max(audit_key) as high_audit_key from dba.audit_dimension where dba.audit_dimension.audit_key < 1000000000000000 group by location_number

When I employ the base query as a temporary view, and simply select everything from the temporary view the results are different:

with keys_by_location as (
select location_number, max(audit_key) as high_audit_key from dba.audit_dimension where dba.audit_dimension.audit_key < 1000000000000000 group by location_number)
select * from keys_by_location

If I restrict parallelism with the option max_query_tasks = 1, the results are correct. (The same as running the 1st query above.

with keys_by_location as (select location_number, max(audit_key) as high_audit_key from  dba.audit_dimension where dba.audit_dimension.audit_key < 1000000000000000 group by location_number) select * from keys_by_location option ( max_query_tasks = '1')

The primary key in audit_dimension is a global autoincrement unsigned bigint. The second result set is incorrect in that the high_audit_key associated with each location_number is not always the highest audit_key < 1000000000000000 for that location_number. Though some of the rows are correct and match the results of the 1st and 3rd queries. In all cases the correct set of location_numbers is being returned.

[Breck_Carter]

This article won't answer your question but it might offer some comfort: http://sqlanywhere.blogspot.com/2011/08/new-maxbps768-set-maxquerytasks-1.html

[VolkerBarth]

...and offer some fun with one of my Dilbert favourites:)

[VolkerBarth]

Just a wild guess:

In case you are using an older build (dbsrv12 -v), the following FAQ deals with a - possibly similar - problem with a derived table. And the readme for the 12.0.1.3519 EBF (i.e. the newest available one for Windows) lists a few fixes w.r.t. max_query_tasks and group by.

BTW: I would suggest to add a small sample of the differing result sets - that would make the problem easier to understand (at least for me:)