[sqlite] unable to use function MATCH in the requested context
This is a class of problems I started seeing around 3.8.x. Any insights
would be much appreciated. Sometimes it happens when there are more than a
few INNER JOINS, others, in the case, happen when I ORDER BY the virtual
table's docid. I feel like this used to work.
SQLite version 3.8.2 2013-12-06 14:53:30
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite CREATE VIRTUAL TABLE Terms USING fts3 (term)
... ;
sqlite INSERT INTO Terms VALUES ('hello tim');
sqlite INSERT INTO Terms VALUES ('goodbye tim');
sqlite CREATE TEMP TABLE Combos (combo);
sqlite INSERT INTO Combos VALUES ('hello');
sqlite SELECT * FROM Terms t INNER JOIN Combos c ON t.term MATCH c.combo;
hello tim|hello
sqlite SELECT * FROM Terms t INNER JOIN Combos c ON t.term MATCH c.combo
ORDER BY t.docid;
Error: unable to use function MATCH in the requested context
sqlite
I know ordering by t.docid is probably redundant, but it is an example of
the issue I'm encountering. This is an issue in 3.8.0 also. The last time
it worked was 3.7.17
SQLite version 3.7.17 2013-05-20 00:56:22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite CREATE VIRTUAL TABLE Terms USING fts3 (term)
... ;
sqlite INSERT INTO Terms VALUES ('hello tim');
sqlite INSERT INTO Terms VALUES ('goodbye tim');
sqlite CREATE TEMP TABLE Combos (combo);
sqlite INSERT INTO Combos VALUES ('hello');
sqlite SELECT * FROM Terms t INNER JOIN Combos c ON t.term MATCH c.combo;
hello tim|hello
sqlite SELECT * FROM Terms t INNER JOIN Combos c ON t.term MATCH c.combo
ORDER
BY t.docid;
hello tim|hello
sqlite
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] amalgamation sqlite3.c:142353: warning: assignment from incompatible pointer type
Hello.
During compilation, the warning occurs
sqlite3.c: In function 'deserializeGeometry':
sqlite3.c:142353: warning: assignment from incompatible pointer type
for the sources of http://www.sqlite.org/2013/sqlite-autoconf-3080200.tar.gz
with -DSQLITE_RTREE_INT_ONLY=1
and really
142314 /*
142315 ** This function is called to configure the RtreeConstraint
object passed
142316 ** as the second argument for a MATCH constraint. The value
passed as the
142317 ** first argument to this function is the right-hand operand
to the MATCH
142318 ** operator.
142319 */
142320 static int deserializeGeometry(sqlite3_value *pValue,
RtreeConstraint *pCons){
142321 RtreeMatchArg *p;
142322 sqlite3_rtree_geometry *pGeom;
142323 int nBlob;
142324
142325 /* Check that value is actually a blob. */
142326 if( sqlite3_value_type(pValue)!=SQLITE_BLOB ) return SQLITE_ERROR;
142327
142328 /* Check that the blob is roughly the right size. */
142329 nBlob = sqlite3_value_bytes(pValue);
142330 if( nBlob(int)sizeof(RtreeMatchArg)
142331|| ((nBlob-sizeof(RtreeMatchArg))%sizeof(RtreeDValue))!=0
142332 ){
142333 return SQLITE_ERROR;
142334 }
142335
142336 pGeom = (sqlite3_rtree_geometry *)sqlite3_malloc(
142337 sizeof(sqlite3_rtree_geometry) + nBlob
142338 );
142339 if( !pGeom ) return SQLITE_NOMEM;
142340 memset(pGeom, 0, sizeof(sqlite3_rtree_geometry));
142341 p = (RtreeMatchArg *)pGeom[1];
142342
142343 memcpy(p, sqlite3_value_blob(pValue), nBlob);
142344 if( p-magic!=RTREE_GEOMETRY_MAGIC
142345|| nBlob!=(int)(sizeof(RtreeMatchArg) +
(p-nParam-1)*sizeof(RtreeDValue))
142346 ){
142347 sqlite3_free(pGeom);
142348 return SQLITE_ERROR;
142349 }
142350
142351 pGeom-pContext = p-pContext;
142352 pGeom-nParam = p-nParam;
142353 pGeom-aParam = p-aParam;
| |
| this is double or
sqlite3_int64 depending on the SQLITE_RTREE_INT_ONLY
|
this is always double
142354
142355 pCons-xGeom = p-xGeom;
142356 pCons-pGeom = pGeom;
142357 return SQLITE_OK;
142358 }
142359
7347
7348 /*
7349 ** A pointer to a structure of the following type is passed as the first
7350 ** argument to callbacks registered using rtree_geometry_callback().
7351 */
7352 struct sqlite3_rtree_geometry {
7353 void *pContext; /* Copy of pContext passed
to s_r_g_c() */
7354 int nParam; /* Size of array aParam[] */
7355 double *aParam; /* Parameters passed to SQL
geom function */
|
maybe this field structure must be of type RtreeDValue?
7356 void *pUser;/* Callback implementation
user data */
7357 void (*xDelUser)(void *); /* Called by SQLite to clean
up pUser */
7358 };
7359
Thank.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] unable to use function MATCH in the requested context
On 01/04/2014 03:23 PM, E. Timothy Uy wrote:
This is a class of problems I started seeing around 3.8.x. Any insights
would be much appreciated. Sometimes it happens when there are more than a
few INNER JOINS, others, in the case, happen when I ORDER BY the virtual
table's docid. I feel like this used to work.
SQLite version 3.8.2 2013-12-06 14:53:30
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite CREATE VIRTUAL TABLE Terms USING fts3 (term)
... ;
sqlite INSERT INTO Terms VALUES ('hello tim');
sqlite INSERT INTO Terms VALUES ('goodbye tim');
sqlite CREATE TEMP TABLE Combos (combo);
sqlite INSERT INTO Combos VALUES ('hello');
sqlite SELECT * FROM Terms t INNER JOIN Combos c ON t.term MATCH c.combo;
hello tim|hello
sqlite SELECT * FROM Terms t INNER JOIN Combos c ON t.term MATCH c.combo
ORDER BY t.docid;
Error: unable to use function MATCH in the requested context
sqlite
I know ordering by t.docid is probably redundant, but it is an example of
the issue I'm encountering. This is an issue in 3.8.0 also. The last time
it worked was 3.7.17
Thanks for reporting this. Now fixed here:
http://www.sqlite.org/src/info/fa8be488a3
Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On Thu, 2 Jan 2014 01:29:52 + Simon Slavin slav...@bigfraud.org wrote: Hmm. Even update t set i = i + 1 - 1 with i being UNIQUE might be a good test case. Well, that actually works: sqlite create table t (t int primary key); sqlite insert into t values (1); sqlite insert into t values (2); sqlite update t set t = t + 1 - 1; sqlite select * from t; t -- 1 2 presumably because t + 1 - 1 is fully evaluated before it is applied to the row. Another example I used off-list: sqlite delete from t; sqlite insert into t values (-1); sqlite insert into t values ( 1); sqlite update t set t = -t; Error: column t is not unique --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On Wed, 1 Jan 2014 23:23:04 +0100 Petite Abeille petite.abei...@gmail.com wrote: Yes, a ?with? clause is just syntax sugar providing named subqueries. But this sugar open the door to drastically different ways to write queries, bringing structure, clarity of thoughts and purpose to otherwise labyrinthine constructs. That's an aesthetic judgement. Even if I agreed, it doesn't change the fact that every language feature is an element of complexity, and redundant language features are needless complexity. WITH only creates another way to express something already expressible in fewer words. If the CTE appears only once in the query, hardly anything is changed except that the subquery appears first. I have no objection to new syntax. But it must add functionality to the language. It must allow the programmer to express the same query more clearly and *succinctly*. Think about ?with? clause as modern SQL syntax, in the same way as ANSI join syntax brings clarity of purpose to what otherwise would be a shambles of cross-purpose ?where? clauses. Oh, I have lots of complaints about SQL! ;-) But the two aren't really so analogous, because FROM ... ON added something new to the language. The old FROM A, B WHERE has a pretty good basis in relational algebra. But RA doesn't define outer joins (really the union of a join and antijoin), and the syntax had problems expressing them. WHERE had (and has) no way to refer to the inner table, so ON was invented. Oh, and, also? Happy New Year! :) And you the same. :-) --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Amalgamation Build Errors on current head
At 24f84b38131866f7b435ffe641bb2f6991a70db2 the function setEstimatedRows was added to fts3.c. This function is already defined so the amalgamation build fails with a duplicate function definition. At 4e725f53131d3584319c710c8710a068989543c6 expr.c 3391 sqlite3ExplainPop was set to have two arguments. The function definition only has one argument. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On Jan 4, 2014, at 8:05 PM, James K. Lowden jklow...@schemamania.org wrote: That's an aesthetic judgement. Even if I agreed, it doesn't change the fact that every language feature is an element of complexity, and redundant language features are needless complexity. Things change. Syntax evolves. Languages matures, even SQL. The ‘with’ clause is a change for the better. As is merge. As are windowing functions. SQLite cannot pretend it’s 1986 forever. It has to move with the times or it will become ossified, obsolete, a mummy. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On 2014/01/05 00:03, Petite Abeille wrote: Things change. Syntax evolves. Languages matures, even SQL. The ‘with’ clause is a change for the better. As is merge. As are windowing functions. SQLite cannot pretend it’s 1986 forever. It has to move with the times or it will become ossified, obsolete, a mummy. But of course, things evolve and SQLite (like any other system) needs to keep up with times! This is however much the same as telling somebody that they must breathe else they will slowly lose oxygen and suffocate. I think in America the term Captain Obvious is used for the author of such a statement. To be clear: the statement is overly obvious and does nothing to aid the point - we are not disagreeing about the need to evolve, we both (along with many other distinguished members of this list) feel that it should evolve - AND I am happy to report that it is evolving as is evidenced by the regular updates and releases and oft-added functionality. Changes must be planned and reasonable though. The point of contention is _how_ it should evolve, not _whether_ it should evolve - so your statement urging to push forward is a moot point - things _are_ moving forward and are changing almost daily, whether you like it or not, even though it may not move in the format and time-step you envisioned. (I am quite sure you already know all this and merely appealed to the general wisdom of change is good to try and support your unrelated point, so please forgive my dissertation). Lastly, a small digression: I put it to you that if SQLite stops evolving completely today, and stays as is (with exception of bug-fixes), it will STILL last longer as the DB of choice for 90% of current implementations long after you and I have met our demise. If you do not agree on this point you may have no real idea of the actual width of implementation of SQLite3. It is this same width of implementation that makes us disagree on what should be added and what not. I will forgo another iteration on my POV as I think James K.L. made it very clear already. May you (and all other list members of course) have an awesome 2014! Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Common Table Expression
On Jan 4, 2014, at 11:34 PM, RSmith rsm...@rsweb.co.za wrote: . I think in America the term Captain Obvious is used for the author of such a statement. This sounds like a job for ObviousMan! http://treesflowersbirds.files.wordpress.com/2010/01/obviousman.jpg things _are_ moving forward and are changing almost daily, I would argue that, as far as SQL itself goes, they are not. Sure, bug fixes, implementation details, optimizations, tests, etc, etc… but when was the last time there was an actual, palpable *SQL* enhancement in SQLite? I personally cannot recall. P.S. Just for fun, look up ‘Tabibitosan method’ for an entertaining example of ‘modern’ SQL... http://www.orchestrapit.co.uk/?tag=tabibitosan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
