Re[4]: [Firebird-devel] Null ordering in select expression, my TODO list

[Nickolay S. <sk...@bs...>]

Hello firebird-devel-admin,

Sunday, September 08, 2002, 8:43:01 PM, you wrote:

> Nickolay,

>> My TODO:
>>
>> Features:
>> 1) nulls ordering,
>> 2) disable/enable constraint clause

> Both seems good, IMO.

>> 3) allow using "order by" clause in TOP-N subquery (if it is not yet
>> done)

4) I also want to allow unique constraints on nullable fields. SQL92
allows to have one null value in unique index.

> Do you mean allowing usage of ORDER BY in selection expressions? In all of
> them? In subqueries only? In subqueries with FIRST..SKIP subclause only? Or
> is it anything completely different?

with FIRST..SKIP subclause only - like in MSSQL

>> Bugs (not tested by our QA on FB1.5 yet, but exist in 1.0-796):
>> 1) lots of DDL bugs (I posted a list),

> IIRC, a few of them are definitely not bugs, but "as designed" features,
> whilst others are real bugs. I'll comment on them later.

>> 2) outer join bugs (outer join works like inner if join
>> expression uses fields from one index twice),

> Didn't know about this one, but you're welcome to solve it.

Very easy to reproduce:

create table test2 (id integer not null primary key)
create table test1 (id integer not null primary key)
create table test (id integer not null primary key, test1_id integer
references test1, test2_id integer references test2)

insert into test2 values (0)
insert into test1 values (0)
insert into test values (1, null, null)

SELECT test.*
FROM test t
  LEFT JOIN test1 t1 ON t1.ID = t.test1_id AND t1.ID <> 0
  LEFT JOIN test2 t2 ON t2.ID = t.test2_id AND t2.ID <> 0

returns ONE row correctly

SELECT test.*
FROM test t
  LEFT JOIN test1 t1 ON t1.ID = t.test1_id AND t1.ID > 0
  LEFT JOIN test2 t2 ON t2.ID = t.test2_id AND t2.ID > 0
  
returns NO rows

There are many simular cases of failure when outer joins with complex
join expressions are used


>> 3) query parameter bugs ("SELECT FIRST ? ..." syntax fails in some
>> cases, query parameters from fields go after all other params, etc),

> Could you be more specific here?

I posted example of one this bug incarnation to the tracker a
couple weeks ago.

>> 4) some bugs in JCA JDBC driver (all known are fixed, but one
>> my patch is
>> not yet applied by Firebird team - it makes driver handle serializable
>> mode correctly, current CVS driver handles SERIALIZABLE mode correctly
>> only when connection is in REPEATABLE_READ mode :))) )

> I remember this one, but it seems you haven't achieved an agreement with
> David Jencks regarding it.

Last thing he did he posted mail here and asked Ann Harrison if my
posting about SNAPSHOT and SERIALIZEABLE equivalence right. Silence
was an answer as I remember.

> Dmitry

-- 
Best regards,
 Nickolay                            mailto:sk...@bs...