On Tue, Jul 29, 2014 at 8:13 AM, Michael <[email protected]> wrote:
> I coudn't imagine that nobody else had such problems.
>
> The fallowing select statement was existing without problems in older
> sqlite database.
> It's created with another application of me and so it's hard to replace
> the subselects.
>
> which other infos would help you to find the problem?
>
The database schema.
To study and understand these kinds of problems, we need to run your SQL
statements to see what is happening. But we cannot do that unless we know
the database schema.
>
>
> CREATE VIEW v_food_and_drinks as
> select infos.name, infos.text, count(*) as category_match, infos.rating,
> DateTime(infos.dateAdded), DateTime(infos.expiration)
> from ( select v_infos.id, v_infos.name, v_infos.text, v_infos.rating,
> v_infos.zone, DateTime(v_infos.dateAdded) as dateAdded,
> DateTime(v_infos.expiration) as expiration from v_infos where v_infos.id
> in
> (
> select item_id from category_info, category where
> category_info.category_id=category.id AND category.id in
> (
> select id from category where id in (select id from category
> where (lower(category.name)=lower('hometown') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('hometown')))
> or parent in
> (select id from category where
> (lower(category.name)=lower('hometown') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('hometown'))))
> or parent in
> (
> select id from category where parent in
>
> (select id from category where
> (lower(category.name)=lower('hometown') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('hometown'))))
>
> )
> or parent in
> (
> select id from category where parent in
> (
> select id from category where parent in
>
> (select id from category where
> (lower(category.name)=lower('hometown') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('hometown'))))
> )
> )
> or parent in
> (
> select id from category where parent in
> (
> select id from category where parent in
> (
> select id from category where parent in
>
> (select id from category where
> (lower(category.name)=lower('hometown') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('hometown'))))
>
> )
>
> )
>
> ) ) and category.zone in (select zone from zones where
> zones.zones=1)
> )
> ) UNION ALL select v_infos.id, v_infos.name, v_infos.text,
> v_infos.rating, v_infos.zone, DateTime(v_infos.dateAdded) as dateAdded,
> DateTime(v_infos.expiration) as expiration from v_infos where v_infos.id
> in
> (
> select item_id from category_info, category where
> category_info.category_id=category.id AND category.id in
> (
> select id from category where id in (select id from category where
> (lower(category.name)=lower('food_and_drinks') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('food_and_drinks')))
> or parent in
> (select id from category where
> (lower(category.name)=lower('food_and_drinks') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('food_and_drinks'))))
> or parent in
> (
> select id from category where parent in
>
> (select id from category where
> (lower(category.name)=lower('food_and_drinks') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('food_and_drinks'))))
>
> )
> or parent in
> (
> select id from category where parent in
> (
> select id from category where parent in
>
> (select id from category where
> (lower(category.name)=lower('food_and_drinks') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('food_and_drinks'))))
>
> )
>
> )
> or parent in
> (
> select id from category where parent in
>
>
> (
> select id from category where parent in
>
>
> (
> select id from category where parent in
>
> (select id from category where
> (lower(category.name)=lower('food_and_drinks') or category.id in (select
> distinct category_id from category_alias where
> lower(name)=lower('food_and_drinks'))))
>
> )
>
> )
>
> ) ) and category.zone in (select zone from zones where zones.zones=1)
> )
> )) as infos where 1=1 and infos.zone in (select zone from zones
> where zones.zones=1)
> group by infos.id having category_match>=2 order by rating desc,
> DateAdded desc;
>
>
>
>
> > Gesendet: Dienstag, 29. Juli 2014 um 12:54 Uhr
> > Von: "Richard Hipp" <[email protected]>
> > An: "General Discussion of SQLite Database" <[email protected]>
> > Betreff: Re: [sqlite] parser stack overflow in view
> >
> > On Tue, Jul 29, 2014 at 5:00 AM, Michael <[email protected]>
> wrote:
> >
> > > I have a view with about 6 Unions and a depth of about 6 subselects in
> > > each select.
> > > Shouldn't be a big thing and it was no problem with sqlite 3.7.17.
> > > Since 3.8 (3.8.4.3) I get "parser stack overflow". I have many queries
> > > with this problem now...
> > >
> > > Can anyone help me please
> > >
> >
> > Can you post an example of your problem. You haven't given us much to go
> > on.
> >
> >
> > --
> > D. Richard Hipp
> > [email protected]
> > _______________________________________________
> > sqlite-users mailing list
> > [email protected]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
