Problem with DISTINCT + ORDER BY RANDOM()

[ania]

Another problem. This time with DISTINCT and ORDER BY RANDOM()

drop table a;
create table a(
id int
)with structure = vectorwise;

insert into a values(1);
insert into a values(1);
insert into a values(2);
\g

select distinct id from a; \g
1
2
select distinct id from a order by random();\g
1
2
1

So it looks like distinct is not working with 'order by random'.

Regards,
Ania

[marcin]

Hi Ania,
We confiirmed this one and working on it. For our internal folks, this is issue #1971.
Ania, we'll keep you posted.
Marcin

[kiria01]

This is not a vectrorwise bug.

Order by random() is an Ingres extension to the standard which would insist that the order by keys be present in the select list. Ingres simply treats the unknown key as a non-printing column of the select list. Clearly, such an expression will then be included in the coverage of distinct.

[ania]

Do you mean that it should be like that? Is it a feature, not a bug?
Or it is a bug, but not only in VW?

[marcin]

Hi Ania,

I think Ian means that the semantics of this query might be unclear. Still, I believe we have a bug here, as some other queries also show the same behavior, this one in particular:

select t.id, random() from ( select distinct id from a) t order by 2; \p\g

We're looking into it.

[kuonirat]

So the "distinct id" becomes "distinct (id, random())" and in the end the "random()" column is hidden in the output. If the standard requires the presence of the column in the select list, this query should not compile. If it compiles, it breaks the standard, but should work as expected, because the substitution "id -> (id, random())" is not what is expressed in the query.

If we would like to do this "properly", we should write something like this:

select first n distinct id from (
  select id, random() as r from a order by r
) q

Because this is the real point of Ania's query (without first n there would be no reason to order by anything in the subquery). But in Ingres we cannot order rows in subqueries. My point is, the unexpected behaviour should still be considered as a bug or at least as an issue in the standard.

[kuonirat]

Marcin, I haven't noticed Your post before I sent mine

[makni01]

BTW this has been fixed in the latest daily build.

[rhann]

Originally Posted by makni01

BTW this has been fixed in the latest daily build.

Now I'm confused.

It can't have been correct behaviour before and fixed now. If it's changed then either it was broken before or it's broken now. (BTW, I thought the old behaviour was correct. Being counter-intutive or unexpected doesn't make it wrong, and it behaved like that for a reason.)

[kschendel]

Originally Posted by rhann

... and it behaved like that for a reason.)

Or not. I suspect that when the ORDER BY <non-result-list-thing> was implemented, nobody considered DISTINCT. And especially ORDER BY <row-variable-function-thing> with DISTINCT. Another victory for the law of unintended consequences!

I am tempted to say something here about how the original Ingres development team might have viewed this issue, but I would just guessing, so I'll stop here.