ADQL Erratum 2

[Mark Taylor]

On Tue, 30 May 2017, Markus Demleitner wrote:

> However,
> 
> > Now, I understand that different backends act differently, and that
> > the usage of the seed value itself is quite confused/confusing,
> > but if we go too far, we may slip out of the context for an erratum.
> 
> goes against ADQL's purpose of having an interoperable syntax and
> semantics for an SQL dialect.  Worse, at least some backends (Gregory
> tried it with H2; my former implementation did about the same) have
> utterly useless and dangerous behaviour: they seed the RNG in each
> row, which means that all calls to random() yield the same value(s)
> in all rows.
> 
> Given that, I think we have to warn people that using the argument
> will very likely not do what at least I would expect: for each
> occurrence of random(n) generate an independent, reproducable
> sequence 
> 
>   row-index -> pseudo random number
> 
> I'm not even sure if there is any DB engine implementing this at all.
> 
> Explaining this in this many words is, I'd say, a bit too much for
> this table.  So, here's my second attempt to provide a text for the
> table:
> 
>   rand([x]) -- Returns a random value between 0.0 and 1.0.  The
>   optional argument, originally intended to provide a random seed,
>   should not be used.  Behaviour for the function with an argument is
>   undefined.  Query writers should not use it.
> 
> Opinions?

I'd be OK with that, though it sounds slightly heavy handed.
Some ADQL implementation/DBMS might do something useful with x,
and if a query writer understands that is the case they might
legitimately want to use it.  Suggest instead:

    rand([x]) -- Returns a random value between 0.0 and 1.0.  The
    optional argument, originally intended to provide a random seed,
    has undefined semantics.  Query writers are advised to use the
    form without the argument.

But, if Markus's suggested text is used instead (e.g. because such
implementation-aware ADQL query construction is deemed evil) it's OK.

Mark

--
Mark Taylor   Astronomical Programmer   Physics, Bristol University, UK
m.b.taylor at bris.ac.uk +44-117-9288776  http://www.star.bris.ac.uk/~mbt/