[Tarantool-patches] [PATCH v2 4/6] sql: extend result set with nullability

Nikita Pettik korablev at tarantool.org
Wed Dec 11 16:44:56 MSK 2019


If member of result set is (solely) column identifier, then metadata
will contain its corresponding field nullability as boolean property.
Note that indicating nullability for other expressions (like x + 1)
may make sense but it requires derived nullability calculation which in
turn seems to be overkill (at least in scope of current patch).

Part of #4407
---
 src/box/execute.c               | 17 ++++++++++++++---
 src/box/iproto_constants.h      |  1 +
 src/box/lua/execute.c           |  7 ++++++-
 src/box/lua/net_box.c           |  7 ++++++-
 src/box/sql/delete.c            |  1 +
 src/box/sql/insert.c            |  1 +
 src/box/sql/pragma.c            |  3 +++
 src/box/sql/prepare.c           |  1 +
 src/box/sql/select.c            |  7 +++++++
 src/box/sql/sqlInt.h            |  3 +++
 src/box/sql/update.c            |  1 +
 src/box/sql/vdbe.h              |  3 +++
 src/box/sql/vdbeInt.h           |  5 +++++
 src/box/sql/vdbeapi.c           |  7 +++++++
 src/box/sql/vdbeaux.c           |  7 +++++++
 test/sql/full_metadata.result   | 36 ++++++++++++++++++++++++++++++++++++
 test/sql/full_metadata.test.lua |  5 +++++
 17 files changed, 107 insertions(+), 5 deletions(-)

diff --git a/src/box/execute.c b/src/box/execute.c
index 72300235a..2e43cdb22 100644
--- a/src/box/execute.c
+++ b/src/box/execute.c
@@ -269,7 +269,8 @@ error:
 }
 
 static size_t
-metadata_map_sizeof(const char *name, const char *type, const char *coll)
+metadata_map_sizeof(const char *name, const char *type, const char *coll,
+		    int nullable)
 {
 	uint32_t members_count = 2;
 	size_t map_size = 0;
@@ -278,6 +279,11 @@ metadata_map_sizeof(const char *name, const char *type, const char *coll)
 		map_size += mp_sizeof_uint(IPROTO_FIELD_COLL);
 		map_size += mp_sizeof_str(strlen(coll));
 	}
+	if (nullable != -1) {
+		members_count++;
+		map_size += mp_sizeof_uint(IPROTO_FIELD_IS_NULLABLE);
+		map_size += mp_sizeof_bool(nullable);
+	}
 	map_size += mp_sizeof_uint(IPROTO_FIELD_NAME);
 	map_size += mp_sizeof_uint(IPROTO_FIELD_TYPE);
 	map_size += mp_sizeof_str(strlen(name));
@@ -312,6 +318,7 @@ sql_get_metadata(struct sql_stmt *stmt, struct obuf *out, int column_count)
 		const char *coll = sql_column_coll(stmt, i);
 		const char *name = sql_column_name(stmt, i);
 		const char *type = sql_column_datatype(stmt, i);
+		int nullable = sql_column_nullable(stmt, i);
 		/*
 		 * Can not fail, since all column names and types
 		 * are preallocated during prepare phase and the
@@ -319,13 +326,13 @@ sql_get_metadata(struct sql_stmt *stmt, struct obuf *out, int column_count)
 		 */
 		assert(name != NULL);
 		assert(type != NULL);
-		size = metadata_map_sizeof(name, type, coll);
+		size = metadata_map_sizeof(name, type, coll, nullable);
 		char *pos = (char *) obuf_alloc(out, size);
 		if (pos == NULL) {
 			diag_set(OutOfMemory, size, "obuf_alloc", "pos");
 			return -1;
 		}
-		uint32_t map_sz = 2 + (coll != NULL);
+		uint32_t map_sz = 2 + (coll != NULL) + (nullable != -1);
 		pos = mp_encode_map(pos, map_sz);
 		pos = mp_encode_uint(pos, IPROTO_FIELD_NAME);
 		pos = mp_encode_str(pos, name, strlen(name));
@@ -335,6 +342,10 @@ sql_get_metadata(struct sql_stmt *stmt, struct obuf *out, int column_count)
 			pos = mp_encode_uint(pos, IPROTO_FIELD_COLL);
 			pos = mp_encode_str(pos, coll, strlen(coll));
 		}
