The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
29.3k287438644
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: 755 times

last updated: 17 Jan '12, 12:35