In theory, INSERTs using a query are suppose to update the table at the same time so the CURRENT_TIMESTAMP really should give the same date/time for every row.

The same applies to UPDATEs. All affected rows are changed at the same time and the assignments are performed simultaneously.

Therefore,

UPDATE aTable
  SET X = Y, Y = X

should swap the values in the columns - which is not happening.

--
Richard Harding
Windella Computer Knowhow
28 Freeman Drive
Lochinvar NSW 2321
Australia
Phone:   61 2 4930 7336
Mobile:   0419 016 032
email:   rharding@wck.com.au

Richard,

<< In theory, INSERTs using a query are suppose to update the table at the
same time so the CURRENT_TIMESTAMP really should give the same date/time for
every row.

The same applies to UPDATEs. All affected rows are changed at the same time
and the assignments are performed simultaneously. >>

Yes, this is how the SQL standard specifies it, and also how other database
engines (SQL Server, Oracle, Sybase ASA, Mimer, MySQL, Interbase, Firebird,
NexusDB) do it.

EDB and DBISAM are obviously out of synch with the industry standard here.

Ole Willy Tuv

Richard,

<< Therefore,

UPDATE aTable
 SET X = Y, Y = X

should swap the values in the columns - which is not happening. >>

That certainly looks like a serious bug.

Ole Willy Tuv

Chris,

<< Isn't it just possible that the function was called 1 million times, but
the value returned only changed every 204th call because the processing was
so fast? >>

Yes, that's exactly what happened.  I should have pointed that out
originally.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

<< That certainly looks like a serious bug. >>

Really ?  Do you think anyone would expect that UPDATE to not result in the
same value for both columns ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Richard,

<< In theory, INSERTs using a query are suppose to update the table at the
same time so the CURRENT_TIMESTAMP really should give the same date/time for
every row. >>

We're not dealing with theory, unfortunately.  We actually have to make
these things work.

<< The same applies to UPDATEs. All affected rows are changed at the same
time and the assignments are performed simultaneously.

Therefore,

UPDATE aTable
  SET X = Y, Y = X

should swap the values in the columns - which is not happening. >>

I'm sorry, but EDB will never be able to execute the above query and swap
the values without a major rewrite to the way the expression evaluation
works.  It just simply isn't possible.  An assignment to a column results in
the column being assigned the value, and that value is present for all
subsequent assignments.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

<< EDB and DBISAM are obviously out of synch with the industry standard
here. >>

Yes, and frankly, this is such a minor issue that I really don't care at
this point.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Really ?  Do you think anyone would expect that UPDATE to not result in
the same value for both columns ? >>

Yes, I do

What makes me think so is that SQL Server, Oracle, Sybase ASA, PostgreSQL
and NexusDB produce the result Richard and I expected.

Interbase, Firebird and MySQL produce the same result as EDB.

Ole Willy Tuv

Tim,

<< What makes me think so is that SQL Server, Oracle, Sybase ASA, PostgreSQL
and NexusDB produce the result Richard and I expected. >>

I forgot to include Mimer SQL in that list. Since Mimer is highly standard
SQL compliant, I'd assume that the result produced by these engines is
according to the SQL specifications.

Ole Willy Tuv

Ole,

<< Yes, I do >>

Well, what I meant was that if you didn't know the SQL spec behavior, would
you expect that result.  There's not a programming language on the planet
that would execute the assignments as SQL does, so I would expect that most
developers would initially be confounded by the spec behavior.  In fact, I
would argue that the behavior is extremely counter-intuitive given that SQL
normally follows a simple left-to-right evaluation pattern.

--
Tim Young
Elevate Software
www.elevatesoft.com