Re: [sqlite] Can a record count column be added to this random hierarchical view?

[petern]

>I was hoping to find a way to force the query planner to evaluate
v_random_hierarchy only once.

There is a way with a CTE if the defect I just reported is fixed.
Replying to my bug report with your vote to fix the problem can help.

I think PostgreSQL, the model for SQLite, has it correct.
Once computed, a named CTE table should be constant until the end of the
statement compilation boundary.

Peter


On Thu, Jan 18, 2018 at 9:26 PM, Shane Dev  wrote:

> On 19 January 2018 at 05:41, petern  wrote:
>
> > Were you expecting random() to return the same sequence when the view
> > materialized again in the subquery?
> >
>
> I was hoping to find a way to force the query planner to evaluate
> v_random_hierarchy
> only once. Perhaps this is not possible since it uses the non-deterministic
> random() function
>
>
> > Your ultimate query works fine when the random view is materialized once
> > into a table.
>
>
> Correct, but I work hoping to avoid the I/O penalty of this strategy when
> generating a large hierarchy
>
>
> >
> >
> FYI. as demonstrated above, to also count the NULL parent, use "IS" instead
> > of "=".
> >
> >
> I didn't know IS also worked with integers, good tip.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a record count column be added to this random hierarchical view?

[Shane Dev]

On 19 January 2018 at 05:41, petern  wrote:

> Were you expecting random() to return the same sequence when the view
> materialized again in the subquery?
>

I was hoping to find a way to force the query planner to evaluate
v_random_hierarchy
only once. Perhaps this is not possible since it uses the non-deterministic
random() function


> Your ultimate query works fine when the random view is materialized once
> into a table.


Correct, but I work hoping to avoid the I/O penalty of this strategy when
generating a large hierarchy


>
>
FYI. as demonstrated above, to also count the NULL parent, use "IS" instead
> of "=".
>
>
I didn't know IS also worked with integers, good tip.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can a record count column be added to this random hierarchical view?

[petern]

Were you expecting random() to return the same sequence when the view
materialized again in the subquery?
Your ultimate query works fine when the random view is materialized once
into a table.

CREATE TABLE v_random_hierarchy AS
WITH r(parent, child) as (select null, 1 union all select
abs(random())%child+1, child+1 from r)
SELECT * FROM r LIMIT 5;

select v1.parent, v1.child,
  (select count(child) from v_random_hierarchy as v2 where v2.parent IS
v1.parent) as number_children_of_parent
from v_random_hierarchy as v1;
parent,child,number_children_of_parent
,1,1
1,2,3
1,3,3
3,4,1
1,5,3

FYI. as demonstrated above, to also count the NULL parent, use "IS" instead
of "=".

Some style suggestions.  Uppercase SQL keywords, linebreaks, and
indentation will make your SQL far more readable and easier to see what's
wrong.

SELECT  
  ,
   ...
FROM 
  
  ...
WHERE 
  

Peter


On Thu, Jan 18, 2018 at 2:18 PM, Shane Dev  wrote:

> Hello,
>
> Here is a view which assigns randomly chosen parents to a sequence of
> children -
>
> CREATE VIEW v_random_hierarchy as with r(parent, child) as (select null, 1
> union all select abs(random())%child+1, child+1 from r) select * from r
> limit 5;
>
> sqlite> select * from v_random_hierarchy;
> parent  child
> 1
> 1   2
> 1   3
> 1   4
> 4   5
>
> Can a column be added to this view which counts the number of children for
> each parent?
>
> Here is my failed attempt -
>
> sqlite> select v1.parent, v1.child, (select count(*) from
> v_random_hierarchy as v2 where v2.parent=v1.parent) as
> number_children_of_parent from v_random_hierarchy as v1;
> parent  child   number_children_of_parent
> 1   0
> 1   2   3
> 2   3   0
> 3   4   0
> 3   5   1
>
> in this case, it should be -
>
> parent  child   number_children_of_parent
> 1   1
> 1   2   1
> 2   3   1
> 3   4   2
> 3   5   2
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can a record count column be added to this random hierarchical view?

[Shane Dev]

Hello,

Here is a view which assigns randomly chosen parents to a sequence of
children -

CREATE VIEW v_random_hierarchy as with r(parent, child) as (select null, 1
union all select abs(random())%child+1, child+1 from r) select * from r
limit 5;

sqlite> select * from v_random_hierarchy;
parent  child
1
1   2
1   3
1   4
4   5

Can a column be added to this view which counts the number of children for
each parent?

Here is my failed attempt -

sqlite> select v1.parent, v1.child, (select count(*) from
v_random_hierarchy as v2 where v2.parent=v1.parent) as
number_children_of_parent from v_random_hierarchy as v1;
parent  child   number_children_of_parent
1   0
1   2   3
2   3   0
3   4   0
3   5   1

in this case, it should be -

parent  child   number_children_of_parent
1   1
1   2   1
2   3   1
3   4   2
3   5   2
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users