* [tarantool-patches] [PATCH v1 1/1] sql: set column types for EXPLAIN and PRAGMA
@ 2018-12-11 20:41 imeevma
2018-12-12 13:21 ` [tarantool-patches] " Vladislav Shpilevoy
0 siblings, 1 reply; 2+ messages in thread
From: imeevma @ 2018-12-11 20:41 UTC (permalink / raw)
To: v.shpilevoy, tarantool-patches
Currently, EXPLAIN and PRAGMA do not set the column types for the
result. This is incorrect, since any returned row must have a
column type. This patch defines the types for these columns.
Closes #3832
---
https://github.com/tarantool/tarantool/issues/3832
https://github.com/tarantool/tarantool/tree/imeevma/gh-3832-no-column-types
src/box/execute.c | 5 +-
src/box/sql/pragma.c | 29 +++----
src/box/sql/pragma.h | 214 +++++++++++++++++++++++++++++++++--------------
src/box/sql/prepare.c | 52 ++++++++----
test/sql/iproto.result | 69 +++++++++++++++
test/sql/iproto.test.lua | 18 +++-
6 files changed, 295 insertions(+), 92 deletions(-)
diff --git a/src/box/execute.c b/src/box/execute.c
index 3a6cadf..d3adacb 100644
--- a/src/box/execute.c
+++ b/src/box/execute.c
@@ -550,11 +550,12 @@ sql_get_description(struct sqlite3_stmt *stmt, struct obuf *out,
const char *name = sqlite3_column_name(stmt, i);
const char *type = sqlite3_column_datatype(stmt, i);
/*
- * Can not fail, since all column names are
- * preallocated during prepare phase and the
+ * Can not fail, since all column names and types
+ * are preallocated during prepare phase and the
* column_name simply returns them.
*/
assert(name != NULL);
+ assert(type != NULL);
size += mp_sizeof_str(strlen(name));
size += mp_sizeof_str(strlen(type));
char *pos = (char *) obuf_alloc(out, size);
diff --git a/src/box/sql/pragma.c b/src/box/sql/pragma.c
index 5c35017..e1598d9 100644
--- a/src/box/sql/pragma.c
+++ b/src/box/sql/pragma.c
@@ -112,25 +112,26 @@ sqlite3GetBoolean(const char *z, u8 dflt)
* the rest of the file if PRAGMAs are omitted from the build.
*/
-/*
- * Set result column names for a pragma.
+/**
+ * Set result column names and types for a pragma.
+ *
+ * @param v the query under construction.
+ * @param pPragma the pragma.
*/
static void
-setPragmaResultColumnNames(Vdbe * v, /* The query under construction */
- const PragmaName * pPragma /* The pragma */
- )
+setPragmaResultColumnNames(Vdbe *v, const PragmaName *pPragma)
{
u8 n = pPragma->nPragCName;
- sqlite3VdbeSetNumCols(v, n == 0 ? 1 : n);
- if (n == 0) {
- sqlite3VdbeSetColName(v, 0, COLNAME_NAME, pPragma->zName,
+ assert(n > 0);
+ sqlite3VdbeSetNumCols(v, n);
+ int i, j;
+ for (i = 0, j = pPragma->iPragCName; i < n; i++, j++) {
+ /* Value of j is index of column name in array */
+ sqlite3VdbeSetColName(v, i, COLNAME_NAME, pragCName[j++],
+ SQLITE_STATIC);
+ /* Value of j is index of column type in array */
+ sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, pragCName[j],
SQLITE_STATIC);
- } else {
- int i, j;
- for (i = 0, j = pPragma->iPragCName; i < n; i++, j++) {
- sqlite3VdbeSetColName(v, i, COLNAME_NAME, pragCName[j],
- SQLITE_STATIC);
- }
}
}
diff --git a/src/box/sql/pragma.h b/src/box/sql/pragma.h
index e608016..c6c3e63 100644
--- a/src/box/sql/pragma.h
+++ b/src/box/sql/pragma.h
@@ -27,50 +27,142 @@
#define PragFlg_SchemaOpt 0x40 /* Schema restricts name search if present */
#define PragFlg_SchemaReq 0x80 /* Schema required - "main" is default */
-/* Names of columns for pragmas that return multi-column result
- * or that return single-column results where the name of the
- * result column is different from the name of the pragma
+/**
+ * Column names and types for pragmas. The type of the column is
+ * the following value after its name.
*/
static const char *const pragCName[] = {
/* Used by: table_info */
/* 0 */ "cid",
- /* 1 */ "name",
- /* 2 */ "type",
- /* 3 */ "notnull",
- /* 4 */ "dflt_value",
- /* 5 */ "pk",
+ /* 1 */ "INTEGER",
+ /* 2 */ "name",
+ /* 3 */ "TEXT",
+ /* 4 */ "type",
+ /* 3 */ "TEXT",
+ /* 6 */ "notnull",
+ /* 1 */ "INTEGER",
+ /* 8 */ "dflt_value",
+ /* 9 */ "TEXT",
+ /* 10 */ "pk",
+ /* 11 */ "INTEGER",
/* Used by: stats */
- /* 6 */ "table",
- /* 7 */ "index",
- /* 8 */ "width",
- /* 9 */ "height",
+ /* 12 */ "table",
+ /* 13 */ "TEXT",
+ /* 14 */ "index",
+ /* 15 */ "TEXT",
+ /* 16 */ "width",
+ /* 17 */ "INTEGER",
+ /* 18 */ "height",
+ /* 19 */ "INTEGER",
/* Used by: index_info */
- /* 10 */ "seqno",
- /* 11 */ "cid",
- /* 12 */ "name",
- /* 13 */ "desc",
- /* 14 */ "coll",
- /* 15 */ "type",
+ /* 20 */ "seqno",
+ /* 21 */ "INTEGER",
+ /* 22 */ "cid",
+ /* 23 */ "INTEGER",
+ /* 24 */ "name",
+ /* 25 */ "TEXT",
+ /* 26 */ "desc",
+ /* 27 */ "INTEGER",
+ /* 28 */ "coll",
+ /* 29 */ "TEXT",
+ /* 30 */ "type",
+ /* 31 */ "TEXT",
/* Used by: index_list */
- /* 16 */ "seq",
- /* 17 */ "name",
- /* 18 */ "unique",
- /* 19 */ "origin",
- /* 20 */ "partial",
+ /* 32 */ "seq",
+ /* 33 */ "INTEGER",
+ /* 34 */ "name",
+ /* 35 */ "TEXT",
+ /* 36 */ "unique",
+ /* 37 */ "INTEGER",
+ /* 38 */ "origin",
+ /* 39 */ "TEXT",
+ /* 40 */ "partial",
+ /* 41 */ "INTEGER",
/* Used by: collation_list */
- /* 21 */ "seq",
- /* 22 */ "name",
+ /* 42 */ "seq",
+ /* 43 */ "INTEGER",
+ /* 44 */ "name",
+ /* 45 */ "TEXT",
/* Used by: foreign_key_list */
- /* 23 */ "id",
- /* 24 */ "seq",
- /* 25 */ "table",
- /* 26 */ "from",
- /* 27 */ "to",
- /* 28 */ "on_update",
- /* 29 */ "on_delete",
- /* 30 */ "match",
+ /* 46 */ "id",
+ /* 47 */ "INTEGER",
+ /* 48 */ "seq",
+ /* 49 */ "INTEGER",
+ /* 50 */ "table",
+ /* 51 */ "TEXT",
+ /* 52 */ "from",
+ /* 53 */ "TEXT",
+ /* 54 */ "to",
+ /* 55 */ "TEXT",
+ /* 56 */ "on_update",
+ /* 57 */ "TEXT",
+ /* 58 */ "on_delete",
+ /* 59 */ "TEXT",
+ /* 60 */ "match",
+ /* 61 */ "TEXT",
/* Used by: busy_timeout */
- /* 31 */ "timeout",
+ /* 62 */ "timeout",
+ /* 63 */ "INTEGER",
+ /* Used by: case_sensitive_like */
+ /* 64 */ "case_sensitive_like",
+ /* 65 */ "INTEGER",
+ /* Used by: count_changes */
+ /* 66 */ "count_changes",
+ /* 67 */ "INTEGER",
+ /* Used by: defer_foreign_keys */
+ /* 68 */ "defer_foreign_keys",
+ /* 69 */ "INTEGER",
+ /* Used by: full_column_names */
+ /* 70 */ "full_column_names",
+ /* 71 */ "INTEGER",
+ /* Used by: parser_trace */
+ /* 72 */ "parser_trace",
+ /* 73 */ "INTEGER",
+ /* Used by: query_only */
+ /* 74 */ "query_only",
+ /* 75 */ "INTEGER",
+ /* Used by: read_uncommitted */
+ /* 76 */ "read_uncommitted",
+ /* 77 */ "INTEGER",
+ /* Used by: recursive_triggers */
+ /* 78 */ "recursive_triggers",
+ /* 79 */ "INTEGER",
+ /* Used by: reverse_unordered_selects */
+ /* 80 */ "reverse_unordered_selects",
+ /* 81 */ "INTEGER",
+ /* Used by: select_trace */
+ /* 82 */ "select_trace",
+ /* 83 */ "INTEGER",
+ /* Used by: short_column_names */
+ /* 84 */ "short_column_names",
+ /* 85 */ "INTEGER",
+ /* Used by: sql_compound_select_limit */
+ /* 86 */ "sql_compound_select_limit",
+ /* 87 */ "INTEGER",
+ /* Used by: sql_default_engine */
+ /* 88 */ "sql_default_engine",
+ /* 89 */ "INTEGER",
+ /* Used by: sql_trace */
+ /* 90 */ "sql_trace",
+ /* 91 */ "INTEGER",
+ /* Used by: vdbe_addoptrace */
+ /* 92 */ "vdbe_addoptrace",
+ /* 93 */ "INTEGER",
+ /* Used by: vdbe_debug */
+ /* 94 */ "vdbe_debug",
+ /* 95 */ "INTEGER",
+ /* Used by: vdbe_eqp */
+ /* 96 */ "vdbe_eqp",
+ /* 97 */ "INTEGER",
+ /* Used by: vdbe_listing */
+ /* 98 */ "vdbe_listing",
+ /* 99 */ "INTEGER",
+ /* Used by: vdbe_trace */
+ /* 100 */ "vdbe_trace",
+ /* 101 */ "INTEGER",
+ /* Used by: where_trace */
+ /* 102 */ "where_trace",
+ /* 103 */ "INTEGER",
};
/* Definitions of all built-in pragmas */
@@ -79,7 +171,7 @@ typedef struct PragmaName {
u8 ePragTyp; /* PragTyp_XXX value */
u8 mPragFlg; /* Zero or more PragFlg_XXX values */
u8 iPragCName; /* Start of column names in pragCName[] */
- u8 nPragCName; /* Num of col names. 0 means use pragma name */
+ u8 nPragCName; /* Num of col names. */
u32 iArg; /* Extra argument */
} PragmaName;
/**
@@ -90,45 +182,45 @@ static const PragmaName aPragmaName[] = {
{ /* zName: */ "busy_timeout",
/* ePragTyp: */ PragTyp_BUSY_TIMEOUT,
/* ePragFlg: */ PragFlg_Result0,
- /* ColNames: */ 31, 1,
+ /* ColNames: */ 62, 1,
/* iArg: */ 0},
{ /* zName: */ "case_sensitive_like",
/* ePragTyp: */ PragTyp_CASE_SENSITIVE_LIKE,
/* ePragFlg: */ PragFlg_NoColumns,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 64, 1,
/* iArg: */ 0},
#if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
{ /* zName: */ "collation_list",
/* ePragTyp: */ PragTyp_COLLATION_LIST,
/* ePragFlg: */ PragFlg_Result0,
- /* ColNames: */ 21, 2,
+ /* ColNames: */ 42, 2,
/* iArg: */ 0},
#endif
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
{ /* zName: */ "count_changes",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 66, 1,
/* iArg: */ SQLITE_CountRows},
#endif
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
{ /* zName: */ "defer_foreign_keys",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 68, 1,
/* iArg: */ SQLITE_DeferFKs},
#endif
{ /* zName: */ "foreign_key_list",
/* ePragTyp: */ PragTyp_FOREIGN_KEY_LIST,
/* ePragFlg: */
PragFlg_NeedSchema | PragFlg_Result1 | PragFlg_SchemaOpt,
- /* ColNames: */ 23, 8,
+ /* ColNames: */ 46, 8,
/* iArg: */ 0},
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
{ /* zName: */ "full_column_names",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 70, 1,
/* iArg: */ SQLITE_FullColNames},
#endif
#if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
@@ -136,76 +228,76 @@ static const PragmaName aPragmaName[] = {
/* ePragTyp: */ PragTyp_INDEX_INFO,
/* ePragFlg: */
PragFlg_NeedSchema | PragFlg_Result1 | PragFlg_SchemaOpt,
- /* ColNames: */ 10, 6,
+ /* ColNames: */ 20, 6,
/* iArg: */ 1},
{ /* zName: */ "index_list",
/* ePragTyp: */ PragTyp_INDEX_LIST,
/* ePragFlg: */
PragFlg_NeedSchema | PragFlg_Result1 | PragFlg_SchemaOpt,
- /* ColNames: */ 16, 5,
+ /* ColNames: */ 32, 5,
/* iArg: */ 0},
#endif
#if defined(SQLITE_DEBUG) && !defined(SQLITE_OMIT_PARSER_TRACE)
{ /* zName: */ "parser_trace",
/* ePragTyp: */ PragTyp_PARSER_TRACE,
/* ePragFlg: */ 0,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 72, 1,
/* iArg: */ 0},
#endif
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
{ /* zName: */ "query_only",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 74, 1,
/* iArg: */ SQLITE_QueryOnly},
{ /* zName: */ "read_uncommitted",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 76, 1,
/* iArg: */ SQLITE_ReadUncommitted},
{ /* zName: */ "recursive_triggers",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 78, 1,
/* iArg: */ SQLITE_RecTriggers},
#endif
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
{ /* zName: */ "reverse_unordered_selects",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 80, 1,
/* iArg: */ SQLITE_ReverseOrder},
#endif
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS) && defined(SQLITE_ENABLE_SELECTTRACE)
{ /* zName: */ "select_trace",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 82, 1,
/* iArg: */ SQLITE_SelectTrace},
#endif
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
{ /* zName: */ "short_column_names",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 84, 1,
/* iArg: */ SQLITE_ShortColNames},
#endif
{ /* zName: */ "sql_compound_select_limit",
/* ePragTyp: */ PragTyp_COMPOUND_SELECT_LIMIT,
/* ePragFlg: */ PragFlg_Result0,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 86, 1,
/* iArg: */ 0},
{ /* zName: */ "sql_default_engine",
/* ePragTyp: */ PragTyp_DEFAULT_ENGINE,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 88, 1,
/* iArg: */ 0},
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
#if defined(SQLITE_DEBUG)
{ /* zName: */ "sql_trace",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 90, 1,
/* iArg: */ SQLITE_SqlTrace},
#endif
#endif
@@ -214,7 +306,7 @@ static const PragmaName aPragmaName[] = {
/* ePragTyp: */ PragTyp_STATS,
/* ePragFlg: */
PragFlg_NeedSchema | PragFlg_Result0 | PragFlg_SchemaReq,
- /* ColNames: */ 6, 4,
+ /* ColNames: */ 12, 4,
/* iArg: */ 0},
#endif
#if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
@@ -230,28 +322,28 @@ static const PragmaName aPragmaName[] = {
{ /* zName: */ "vdbe_addoptrace",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 92, 1,
/* iArg: */ SQLITE_VdbeAddopTrace},
{ /* zName: */ "vdbe_debug",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 94, 1,
/* iArg: */
SQLITE_SqlTrace | SQLITE_VdbeListing | SQLITE_VdbeTrace},
{ /* zName: */ "vdbe_eqp",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 96, 1,
/* iArg: */ SQLITE_VdbeEQP},
{ /* zName: */ "vdbe_listing",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 98, 1,
/* iArg: */ SQLITE_VdbeListing},
{ /* zName: */ "vdbe_trace",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 100, 1,
/* iArg: */ SQLITE_VdbeTrace},
#endif
#endif
@@ -260,7 +352,7 @@ static const PragmaName aPragmaName[] = {
{ /* zName: */ "where_trace",
/* ePragTyp: */ PragTyp_FLAG,
/* ePragFlg: */ PragFlg_Result0 | PragFlg_NoColumns1,
- /* ColNames: */ 0, 0,
+ /* ColNames: */ 102, 1,
/* iArg: */ SQLITE_WhereTrace},
#endif
};
diff --git a/src/box/sql/prepare.c b/src/box/sql/prepare.c
index 824578e..87326da 100644
--- a/src/box/sql/prepare.c
+++ b/src/box/sql/prepare.c
@@ -54,7 +54,6 @@ sqlite3Prepare(sqlite3 * db, /* Database handle. */
{
char *zErrMsg = 0; /* Error message */
int rc = SQLITE_OK; /* Result code */
- int i; /* Loop counter */
Parse sParse; /* Parsing context */
sql_parser_create(&sParse, db);
sParse.pReprepare = pReprepare;
@@ -113,23 +112,48 @@ sqlite3Prepare(sqlite3 * db, /* Database handle. */
if (rc == SQLITE_OK && sParse.pVdbe && sParse.explain) {
static const char *const azColName[] = {
- "addr", "opcode", "p1", "p2", "p3", "p4", "p5",
- "comment",
- "selectid", "order", "from", "detail"
+ /* 0 */ "addr",
+ /* 1 */ "INTEGER",
+ /* 2 */ "opcode",
+ /* 3 */ "TEXT",
+ /* 4 */ "p1",
+ /* 5 */ "INTEGER",
+ /* 6 */ "p2",
+ /* 7 */ "INTEGER",
+ /* 8 */ "p3",
+ /* 9 */ "INTEGER",
+ /* 10 */ "p4",
+ /* 11 */ "TEXT",
+ /* 12 */ "p5",
+ /* 13 */ "TEXT",
+ /* 14 */ "comment",
+ /* 15 */ "TEXT",
+ /* 16 */ "selectid",
+ /* 17 */ "INTEGER",
+ /* 18 */ "order",
+ /* 19 */ "INTEGER",
+ /* 20 */ "from",
+ /* 21 */ "INTEGER",
+ /* 22 */ "detail",
+ /* 23 */ "TEXT",
};
- int iFirst, mx;
+
+ int name_first, name_count;
if (sParse.explain == 2) {
- sqlite3VdbeSetNumCols(sParse.pVdbe, 4);
- iFirst = 8;
- mx = 12;
+ name_first = 16;
+ name_count = 4;
} else {
- sqlite3VdbeSetNumCols(sParse.pVdbe, 8);
- iFirst = 0;
- mx = 8;
+ name_first = 0;
+ name_count = 8;
}
- for (i = iFirst; i < mx; i++) {
- sqlite3VdbeSetColName(sParse.pVdbe, i - iFirst,
- COLNAME_NAME, azColName[i],
+ sqlite3VdbeSetNumCols(sParse.pVdbe, name_count);
+ for (int i = 0; i < name_count; i++) {
+ int name_index = 2 * i + name_first;
+ sqlite3VdbeSetColName(sParse.pVdbe, i, COLNAME_NAME,
+ azColName[name_index],
+ SQLITE_STATIC);
+ sqlite3VdbeSetColName(sParse.pVdbe, i, COLNAME_DECLTYPE,
+ azColName[name_index + 1],
SQLITE_STATIC);
}
}
diff --git a/test/sql/iproto.result b/test/sql/iproto.result
index 9ace282..e128001 100644
--- a/test/sql/iproto.result
+++ b/test/sql/iproto.result
@@ -821,6 +821,75 @@ box.sql.execute('DROP TABLE t1')
cn:close()
---
...
+-- gh-3832: Some statements do not return column type
+box.sql.execute('CREATE TABLE t1(id INTEGER PRIMARY KEY)')
+---
+...
+cn = remote.connect(box.cfg.listen)
+---
+...
+-- PRAGMA:
+res = cn:execute("PRAGMA table_info(t1)")
+---
+...
+res.metadata
+---
+- - name: cid
+ type: INTEGER
+ - name: name
+ type: TEXT
+ - name: type
+ type: TEXT
+ - name: notnull
+ type: INTEGER
+ - name: dflt_value
+ type: TEXT
+ - name: pk
+ type: INTEGER
+...
+-- EXPLAIN
+res = cn:execute("EXPLAIN select 1")
+---
+...
+res.metadata
+---
+- - name: addr
+ type: INTEGER
+ - name: opcode
+ type: TEXT
+ - name: p1
+ type: INTEGER
+ - name: p2
+ type: INTEGER
+ - name: p3
+ type: INTEGER
+ - name: p4
+ type: TEXT
+ - name: p5
+ type: TEXT
+ - name: comment
+ type: TEXT
+...
+res = cn:execute("EXPLAIN QUERY PLAN select count(*) from t1")
+---
+...
+res.metadata
+---
+- - name: selectid
+ type: INTEGER
+ - name: order
+ type: INTEGER
+ - name: from
+ type: INTEGER
+ - name: detail
+ type: TEXT
+...
+cn:close()
+---
+...
+box.sql.execute('DROP TABLE t1')
+---
+...
box.schema.user.revoke('guest', 'read,write,execute', 'universe')
---
...
diff --git a/test/sql/iproto.test.lua b/test/sql/iproto.test.lua
index 6640903..9f9a382 100644
--- a/test/sql/iproto.test.lua
+++ b/test/sql/iproto.test.lua
@@ -263,10 +263,26 @@ box.sql.execute('DROP TABLE t1')
cn:close()
+-- gh-3832: Some statements do not return column type
+box.sql.execute('CREATE TABLE t1(id INTEGER PRIMARY KEY)')
+cn = remote.connect(box.cfg.listen)
+
+-- PRAGMA:
+res = cn:execute("PRAGMA table_info(t1)")
+res.metadata
+
+-- EXPLAIN
+res = cn:execute("EXPLAIN select 1")
+res.metadata
+res = cn:execute("EXPLAIN QUERY PLAN select count(*) from t1")
+res.metadata
+
+cn:close()
+box.sql.execute('DROP TABLE t1')
+
box.schema.user.revoke('guest', 'read,write,execute', 'universe')
box.schema.user.revoke('guest', 'create', 'space')
space = nil
-- Cleanup xlog
box.snapshot()
-
--
2.7.4
^ permalink raw reply [flat|nested] 2+ messages in thread
* [tarantool-patches] Re: [PATCH v1 1/1] sql: set column types for EXPLAIN and PRAGMA
2018-12-11 20:41 [tarantool-patches] [PATCH v1 1/1] sql: set column types for EXPLAIN and PRAGMA imeevma
@ 2018-12-12 13:21 ` Vladislav Shpilevoy
0 siblings, 0 replies; 2+ messages in thread
From: Vladislav Shpilevoy @ 2018-12-12 13:21 UTC (permalink / raw)
To: imeevma, tarantool-patches
Hi! Thanks for the patch! See 3 comments below,
review fixes at the end of the email and on the
branch.
On 11/12/2018 23:41, imeevma@tarantool.org wrote:
> Currently, EXPLAIN and PRAGMA do not set the column types for the
> result. This is incorrect, since any returned row must have a
> column type. This patch defines the types for these columns.
>
> Closes #3832
> ---
> https://github.com/tarantool/tarantool/issues/3832
> https://github.com/tarantool/tarantool/tree/imeevma/gh-3832-no-column-types
>
> src/box/execute.c | 5 +-
> src/box/sql/pragma.c | 29 +++----
> src/box/sql/pragma.h | 214 +++++++++++++++++++++++++++++++++--------------
> src/box/sql/prepare.c | 52 ++++++++----
> test/sql/iproto.result | 69 +++++++++++++++
> test/sql/iproto.test.lua | 18 +++-
> 6 files changed, 295 insertions(+), 92 deletions(-)
>
> diff --git a/src/box/sql/pragma.c b/src/box/sql/pragma.c
> index 5c35017..e1598d9 100644
> --- a/src/box/sql/pragma.c
> +++ b/src/box/sql/pragma.c
> @@ -112,25 +112,26 @@ sqlite3GetBoolean(const char *z, u8 dflt)
> * the rest of the file if PRAGMAs are omitted from the build.
> */
>
> -/*
> - * Set result column names for a pragma.
> +/**
> + * Set result column names and types for a pragma.
> + *
> + * @param v the query under construction.
> + * @param pPragma the pragma.
> */
> static void
> -setPragmaResultColumnNames(Vdbe * v, /* The query under construction */
> - const PragmaName * pPragma /* The pragma */
> - )
> +setPragmaResultColumnNames(Vdbe *v, const PragmaName *pPragma)
> {
> u8 n = pPragma->nPragCName;
> - sqlite3VdbeSetNumCols(v, n == 0 ? 1 : n);
> - if (n == 0) {
> - sqlite3VdbeSetColName(v, 0, COLNAME_NAME, pPragma->zName,
> + assert(n > 0);
> + sqlite3VdbeSetNumCols(v, n);
> + int i, j;
> + for (i = 0, j = pPragma->iPragCName; i < n; i++, j++) {
> + /* Value of j is index of column name in array */
> + sqlite3VdbeSetColName(v, i, COLNAME_NAME, pragCName[j++],
> + SQLITE_STATIC);
> + /* Value of j is index of column type in array */
> + sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, pragCName[j],
> SQLITE_STATIC);
> - } else {
> - int i, j;
> - for (i = 0, j = pPragma->iPragCName; i < n; i++, j++) {
> - sqlite3VdbeSetColName(v, i, COLNAME_NAME, pragCName[j],
> - SQLITE_STATIC);
> - }
> }
> }
>
1. You rewrote this function almost completely. Usually in
such a case we reformat the function into Tarantool code style.
> diff --git a/src/box/sql/pragma.h b/src/box/sql/pragma.h
> index e608016..c6c3e63 100644
> --- a/src/box/sql/pragma.h
> +++ b/src/box/sql/pragma.h
> @@ -27,50 +27,142 @@
> #define PragFlg_SchemaOpt 0x40 /* Schema restricts name search if present */
> #define PragFlg_SchemaReq 0x80 /* Schema required - "main" is default */
>
> -/* Names of columns for pragmas that return multi-column result
> - * or that return single-column results where the name of the
> - * result column is different from the name of the pragma
> +/**
> + * Column names and types for pragmas. The type of the column is
> + * the following value after its name.
> */
> static const char *const pragCName[] = {
> /* Used by: table_info */
> /* 0 */ "cid",
> - /* 1 */ "name",
> - /* 2 */ "type",
> - /* 3 */ "notnull",
> - /* 4 */ "dflt_value",
> - /* 5 */ "pk",
> + /* 1 */ "INTEGER",
> + /* 2 */ "name",
> + /* 3 */ "TEXT",
> + /* 4 */ "type",
> + /* 3 */ "TEXT",
> + /* 6 */ "notnull",
> + /* 1 */ "INTEGER",
> + /* 8 */ "dflt_value",
> + /* 9 */ "TEXT",
> + /* 10 */ "pk",
> + /* 11 */ "INTEGER",
> /* Used by: stats */
> - /* 6 */ "table",
> - /* 7 */ "index",
> - /* 8 */ "width",
> - /* 9 */ "height",
> + /* 12 */ "table",
> + /* 13 */ "TEXT",
> + /* 14 */ "index",
> + /* 15 */ "TEXT",
> + /* 16 */ "width",
> + /* 17 */ "INTEGER",
> + /* 18 */ "height",
> + /* 19 */ "INTEGER",
2. I see that sql_pragma_table_stats returns rows of different types
of the same columns. First row has 4 columns with types "ssii", other
rows have 3 columns "sii". I think, all rows should have the same
number of columns of the same types. Also, the first row actually
duplicates the second - they both describe the primary index.
Please, consult server team chat what to do with it. I think, that we
should always return 3 columns "sii": index name, avg tuple size, index size.
So we just should delete the current first row.
> /* Used by: index_info */
> - /* 10 */ "seqno",
> - /* 11 */ "cid",
> - /* 12 */ "name",
> - /* 13 */ "desc",
> - /* 14 */ "coll",
> - /* 15 */ "type",
> + /* 20 */ "seqno",
> + /* 21 */ "INTEGER",
> + /* 22 */ "cid",
> + /* 23 */ "INTEGER",
> + /* 24 */ "name",
> + /* 25 */ "TEXT",
> + /* 26 */ "desc",
> + /* 27 */ "INTEGER",
> + /* 28 */ "coll",
> + /* 29 */ "TEXT",
> + /* 30 */ "type",
> + /* 31 */ "TEXT",
> /* Used by: index_list */
> - /* 16 */ "seq",
> - /* 17 */ "name",
> - /* 18 */ "unique",
> - /* 19 */ "origin",
> - /* 20 */ "partial",
> + /* 32 */ "seq",
> + /* 33 */ "INTEGER",
> + /* 34 */ "name",
> + /* 35 */ "TEXT",
> + /* 36 */ "unique",
> + /* 37 */ "INTEGER",
> + /* 38 */ "origin",
> + /* 39 */ "TEXT",
> + /* 40 */ "partial",
> + /* 41 */ "INTEGER",
> /* Used by: collation_list */
> - /* 21 */ "seq",
> - /* 22 */ "name",
> + /* 42 */ "seq",
> + /* 43 */ "INTEGER",
> + /* 44 */ "name",
> + /* 45 */ "TEXT",
> /* Used by: foreign_key_list */
> - /* 23 */ "id",
> - /* 24 */ "seq",
> - /* 25 */ "table",
> - /* 26 */ "from",
> - /* 27 */ "to",
> - /* 28 */ "on_update",
> - /* 29 */ "on_delete",
> - /* 30 */ "match",
> + /* 46 */ "id",
> + /* 47 */ "INTEGER",
> + /* 48 */ "seq",
> + /* 49 */ "INTEGER",
> + /* 50 */ "table",
> + /* 51 */ "TEXT",
> + /* 52 */ "from",
> + /* 53 */ "TEXT",
> + /* 54 */ "to",
> + /* 55 */ "TEXT",
> + /* 56 */ "on_update",
> + /* 57 */ "TEXT",
> + /* 58 */ "on_delete",
> + /* 59 */ "TEXT",
> + /* 60 */ "match",
> + /* 61 */ "TEXT",
> /* Used by: busy_timeout */
> - /* 31 */ "timeout",
> + /* 62 */ "timeout",
> + /* 63 */ "INTEGER",
> + /* Used by: case_sensitive_like */
> + /* 64 */ "case_sensitive_like",
> + /* 65 */ "INTEGER",
3. I do not see where case_sensitive_like returns anything.
Looks like it is has 'void' return type. So it should not
have any out names/types. The same about all other pragmas
below. Or am I wrong?
> + /* Used by: count_changes */
> + /* 66 */ "count_changes",
> + /* 67 */ "INTEGER",
> + /* Used by: defer_foreign_keys */
> + /* 68 */ "defer_foreign_keys",
> + /* 69 */ "INTEGER",
> + /* Used by: full_column_names */
> + /* 70 */ "full_column_names",
> + /* 71 */ "INTEGER",
> + /* Used by: parser_trace */
> + /* 72 */ "parser_trace",
> + /* 73 */ "INTEGER",
> + /* Used by: query_only */
> + /* 74 */ "query_only",
> + /* 75 */ "INTEGER",
> + /* Used by: read_uncommitted */
> + /* 76 */ "read_uncommitted",
> + /* 77 */ "INTEGER",
> + /* Used by: recursive_triggers */
> + /* 78 */ "recursive_triggers",
> + /* 79 */ "INTEGER",
> + /* Used by: reverse_unordered_selects */
> + /* 80 */ "reverse_unordered_selects",
> + /* 81 */ "INTEGER",
> + /* Used by: select_trace */
> + /* 82 */ "select_trace",
> + /* 83 */ "INTEGER",
> + /* Used by: short_column_names */
> + /* 84 */ "short_column_names",
> + /* 85 */ "INTEGER",
> + /* Used by: sql_compound_select_limit */
> + /* 86 */ "sql_compound_select_limit",
> + /* 87 */ "INTEGER",
> + /* Used by: sql_default_engine */
> + /* 88 */ "sql_default_engine",
> + /* 89 */ "INTEGER",
> + /* Used by: sql_trace */
> + /* 90 */ "sql_trace",
> + /* 91 */ "INTEGER",
> + /* Used by: vdbe_addoptrace */
> + /* 92 */ "vdbe_addoptrace",
> + /* 93 */ "INTEGER",
> + /* Used by: vdbe_debug */
> + /* 94 */ "vdbe_debug",
> + /* 95 */ "INTEGER",
> + /* Used by: vdbe_eqp */
> + /* 96 */ "vdbe_eqp",
> + /* 97 */ "INTEGER",
> + /* Used by: vdbe_listing */
> + /* 98 */ "vdbe_listing",
> + /* 99 */ "INTEGER",
> + /* Used by: vdbe_trace */
> + /* 100 */ "vdbe_trace",
> + /* 101 */ "INTEGER",
> + /* Used by: where_trace */
> + /* 102 */ "where_trace",
> + /* 103 */ "INTEGER",
> };
=======================================================
commit 2b019a9be547484036a8e666a2cf8b1941ce0b30
Author: Vladislav Shpilevoy <v.shpilevoy@tarantool.org>
Date: Wed Dec 12 16:19:27 2018 +0300
Review fixes
diff --git a/src/box/sql/pragma.c b/src/box/sql/pragma.c
index e1598d951..ef80bf801 100644
--- a/src/box/sql/pragma.c
+++ b/src/box/sql/pragma.c
@@ -112,25 +112,19 @@ sqlite3GetBoolean(const char *z, u8 dflt)
* the rest of the file if PRAGMAs are omitted from the build.
*/
-/**
- * Set result column names and types for a pragma.
- *
- * @param v the query under construction.
- * @param pPragma the pragma.
- */
+/** Set result column names and types for a pragma. */
static void
-setPragmaResultColumnNames(Vdbe *v, const PragmaName *pPragma)
+vdbe_set_pragma_result_columns(struct Vdbe *v, const struct PragmaName *pragma)
{
- u8 n = pPragma->nPragCName;
+ int n = pragma->nPragCName;
assert(n > 0);
sqlite3VdbeSetNumCols(v, n);
- int i, j;
- for (i = 0, j = pPragma->iPragCName; i < n; i++, j++) {
+ for (int i = 0, j = pragma->iPragCName; i < n; ++i) {
/* Value of j is index of column name in array */
sqlite3VdbeSetColName(v, i, COLNAME_NAME, pragCName[j++],
SQLITE_STATIC);
/* Value of j is index of column type in array */
- sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, pragCName[j],
+ sqlite3VdbeSetColName(v, i, COLNAME_DECLTYPE, pragCName[j++],
SQLITE_STATIC);
}
}
@@ -461,17 +455,15 @@ sqlite3Pragma(Parse * pParse, Token * pId, /* First part of [schema.]id field */
}
/* Register the result column names for pragmas that return results */
if ((pPragma->mPragFlg & PragFlg_NoColumns) == 0
- && ((pPragma->mPragFlg & PragFlg_NoColumns1) == 0 || zRight == 0)
- ) {
- setPragmaResultColumnNames(v, pPragma);
- }
+ && ((pPragma->mPragFlg & PragFlg_NoColumns1) == 0 || zRight == 0))
+ vdbe_set_pragma_result_columns(v, pPragma);
/* Jump to the appropriate pragma handler */
switch (pPragma->ePragTyp) {
#ifndef SQLITE_OMIT_FLAG_PRAGMAS
case PragTyp_FLAG:{
if (zRight == 0) {
- setPragmaResultColumnNames(v, pPragma);
+ vdbe_set_pragma_result_columns(v, pPragma);
returnSingleInt(v,
(user_session->
sql_flags & pPragma->iArg) !=
^ permalink raw reply [flat|nested] 2+ messages in thread
end of thread, other threads:[~2018-12-12 13:21 UTC | newest]
Thread overview: 2+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2018-12-11 20:41 [tarantool-patches] [PATCH v1 1/1] sql: set column types for EXPLAIN and PRAGMA imeevma
2018-12-12 13:21 ` [tarantool-patches] " Vladislav Shpilevoy
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox