Adaptive Server Anywhere Database Engine Version 9.0.2.3228 on WinXP

strange effect when select list includes function and the table selected
from includes computed column that matches the function. Really simple
reproducible shown below.

create table ttt
(
pk int default autoincrement,
val text,
change datetime compute(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;
-- The bizarre bit is that the first column name reported and the values for
that column seem to have picked up on the 'change' computed column and
decided just to use that

-- by contrast the following query does exactly what you would expect:
select current utc timestamp, * from ttt;

Any ideas?

TIA

John Davey

I vote "bug" ...

Just to be *absolutely* clear about what you have discovered:

select current timestamp, * from ttt;

returns this:

change,pk,val,change
'2006-04-07 08:04:12.234',1,'t1','2006-04-07 08:04:12.234'
'2006-04-07 08:04:12.250',2,'t2','2006-04-07 08:04:12.250'
'2006-04-07 08:04:12.250',3,'t3','2006-04-07 08:04:12.250'

It has displayed ttt.change instead of CURRENT TIMESTAMP in the first
column.

Breck


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

I suspect what you intended when you created the table would have been
accomplished with:

create table ttt(
pk int default autoincrement,
val text,
change datetime default timestamp,
primary key(pk)
)

The value of "change" will be updated whenever a row in ttt is modified.

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

"John Davey" <john.davey (AT) automationpartnership (DOT) com> wrote

exactly correct - that is what I found odd

It seems like maybe the engine has spotted a computed column which has the
same definition as the required select value, and chosen to return that. In
the docs the 'working with computed columns' page says

"During query optimization, the Adaptive Server Anywhere optimizer
automatically attempts to transform a predicate involving a complex
expression into one that simply refers to the computed column's definition."

Not clear if this is intended to refer to the select list as well. Generally
a good idea, but not so good if the computed expression is volatile.

I think bug too - will submit a case unless other responses to this post
explain otherwise.

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter (AT) risingroad (DOT) com> wrote in
message news:3elc3211noun68fsbjpv0l0aomqvip5kia (AT) 4ax (DOT) com...

I understand what you are saying. For other reasons (in the real case from
which this minimal example is abstracted) we chose not to use default
timestamp. I think the behaviour given this choice is a bit odd. I can't
right now come up with another example of the same problem that isn't
avoided by the default timestamp approach, but I'm fairly sure such a thing
could be found.

The reason we avoided default timestamp is to do with the whole 'unique
timestamp' behaviour this has. We have restricted timestamps in our database
to 3dp precision (ie only to the millisecond) - this using
TRUNCATE_TIMESTAMP_VALUES and DEFAULT_TIMESTAMP_INCREMENT options. Having
done this we find that columns defined with default timestamp as a way of
capturing change time start to diverge from real time when we have a high
rate of row change (the unique constraint means that if we have more than 1
row being changed per ms the timestamps applied start to run ahead of real
time - quite confusing). If we could solve this another way I guess we could
revert to using default timestamp.

John Davey


"Bruce Hay" <h_a_y~a_t~i_a_n_y_w_h_e_r_e~d_o_t~c_o_m> wrote

On 7 Apr 2006 07:27:39 -0800, "Bruce Hay"
<h_a_y~a_t~i_a_n_y_w_h_e_r_e~d_o_t~c_o_m> wrote:

That may be a workaround for that particular case, but not here; it is
simply returning the *wrong value* for the first select item:

create table ttt
(
pk int default autoincrement,
val text,
change datetime compute(current timestamp+1),
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+1, * from ttt;

change,pk,val,change
'2006-04-08 09:46:25.281',1,'t1','2006-04-08 09:46:25.281'
'2006-04-08 09:46:25.296',2,'t2','2006-04-08 09:46:25.296'
'2006-04-08 09:46:25.296',3,'t3','2006-04-08 09:46:25.296'

Breck

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

On 7 Apr 2006 07:40:23 -0800, "John Davey"
<john.davey (AT) automationpartnership (DOT) com> wrote:

I don't think it should do even that, in this case, and certainly not
transform a select list item... not one involving current timestamp
and the like.

Breck


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

The server uses the value of a computed column whenever it sees an
expression matching the column's COMPUTE definition. That includes the
SELECT list as well as in predicates. It would be a serious limitation to do
otherwise. As Breck has mentioned, you'll likely get unexpected results when
executing:
select * from ttt where change=current timestamp
==> all rows returns

A workaround is to define the "change" column using an expression that is
unlikely to match one that you will use elsewhere when referencing the
table, and yet still evaluates to the current date/time. For example:

create table ttt(
pk int default autoincrement,
val text,
change datetime compute(dateadd(ms,0,current timestamp)),
primary key(pk)
);
insert into ttt(val) values ('t1');
insert into ttt(val) values ('t2');
insert into ttt(val) values ('t3');
waitfor delay '00:00:02';
select current timestamp,change from ttt;

"John Davey" <john.davey (AT) automationpartnership (DOT) com> wrote

On 7 Apr 2006 09:47:37 -0800, "Bruce Hay"
<h_a_y~a_t~i_a_n_y_w_h_e_r_e~d_o_t~c_o_m> wrote:

OK, it's a feature, not a bug <g>... however, allow me to *exhort*...
please ask the doc team to expand the "During query optimization..."
sentence below to (a) mention other parts of the SELECT being
transformed and (b) warn about unexpected query results because of
these transformations.

=====
ASA SQL User's Guide
Working with Database Objects
Working with tables
Working with computed columns

A computed column is a column whose value is an expression that can
refer to the values of other columns, termed dependent columns, in the
same row. Computed columns are especially useful in situations where
you want to index a complex expression that may include the values of
one or more dependent columns. During query optimization, the Adaptive
Server Anywhere optimizer automatically attempts to transform a
predicate involving a complex expression into one that simply refers
to the computed column's definition.
=====

Breck

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

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;

--
Ivan Bowman
ASA Query Processing
iAnywhere Solutions Engineering