Null and concat (Solved)

[Bart Pietercil]

Hi Ivan,

On 5-okt-07, at 18:27, Ivan Smahin wrote:

> 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.
>>>
>
>
> 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

Upon examination of your example I found the culprit to be in the  
definition of the unique method

It was

VarCharField = aTable.CreateVarCharField ("unique_name_organisation", 
1022,EVFlag.fIndexed +EVFlag.fUnique,"CONCAT(typelistname,  
objptr_organisation)")

and it should have been :

VarCharField = aTable.CreateVarCharField ("unique_name_organisation", 
1022,
EVFlag.fIndexed +EVFlag.fUnique+ EVFlag.fNullable,"CONCAT 
(typelistname, objptr_organisation)")
								^^^^^^^^^^^^^^^^^

Now it accepts both procedures and the method field shows <null>.

OK now this brings the question why did it accept (before it was  
nullable) the first procedure. Why did it not complain about a null  
value not being acceptable for the method field?
I believe the error reporting on this case is not what it should be.  
The only error that came was when the second procedure created a  
second record then Valentina complained about duplicate key in method  
unique.

The only answer I see is that the result of the concat (which ought  
to be <null>) was not acceptable to the method and converted somehow  
to "something" else. Again I think it should have errored out on  
"null value not acceptable for field x"

But anyway I can move along now

Thanks again


Bart Pietercil
PS: don't forget on the little error in the WIKI page on concat  
missing ' in the second example