Login Register Actian.com  

Actian Community Forum


Go Back   Actian Community Forums > Actian Vector > Actian Vector Discussion
 

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
Old 2011-05-20   #1 (permalink)
Ingres Community
 
Join Date: Oct 2010
Posts: 20
Default Problem with DISTINCT + ORDER BY RANDOM()

Another problem. This time with DISTINCT and ORDER BY RANDOM()
Code:
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
ania is offline   Reply With Quote
Old 2011-05-22   #2 (permalink)
Ingres Community
 
marcin's Avatar
 
Join Date: Oct 2008
Location: Amsterdam, NL
Posts: 297
Default

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
marcin is offline   Reply With Quote
Old 2011-05-24   #3 (permalink)
Ingres Community
 
Join Date: Mar 2007
Posts: 16
Default

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.
kiria01 is offline   Reply With Quote
Old 2011-05-26   #4 (permalink)
Ingres Community
 
Join Date: Oct 2010
Posts: 20
Default

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?
ania is offline   Reply With Quote
Old 2011-05-26   #5 (permalink)
Ingres Community
 
marcin's Avatar
 
Join Date: Oct 2008
Location: Amsterdam, NL
Posts: 297
Default

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.
marcin is offline   Reply With Quote
Old 2011-05-26   #6 (permalink)
Ingres Community
 
Join Date: May 2010
Location: Poland
Posts: 383
Blog Entries: 3
Default

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:

Code:
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.
__________________
Konrad Procak
kuonirat is offline   Reply With Quote
Old 2011-05-26   #7 (permalink)
Ingres Community
 
Join Date: May 2010
Location: Poland
Posts: 383
Blog Entries: 3
Default

Marcin, I haven't noticed Your post before I sent mine
__________________
Konrad Procak
kuonirat is offline   Reply With Quote
Old 2011-05-31   #8 (permalink)
Ingres Community
 
Join Date: Mar 2007
Posts: 270
Default

BTW this has been fixed in the latest daily build.
makni01 is offline   Reply With Quote
Old 2011-05-31   #9 (permalink)
Ingres Community
 
rhann's Avatar
 
Join Date: Mar 2007
Location: roy.hann@rationalcommerce.com
Posts: 1,429
Blog Entries: 32
Default

Quote:
Originally Posted by makni01 View Post
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.)
__________________
Roy Hann
rhann is offline   Reply With Quote
Old 2011-05-31   #10 (permalink)
Ingres Community
 
kschendel's Avatar
 
Join Date: Mar 2007
Location: Pittsburgh, PA
Posts: 2,046
Default

Quote:
Originally Posted by rhann View Post
... 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.
kschendel is offline   Reply With Quote

Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


© 2011 Actian Corporation. All Rights Reserved