RAND function results changed [CR 397838]

The values returned by RAND vary depending on whether you use a FROM clause or not and whether the referenced table was created in SYSTEM or in an IQ store.

If RAND is called with a constant or no argument in a query containing only tables in IQ stores, Sybase IQ evaluates the function once per query.

If RAND is called with a FROM clause and an argument in a query containing only tables in IQ stores, the function returns an arbitrary but repeatable value.

When no argument is called, RAND is a non-deterministic function. Successive calls to RAND may return different values. The query optimizer does not cache the results of the RAND function.

The following example will be added to the Sybase IQ Reference Manual:

The following statement returns an arbitrary 5% sampling of a table:

SELECT AVG(table1.number_of_cars),
AVG(table1.number_of_tvs)
FROM table1 WHERE RAND(ROWID(table1)) < .05 
and table1.income < 50000;