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
