Re: [sqlite] virtual table xBestIndex and SQLITE_CONSTRAINT and zErrMsg

[Richard Hipp]

On 2/19/19, dave  wrote:
> I noticed that in 3.26 a feature was added whereby a proposed execution plan
> can be rejected in vtables by returning SQLITE_CONSTRAINT.  I welcome this
> addition, but I have lost a capability relative to the prior scheme of using
> high query cost along with a special flag communicated in pIdxInfo->idxNum,
> that being the ablilty to emit contextual info as to why the query failed.

Yeah.  There is no way to report an error out of xBestIndex.  And, in
fact, you would not want to do that because one or more xBestIndex
calls might actually work.  Or, there might be multiple xBestIndex
calls that all fail for different reasons, in which case it is unclear
which error should be reported.

I will ponder your request.  In the meantime, you can continue to use
the old method, which still works like it always has.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] virtual table xBestIndex and SQLITE_CONSTRAINT and zErrMsg

[dave]

I noticed that in 3.26 a feature was added whereby a proposed execution plan
can be rejected in vtables by returning SQLITE_CONSTRAINT.  I welcome this
addition, but I have lost a capability relative to the prior scheme of using
high query cost along with a special flag communicated in pIdxInfo->idxNum,
that being the ablilty to emit contextual info as to why the query failed.

Under the new scheme, a failed query is met with:

  Error: no query solution

But under the old scheme I was able to emit:

  Error: GROUPACCTS: There must be equality constraints on GNAME and ISLOCAL

The context info is handy for developers building the query so they can know
what they are missing, since required constraints like this are non-obvious
from a pure SQL standpoint.  This is especially true in the context of
joins, since then you otherwise wouldn't even know what table is
problemattic.

Under the old scheme I would have to fail my query in xFilter, and I would
set the error text like this:

 if ( IDXVAL_FAILQUERYPLAN == idxNum )
 {
  sqlite3_free( pThis->pVtab->zErrMsg );
  pThis->pVtab->zErrMsg = sqlite3_mprintf( VTBLA4GNAME": There must be
equality constraints on GNAME and ISLOCAL" );
  return SQLITE_CONSTRAINT;
 }

I did try setting the error text in a similar manner in the xFilter method,
however it seems this text is ignored in that case, and I only get the 'no
solution message'.

My suggestion would be to not ignore it in the case of failing xBestIndex
for no query plan, and to emit it if it has been set.

If this is done, I imagine some additional consideration would have to be
made for the case where one proposed query plan is rejected, and another
plan has been accepted.  In that case, maybe the net successful plan would
still have error texts from the previous rejected plan?  I don't know if
this would cause a problem or not.

Cheers!
-dave


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users