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.

asked 12 Jan '12, 11:59

jill's gravatar image

jill
106229
accept rate: 0%

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

(12 Jan '12, 17:53) Breck Carter
Replies hidden

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

(13 Jan '12, 03:39) Volker Barth

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:)

permanent link

answered 12 Jan '12, 12:17

Volker%20Barth's gravatar image

Volker Barth
30.6k304455662
accept rate: 32%

edited 12 Jan '12, 12:21

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×7
×3

question asked: 12 Jan '12, 11:59

question was seen: 831 times

last updated: 17 Jan '12, 12:35