+		if (nullable != -1) {
+			pos = mp_encode_uint(pos, IPROTO_FIELD_IS_NULLABLE);
+			pos = mp_encode_bool(pos, nullable);
+		}
 	}
 	return 0;
 }
diff --git a/src/box/iproto_constants.h b/src/box/iproto_constants.h
index fa9c029a2..53d014b60 100644
--- a/src/box/iproto_constants.h
+++ b/src/box/iproto_constants.h
@@ -132,6 +132,7 @@ enum iproto_metadata_key {
 	IPROTO_FIELD_NAME = 0,
 	IPROTO_FIELD_TYPE = 1,
 	IPROTO_FIELD_COLL = 2,
+	IPROTO_FIELD_IS_NULLABLE = 3,
 };
 
 enum iproto_ballot_key {
diff --git a/src/box/lua/execute.c b/src/box/lua/execute.c
index 55b8a8fef..debe94cb9 100644
--- a/src/box/lua/execute.c
+++ b/src/box/lua/execute.c
@@ -23,7 +23,8 @@ lua_sql_get_metadata(struct sql_stmt *stmt, struct lua_State *L,
 		const char *coll = sql_column_coll(stmt, i);
 		const char *name = sql_column_name(stmt, i);
 		const char *type = sql_column_datatype(stmt, i);
-		size_t table_sz = 2 + (coll != NULL);
+		int nullable = sql_column_nullable(stmt, i);
+		size_t table_sz = 2 + (coll != NULL) + (nullable != -1);
 		lua_createtable(L, 0, table_sz);
 		/*
 		 * Can not fail, since all column names are
@@ -40,6 +41,10 @@ lua_sql_get_metadata(struct sql_stmt *stmt, struct lua_State *L,
 			lua_pushstring(L, coll);
 			lua_setfield(L, -2, "collation");
 		}
+		if (nullable != -1) {
+			lua_pushboolean(L, nullable);
+			lua_setfield(L, -2, "is_nullable");
+		}
 		lua_rawseti(L, -2, i + 1);
 	}
 }
diff --git a/src/box/lua/net_box.c b/src/box/lua/net_box.c
index 025143307..644b373c9 100644
--- a/src/box/lua/net_box.c
+++ b/src/box/lua/net_box.c
@@ -651,6 +651,11 @@ decode_metadata_optional(struct lua_State *L, const char **data,
 			const char *coll = mp_decode_str(data, &len);
 			lua_pushlstring(L, coll, len);
 			lua_setfield(L, -2, "collation");
+		} else {
+			assert(key == IPROTO_FIELD_IS_NULLABLE);
+			bool is_nullable = mp_decode_bool(data);
+			lua_pushboolean(L, is_nullable);
+			lua_setfield(L, -2, "is_nullable");
 		}
 	}
 }
@@ -667,7 +672,7 @@ netbox_decode_metadata(struct lua_State *L, const char **data)
 	lua_createtable(L, count, 0);
 	for (uint32_t i = 0; i < count; ++i) {
 		uint32_t map_size = mp_decode_map(data);
-		assert(map_size == 2 || map_size == 3);
+		assert(map_size >= 2 && map_size <= 4);
 		uint32_t key = mp_decode_uint(data);
 		assert(key == IPROTO_FIELD_NAME);
 		(void) key;
diff --git a/src/box/sql/delete.c b/src/box/sql/delete.c
index 169814a2e..cc5891bb8 100644
--- a/src/box/sql/delete.c
+++ b/src/box/sql/delete.c
@@ -420,6 +420,7 @@ sql_table_delete_from(struct Parse *parse, struct SrcList *tab_list,
 		sqlVdbeSetNumCols(v, 1);
 		vdbe_metadata_set_col_name(v, 0, "rows deleted");
 		vdbe_metadata_set_col_type(v, 0, "integer");
+		vdbe_metadata_set_col_nullability(v, 0, -1);
 	}
 
  delete_from_cleanup:
diff --git a/src/box/sql/insert.c b/src/box/sql/insert.c
index f1290e01c..159acec25 100644
--- a/src/box/sql/insert.c
+++ b/src/box/sql/insert.c
@@ -787,6 +787,7 @@ sqlInsert(Parse * pParse,	/* Parser context */
 			column_name = "rows inserted";
 		vdbe_metadata_set_col_name(v, 0, column_name);
 		vdbe_metadata_set_col_type(v, 0, "integer");
+		vdbe_metadata_set_col_nullability(v, 0, -1);
 	}
 
  insert_cleanup:
diff --git a/src/box/sql/pragma.c b/src/box/sql/pragma.c
index 00ecde0a9..a6a5f25ce 100644
--- a/src/box/sql/pragma.c
+++ b/src/box/sql/pragma.c
@@ -122,6 +122,7 @@ vdbe_set_pragma_result_columns(struct Vdbe *v, const struct PragmaName *pragma)
 	for (int i = 0, j = pragma->iPragCName; i < n; ++i) {
 		vdbe_metadata_set_col_name(v, i, pragCName[j++]);
 		vdbe_metadata_set_col_type(v, i, pragCName[j++]);
+		vdbe_metadata_set_col_nullability(v, i, -1);
 	}
 }
 
