>From xBestIndex and xFilter, there is no way to determine the intended >collation. The default collation for a field is binary. However, a LIKE operator needs an ASCII collation, and in the case of a virtual table, the constraints passed in to xBestIndex and xFilter assume an ASCII collation. However, it is impossible for xBestIndex or xFilter to know this.
For example (pseudo code): CREATE VIRTUAL TABLE tab( txt TEXT ); INSERT INTO tab VALUES( 'AA'); INSERT INTO tab VALUES( 'AB'); SELECT * FROM tab WHERE txt LIKE 'AA%'; AA -- This is correct However, xBestIndex receives the equivalent of the following constraints: SELECT * FROM tab WHERE txt >= 'AA' AND txt < 'Ab'; AA AB -- Wrong Now, these constraints are correct if we're working with an ASCII collation, but xBestIndex has no way of knowing that. Am I missing some piece of information here, or is this a genuine shortcoming of the virtual table mechanism? Regards, Ben _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users