On 15-09-2010 03:42, Pavel Ivanov wrote: > Did you by any chance introduced some unique constraint or unique > index on a set of columns one of which is primary key? AFAIK, there > was a problem in SQLite until some recent versions in processing of > redundant unique constraints in conjunction with natural joins. thanks, that's a good idea, I indeed, removed a unique constraint of 1 of the columns (and also renamed that column). So I'll try to replicate the transformations and let you know.
cheers, Stef > But the best idea would be to post the full definition of those two > tables along with their indexes and constraints. > > Pavel > > On Tue, Sep 14, 2010 at 6:09 PM, Stef Mientki <stef.mien...@gmail.com> wrote: >> On 14-09-2010 23:50, Oliver Peters wrote: >>> your version of sqlite? >> I used several programs >> SQLiteSpy, >> SQLcc, >> Python-programs, >> so I don't know which versions of sqlite I used, >> might that be the problem ? >>> your OS? >> windows XP >>> your backend? >> several, see above >>> What exactly have you done before the Natural Join stopped working? >> converted tables from string to unicode, >> column rename + columns added + changed column constraints ( through copy >> table / drop table / >> rename table ) >> >> thanks, >> Stef >>> Oliver >>> >>> >>> Am Dienstag, den 14.09.2010, 23:41 +0200 schrieb Stef Mientki: >>>> hello, >>>> >>>> after modifying some tables, natural join stopped working ??? >>>> >>>> I've 2 tables, each with a field named "vlid", which is the primary key in >>>> one of the tables >>>> >>>> until a few moments ago, this worked perfectly >>>> >>>> select Header from vraag >>>> natural join vraaglist >>>> where Nr = 0 and vraaglist.Name = 'eortc_br23' >>>> >>>> but now it returns an empty string (while the string shouldn't be empty). >>>> >>>> I test that by doing an explicit join >>>> >>>> select Header from vraag >>>> join vraaglist on vraag.vlid = vraaglist.vlid >>>> where Nr = 0 and vraaglist.Name = 'eortc_br23' >>>> >>>> which gives me the correct string of the field Header. >>>> >>>> exchanging the 2 tables in the above statements, gives exactly the same >>>> results. >>>> >>>> Is my database corrupt ? >>>> >>>> thanks, >>>> Stef Mientki >>>> _______________________________________________ >>>> sqlite-users mailing list >>>> sqlite-users@sqlite.org >>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
