FirebirdSQL logo
  • Anglaiskeyboard_arrow_down

Expressions involving NULL

In SQL, NULL is not a value.It is a condition, or state, of a data item, in which its value is unknown.Because it is unknown, NULL cannot behave like a value.When you try to perform arithmetic on NULL, or involve it with values in other expressions, the result of the operation will almost always be NULL.It is not zero or blank or an “empty string”, and it does not behave like any of these values.

Below are some examples of the types of surprises you will get if you try to perform calculations and comparisons with NULL.

The following expressions all return NULL:

  • 1 + 2 + 3 + NULL

  • not (NULL)

  • 'Home ' || 'sweet ' || NULL

You might have expected 6 from the first expression and “Home sweet” from the third, but as we just said, NULL is not like the number 0 or an empty string — it’s far more destructive!

The following expression:

FirstName || ' ' || LastName

will return NULL if either FirstName or LastName is NULL.Otherwise, it will nicely concatenate the two names with a space in between — even if any one of the variables is an empty string.

Tip

Think of NULL as UNKNOWN and these strange results suddenly start to make sense!If the value of Number is unknown, the outcome of ‘1 + 2 + 3 + Number’ is also unknown (and therefore NULL).If the content of MyString is unknown, then so is ‘MyString || YourString’ (even if YourString is non-NULL).Et cetera.

Now let’s examine some PSQL (Procedural SQL) examples with if-constructs:

  • Equals (‘=’)

    if (a = b) then
  MyVariable = 'Equal';
else
  MyVariable = 'Not equal';

    After executing this code, MyVariable will be 'Not equal' if both a and b are NULL.The reason is that a = b yields NULL if at least one of them is NULL.If the test expression of an “if” statement is NULL, it behaves like false: the ‘then’ block is skipped, and the ‘else’ block executed.

    Warning

    Although the expression may behave like false in this case, it’s still NULL.If you try to invert it using not(), what you get is another NULL — not “true”.

  • Not equals (‘<>’)

    if (a <> b) then
  MyVariable = 'Not equal';
else
  MyVariable = 'Equal';

    Here, MyVariable will be 'Equal' if a is NULL and b isn’t, or vice versa.The explanation is analogous to that of the previous example.

The DISTINCT keyword comes to the rescue!

Firebird 2 and above implement IS [NOT] DISTINCT allowing you to perform (in)equality tests that take NULL into account.The semantics are as follows:

  • Two expressions are DISTINCT if they have different values or if one is NULL and the other isn’t;

  • They are NOT DISTINCT if they have the same value or if they are both NULL.

Notice that if neither operand is NULL, IS DISTINCT works exactly like the “<>” operator, and IS NOT DISTINCT like the “=” operator.

IS DISTINCT and IS NOT DISTINCT always return true or false, never NULL.

Using DISTINCT, you can rewrite the first PSQL example as follows:

if (a is not distinct from b) then
  MyVariable = 'Equal';
else
  MyVariable = 'Not equal';

And the second as:

if (a is distinct from b) then
  MyVariable = 'Not equal';
else
  MyVariable = 'Equal';

These versions will give you the results that a normal (i.e. not SQL-brainwashed) human being would expect, whether there are NULLs involved or not.

docnext count = 11

More about NULLs

A lot more information about NULL behaviour can be found in the Firebird Null Guide, at these locations:

https://www.firebirdsql.org/file/documentation/html/en/firebirddocs/nullguide/firebird-null-guide.html (HTML)
https://www.firebirdsql.org/file/documentation/pdf/en/firebirddocs/nullguide/firebird-null-guide.pdf (PDF)

Protecting your data

Backup

Firebird comes with two utilities for backing up and restoring your databases: gbak and nbackup.Both can be found in the Firebird installation directory (Windows) or its bin subdirectory (Linux).Firebird databases can be backed up while users are connected to the system and going about their normal work.The backup will be taken from a snapshot of the database at the time the backup began.

Regular backups and occasional restores should be a scheduled part of your database management activity.

Warning

