I mentioned several times a technique involving particular virtual table implementation that allows interpreting comma-list fields as tables.
Basically this techique uses a virtual table that virtually contains all data possible, but useful only when it is constrained with WHERE clause working logically similar to IN operator. So with statement... SELECT value FROM cmlist WHERE commalist='45,56,78,125' ... internal code accepts this Where data through xFilter and outputs only values coming from this list. The shema for the virtual table is CREATE TABLE [xxx] ([CommaList] TEXT, [Value] TEXT) so it returns the commalist itself for sqlite to double check (as I recall it always compares not relying on the implementation of the virtual table) and the required value to be used by the user of this query. This technique worked successfully even with joins. For joins this works like this. Assume we have a table CREATE TABLE [t] ([Ids] TEXT) and it is populated with two rows "1,5" "6,7" In this case the query SELECT * FROM t left join cmlist on Ids=commalist works as a multiplier producing 4 rows in total. Ids CommaList Value "1,5" "1,5" "1" "1,5" "1,5" "5" "6,7" "6,7" "6" "6,7" "6,7" "7" This works for an old version of sqlite (3.6.10), but today Dominique Devienne mentioned some doubt about this approach and I decided to test it with some data with a recent version of sqlite. With 3.8.4.3 the same join query produced two rows and Nulls for CommaList and Value Ids CommaList Value "1,5" "" "" "6,7" "" "" I looked in the code coming from xFilter for several versions and I noticed that for versions up to 3.7.15.2 the xFilter call had idxNum equal to 1 and idxStr contained actual constraint data, while starting 3.8.0 the idxNum was 0 and no constaint was passed. So my virtual table actually said no data since it always assumes there's no data without required WHERE clause info. What can be wrong here? I see there are possibly some optimization involved that decides not constraint the virtual table at all, but I don't know why. I also understand that without actual code and test data it's hard to detect exactly what's wrong, but since the probable version of change is known and at least queries are explained here, at least an educated guess is possible. Thanks Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
