Hello !
When querying views sqlite shows qualified column names if they are specified
individually.
Is this the expected result or a bug ?
This behavior breaks some of my code that uses column names for other
purposes.
Cheers !
====output of "test-view-alias"
SQL: SELECT a.* FROM tbl AS a;
Column 0: id
Column 1: name
SQL: SELECT a.id, a.name FROM tbl AS a;
Column 0: id
Column 1: name
SQL: SELECT a.* FROM tbl_view AS a;
Column 0: id
Column 1: name
SQL: SELECT a.id, a.name FROM tbl_view AS a;
Column 0: a.id?? <<<<<<<<<<<<<<<<<<<<< only with individual fields
Column 1: a.name <<<<<<<<<<<<<<<<<<<
====
==== test-view-alias.c
#include <stdio.h>
#include "sqlite3.h"
void showStmtColNames(sqlite3 *db, const char *szSQL)
{
??? sqlite3_stmt* stmt;
??? printf("SQL: %s\n", szSQL);
??? int rc = sqlite3_prepare_v2(db, szSQL, -1, &stmt, 0);
??? int i, col_count = sqlite3_column_count(stmt);
??? for(i=0; i < col_count; ++i)
??? {
??? ??? printf("Column %d: %s\n", i, sqlite3_column_name(stmt, i));
??? }
??? sqlite3_finalize(stmt);
}
int main(int argc, char *argv[])
{
??? sqlite3 *db;
??? const char dbname_szSQL[] = ":memory:";
??? int rc = sqlite3_open(dbname_szSQL, &db);
??? if(rc == SQLITE_OK)
??? {
??? ??? char *errmsg;
??? ??? rc = sqlite3_exec(db, "CREATE TABLE tbl(id, name);", NULL,
NULL, &errmsg);
??? ??? rc = sqlite3_exec(db, "CREATE VIEW tbl_view AS SELECT a.id,
a.name FROM tbl AS a;", NULL, NULL, &errmsg);
??? ??? rc = sqlite3_exec(db, "INSERT INTO tbl(id, name) VALUES(1,
'dad');", NULL, NULL, &errmsg);
??? ???
??? ??? showStmtColNames(db, "SELECT a.* FROM tbl AS a;");
??? ??? showStmtColNames(db, "SELECT a.id, a.name FROM tbl AS a;");
??? ??? showStmtColNames(db, "SELECT a.* FROM tbl_view AS a;");
??? ??? showStmtColNames(db, "SELECT a.id, a.name FROM tbl_view AS
a;"); //here only we get qualified names
??? ???
??? ??? sqlite3_close(db);
??? }
??? return 0;
}
====
====script to compile "test-view-alias.c"
MYINC=.
gcc -g -O2 \
??? -DSQLITE_DEBUG=1 \
??? -DSQLITE_ENABLE_EXPLAIN_COMMENTS=1 \
??? -DTHREADSAFE=1 \
??? -DSQLITE_DEFAULT_FILE_FORMAT=4 \
??? -DSQLITE_DEFAULT_AUTOVACUUM=1 \
??? -DSQLITE_DEFAULT_FOREIGN_KEYS=1 \
??? -DSQLITE_ENABLE_COLUMN_METADATA=1 \
??? -DSQLITE_ENABLE_FTS4=1 \
??? -DSQLITE_ENABLE_FTS3_PARENTHESIS=1 \
??? -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 \
??? -DSQLITE_ENABLE_RTREE=1 \
??? -DSQLITE_ENABLE_STAT4=1 \
??? -DSQLITE_OMIT_TCL_VARIABLE=1 \
??? -DSQLITE_USE_URI=1 \
??? -DSQLITE_SOUNDEX=1\
??? -o test-view-alias test-view-alias.c -I $MYINC $MYINC/sqlite3.c
-lpthread -lm -ldl
====