@@ -168,8 +169,10 @@ vdbe_emit_pragma_status(struct Parse *parse)
 	sqlVdbeSetNumCols(v, 2);
 	vdbe_metadata_set_col_name(v, 0, "pragma_name");
 	vdbe_metadata_set_col_type(v, 0, "text");
+	vdbe_metadata_set_col_nullability(v, 0, -1);
 	vdbe_metadata_set_col_name(v, 1, "pragma_value");
 	vdbe_metadata_set_col_type(v, 1, "integer");
+	vdbe_metadata_set_col_nullability(v, 1, -1);
 
 	parse->nMem = 2;
 	for (int i = 0; i < ArraySize(aPragmaName); ++i) {
diff --git a/src/box/sql/prepare.c b/src/box/sql/prepare.c
index 39e897ba3..5b5981c46 100644
--- a/src/box/sql/prepare.c
+++ b/src/box/sql/prepare.c
@@ -150,6 +150,7 @@ sqlPrepare(sql * db,	/* Database handle. */
 						   azColName[name_index]);
 			vdbe_metadata_set_col_type(sParse.pVdbe, i,
 						   azColName[name_index + 1]);
+			vdbe_metadata_set_col_nullability(sParse.pVdbe, i, -1);
 		}
 	}
 
diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index 506c1a7c7..73ce95eba 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1813,6 +1813,7 @@ generate_column_metadata(struct Parse *pParse, struct SrcList *pTabList,
 								coll_id->name_len);
 			}
 		}
+		vdbe_metadata_set_col_nullability(v, i, -1);
 		if (p->op == TK_COLUMN || p->op == TK_AGG_COLUMN) {
 			char *zCol;
 			int iCol = p->iColumn;
@@ -1839,6 +1840,12 @@ generate_column_metadata(struct Parse *pParse, struct SrcList *pTabList,
 				}
 			}
 			vdbe_metadata_set_col_name(v, i, name);
+			if (is_full_meta) {
+				bool is_nullable =
+					space_def->fields[iCol].is_nullable;
+				vdbe_metadata_set_col_nullability(v, i,
+								  is_nullable);
+			}
 		} else {
 			const char *z = NULL;
 			if (pEList->a[i].zName != NULL)
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index e9ad068f8..3698a5942 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -579,6 +579,9 @@ sql_column_datatype(sql_stmt *, int N);
 const char *
 sql_column_coll(sql_stmt *stmt, int n);
 
+int
+sql_column_nullable(sql_stmt *stmt, int n);
+
 int
 sql_initialize(void);
 
diff --git a/src/box/sql/update.c b/src/box/sql/update.c
index c08777a2d..489328960 100644
--- a/src/box/sql/update.c
+++ b/src/box/sql/update.c
@@ -500,6 +500,7 @@ sqlUpdate(Parse * pParse,		/* The parser context */
 		sqlVdbeSetNumCols(v, 1);
 		vdbe_metadata_set_col_name(v, 0, "rows updated");
 		vdbe_metadata_set_col_type(v, 0, "integer");
+		vdbe_metadata_set_col_nullability(v, 0, -1);
 	}
 
  update_cleanup:
diff --git a/src/box/sql/vdbe.h b/src/box/sql/vdbe.h
index 22ba0b756..384d7dc0a 100644
--- a/src/box/sql/vdbe.h
+++ b/src/box/sql/vdbe.h
@@ -257,6 +257,9 @@ int
 vdbe_metadata_set_col_collation(struct Vdbe *p, int idx, const char *coll,
 				size_t coll_len);
 
+void
+vdbe_metadata_set_col_nullability(struct Vdbe *p, int idx, int nullable);
+
 void sqlVdbeCountChanges(Vdbe *);
 sql *sqlVdbeDb(Vdbe *);
 void sqlVdbeSetSql(Vdbe *, const char *z, int n, int);
diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
index d3de5770b..92a50dd7b 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -350,6 +350,11 @@ struct sql_column_metadata {
 	char *name;
 	char *type;
 	char *collation;
+	/**
+	 * -1 is for any member of result set except for pure
+	 * columns: all other expressions are nullable by default.
+	 */
+	int8_t nullable : 2;
 };
 
 /*
diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c
index e57a80334..30f173984 100644
--- a/src/box/sql/vdbeapi.c
+++ b/src/box/sql/vdbeapi.c
@@ -753,6 +753,13 @@ sql_column_coll(sql_stmt *stmt, int n)
 	return p->metadata[n].collation;
 }
 
+int
+sql_column_nullable(sql_stmt *stmt, int n)
+{
+	struct Vdbe *p = (struct Vdbe *) stmt;
+	assert(n < sql_column_count(stmt) && n >= 0);
+	return p->metadata[n].nullable;
+}
 
 /******************************* sql_bind_  **************************
  *
diff --git a/src/box/sql/vdbeaux.c b/src/box/sql/vdbeaux.c
index 02d9fc6ce..2de41a70a 100644
--- a/src/box/sql/vdbeaux.c
+++ b/src/box/sql/vdbeaux.c
@@ -1904,6 +1904,13 @@ vdbe_metadata_set_col_collation(struct Vdbe *p, int idx, const char *coll,
 	return 0;
 }
 
+void
+vdbe_metadata_set_col_nullability(struct Vdbe *p, int idx, int nullable)
+{
+	assert(idx < p->nResColumn);
+	p->metadata[idx].nullable = nullable;
+}
+
 /*
  * This routine checks that the sql.nVdbeActive count variable
  * matches the number of vdbe's in the list sql.pVdbe that are
diff --git a/test/sql/full_metadata.result b/test/sql/full_metadata.result
index aebb938fe..20ed29e1c 100644
--- a/test/sql/full_metadata.result
+++ b/test/sql/full_metadata.result
@@ -65,6 +65,7 @@ execute("SELECT c FROM t;")
  | ---
  | - metadata:
  |   - type: string
+ |     is_nullable: true
  |     name: C
  |     collation: unicode_ci
  |   rows:
@@ -80,6 +81,41 @@ execute("SELECT c COLLATE \"unicode\" FROM t;")
  |   - ['aSd']
  | ...
 
+-- Make sure that nullability is presented.
+--
+execute("SELECT id, a, c FROM t;")
+ | ---
+ | - metadata:
+ |   - type: integer
+ |     name: ID
+ |     is_nullable: false
+ |   - type: integer
+ |     name: A
+ |     is_nullable: false
+ |   - type: string
+ |     is_nullable: true
+ |     name: C
+ |     collation: unicode_ci
+ |   rows:
+ |   - [1, 1, 'aSd']
+ | ...
+execute("SELECT * FROM t;")
+ | ---
+ | - metadata:
+ |   - type: integer
+ |     name: ID
+ |     is_nullable: false
+ |   - type: integer
+ |     name: A
+ |     is_nullable: false
+ |   - type: string
+ |     is_nullable: true
+ |     name: C
+ |     collation: unicode_ci
+ |   rows:
+ |   - [1, 1, 'aSd']
+ | ...
+
 execute("PRAGMA full_metadata = false;")
  | ---
  | - row_count: 0
diff --git a/test/sql/full_metadata.test.lua b/test/sql/full_metadata.test.lua
index 4aa2492a1..0ff5fefe6 100644
--- a/test/sql/full_metadata.test.lua
+++ b/test/sql/full_metadata.test.lua
@@ -30,6 +30,11 @@ execute("SELECT 'aSd' COLLATE \"unicode_ci\";")
 execute("SELECT c FROM t;")
 execute("SELECT c COLLATE \"unicode\" FROM t;")
 
+-- Make sure that nullability is presented.
+--
+execute("SELECT id, a, c FROM t;")
+execute("SELECT * FROM t;")
+
 execute("PRAGMA full_metadata = false;")
 
 test_run:cmd("setopt delimiter ';'")
-- 
2.15.1



More information about the Tarantool-patches mailing list