Null and concat

[Ivan Smahin]

Hello Bart,

Friday, October 5, 2007, 3:55:41 PM, you wrote:

> Hi Thorsten,
> On 5-okt-07, at 14:38, Thorsten Hohage wrote:

>> Hi Bart,
>>
>> On 2007-10-05, at 14:03, Bart Pietercil wrote:
>>
>>> SELECT CONCAT('Va’, NULL, ‘na’);
>>>
>>> crashes VStudio
>>
>> no - I got a "unexpected token NULL"
>>
>>> SELECT CONCAT('Va’, NULL)
>>> I get unexpected token NULL.
>>
>> same here and IMHO this is ok, because NOBODY would type a constant  
>> NULL in a concat statement.
>>


> call me NOBODY

> I have defined a unique_test method(field) in a table.
> Table has fields

>      aTable = CreateTable("tbl_typelists",EVTableKind.kTblPermanent)

>      VarCharField = aTable.CreateVarCharField("typelistname", 
> 512,EVFlag.fIndexed)
>      VarCharField = aTable.CreateVarCharField("created_by", 
> 250,EVFlag.fIndexed)

>      DateTimeField = aTable.CreateDateTimeField("creation_dt")

>      PtrField = aTable.CreateObjectPtrField 
> ("objptr_organisation",Table 
> ("tbl_organisations"),EVOnDelete.kCascade,EVFlag.fIndexed 
> +EVFlag.fNullable,"typelist_for_organisation")
>     VarCharField = aTable.CreateVarCharField 
> ("unique_name_organisation",1022,EVFlag.fIndexed 
> +EVFlag.fUnique,"CONCAT(typelistname, objptr_organisation)")


> It is this last one that is giving me trouble

> Since objptr_organisation can be NULL my method in  
> unique_name_organisation returns in that case a NULL value. I would  
> have preferred it would return the typelistname, but I can understand
> I returns null (because a param is null).

> so I call my stored procedure

> create or replace procedure cm_spBuildUserTypes()
> begin
> DECLARE mylastrecid Long;
> insert into tbl_typelists(created_by,typelistname,creation_dt) values
> ('cm_spBuildUserTypes','usertypes',NOW());
> set mylastrecid = select recid FROM tbl_typelists where typelistname  
> = 'usertypes';
> insert into
> tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
> values ('active',1,mylastrecid);
> insert into
> tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
> values ('deactivated',2,mylastrecid);
> insert into
> tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
> values ('blocked',3,mylastrecid);
> exception
> when others then
> call cm_CoreErrorHandler();
> end;

> no problem (but mind you objptr_organisation is NULL, so  
> unique_name_organisation is also NULL)

> now I call second procedure

> create or replace procedure cm_spBuildUserRoles()
> begin
> DECLARE mylastrecid Long;
> insert into tbl_typelists(created_by,typelistname) values  
> ('cm_spBuildUserRoles','userroles');
> set mylastrecid = select Last_RecID();
> insert into
> tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
> values ('Coachee',1,mylastrecid);
> insert into
> tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
> values ('Coach',2,mylastrecid);
> insert into
> tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
> values ('Administrator',3,mylastrecid);
> insert into
> tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
> values ('Tutor Administrator',4,mylastrecid);
> insert into
> tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
> values ('Tutor Editor',5,mylastrecid);
> insert into
> tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
> values ('Tutor Reviewer',6,mylastrecid);
> insert into
> tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
> values ('External Trainer',7,mylastrecid);
> insert into
> tbl_typelistvalues(displayname,listvalue,objptr_typelist)  
> values ('Competence Editor',8,mylastrecid);
> exception
> when others then
> call cm_CoreErrorHandler();
> end;


> This now fails with unique constraint violation because also for this
> list the objptr_organisation = NULL so unique_name_organisation = NULL.

> Now the strange thing is I asked yesterday Ruslan specificly about  
> NULL values in UNIQUES and he assured me that NULLs are excluded from
> the index.
> So now I wonder why I am getting this message about unique constrain  
> violation.

> Mind you at this point there is 1 record in the table (the first list
> created by the first procedure)


> Any insight welcome

I'm not able to reproduce it. Please try following statements in order
to see is it just a fixed issue or you do something special...

CREATE TABLE "t2" ( "f1" LONG );
CREATE TABLE "t3" (
        "ptr" ULONG  INDEXED,
        "f1" STRING (20) ,
        "m1" STRING (20) METHOD ('concat( f1, ptr )')  UNIQUE INDEXED);
ALTER TABLE "t3" MODIFY ptr OBJECTPTR CONSTRAINT "l1" REFERENCES "t2" ON DELETE RESTRICT INDEXED;


INSERT INTO "t2" ("f1") VALUES (1);
INSERT INTO "t2" ("f1") VALUES (2);
INSERT INTO "t2" ("f1") VALUES (3);

INSERT INTO "t3" ("ptr", "f1") VALUES (2, '33');
INSERT INTO "t3" ("ptr", "f1") VALUES (NULL, NULL);
INSERT INTO "t3" ("ptr", "f1") VALUES (NULL, '123456');

--
SELECT ** FROM t3



-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com