SQL Anywhere database - non-deterministic error

Author

non-deterministic error

Juan Garcia

2005-06-21, 1:23 pm

Using ASA build 9.0.2.3124.

I am getting the "the result returned is non-deterministic" when running the
following select statment:

select first log_id,log_pub
from p_editlog
inner join
WasteStream on
p_editlog.p_id = wastestream.iwts_id
and p_editlog.p_pub = wastestream.iwts_pub
where wastestream.wsid = intWSID
order by p_editlog.edit_date desc;

If I take the "first" clause out then the query returns the ordered result
set. Am I going to have to re-write the procedure using a cursor?

Thanks,
Juan ..............

Glenn Paulley

2005-06-21, 8:23 pm

"Juan Garcia" <jgarcia@stoller.com> wrote in
news:42b84890$1@foru
ms-1-dub:

> Using ASA build 9.0.2.3124.
>
> I am getting the "the result returned is non-deterministic" when
> running the following select statment:
>
> select first log_id,log_pub
> from p_editlog
> inner join
> WasteStream on
> p_editlog.p_id = wastestream.iwts_id
> and p_editlog.p_pub = wastestream.iwts_pub
> where wastestream.wsid = intWSID
> order by p_editlog.edit_date desc;
>
> If I take the "first" clause out then the query returns the ordered
> result set. Am I going to have to re-write the procedure using a
> cursor?
>
> Thanks,
> Juan ..............
>
>
>

The engine issues this warning when a query contains SELECT FIRST (or TOP
N) and does not contain an ORDER BY clause. If the query above is
actually generating this warning (it should not due to the presence of
the ORDER BY) then I would ask that you submit this as a bug case.

In any event, the message is not an error, but a warning; the query will
execute correctly.

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Erik Anderson

2005-06-21, 8:23 pm

Is this warning generated if the ORDER BY does not completely "cover" a pk
or uniqueness constraint, so that queries could still conceivably return
different answers?

"Glenn Paulley" <paulley@ianywhere.com> wrote in message
news:Xns967CB4BD3444
Bpaulleyianywherecom
@10.22.241.106...
> "Juan Garcia" <jgarcia@stoller.com> wrote in
> news:42b84890$1@foru
ms-1-dub:
>
>
> The engine issues this warning when a query contains SELECT FIRST (or TOP
> N) and does not contain an ORDER BY clause. If the query above is
> actually generating this warning (it should not due to the presence of
> the ORDER BY) then I would ask that you submit this as a bug case.
>
> In any event, the message is not an error, but a warning; the query will
> execute correctly.
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288

Glenn Paulley

2005-06-22, 7:23 am

"Erik Anderson" < erikba@teamworkgroup
.com> wrote in
news:42b89567$1@foru
ms-1-dub:

> Is this warning generated if the ORDER BY does not completely "cover"
> a pk or uniqueness constraint, so that queries could still conceivably
> return different answers?
>
> "Glenn Paulley" <paulley@ianywhere.com> wrote in message
> news:Xns967CB4BD3444
Bpaulleyianywherecom
@10.22.241.106...
>
>
>

The warning is not issued as long as there is an ORDER BY clause. Since
SQL result sets can contain duplicate rows (bag semantics), unlike formal
relational algebra, there is no attempt to analyze individual expressions
in the ORDER BY clause.

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Juan Garcia

2005-06-27, 8:23 pm

Glenn/Erik

The query, as submitted, works fine with ASA 9.0.0.1110. It appears that
there is a bug in the later build. I'll submit the bug to Sybase.

Thanks ....................

"Erik Anderson" < erikba@teamworkgroup
.com> wrote in message
news:42b89567$1@foru
ms-1-dub...
> Is this warning generated if the ORDER BY does not completely "cover" a pk
> or uniqueness constraint, so that queries could still conceivably return
> different answers?
>
> "Glenn Paulley" <paulley@ianywhere.com> wrote in message
> news:Xns967CB4BD3444
Bpaulleyianywherecom
@10.22.241.106...
TOP[color=darkred]
>
>

Sponsored Links