Trouble ticket for this problem here: http://www.sqlite.org/src/info/98825a79ce145686392d8074032ae54863aa21a3
On Thu, Apr 3, 2014 at 9:24 AM, Hinrichsen, John <jhinrich...@c10p.com>wrote: > I am posting my C repro for the virtual table join issue inline, below: > > /* > * This repro is intended to demonstrate a possible bug when joining > * two virtual table instances together: only a subset of the expected > * rows is returned by sqlite. As far as we can tell, it only happens > * when the virtual tables are themselves backed by nontrivial sqlite > * queries. > * > * This problem occurs with sqlite-3.8.4.1. > * It does not occur with sqlite-3.8.3.1 or earlier versions. > * > * The output of the program first shows the result of a join using > * two virtual table instances; the second result shows the output > * of the equivalent join on two non-virtual tables created from > * 'SELECT * FROM' the two respective virtual table instances. > */ > > #include <stdio.h> > #include <string.h> > #include <assert.h> > #include <sqlite3.h> > > static sqlite3 * s_db = NULL; > > static char * remove_optional_quotes(const char * const input) > { > char * ret; > const int len = strlen(input); > > if (input[0] == input[len-1] && (input[0] == '"' || input[0] == '\'')) > { > ret = sqlite3_malloc(len - 1); > strncpy(ret, input+1, len-2); > ret[len-2] = '\0'; > } > else > { > ret = sqlite3_malloc(len + 1); > strncpy(ret, input, len); > ret[len] = '\0'; > } > return ret; > } > > struct test_vtab > { > sqlite3_vtab base; > char * query; > }; > > static int repro_create_connect(sqlite3 * db, void * pAux, int argc, const > char * const argv[], sqlite3_vtab ** ppVTab, char ** pzErr) > { > int err; > char * unquoted_schema; > > * ppVTab = sqlite3_malloc(sizeof(struct test_vtab)); > if (!* ppVTab) > return SQLITE_NOMEM; > memset(* ppVTab, 0, sizeof(** ppVTab)); > > struct test_vtab * vtab = (struct test_vtab *) * ppVTab; > vtab->query = remove_optional_quotes(argv[4]); > > unquoted_schema = remove_optional_quotes(argv[3]); > err = sqlite3_declare_vtab(db, unquoted_schema); > sqlite3_free(unquoted_schema); > > if (err != SQLITE_OK) { > sqlite3_free(vtab->query); > sqlite3_free(vtab); > * ppVTab = NULL; > return err; > } > > return SQLITE_OK; > } > > static int bitcount(unsigned int n) > { > int count = 0; > while (n) > { > ++count; > n &= (n - 1); > } > return count; > } > > static int test_bit(const int value, const int bit) > { > const int mask = 1 << bit; > > return ((value & mask) == mask); > } > > static int repro_best_index(sqlite3_vtab * pVTab, sqlite3_index_info * > index_info) > { > const int column_count = 2; > > int index_number = 0, constraint_index, column_index; > > int constraint_by_column[2]; > > for (constraint_index = 0; constraint_index < index_info->nConstraint; > ++constraint_index) > { > const struct sqlite3_index_constraint * constraint = > &index_info->aConstraint[constraint_index]; > > const int is_usable = (constraint->usable != 0); > > if (is_usable) > { > const int is_indexed_column = (constraint->iColumn >= 0) && > (constraint->iColumn < column_count); > const int is_equality = (constraint->op == > SQLITE_INDEX_CONSTRAINT_EQ); > > if (is_indexed_column && is_equality) > { > constraint_by_column[constraint->iColumn] = > constraint_index; > > index_number |= (1 << constraint->iColumn); > } > } > } > > int argument_index = 0; > > for (column_index = 0; column_index < column_count; ++column_index) > { > const int using_column = test_bit(index_number, column_index); > > if (using_column) > { > const int constraint_index = > constraint_by_column[column_index]; > > assert(constraint_index < index_info->nConstraint); > > struct sqlite3_index_constraint_usage * usage = > &index_info->aConstraintUsage[constraint_index]; > > usage->argvIndex = argument_index + 1; > usage->omit = 0; > > ++argument_index; > } > } > > index_info->estimatedCost = 1.0; > > for (column_index = 0; column_index < column_count; ++column_index) > { > const int using_column = test_bit(index_number, column_index); > > if (! using_column) > { > const double penalty = 10.0 + (column_count - column_index); > > index_info->estimatedCost *= penalty; > } > } > > index_info->idxNum = index_number; > index_info->idxStr = ""; > index_info->needToFreeIdxStr = 0; > index_info->orderByConsumed = 0; > > return SQLITE_OK; > } > > static int repro_disconnect_destroy(sqlite3_vtab * pVTab) > { > struct test_vtab * vtab = (struct test_vtab *) pVTab; > > sqlite3_free(vtab->query); > sqlite3_free(vtab); > return SQLITE_OK; > } > > struct test_cursor > { > sqlite3_vtab_cursor base; > sqlite3_stmt * stmt; > int row; > }; > > static int repro_open(sqlite3_vtab * pVTab, sqlite3_vtab_cursor ** > ppCursor) > { > struct test_cursor * cursor; > > cursor = sqlite3_malloc(sizeof(struct test_cursor)); > if (!cursor) > return SQLITE_NOMEM; > memset(cursor, 0, sizeof(* cursor)); > > * ppCursor = &cursor->base; > > return SQLITE_OK; > } > > static int repro_close(sqlite3_vtab_cursor * pCursor) > { > sqlite3_free(pCursor); > return SQLITE_OK; > } > > static int repro_eof(sqlite3_vtab_cursor * pCursor) > { > struct test_cursor * cursor = (struct test_cursor *) pCursor; > > return cursor->row < 0; > } > > static int repro_next(sqlite3_vtab_cursor * pCursor) > { > struct test_cursor * cursor = (struct test_cursor *) pCursor; > > const int res = sqlite3_step(cursor->stmt); > > switch (res) > { > case SQLITE_ROW: > cursor->row++; > break; > case SQLITE_DONE: > cursor->row = -1; > sqlite3_finalize(cursor->stmt); > break; > default: > assert(0); > } > > return SQLITE_OK; > } > > static int repro_filter(sqlite3_vtab_cursor * pCursor, int idxNum, const > char * idxStr, int argc, sqlite3_value ** argv) > { > struct test_cursor * cursor = (struct test_cursor *) pCursor; > struct test_vtab * vtab = (struct test_vtab *) pCursor->pVtab; > > int column, res, arg_index; > > char buffer[512]; > > buffer[0] = '\0'; > cursor->row = 0; > > const char * add_filter = strstr(vtab->query, " ORDER BY "); > > for (arg_index = 0, column = 0; column < 2; ++column) > { > if (test_bit(idxNum, column)) > { > sqlite3_value * const argument = argv[arg_index]; > strcat(buffer, arg_index == 0 ? " WHERE (A='" : " AND (B='"); > strcat(buffer, sqlite3_value_text(argument)); > strcat(buffer, "')"); > ++arg_index; > } > } > > if (buffer[0]) > { > char * malloced = sqlite3_malloc(strlen(buffer) + > strlen(vtab->query)); > strncpy(malloced, vtab->query, add_filter - vtab->query); > strcpy(malloced + (add_filter - vtab->query), buffer); > strcat(malloced, add_filter); > res = sqlite3_prepare_v2(s_db, malloced, -1, &cursor->stmt, NULL); > sqlite3_free(malloced); > } > else > { > res = sqlite3_prepare_v2(s_db, vtab->query, -1, &cursor->stmt, > NULL); > } > > assert(res == SQLITE_OK); > repro_next(pCursor); > > return SQLITE_OK; > } > > static int repro_column(sqlite3_vtab_cursor * pCursor, sqlite3_context * > context, const int column) > { > struct test_cursor * cursor = (struct test_cursor *) pCursor; > > assert(cursor->row >= 0); > > sqlite3_result_value(context, sqlite3_column_value(cursor->stmt, > column)); > > return SQLITE_OK; > } > > static int repro_rowid(sqlite3_vtab_cursor * pCursor, sqlite_int64 * > pRowid) > { > struct test_cursor * cursor = (struct test_cursor *) pCursor; > > * pRowid = cursor->row; > return SQLITE_OK; > } > > static int repro_rename(sqlite3_vtab * pVTab, const char * zNew) > { > return SQLITE_OK; > } > > static sqlite3_module module = > { > .iVersion = 1, > .xCreate = repro_create_connect, > .xConnect = repro_create_connect, > .xBestIndex = repro_best_index, > .xDisconnect = repro_disconnect_destroy, > .xDestroy = repro_disconnect_destroy, > .xOpen = repro_open, > .xClose = repro_close, > .xFilter = repro_filter, > .xNext = repro_next, > .xEof = repro_eof, > .xColumn = repro_column, > .xRowid = repro_rowid, > .xRename = repro_rename, > }; > > static void execute_statement_with_result(sqlite3 * db, const char * query) > { > sqlite3_stmt * stmt; > int i, cols, res = sqlite3_prepare_v2(db, query, -1, &stmt, NULL); > assert(res == SQLITE_OK); > cols = sqlite3_column_count(stmt); > res = sqlite3_step(stmt); > while (res == SQLITE_ROW) > { > for (i = 0; i < cols; i++) > printf(" %s", sqlite3_column_text(stmt, i)); > putchar('\n'); > res = sqlite3_step(stmt); > } > assert(res == SQLITE_DONE); > sqlite3_finalize(stmt); > } > > int main(int argc, char * argv[]) > { > sqlite3 * db; > int res = sqlite3_open(":memory:", &db); > > assert(res == SQLITE_OK); > > s_db = db; > > res = sqlite3_create_module(db, "test", &module, NULL); > assert(res == SQLITE_OK); > > res = sqlite3_exec(db, "CREATE TABLE t0 AS " > "SELECT 'ABCDEF' AS A, 'A' AS B, '1_XYZ' AS C, 3.76983863156436 > AS D " > "UNION ALL SELECT 'ABCDEF','A','2_XYZ',3.78321223983948 " > "UNION ALL SELECT 'ABCDEF','B','1_XYZ',5.3721319058827 " > "UNION ALL SELECT 'ABCDEF','B','2_XYZ',5.43295877241399 " > , NULL, NULL, NULL); > assert(res == SQLITE_OK); > > res = sqlite3_exec(db, "CREATE TABLE t9 AS " > "SELECT 'ABCDEF' AS A,'A' AS B,'1_JK' AS C,0.0 AS D " > "UNION ALL SELECT 'ABCDEF','B','1_JK',0.0 " > "UNION ALL SELECT 'ABCDEF','A','2_JK',0.0 " > "UNION ALL SELECT 'ABCDEF','B','2_JK',0.0 " > , NULL, NULL, NULL); > assert(res == SQLITE_OK); > > res = sqlite3_exec(db, "CREATE VIRTUAL TABLE t1 USING test(" > "'CREATE TABLE x(C1 TEXT, C2 TEXT, PRIMARY > KEY (C1,C2))'," > "'SELECT DISTINCT A,B FROM (SELECT A,B FROM > t0 GROUP BY A,B) ORDER BY A,B')", > NULL, NULL, NULL); > assert(res == SQLITE_OK); > > res = sqlite3_exec(db, "CREATE VIRTUAL TABLE t2 USING test(" > "'CREATE TABLE x(C1 TEXT, C2 TEXT, PRIMARY > KEY (C0,C2))'," > "'SELECT DISTINCT A,B FROM (SELECT A,B FROM > t9 GROUP BY A,B) ORDER BY A,B')", > NULL, NULL, NULL); > assert(res == SQLITE_OK); > > printf("Joining virtual tables:\n"); > execute_statement_with_result(db, "SELECT L.C1, L.C2 FROM t1 L JOIN t2 > R ON L.C1=R.C1 AND L.C2=R.C2"); > > res = sqlite3_exec(db, "CREATE TABLE t3 AS SELECT * FROM t1", NULL, > NULL, NULL); > assert(res == SQLITE_OK); > > res = sqlite3_exec(db, "CREATE TABLE t4 AS SELECT * FROM t2", NULL, > NULL, NULL); > assert(res == SQLITE_OK); > > printf("Joining nonvirtual tables based on virtual tables:\n"); > execute_statement_with_result(db, "SELECT L.C1, L.C2 FROM t3 L JOIN t4 > R ON L.C1=R.C1 AND L.C2=R.C2"); > > sqlite3_close(db); > s_db = db = NULL; > > return 0; > } > > > > On Wed, Apr 2, 2014 at 7:53 PM, Donald Griggs <dfgri...@gmail.com> wrote: > > > Attachments can't appear on this list. You can use a shared file > service > > and post a link, or for smallish amounts of text use something like > > pastbin.com. > > > > > > On Wed, Apr 2, 2014 at 6:42 PM, Andy Goth <andrew.m.g...@gmail.com> > wrote: > > > > > On 4/2/2014 4:52 PM, Hinrichsen, John wrote: > > > > > >> sqlite 3.8.4.1 can return an incorrect result when joining two virtual > > >> tables that are themselves based on underlying sqlite tables. > > >> > > >> This problem does not happen with sqlite 3.8.3.1 or earlier. > > >> > > >> Please see the attached repro. > > >> > > > > > > Attachment appears to be missing. > > > > > > -- > > > Andy Goth | <andrew.m.goth/at/gmail/dot/com> > > > _______________________________________________ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > > This message contains confidential information and is intended only for the > individual named. If you are not the named addressee, you should not > disseminate, distribute, alter or copy this e-mail. Please notify the > sender immediately by e-mail if you have received this e-mail by mistake > and delete this e-mail from your system. E-mail transmissions cannot be > guaranteed to be secure or without error as information could be > intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The > sender, therefore, does not accept liability for any errors or omissions in > the contents of this message which arise during or as a result of e-mail > transmission. If verification is required, please request a hard-copy > version. This message is provided for information purposes and should not > be construed as a solicitation or offer to buy or sell any securities or > related financial instruments in any jurisdiction. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users