On 24 Jul 2012, at 2:29pm, "Steven E. Harris" <s...@panix.com> wrote:
> A (too) literal reading of this would suggest that the index is the > key-encoded concatenation of -- to use the example from that first Wiki > page -- four values for columns a, b, c, and d. However, if one were to > query for c and d while only constraining a and b, then the values for > columns c and d would need to be extracted from the encoded key. > > If, as you say, SQLite cannot (or will not) extract values from an > encoded key, then does the covering index really just encode column > values a and b as the key, and store values for c and d in a record > corresponding to the key? The encoding function may be a trapdoor function. For instance, consider COLLATE NOCASE in SQLite3. In this, the case of the text is ignored, allowing the sequence 'xxx','YY','z' to be considered as ordered. So the value stored in the key does not need to have all the detail of the original field: it might lose the case of text, or any accents over letters, or some other detail not needed for that particular type of collation. And therefore it may be impossible to work backwards from the key values to the values stored in the field. I have an application in which I define a 'DISTANCE' collation, which simply drops the sign of the numeric field, since for the purposes of a search I care only about how far apart two things are, not which one is to the west of the other. In this case it's impossible to work backwards from the value stored in the index and figure out whether the value stored is negative or positive. I don't know whether SQLite4 actually does work like that. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
