GROUP BY RAND() returns infinite rows [CORE1943]

[firebird-automations]

Submitted by: fabianobonin (fabianobonin)

Is related to QA349

The query below returns infinite rows:

select
1
from
rdb$database a
group by
rand()

Commits: f9e3032

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

Fabiano,

This seems to be a "non-sense case" -- it does not reflected anything close to a real-world scenario -- as such, I think this should be considered a "won't fix" case.

Can you explain why GROUP BY RAND() would ever be used?

[firebird-automations]

Commented by: @asfernandes

Database engines should deal with "non-sense cases" too. Job of GROUP BY is not produce rows.

[firebird-automations]

Commented by: @dyemanov

I agree with Adriano. We already had such a problem previously with GROUP BY <constant>. The current one should also be solved. Our GROUP BY processing is somewhat non-optimal (to say at least).

[firebird-automations]

Commented by: fabianobonin (fabianobonin)

In my case, i was grouping by a "case", 'cause i need to group some rows from a procedure and keep others not grouped, but as the procedure didn't have a unique column, i used rand() to fake one. I fixed it changing the procedure to return a unique column and using it instead.

group by
case when ... then ... else rand() end

[firebird-automations]

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5 Beta 1 [ 10251 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA349 [ QA349 ]

[firebird-automations]

Commented by: @pcisar

QA test added.

[firebird-automations]

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test => Done successfully