Hi there,

we wondered why a few of our triggers dont work anymore... (version 2.28b5 is the last working version)
we found out that:

 if standard null behavior is enabled string consts can be miss-parsed and lead to a ?internal-crash? of the engine.

e.g.
 the const string 'NULL' will lead to partital internal break down.

code:

SCRIPT
BEGIN
 DECLARE MyString1 VARCHAR(50) DEFAULT 'NULL';
 DECLARE MyString2 VARCHAR(50) DEFAULT 'TEST';
 DECLARE Comp Bool DEFAULT false;
 SET Comp = MyString1 <> MyString2;
 RAISE ERROR CODE 10000 MESSAGE 'Comp Result: ' + CAST(Comp AS VARCHAR);
END

you will see the error-page containing no text due to the fact that the comparision of the two strings are fatal.

the following code will work:

SCRIPT
BEGIN
 DECLARE MyString1 VARCHAR(50) DEFAULT 'NULL';
 DECLARE MyString2 VARCHAR(50) DEFAULT 'TEST';
 DECLARE Comp Bool DEFAULT false;
 SET Comp = MyString1 <> MyString2;
 SET Comp = false;
 RAISE ERROR CODE 10000 MESSAGE 'Comp Result: ' + CAST(Comp AS VARCHAR);
END

in our Triggers we noticed that every variable accessed after the Comparision, isnt working anymore they are handled as "null".
so all following inserts and operations dont work anymore.

Volker


Someone will correct me if I'm wrong but what you're seeing seems in accordance with standard null behaviour (have a look at 1.9 NULLs in the pdf sql manual). However, I fail to see why you're not getting the error generated in both cases. I've tried it in EDBManager and both scripts fail to complete. Exactly what I'd expect with the RAISE statement.

It would be helpful if you could display the actual error code that ElevateDB generated.

Roy Lambert

In my opinion the Bool-Expression:
('NULL' <> 'ANYTHING')
should always return: FALSE
at the moment this Expression results: [NULL] an the Engine crashs ...

The RAISE command is only used to show that the engine is busting.
due to its different output .. i attached 2 images .. both should show the same.

Here how it is supposedto be:

and here what actually happens due to the bug:

One more example:

SCRIPT
BEGIN
DECLARE MyString1 VARCHAR(50) DEFAULT 'NULL';
DECLARE MyString2 VARCHAR(50) DEFAULT 'TEST';
DECLARE Comp Bool DEFAULT false;           

SET LOG MESSAGE TO 'Comp: '+CAST(Comp AS VARCHAR);
SET LOG MESSAGE TO 'MyString1: '+MyString1;
SET LOG MESSAGE TO 'MyString2: '+MyString2;

SET Comp = MyString1 <> MyString2;

SET LOG MESSAGE TO 'Comp: '+CAST(Comp AS VARCHAR);
SET LOG MESSAGE TO 'MyString1: '+MyString1;
SET LOG MESSAGE TO 'MyString2: '+MyString2;
END

as you can see in the screenshot ... as soon as the String 'NULL' is involved a bool operation is no more possible.

Volker

>In my opinion the Bool-Expression:
> ('NULL' <> 'ANYTHING')
>should always return: FALSE

I have the opinion that null and emptystring are the same. Unfortunately the sql standards committee do not share that opinion. Our opinions do not matter. What does matter is what's in the standard.

From the sql manual

NULLs and Operators
The primary rule to remember with NULLs is that any operator that uses a NULL as an operand will result
in a NULL. In other words, it is impossible for any operator using an unknown value to return a known
value. For example, in the following UPDATE statement any rows with a NULL in the Quantity column will
still have a NULL in the Quantity column after the statement is executed:

Roy

Volker


What you're actually demonstrating is not a bug its the result of casting a null to a varchar

Roy Lambert

I know little of the specifics, but I don't understand why anyone is talking about 'NULL' in quotes. Default NULL is what I'd expect to see, not Default 'NULL' which is a 4 letter string. What am I missing?


--

Matthew Jones

Volker


It seems as though you share my opinion that null is the same as emptystring. Trust me in the newer SQL standards it isn't. I wish it was but it isn't.

What you're demonstrating is not a bug its the correct behaviour according to the SQL 2003 standard. As Tim states in the sql manual


ElevateDB was developed according to the SQL 2003 standard (ANSI ISO/IEC 9075:2003), and every
effort was made to make sure that the product adheres to this standard as much as possible with no
deviations. However, there are certain areas where ElevateDB does deviate from the standard. Each
type, operator, statement, or function reference in this manual includes a summary of any deviation from
the SQL 2003 standard at the end of the reference entitled SQL 2003 Standard Deviations that will detail
any deviations from the SQL 2003 standard.

The only way to achieve what you seem to wat is to default to an emptystring (ie '') not null or use the correct form of testing, or possibly use the non-standard null behaviour. I have no idea if that will do what you want since I use the standard behaviour.

Roy Lambert

Matthew


Ouch - I totally missed the quotes, even when I pasted into EDBManager.

I retract everything I've posted in this thread until I reboot my eyes.

Roy Lambert