Forum

General discussion on using the Kognitio Analytical Platform.
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Getting variable / non-determinstic results from a query

by markc » Thu Nov 06, 2014 4:59 pm

I have a query which is giving me variable results on each run. Is this a bug?

For example:

>create table variable disk from select 1 a, value b from values between 1 and 1000000;
Query 1 Complete ---- 0:09.7 0:09.7 0:09.7
>set maxrows 3;
>select b from variable order by a;
B
965181
965177
965173
Query 2 3 rows(max) ---- 0:00.0 0:00.5 0:00.5
>!!
select b from variable order by a;
B
965177
965173
965169
Query 3 3 rows(max) ---- 0:00.0 0:00.5 0:00.5
>
Reply with quote Top
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: Getting variable / non-determinstic results from a query

by markc » Thu Nov 06, 2014 5:04 pm

The example given is not a bug, it is just a result of not specifying the query sufficiently to get deterministic results.

In this case, the ORDER BY clause does not specify a unique ordering, so the software is free to return rows in any order as long as they satisfy the ordering specified. In this case, all rows have the same value for the ordered column, so they can be returned in ANY order.

The same sort of problem can occur if:
1) a similar non-unique ordering is specified in a window function, so each partition can return different results each time.
2) floating point aggregation is used, which can lead to different results depending on the order in which rows are aggregated (as adding lots of small FP values then larger ones will give a different result to adding those same values in the opposite order with FP).
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron