Re: [sqlite] application function value in sql where

[R Smith]

On 2017/01/26 8:29 PM, dspub...@freemail.hu wrote:

On 2017/01/26 6:23 PM, dspub...@freemail.hu wrote:

Thanks for responses, I have an other problem

select * from (select row_number(name) as id,name from example order 
by name desc) t order by name In this query, the server why doesn't 
use the nested "order by"?
I want to numbering the inner data descending, but I can't, because 
looks like, the server ignores it.
There exists no such thing as "nested" ORDER BY clauses. One and only 
one ORDER BY clause can ever determine ordering. The outer-most is 
the Boss.

What you perhaps are trying to achieve is this:

SELECT COUNT(B.name) AS id, A.name
 FROM example AS A
 LEFT JOIN example AS B ON B.name < A.name
GROUP BY A.name
ORDER BY A.name

Couldn't test it so there might be a typo in there, but the principle 
works.


Actually , I want to adopt some sql analytic function with application 
defined function, and I want to use them with "with" statement.

For example:

with
seg1 (account_num,account_type) as (select account_num,account_type 
from account where account_name like 'p%'  )
,seg2 (t10,t10rid,t12,t12rid) as (select t10.account_num 
,t10.rid,t12.item,t12.rid

 from (select *,row_number() as rid from seg1 order by account_num) t10
left join (select *,row_number() as rid from account_sub order by item 
DESC) t12 on t12.account_num=t10.account_num

)
select * from seg2 where t10rid<=5  order by t10rid,t12rid

('00032', 1, 'a', 1)
('00032ST', 2, 'a', 1)
('0014615', 3, 'a', 1)
('001604313', 4, 'A', 1) problem is here, it should be ('001604313', 
4, 1, 'B') because of order by item DESC
('001604313', 4, 'B', 2)problem is here, it should be ('001604313', 4, 
1, 'A') because of order by item DESC

('001610179', 5, 'a', 1)


Well, that's easy to fix...

with
 seg1 (account_num,account_type) as (
select account_num,account_type from account where account_name 
like 'p%'

), seg2 (t10,t10rid,t12,t12rid) as (
select t10.account_num ,t10.rid,t12.item,t12.rid
  from (select *,row_number() as rid from seg1) t10
  left join (select *,row_number() as rid from account_sub) t12 on 
t12.account_num=t10.account_num

)
select * from seg2 where t10rid<=5  order by t10rid, t12rid DESC



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] application function value in sql where

[dspublic]

On 2017/01/26 6:23 PM, dspub...@freemail.hu wrote:

Thanks for responses, I have an other problem

select * from (select row_number(name) as id,name from example order by 
name desc) t order by name In this query, the server why doesn't use the 
nested "order by"?
I want to numbering the inner data descending, but I can't, because looks 
like, the server ignores it.
There exists no such thing as "nested" ORDER BY clauses. One and only one 
ORDER BY clause can ever determine ordering. The outer-most is the Boss.

What you perhaps are trying to achieve is this:

SELECT COUNT(B.name) AS id, A.name
 FROM example AS A
 LEFT JOIN example AS B ON B.name < A.name
GROUP BY A.name
ORDER BY A.name

Couldn't test it so there might be a typo in there, but the principle 
works.


Actually , I want to adopt some sql analytic function with application 
defined function, and I want to use them with "with" statement.

For example:

with
seg1 (account_num,account_type) as (select account_num,account_type from 
account where account_name like 'p%'  )
,seg2 (t10,t10rid,t12,t12rid) as (select t10.account_num 
,t10.rid,t12.item,t12.rid

 from (select *,row_number() as rid from seg1 order by account_num) t10
left join (select *,row_number() as rid from account_sub order by item DESC) 
t12 on t12.account_num=t10.account_num

)
select * from seg2 where t10rid<=5  order by t10rid,t12rid

('00032', 1, 'a', 1)
('00032ST', 2, 'a', 1)
('0014615', 3, 'a', 1)
('001604313', 4, 'A', 1) problem is here, it should be ('001604313', 4, 1, 
'B') because of order by item DESC
('001604313', 4, 'B', 2)problem is here, it should be ('001604313', 4, 1, 
'A') because of order by item DESC

('001610179', 5, 'a', 1)

Thanks, 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] application function value in sql where

[David Raymond]

Did you mean to order by name in both the inside and outside? Or did you mean 
"order by id desc" for the inner part? If so you can always do that on the 
outside, which as mentioned is the only one that counts in the end.

select row_number(name) as id, name from example order by name asc, id desc;



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of dspub...@freemail.hu
Sent: Thursday, January 26, 2017 11:24 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] application function value in sql where


Thanks for responses, I have an other problem

select * from (select row_number(name) as id,name from example order by name 
desc) t order by name
In this query, the server why doesn't use the nested "order by"?
I want to numbering the inner data descending, but I can't, because looks 
like, the server ignores it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] application function value in sql where

[R Smith]

On 2017/01/26 6:23 PM, dspub...@freemail.hu wrote:


Thanks for responses, I have an other problem

select * from (select row_number(name) as id,name from example order 
by name desc) t order by name

In this query, the server why doesn't use the nested "order by"?
I want to numbering the inner data descending, but I can't, because 
looks like, the server ignores it.


There exists no such thing as "nested" ORDER BY clauses. One and only 
one ORDER BY clause can ever determine ordering. The outer-most is the Boss.


What you perhaps are trying to achieve is this:

SELECT COUNT(B.name) AS id, A.name
  FROM example AS A
  LEFT JOIN example AS B ON B.name < A.name
 GROUP BY A.name
 ORDER BY A.name

Couldn't test it so there might be a typo in there, but the principle works.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] application function value in sql where

[dspublic]

On 2017/01/25 3:18 PM, Dominique Devienne wrote:

On Wed, Jan 25, 2017 at 1:54 PM, Richard Hipp  wrote:


On 1/25/17, Richard Hipp  wrote:

On 1/25/17, dspub...@freemail.hu  wrote:

I get weird sql result with subselect too
select * from (select row_number(name) as id,name from example ) t 
where

id<=5


SQLite is invoking your row_number() function twice for each row -
once for the return value and a second time when evaluating the "id<5"
expression.

Further information:

The query optimizer is transforming your nested query into a single
query.  You wrote:

 SELECT * FROM (SELECT func(name) AS id, name FROM example) WHERE 
id<5;


Evaluated directly, this would require two separate queries.  For
improved performance, SQLite "flattens" the inner query into the
second, like this:

 SELECT func(name), name FROM example WHERE func(name)<5;


Hi Richard,

Would SQLite invoke the function only once though, had the function been
declared "deterministic"?
I.e. when compiling that "flattened" query into VDBE, it would use a
"register" to avoid calling it twice?


Not only would it avoid calling it twice, the QP might even cache it for 
future iterations with the same parameter... The immediate problem here is 
that his function is specifically NOT deterministic, it returns an 
ever-growing result upon each call (from the looks of it - untested).


Thanks for responses, I have an other problem

select * from (select row_number(name) as id,name from example order by name 
desc) t order by name

In this query, the server why doesn't use the nested "order by"?
I want to numbering the inner data descending, but I can't, because looks 
like, the server ignores it.

___
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] application function value in sql where

[R Smith]

On 2017/01/25 3:18 PM, Dominique Devienne wrote:

On Wed, Jan 25, 2017 at 1:54 PM, Richard Hipp  wrote:


On 1/25/17, Richard Hipp  wrote:

On 1/25/17, dspub...@freemail.hu  wrote:

I get weird sql result with subselect too
select * from (select row_number(name) as id,name from example ) t where
id<=5


SQLite is invoking your row_number() function twice for each row -
once for the return value and a second time when evaluating the "id<5"
expression.

Further information:

The query optimizer is transforming your nested query into a single
query.  You wrote:

 SELECT * FROM (SELECT func(name) AS id, name FROM example) WHERE id<5;

Evaluated directly, this would require two separate queries.  For
improved performance, SQLite "flattens" the inner query into the
second, like this:

 SELECT func(name), name FROM example WHERE func(name)<5;


Hi Richard,

Would SQLite invoke the function only once though, had the function been
declared "deterministic"?
I.e. when compiling that "flattened" query into VDBE, it would use a
"register" to avoid calling it twice?


Not only would it avoid calling it twice, the QP might even cache it for 
future iterations with the same parameter... The immediate problem here 
is that his function is specifically NOT deterministic, it returns an 
ever-growing result upon each call (from the looks of it - untested).


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] application function value in sql where

[Dominique Devienne]

On Wed, Jan 25, 2017 at 1:54 PM, Richard Hipp  wrote:

