Edited at

13章 Fear of the Unknown(恐怖のunknown)

More than 3 years have passed since last update.


13.0 起因

bugsのDBで、名前をSelectすること。



SELECT first_name || ' ' || last_name AS full_name FROM Accounts;

Junyan Chen

ミドルネームを追加する要望

ALTER TABLE Account ADD COLUMN middle_initial CHAR(2);

UPDATE Accounts SET middle_initial = 'J.' WHERE account_id = 123;
UPDATE Accounts SET middle_initial = 'C.' WHERE account_id = 321;

SELECT first_name || ' ' || middle_name || ' ' || last_name AS full_name FROM Accounts;

ミドルネームがあるか、ないか、分からないので、なかったらどうなる?




A



JunyanChen



B



Junyan _ _ Chen



C



Junyan _ _ _ Chen



D



””



E



その他




13.1 目的: Distinguish Missing Values

Nullの意味


  • 分からない値の追加する時:課程テーブルに学生を登録する時、成績カラムがNULL

  • 意味がない値:個人情報を登録する時、ミドルネームや前名とか

  • 関数の結果がNull:DAY('2014−09−32')

  • Outer Joinした時、マーチされなかったら、NullをPlaceholderにする


13.2 アンチパターン:Nullを普通の値にする


  • SQL: 特殊な値

  • Oracle, Sybase : Lengthが0の空文字列


13.2.1 表現中のNULL

hoursがNULLの時



SELECT hours + 10 FROM Bugs;



結果がNULL


13.2.2 NULLの行を検索

Nullの行を検索したい



SELECT * FROM Bugs WHERE assigned_to = 123;

SELECT * FROM Bugs WHERE NOT (assigned_to = 123);



どっちでも返しない



SELECT * FROM Bugs WHERE assigned_to = NULL;

SELECT * FROM Bugs WHERE assigned_to <> NULL;



ブーブー


13.2.3 NULLをパラメータにする

SELECT * FROM Bugs WHERE assigned_to = ?;

PlaceHolderでする時もブーブーだ


13.2.4 NULLを避ける

避けろおと



CREATE TABLE Bugs (

bug_id SERIAL PRIMARY KEY,

-- other columns

assigned_to BIGINT UNSIGNED NOT NULL,

hours NUMERIC(9,2) NOT NULL,

FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id)

);



”未定義”の時が、特別なPlaceHolderが必要です。

NULLを避けるため、−1にする



INSERT INTO Bugs (assigned_to, hours) VALUES (-1, -1);

SELECT AVG(hours) AS average_hours_per_hug FROM Bugs WHERE hours <> -1;



余計なロジックが必要

まだ齟齬がある

SUMとかAVGすると、誤りが発生

assigned_toを外鍵する時、MATCHしましたか分からない


13.3 アンチパターンの見つけ方


  • NULLの行をSelectできないって

  • DBにある行をSelectすると、結果が見えないって

  • Select+Whereの結果が一部分しかないって

  • 特殊な値を利用したので、コストが発生したって


13.4 アンチパターンを用いても良い場合

NULLの使用がアンチパターンじゃない

NULLを普通の値として使うのがアンチパターンだ。

* Textの導入(mysqlimport)\N がNULLを代表する

* ConvertEmptyString-ToNull (.NET)

* 複数の特殊値が有る場合。例えば、”アサインしなかった” と ”アサインした人がいない”


13.5 解決策:NULLを特殊値にする


13.5.1 スケラー表現中のNULL:

expression
expected
actual
because

NULL=0
TRUE
NULL
Null is not 0

NULL=12345
FALSE
NULL
Unknown if the unspecified value is equal to a given value

NULL<>12345
TRUE
NULL
Also unknown if it is unequal

NULL+12345
12345
NULL
Null is not 0

NULL || 'string'
'string'
NULL
Null is not an empty string

NULL = NULL
TRUE
NULL
Unknown if one unspecified value is the same as another

NULL <> NULL
FALSE
NULL
Also unknown if they are different


13.5.2 ブール表現中のNULL:

expression
expected
actual
because

NULL AND TRUE
FALSE
NULL
NULL is not false

NULL AND FALSE
FALSE
FALSE
Any truth value AND FALSE is false

NULL OR FALSE
FALSE
NULL
NULL is not false

NULL OR TRUE
TRUE
TRUE
NULL is not 0

NOT(NULL)
TRUE
NULL
NULL is not false


13.5.3 NULLの行を検索

一番正しい



SELECT * FROM Bugs WHERE assigned_to IS NULL;

SELECT * FROM Bugs WHERE assigned_to IS NOT NULL;



これも行ける



SELECT * FROM Bugs WHERE assigned_to IS NULL OR assigned_to <> 1;

SELECT * FROM Bugs WHERE assigned_to IS DISTINCT FROM 1;



IS DISTINCT FROMは

PostgreSQL, IBM DB2, and Firebird が支える

Oracle and Microsoft SQL Server が支えない

MySQLの場合、<=>

補足



'a' IS NULL ==> 'a' <=> NULL

'a' IS NOT NULL ==> NOT('a' <=> NULL)

``````


13.5.3 NOT NULLの宣言

下記のパータンが必ずNOT NULLしたほうが

* 必須項目:reported_by

* Index:id

* 時間系:created_at

* status系:enum


13.5.3 Dynamic Defaults

最初の問題がまだ解決できてない



SELECT first_name || ' ' || middle_name || ' ' || last_name AS full_name FROM Accounts;



結局、NULLになった。

ダメだ!

ところが、

Func COALESCE(value,...)

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce



SELECT first_name || COALESCE(' ' || middle_name || ' ', '' ) || last_name AS full_name FROM Accounts;