Except in nbackup’s lock mode, do not use external proprietary backup utilities or file-copying tools such as WinZip, tar, copy, xcopy, etc., on a database which is running.Not only will the backup be unreliable, but the disk-level blocking used by these tools can corrupt a running database.
Important

Study the warnings in the next section about database activity during restores!

More information about gbak can be found here (HTML and PDF version, same content):

https://www.firebirdsql.org/file/documentation/html/en/firebirddocs/gbak/firebird-gbak.html
https://www.firebirdsql.org/file/documentation/pdf/en/firebirddocs/gbak/firebird-gbak.pdf

The nbackup manual is here (again same content in HTML and PDF):

https://www.firebirdsql.org/file/documentation/html/en/firebirddocs/nbackup/firebird-nbackup.html
https://www.firebirdsql.org/file/documentation/pdf/en/firebirddocs/nbackup/firebird-nbackup.pdf

How to corrupt a database

The following sections constitute a summary of things not to do if you want to keep your Firebird databases in good health.

Disabling forced writes

Firebird is installed with forced writes (synchronous writes) enabled by default.Modifications are written to disk immediately upon posting.

It is possible to configure a database to use asynchronous data writes — whereby modified or new data are held in the memory cache for periodic flushing to disk by the operating system’s I/O subsystem.The common term for this configuration is forced writes off (or disabled).It is sometimes resorted to in an attempt to improve performance during large batch operations.

Disabling forced writes on Windows

The big warning here is: do not disable forced writes on a Windows server.It has been observed that the Windows server platforms do not flush the write cache until the Firebird service is shut down.Apart from power interruptions, there is just too much that can go wrong on a Windows server.If it should hang, the I/O system goes out of reach and your users' work will be lost in the process of rebooting.

Disabling forced writes on Linux

Linux servers are safer for running an operation with forced writes disabled temporarily.Still, do not leave it disabled once your large batch task is completed, unless you have a very robust fall-back power system.

Restoring a backup to a running database

One of the restore options in the gbak utility (gbak -rep[lace_database]) allows you to restore a gbak file over an existing database.It is possible for this style of restore to proceed without warning while users are logged in to the database.Database corruption is almost certain to be the result.

Note

Notice that the shortest form of this command is gbak -rep, not gbak -r as it used to be in older Firebird versions.What happened to gbak -r?It is now short for gbak -recreate_database, which functions the same as gbak -c[reate] and throws an error if the specified database already exists.You can force overwriting of the existing database by adding the o[verwrite] flag though.This flag is only supported with gbak -r, not with gbak -c.

These changes have been made because many users thought that the -r switch meant restore instead of replace — and only found out otherwise when it was too late.
Warning

Be aware that you will need to design your admin tools and procedures to prevent any possibility for any user (including SYSDBA) to restore to your active database if any users are logged in.

If is practicable to do so, it is recommended to restore to spare disk space using the gbak -c option and test the restored database using isql or your preferred admin tool.If the restored database is good, shut down the old database (you can use the gfix command-line tool for this;see Firebird Database Housekeeping Utility (HTML) or Firebird Database Housekeeping Utility (PDF)).Make a filesystem copy of the old database just in case and then copy the restored database file(s) over their existing counterparts.

Allowing users to log in during a restore

If you do not block access to users while performing a restore using gbak -rep then users may be able to log in and attempt to do operations on data.Corrupted structures will result.

How to get help

The community of willing helpers around Firebird goes a long way back, to many years before the source code for its ancestor, InterBase® 6, was made open source.Collectively, the Firebird community does have all the answers!It even includes some people who have been involved with it since it was a design on a drawing board in a bathroom in Boston.

How to give help

Firebird exists, and continues to be improved, thanks to a community of volunteers who donate their time and skills to bring you this wonderful piece of software.But volunteer work alone is not enough to keep an enterprise-level RDBMS such as Firebird up-to-date.The Firebird Foundation supports Firebird development financially by issuing grants to designers and developers.If Firebird is useful to you, and you’d like to give something back, please visit the Foundation’s pages and consider making a donation or becoming a member or sponsor.