Problem with recursive CTE

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Problem with recursive CTE

Frank Millman
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with recursive CTE

Frank Millman
From: "Frank Millman" <[hidden email]>
>
> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with recursive CTE

Richard Hipp-3
In reply to this post by Frank Millman
On Mon, Aug 25, 2014 at 6:10 AM, Frank Millman <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with recursive CTE

Keith Medcalf
In reply to this post by Frank Millman

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: [hidden email] [mailto:sqlite-users-
>[hidden email]] On Behalf Of Frank Millman
>Sent: Monday, 25 August, 2014 04:11
>To: [hidden email]
>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
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with recursive CTE

Frank Millman
In reply to this post by Frank Millman
From: "Frank Millman" <[hidden email]>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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with recursive CTE

Frank Millman
In reply to this post by Keith Medcalf

----- Original Message -----
From: "Keith Medcalf" <[hidden email]>
To: "General Discussion of SQLite Database" <[hidden email]>
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with recursive CTE

Frank Millman
In reply to this post by Frank Millman

----- Original Message -----
From: "Frank Millman" <[hidden email]>
To: <[hidden email]>
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with recursive CTE

decalek
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Problem with recursive CTE

Frank Millman

----- Original Message -----
From: "Alek Paunov" <[hidden email]>
To: "General Discussion of SQLite Database" <[hidden email]>
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users