Re: [sqlite] Problem with recursive CTE
- Original Message - From: "Alek Paunov" To: "General Discussion of SQLite Database" Sent: Monday, August 25, 2014 4:04 PM Subject: Re: [sqlite] Problem with recursive CTE On 25.08.2014 15:42, Frank Millman wrote: I have upgraded to version 3.8.6, and I can confirm that it now works. Thanks very much, Richard and Keith Now I have to figure out how to get Python to use the upgraded version, but that is one for the python mailing list. You may consider upgrade to the current Fedora release - F20, which comes with sqlite-3.8.6 [1]. F18 is already out of support anyway. Will do. Thanks for the advice, Alek. Frank ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with recursive CTE
On 25.08.2014 15:42, Frank Millman wrote: I have upgraded to version 3.8.6, and I can confirm that it now works. Thanks very much, Richard and Keith Now I have to figure out how to get Python to use the upgraded version, but that is one for the python mailing list. You may consider upgrade to the current Fedora release - F20, which comes with sqlite-3.8.6 [1]. F18 is already out of support anyway. [As you know, in Fedora every binding points to the system sqlite, including both the standard Python library module (sqlite3) and the well known specialized/full wrapper python-apsw] Kind regards, Alek [1] https://apps.fedoraproject.org/packages/sqlite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with recursive CTE
- Original Message - From: "Frank Millman" To: Sent: Monday, August 25, 2014 2:32 PM Subject: Re: [sqlite] Problem with recursive CTE Could it be a version problem? I am using the version bundled with Python3.4.1 for Windows. sqlite3.sqlite_version shows '3.8.3.1' I have just tried it on a Fedora 18 machine, which has version 3.7.13. There it gives 'near "WITH": syntax error' It seems that the WITH statement is a fairly recent addition. I will upgrade and try again. I have upgraded to version 3.8.6, and I can confirm that it now works. Thanks very much, Richard and Keith Now I have to figure out how to get Python to use the upgraded version, but that is one for the python mailing list. Frank ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with recursive CTE
- Original Message - From: "Keith Medcalf" To: "General Discussion of SQLite Database" Sent: Monday, August 25, 2014 2:28 PM Subject: Re: [sqlite] Problem with recursive CTE Looking backwards on the list one finds that: There was a bug regarding compound SELECT statements that use CTEs discovered shortly after 3.8.3 was released: http://www.sqlite.org/src/info/67bfd59d9087a987 http://www.sqlite.org/src/info/31a19d11b97088296a The fix appeared in 3.8.4. If you upgrade, the statement will work. Since you do not mention what version you are using, one assumes it is prior to this fix? Thanks, Keith. I think you are spot on. See my reply to Richard. I will upgrade and try again. Frank ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with recursive CTE
From: "Frank Millman" Thanks for the reply, Richard. WITH RECURSIVE temp(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM temp WHERE x<5 ) SELECT x FROM temp UNION SELECT x+5 FROM temp; And did indeed get integers 1 through 10 as an answer. I get the same error as before - 'no such table: temp'. Could it be a version problem? I am using the version bundled with Python3.4.1 for Windows. sqlite3.sqlite_version shows '3.8.3.1' I have just tried it on a Fedora 18 machine, which has version 3.7.13. There it gives 'near "WITH": syntax error' It seems that the WITH statement is a fairly recent addition. I will upgrade and try again. Frank ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with recursive CTE
Looking backwards on the list one finds that: There was a bug regarding compound SELECT statements that use CTEs discovered shortly after 3.8.3 was released: http://www.sqlite.org/src/info/67bfd59d9087a987 http://www.sqlite.org/src/info/31a19d11b97088296a The fix appeared in 3.8.4. If you upgrade, the statement will work. Since you do not mention what version you are using, one assumes it is prior to this fix? >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Frank Millman >Sent: Monday, 25 August, 2014 04:11 >To: sqlite-users@sqlite.org >Subject: [sqlite] Problem with recursive CTE > >Hi all > >According to the documentation for the WITH clause, "the recursive table >must appear exactly once in the FROM clause of the recursive-select and >must >not appear anywhere else in either the initial-select or the >recursive-select, including subqueries". > >I am trying to do the following - > >WITH RECURSIVE temp AS ( > [initial-select UNION ALL recursive-select] > ) >SELECT * FROM temp UNION * FROM temp > >Obviously this is a simplification, but you get the idea. > >I get the error 'no such table: temp'. > >This query works with SQL Server and with PostgreSQL. > >Is this a limitation of sqlite3, or is there any workaround? > >Thanks > >Frank Millman > >___ >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
Re: [sqlite] Problem with recursive CTE
On Mon, Aug 25, 2014 at 6:10 AM, Frank Millman wrote: > > I am trying to do the following - > > WITH RECURSIVE temp AS ( > [initial-select UNION ALL recursive-select] > ) > SELECT * FROM temp UNION * FROM temp > > I get the error 'no such table: temp'. > Hard to test without the exact SQL. When I tried: WITH RECURSIVE temp(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM temp WHERE x<5 ) SELECT * FROM temp UNION SELECT * FROM temp; it works fine for me given integers 1 through 5 as the answer. I double-checked with: WITH RECURSIVE temp(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM temp WHERE x<5 ) SELECT x FROM temp UNION SELECT x+5 FROM temp; And did indeed get integers 1 through 10 as an answer. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with recursive CTE
From: "Frank Millman" WITH RECURSIVE temp AS ( [initial-select UNION ALL recursive-select] ) SELECT * FROM temp UNION * FROM temp Sorry, I meant SELECT * FROM temp UNION SELECT * FROM temp Frank ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
