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
