Create an sql dump using the ".dump" command of the sqlite3 shell tool:
$ echo .dump | sqlite3 database_file.db > dump.sql Or just put the database file up for download somewhere. Or if you prefer, send it to me by email. If this bug is present in current versions, we need to fix it. But it's much more difficult to figure out if it is still present or not without a database to run your queries against. Thanks, Dan. On Mar 4, 2009, at 9:17 PM, Jonathon wrote: > Thanks Dan for the reply, > > How would I go about creating a sql dump? > > As for the ORDER BY clause, I do this: > > SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER > JOIN > tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND > a.d_id = > d.id AND c.e_id = e.id ORDER BY a.some_other_value ASC; > > Although a.some_other_value is not displayed in the tableA below, I > forgot > to add it. So, I add the ORDER BY clause on a.some_other_value and it > returns a ton of records (mostly duplicates). I am pretty sure this > is a > bug because if I wrap this query inside of a subquery and perform > the ORDER > BY on the subquery, everything works. > > This is what WORKS: > > SELECT * FROM ( > SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER > JOIN > tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND > a.d_id = > d.id AND c.e_id = e.id ) ORDER BY a.some_other_value ASC; > > So for now, I am just leaving my original query inside the subquery > to fix > it. However, I wouldn't mind posting some debugging information if > it will > help fix the bug. > > Thanks, > J > > > On Tue, Mar 3, 2009 at 8:23 PM, Dan <danielk1...@gmail.com> wrote: > >> >> On Mar 4, 2009, at 4:41 AM, Jonathon wrote: >> >>> Hello, >>> >>> I am executing a query such as: >> >> Can you post a database (or sql dump thereof) to run your queries >> against? Also say exactly what ORDER BY clause you are adding to >> the end of the query that causes it to return incorrect results? >> >> Thanks, >> Dan. >> >> >> >>> 1. SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c >>> INNER >>> JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id >>> AND >>> a.d_id = d.id AND c.e_id = e.id; >>> 2. >>> 3. CREATE TABLE tableA ( >>> 4. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, >>> 5. b_id INTEGER NOT NULL, >>> 6. c_id INTEGER NOT NULL, >>> 7. d_id INTEGER NOT NULL >>> 8. ) >>> 9. >>> 10. CREATE TABLE tableB ( >>> 11. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT >>> 12. ); >>> 13. >>> 14. CREATE TABLE tableC ( >>> 15. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, >>> 16. e_id INTEGER NOT NULL >>> 17. ); >>> 18. >>> 19. CREATE TABLE tableD ( >>> 20. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT >>> 21. ); >>> 22. >>> 23. CREATE TABLE tableE ( >>> 24. id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT >>> 25. ); >>> >>> >>> This seems to be returning the correct records. However, when I >>> append an >>> ORDER BY at the end of the query, it seems to be changing the number >>> of >>> records that are returned back to me. From my understanding, an >>> ORDER BY >>> can not change the number of records correct? If I do not use the >>> ORDER BY, >>> I get around 150 records. If I do an ORDER BY on any column that >>> is not in >>> tableA, the number of records blows up (~4k) and there are >>> duplicates. >>> >>> Any ideas? >>> >>> I thought that it was because of a buggy parser, so I wrote the >>> query again >>> as: >>> >>> >>> 1. SELECT * FROM tableA a >>> 2. INNER JOIN tableB b >>> 3. ON a.b_id = b.id >>> 4. INNER JOIN tableD d >>> 5. ON a.d_id = d.id >>> 6. INNER JOIN tableC c >>> 7. LEFT JOIN tableE e ON c.e_id = e.id >>> 8. ON ON a.c_id = c.id >>> >>> and it still gave me the same results... >>> >>> Thanks, >>> J >>> _______________________________________________ >>> 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
