Issue Details (XML | Word | Printable)

Key: CORE-2134
Type: New Feature New Feature
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Igor Lobov
Votes: 2
Watchers: 4
Operations

If you were logged in you would be able to see more operations.
Firebird Core

New additional functions similar LIST

Created: 20/Oct/08 05:10 PM   Updated: 25/Oct/08 10:40 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: None


 Description  « Hide
Create additional functions that are similar LIST, but returns only FIRST, LAST or ANY value from the list.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Bjoern Reimer added a comment - 20/Oct/08 05:34 PM
Wouldn't MAX and MIN do the job?

Igor Lobov added a comment - 20/Oct/08 06:48 PM - edited
No. I think MAX and MIN is not applicable in lots cases.

For example:

Table
    FIELD1, FIELD2, FIELD3
 
    6 2 1
    4 2 2
    5 2 3
   

SELECT FIELD2, COUNT(FIELD2), MIN(FIELD3), FIRST(FIELD3)
GROUP BY FIELD2
ORDER BY FIELD1

In this SELECT the functions MIN and FIRST will return different value.

The result of MIN will be "1", but the result of FIRST will be "2"

Adriano dos Santos Fernandes added a comment - 21/Oct/08 12:28 PM
Your desire will be achievable when full support of window functions is present. Something like:
select
    field2,
    count(field2),
    min(field3)
    min(field3) over (
      partition by field2
      order by field1
      range between unbounded preceding and unbounded following
    ) first
  from table;

Igor Lobov added a comment - 21/Oct/08 01:47 PM - edited
Yes, this good way is fit for me.
I hope it will be realized in nearest future.

But ORACLE SQL that includes window functions contains FIRST_VALUE() and LAST_VALUE(), which return the first and last values in a window.

Smirnoff Serg added a comment - 22/Oct/08 06:25 AM
select field2, cast(substring(min(lpad(field1, 10, '0') || field3) from 11) as int)
  from Table
  group by 1

Igor Lobov added a comment - 22/Oct/08 07:14 AM
May be
select field2, cast(substring(min(lpad(cast(field1 as varchar(10)), 10, '0') || field3) from 11) as int)
                                                                     ^^^^^^^^^^^^^^^^^^^^^^^^
  from Table
  group by 1

But if I have more than one group field I obtain very long expression.
I think the realization of FIRST would be more short and universal solution of this problem.

Smirnoff Serg added a comment - 22/Oct/08 10:56 AM
1. There's no need to cast int as varchar. Moreover, no need external cast.
2. Ok, how about another solutions:

select t1.field2, (select first 1 field3 from Table where field2 = t1.field2 order by field1)
  from Table t1
  group by 1

execute block returns (field2 int, field3 int) as
  declare variable cur_field2 int = -1;
begin
  for select field2, field3 from Table order by 1, 2 into field2, field3
  do
    if (field2 != cur_field2) then
    begin
      suspend; cur_field2 = field2;
    end
end

Igor Lobov added a comment - 24/Oct/08 02:36 PM - edited
I think that a lot of different solutions exist in the world.
As for me exist two very desired criteries:
1. Performance
2. Simplicity

I created the simple database with a single table
CREATE TABLE TEST_TABLE (
    FIELD1 INTEGER,
    FIELD2 INTEGER,
    FIELD3 INTEGER
);

I inserted 1000000 records into the table.
And I executed some queries:

Query 1

SELECT FIELD2, MIN(FIELD3) FROM TEST_TABLE
GROUP BY FIELD2

Execute time = 2s 797ms

Query 2

SELECT FIELD2, LIST(FIELD3) FROM TEST_TABLE
GROUP BY FIELD2

Query 3

Execute time = 3s 359ms

SELECT FIELD2, CAST(SUBSTRING(MIN(LPAD(FIELD1, 10, '0') || FIELD3) FROM 11) AS INT) FROM TEST_TABLE GROUP BY 1

Execute time = 3s 422ms

Query 4


SELECT T1.FIELD2, (SELECT FIRST 1 FIELD3 FROM TEST_TABLE WHERE FIELD2 = T1.FIELD2 ORDER BY FIELD1) FROM TEST_TABLE T1
  GROUP BY 1

Execute time = 4m 8s 890ms

Query 5

EXECUTE BLOCK RETURNS (FIELD2 INT, FIELD3 INT) AS
   DECLARE VARIABLE CUR_FIELD2 INT = -1;
BEGIN
   FOR SELECT FIELD2, FIELD3 FROM TEST_TABLE ORDER BY 1, FIELD1, 3 INTO FIELD2, FIELD3
  DO
    IF (FIELD2 != CUR_FIELD2) THEN
    BEGIN
      SUSPEND; CUR_FIELD2 = FIELD2;
    END
END

Execute time = 3s 500ms

First and second query were written as a sample of performance.
Query 3 has good performance but it is not universal.
The performance of Query 4 is bad very much.
Query 5 was changed a little bit (because yours was mistaken). The performance of the query is good, but it is not universal too.

I think that built-in function could be the universal and high-speed solution.

Firebird 2.1.1 for Linux AMD64 under Fedora 9 x86_64 was used for my experiment.

Smirnoff Serg added a comment - 25/Oct/08 10:21 AM
> The performance of Query 4 is bad very much.

CREATE INDEX IDX1 ON TEST_TABLE (FIELD2, FIELD1);

Igor Lobov added a comment - 25/Oct/08 10:36 AM - edited
You can not add index to every field.
That is not very rational solution.