virtual table xBestIndex and SQLITE_CONSTRAINT and zErrMsg

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: virtual table xBestIndex and SQLITE_CONSTRAINT and zErrMsg

Richard Hipp-3
On 2/19/19, dave <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: virtual table xBestIndex and SQLITE_CONSTRAINT andzErrMsg

dave
> On 2/19/19, dave <[hidden email]> wrote:
> > 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


OK, well the theory being that the message would be emitted only when all
the candidate plans were tried, and still no solution waa found (I guess at
the same spot where the current message is emitted).  But maybe that is too
late, and any messages set along the way are already gone.

As for multiple messages, even just emitting an arbitrary one is useful.
These failures happen at design time and the developer incrementally refines
his/her query until there were no such errors.  I'm not sure if it is
possible to happen once a working query has been created.  I would think
that if you had defined a query that was demonstably solvable once, that any
subsequent executions would at worst gravitate to that known working soluton
even if the planner tried to do things differently that time (maybe based on
data values).

OK, for now I will revert to the old method.

Cheers!

-dave


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users