Strange SELECT results

#11

Thanks very much for another excerpt from your forthcoming book "SQL
Anywhere In Depth" by Ivan T. Bowman... what's that? you're not
writing a book? well you should be

IMO folks understand the deterministic-versus-non-deterministic issue
when it comes to the values of the computed columns themselves, for
two reasons: the rules are well explained in the docs, and people are
already used to DEFAULT TIMESTAMP which works more-or-less the same
way.

In fact, one of the reasons computed columns are very valuable is
*because* they extend the DEFAULT TIMESTAMP-type of construction to
user-defined expressions.

It's this "expression matching" stuff that is virtually
undocumented... not only that, but I'm having a hard time figuring out
who needs it.

If I went to the trouble of creating a computed column X based on
<expression>, and I want to refer to the previously-calculated value
in a query, why wouldn't I refer to X? Why would I code <expression>?
How stupid do I have to be? (that was rhetorical, don't answer

And if I *want* to refer to "today's value" of <expression>, why am I
not allowed to write <expression>?

I appreciate that expression matching was probably hard to implement.
So was Java-classes-as-column-datatypes, and IMO like
Java-classes-as-column-datatypes, expression matching should be
dropped. For every one query that benefits *significantly* even though
the author forgot to refer to the computed column instead of the
expression, there are probably two irritating bugs that have to be
chased down because the author did not want, and did not code, but
got, a reference to the computed column.

Another reason to drop it: The doc team has enough to do already

Breck

On 11 Apr 2006 14:25:51 -0700, "Ivan T. Bowman"
<ibowman (AT) ianywhere (DOT) NOSPAM.com> wrote:

Quote:

Expressions in a query are matched with any available computed columns so
that the cost of the computation can be avoided. Further, this matching
allows an index to be used to satisfy a search condition where otherwise a
sequential scan might be needed. Since the original implementation of
computed columns, a few restrictions have been put in place to avoid
unexpected results. Computed columns forbid the use of non-deterministic
functions such as rand() or user functions explicitly marked as
non-deterministic because matching these expressions would expose the types
of anomaly that is described below.

The current behavior matches the origianl design of computed columns, but
the result is indeed surprising and likely not what was intended by the
table creator. A future version of SQL Anywhere will likely either a) forbid
any expression in a compute clause where matching would give anomalies as
described below, or, b) allow such expressions but forbid matching the
computed expression. Also, further documentation of how common
sub-expression matching is performed will be included in a future version of
the software.

Even with those restrictions on allowed expressions or matching, there
remain temporal anomalies that can occur with computed columns. For example,
if a computed column uses a UDF and the UDF is later modified so that it
returns a different result, the computed column will continue to reflect the
old result. Any computed column that relies on server state can also exhibit
these temporal anomalies. One possibility we have discussed in the past is
to disallow computed columns except for those expressions that are
explicitly marked as DETERMINISTIC. Such an approach would avoid anomalies
at the expense of preventing useful implementations. An alternative to
disallowing expressions that might exhibit anomalies would be to give a
warning at the time the computed column is created that the column might
give rise to anomalies in queries, even queries that don't directly
reference the column. These options are under discussion for future versions
of SQL Anywhere.

In any case, given the current behavior of computed columns, you should use
caution when creating a computed column that contains expressions such as
CURRENT XXX, now(), XXX_property(), or so on. The work-around that Bruce
described can be made more robust using a UDF that is not used in any other
query (as shown in the attached example):

create function F_dontmatch_ts( x timestamp )
returns timestamp
begin
return x
end;

create table ttt
(
pk int default autoincrement,
val text,
change datetime compute( F_dontmatch_ts(current timestamp) ),
primary key(pk)
);
insert into ttt(val) values ('t1');
insert into ttt(val) values ('t2');
insert into ttt(val) values ('t3');
select * from ttt;

select current timestamp, * from ttt;

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhe...ers_Guide.html
breck.carter (AT) risingroad (DOT) com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Strange SELECT results

sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general

Re: Strange SELECT results - 04-12-2006 , 08:58 AM