Re: [sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1
That was a fast turn-around. Thank you for addressing this issue so quickly! -- 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
Re: [sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1
Trouble ticket for this problem here:
http://www.sqlite.org/src/info/98825a79ce145686392d8074032ae54863aa21a3
On Thu, Apr 3, 2014 at 9:24 AM, Hinrichsen, John 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
> #include
> #include
> #include
>
> 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 tes
[sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1
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
#include
#include
#include
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
Re: [sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1
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 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 | > ___ > 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
Re: [sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1
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 | ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Join of two virtual tables returns incorrect result set in 3.8.4.1
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. -- 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
