Firebird Null Guide NULL behaviour and pitfalls in Firebird SQL Paul Vinkenoog Version 1.2, 30 June 2020 Table of Contents 1. What is NULL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 1.1. NULL as a default state. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2. NULL support in Firebird 1. Disallowing NULL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 2.2. Testing for NULL . . . . . . .

Nulls and other C# stories from the future Chapter One Herding nulls Every reference type allows null! Code must be defensive about it C# shares this with most object oriented languages Not all languages public string? MiddleName { get; set; } public string LastName { get; set; } } J 1. Protect non-null types from nulls 2. Protect nulls from dereference Must be optional Turn it on when you’re ready job with existing code Can’t force you to rewrite good code Recognize existing ways of ensuring null safety (checks and assignments) Can’t be exhaustive Tradeoff between completeness and convenience

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 3.9.1. SQL_NULL Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 4.1.4. NULL in Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . CREATE TABLE T (ABS INT NOT NULL); On the contrary, the following statement will return an error because ADD is both a keyword and a reserved word. CREATE TABLE T (ADD INT NOT NULL); Refer to the list of

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 3.7.1. SQL_NULL Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 4.1.4. NULL in Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . CREATE TABLE T (ABS INT NOT NULL); On the contrary, the following statement will return an error because ADD is both a keyword and a reserved word. CREATE TABLE T (ADD INT NOT NULL); Refer to the list of

the projection result is empty #23887 – Fix the issue of wrong query results when a column contains NULL values in some cases #23891 – Forbid generating MPP plans when the scan contains virtual columns consecutive and fast writes might make TiFlash out of memory – Fix the potential issue that the exception of null pointer might be raised during the table GC – Fix the TiFlash panic issue that occurs when writing bikeshare; USE bikeshare; CREATE TABLE trips ( 87 trip_id bigint NOT NULL PRIMARY KEY AUTO_INCREMENT, duration integer not null, start_date datetime, end_date datetime, start_station_number integer,

SHOW VIEW permission to execute the SHOW CREATE VIEW statement. • The system variable sql_auto_is_null is added to the noop functions. When tidb_enable_noop_functions = 0/OFF, modifying this variable value value of the plugin_dir configuration #28084 – Fix the issue that the CONVERT_TZ function returns NULL when it is given a named timezone and a UTC offset #8311 – Fix the issue that CREATE SCHEMA does DATABASE bikeshare; USE bikeshare; CREATE TABLE trips ( trip_id bigint NOT NULL PRIMARY KEY AUTO_INCREMENT, 107 duration integer not null, start_date datetime, end_date datetime, start_station_number integer

characters by other characters in a string. In TiDB, this function does not treat empty strings as NULL as Oracle does. User document • Support spilling HashAgg Support spilling HashAgg into disk. When panic issue that might occur when the Date_Format function is called with the STRING type argument and NULL values • Tools – TiCDC * Fix a bug that TiCDC owner exits abnormally when refreshing the checkpoint DATABASE bikeshare; USE bikeshare; CREATE TABLE trips ( trip_id bigint NOT NULL PRIMARY KEY AUTO_INCREMENT, 95 duration integer not null, start_date datetime, end_date datetime, start_station_number integer

of the left table. When outputting a left-table row for which there is no right-table match, empty (null) values are substituted for the right-table columns. Exercise: There are also right outer joins real, elevation int -- (in ft) ); CREATE TABLE capitals ( state char(2) UNIQUE NOT NULL ) INHERITS (cities); In this case, a row of capitals inherits all columns (name, population, and Constraints ............................................................................... 61 5.4.2. Not-Null Constraints ............................................................................ 63 5.4.3

of the left table. When outputting a left-table row for which there is no right-table match, empty (null) values are substituted for the right-table columns. Exercise: There are also right outer joins real, elevation int -- (in ft) ); CREATE TABLE capitals ( state char(2) UNIQUE NOT NULL ) INHERITS (cities); In this case, a row of capitals inherits all columns (name, population, and ................................................................................... 65 5.4.2. Not-Null Constraints ................................................................................. 68 5

AUTO_INCREMENT PRIMARY KEY, title VARCHAR(50), body TEXT, created DATETIME DEFAULT NULL, modified DATETIME DEFAULT NULL ); /* Then insert some posts for testing: */ INSERT INTO posts (title, body, created) index() { $this->set('posts', $this->Post->find('all')); } public function view($id = null) { if (!$id) { throw new NotFoundException(__('Invalid post')); } Here’s what the edit() action of the PostsController would look like: public function edit($id = null) { if (!$id) { throw new NotFoundException(__('Invalid post')); } $post = $this