> On 1/25/17, Richard Hipp  wrote:
> > On 1/25/17, dspub...@freemail.hu  wrote:
> >>
> >> I get weird sql result with subselect too
> >> select * from (select row_number(name) as id,name from example ) t where
> >> id<=5
> >>
> >
> > SQLite is invoking your row_number() function twice for each row -
> > once for the return value and a second time when evaluating the "id<5"
> > expression.
>
> Further information:
>
> The query optimizer is transforming your nested query into a single
> query.  You wrote:
>
> SELECT * FROM (SELECT func(name) AS id, name FROM example) WHERE id<5;
>
> Evaluated directly, this would require two separate queries.  For
> improved performance, SQLite "flattens" the inner query into the
> second, like this:
>
> SELECT func(name), name FROM example WHERE func(name)<5;


Hi Richard,

Would SQLite invoke the function only once though, had the function been
declared "deterministic"?
I.e. when compiling that "flattened" query into VDBE, it would use a
"register" to avoid calling it twice?

Thanks, --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] application function value in sql where

[Richard Hipp]

On 1/25/17, Richard Hipp  wrote:
> On 1/25/17, dspub...@freemail.hu  wrote:
>>
>> I get weird sql result with subselect too
>> select * from (select row_number(name) as id,name from example ) t where
>> id<=5
>>
>
> SQLite is invoking your row_number() function twice for each row -
> once for the return value and a second time when evaluating the "id<5"
> expression.

Further information:

The query optimizer is transforming your nested query into a single
query.  You wrote:

SELECT * FROM (SELECT func(name) AS id, name FROM example) WHERE id<5;

Evaluated directly, this would require two separate queries.  For
improved performance, SQLite "flattens" the inner query into the
second, like this:

SELECT func(name), name FROM example WHERE func(name)<5;

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] application function value in sql where

[Richard Hipp]

On 1/25/17, dspub...@freemail.hu  wrote:
>
> I get weird sql result with subselect too
> select * from (select row_number(name) as id,name from example ) t where
> id<=5
>

SQLite is invoking your row_number() function twice for each row -
once for the return value and a second time when evaluating the "id<5"
expression.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] application function value in sql where

[dspublic]

Hi Everyone,

I would like to report a probably bug, what I found in sqlite (3.16.2)
I want to make and use some application defined function (ADF). If I use ADF 
value in sql where expression, then the server result is incorrect.

  example data: 
'1_one','2_two','3_three','4_four','5_five','6_six','7_seven','8_eight','9_nine'

  with t (id,name) as (select row_number(name),name from example )
select * from t order by id

   expected result: [(1, '1_one'), (2, '2_two'), (3, '3_three'), (4, '4_four'), 
(5, '5_five'), (6, '6_six'), (7, '7_seven'), (8, '8_eight'), (9, '9_nine')] 
   real result: same, CORRECT


  with t (id,name) as (select row_number(name),name from example )
   select * from t WHERE ID<=5 order by id

   expected result: [(1, '1_one'), (2, '2_two'), (3, '3_three'), (4, '4_four'), 
(5, '5_five')] 
   real result: [(2, '1_one'), (4, '2_two'), (6, '3_three')] INCORRECT

I get weird sql result with subselect too
select * from (select row_number(name) as id,name from example ) t where id<=5


I've made a python script for testing and issue reproduction:

import sqlite3

row_number_buffer=0;
def row_number(v):
  print('row_number called:',v);
  global row_number_buffer;
  row_number_buffer+=1;
  return row_number_buffer;


if __name__ == '__main__':
  db = sqlite3.connect(':memory:');
  db.create_function("row_number", 1, row_number);

  c = db.cursor();
  c.execute('''CREATE TABLE example(name)''');

  for i in 
('1_one','2_two','3_three','4_four','5_five','6_six','7_seven','8_eight','9_nine'):
c.execute('''INSERT INTO example(name) VALUES(?)''', (i,));
  db.commit();

  print('Test1 OK');
  #call a "with" statement without sql where expression
  c.execute("""with t (id,data) as (select row_number(name),name from example ) 
select * from t order by id""");
  print('Correct result (without ID filter):',c.fetchall());

  print('Test2 NOK');
  row_number_buffer=0;
  #call a "with" statement with application sql function filter ID<=5
  c.execute("""with t (id,name) as (select row_number(name),name from example ) 
select * from t WHERE ID<=5 order by id""");
  print('Incorrect result (WHERE ID<=5):',c.fetchall());

  db.close();

Best regards, Tibor
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users