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
