Closed9
handy-spanner で NOT NULL AS (hoge) STORED なカラムを扱いたい
toga4課題
生成された列 (generated column) が NOT NULL だとカラムに値を設定できないのを解決したい
% SPANNER_EMULATOR_HOST=localhost:9999 spanner-cli -p fake -i fake -d fake
Connected.
spanner> CREATE TABLE hoge (
-> id STRING(64) NOT NULL,
-> fingerprint INT64 NOT NULL AS (FARM_FINGERPRINT(id)) STORED,
-> ) PRIMARY KEY(id);
Query OK, 0 rows affected (0.01 sec)
spanner> INSERT INTO hoge (id) VALUES ("foobar");
ERROR: spanner: code = "FailedPrecondition", desc = "A new row in table hoge does not specify a non-null value for these NOT NULL columns: fingerprint"
NOT NULL を付けなければうまくいくので generated column そのものに対応はしている
% SPANNER_EMULATOR_HOST=localhost:9999 spanner-cli -p fake -i fake -d fake
Connected.
spanner> CREATE TABLE fuga (
-> id STRING(64) NOT NULL,
-> fingerprint INT64 AS (FARM_FINGERPRINT(id)) STORED,
-> ) PRIMARY KEY(id);
Query OK, 0 rows affected (0.00 sec)
spanner> INSERT INTO fuga (id) VALUES ("foobar");
Query OK, 1 rows affected (0.00 sec)
spanner> SELECT * FROM fuga;
+--------+----------------------+
| id | fingerprint |
+--------+----------------------+
| foobar | -4305526341195793154 |
+--------+----------------------+
1 rows in set (0.207 msecs)
spanner>
エラーメッセージを生成している場所を探す
DB に書き込む前にチェックして弾いてる
toga4実際の Spanner を使って generated column が NULL になる場合の挙動を確かめる
% spanner-cli -p *** -i *** -d ***
Connected.
spanner> CREATE TABLE hoge (
-> id STRING(64) NOT NULL,
-> foo INT64 NOT NULL,
-> bar STRING(64) NOT NULL AS (NULLIF(id, "fuga")) STORED,
-> ) PRIMARY KEY(id);
Query OK, 0 rows affected (11.18 sec)
spanner> INSERT INTO hoge (id) VALUES ("hoge");
ERROR: spanner: code = "FailedPrecondition", desc = "A new row in table hoge does not specify a non-null value for these NOT NULL columns: foo."
spanner> INSERT INTO hoge (id, foo) VALUES ("hoge", 1);
Query OK, 1 rows affected (0.34 sec)
spanner> INSERT INTO hoge (id, foo) VALUES ("fuga", 1);
ERROR: spanner: code = "FailedPrecondition", desc = "bar must not be NULL in table hoge."
結果
- 普通の NOT NULL カラムに値を設定していない場合
- handy-spanner と同じエラーメッセージ
- 一瞬で結果が返るので事前にチェックしてそう
- NOT NULL な生成列の計算結果が NULL の場合
- 別のエラーメッセージになる
- ちょっと時間がかかるので、おそらく一度書き込みを試行している
toga4handy-spanner の実装からさっきのチェックを実行する条件を探る
nonNullCheck は write メソッドの第6引数
Insert
Update
Replace
InsertOrUpdate
Update のときにはチェックされない
toga4Update で NOT NULL カラムに NULL を設定した場合の挙動を handy-spanner と実際の Spanner で比較する
handy-spanner
% SPANNER_EMULATOR_HOST=localhost:9999 spanner-cli -p fake -i fake -d fake
Connected.
spanner> CREATE TABLE hogehoge (
-> id STRING(64) NOT NULL,
-> foo INT64 NOT NULL,
-> ) PRIMARY KEY(id);
Query OK, 0 rows affected (0.01 sec)
spanner> INSERT INTO hogehoge(id, foo) VALUES("hoge", 1);
Query OK, 1 rows affected (0.00 sec)
spanner> UPDATE hogehoge SET foo = NULL WHERE id = "hoge";
ERROR: spanner: code = "Unknown", desc = "rpc error: code = Unknown desc = failed to write into sqlite: NOT NULL constraint failed: hogehoge.foo"
Spanner
% spanner-cli -p *** -i *** -d ***
Connected.
spanner> SHOW CREATE TABLE hoge;
+-------+-----------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------+
| hoge | CREATE TABLE hoge ( |
| | id STRING(64) NOT NULL, |
| | foo INT64 NOT NULL, |
| | bar STRING(64) NOT NULL AS (NULLIF(id, "fuga")) STORED, |
| | ) PRIMARY KEY(id) |
+-------+-----------------------------------------------------------+
1 rows in set (1.23 sec)
spanner> SELECT * FROM hoge;
+------+-----+------+
| id | foo | bar |
+------+-----+------+
| hoge | 1 | hoge |
+------+-----+------+
1 rows in set (2.74 msecs)
spanner> UPDATE hoge SET foo = NULL WHERE id = "hoge";
ERROR: spanner: code = "FailedPrecondition", desc = "foo must not be NULL in table hoge."
handy-spanner は事前 null check をしない場合は sqlite の NOT NULL 制約にまかせている感じ。
generated column だったら事前の null check を単にスルーすれば良さそう？
toga4diff
diff --git a/server/database.go b/server/database.go
index fbcc178..6a0b7da 100644
--- a/server/database.go
+++ b/server/database.go
@@ -762,7 +762,7 @@ func (d *database) write(ctx context.Context, tx *transaction, tbl string, cols
// Check not nullable columns are specified for Insert/Replace
if nonNullCheck {
- if exist, nonNullables := table.NonNullableAndNonGeneratedColumnsExist(cols); exist {
+ if exist, nonNullables := table.NonNullableColumnsExist(cols); exist {
columns := strings.Join(nonNullables, ", ")
return status.Errorf(codes.FailedPrecondition,
"A new row in table %s does not specify a non-null value for these NOT NULL columns: %s",
diff --git a/server/query.go b/server/query.go
index b0f4439..ddef21e 100644
--- a/server/query.go
+++ b/server/query.go
@@ -480,7 +480,7 @@ func (b *QueryBuilder) buildInsert(up *ast.Insert) (string, []interface{}, error
}
// Check not nullable columns
- if exist, nonNullables := t.NonNullableAndNonGeneratedColumnsExist(columns); exist {
+ if exist, nonNullables := t.NonNullableColumnsExist(columns); exist {
columns := strings.Join(nonNullables, ", ")
return "", nil, status.Errorf(codes.FailedPrecondition,
"A new row in table %s does not specify a non-null value for these NOT NULL columns: %s",
diff --git a/server/table.go b/server/table.go
index 3fea5d1..ac19fc4 100644
--- a/server/table.go
+++ b/server/table.go
@@ -60,9 +60,9 @@ func (t *Table) TableViewWithAlias(alias string) *TableView {
return createTableViewFromTable(t, alias)
}
-// NonNullableAndNonGeneratedColumnsExist checks non nullable columns exist in the spciefied columns.
-// It returns true and the columns if non nullable and non generated columns exist.
-func (t *Table) NonNullableAndNonGeneratedColumnsExist(columns []string) (bool, []string) {
+// NonNullableColumnsExist checks non nullable columns exist in the spciefied columns.
+// It returns true and the columns if non nullable columns exist.
+func (t *Table) NonNullableColumnsExist(columns []string) (bool, []string) {
usedColumns := make(map[string]struct{}, len(columns))
for _, name := range columns {
usedColumns[name] = struct{}{}
@@ -73,9 +73,6 @@ func (t *Table) NonNullableAndNonGeneratedColumnsExist(columns []string) (bool,
if c.nullable {
continue
}
- if c.ast != nil && c.ast.GeneratedExpr != nil {
- continue
- }
n := c.Name()
if _, ok := usedColumns[n]; !ok {
go install ./cmd/handy-spanner して試す
% SPANNER_EMULATOR_HOST=localhost:9999 spanner-cli -p fake -i fake -d fake
Connected.
spanner> CREATE TABLE hoge (
-> id STRING(64) NOT NULL,
-> fingerprint INT64 NOT NULL AS (FARM_FINGERPRINT(id)) STORED,
-> ) PRIMARY KEY(id);
Query OK, 0 rows affected (0.00 sec)
spanner> INSERT INTO hoge (id) VALUES ("foobar");
Query OK, 1 rows affected (0.00 sec)
spanner> SELECT * FROM hoge;
+--------+----------------------+
| id | fingerprint |
+--------+----------------------+
| foobar | -4305526341195793154 |
+--------+----------------------+
1 rows in set (0.282 msecs)
最初のパターンは通るようになった
toga4他のパターンも試す
spanner> CREATE TABLE fuga (
-> id STRING(64) NOT NULL,
-> foo INT64 NOT NULL,
-> bar STRING(64) NOT NULL AS (NULLIF(id, "fuga")) STORED,
-> ) PRIMARY KEY(id);
Query OK, 0 rows affected (0.00 sec)
spanner> INSERT INTO fuga (id) VALUES ("hoge");
ERROR: spanner: code = "FailedPrecondition", desc = "A new row in table fuga does not specify a non-null value for these NOT NULL columns: foo"
spanner> INSERT INTO fuga (id, foo) VALUES ("hoge", 1);
Query OK, 1 rows affected (0.00 sec)
spanner> INSERT INTO fuga (id, foo) VALUES ("fuga", 1);
ERROR: spanner: code = "Unknown", desc = "rpc error: code = Unknown desc = failed to write into sqlite: NOT NULL constraint failed: fuga.bar"
- ただの NOT NULL カラムに値を指定しない場合
- 挙動に変化なし
- NOT NULL な generated column の計算結果が NULL になる場合
- sqlite の NOT NULL 制約でエラー
toga4PR 出した
このスクラップは2022/10/15にクローズされました