search

ravel-net / pyotr

Apache License 2.0
0 stars 0 forks source link

Bug: single quotes for integer in the where clause #1

Closed lanfangping closed 2 years ago

lanfangping commented 2 years ago

Problem description

I found the parser of datalog/faure-log has an incorrect representation for integer operand(i.e., y = 1). The generated SQL for rule R(x,z) :- R(x,y)[y = 1],l(y,z) is

select t0.c0 as c0, t1.c1 as c1 from R t0, l t1 where t0.c1 = t1.c0 and t0.c1 = 1

I think t0.c1 = 1 in the where clause should be t0.c1 = '1' because the datatype of t0.c1 is int4_faure. It does not report an error but I think it is a bug in the parser. I tried to use != operator for the int4_faure column (e.g., t0.c1 != 1) in the condition and the generated SQL run in Postgres that reported "operator does not exist for int4_faure and integer" because the integer operand was not surrounded by single quotes.

In addition, the generated SQL for rule R(e1,h3,[a3, x],2) :- R(a3,h3,[x],1),l(a3,e1) is 

select t1.c1 as c0, t0.c1 as c1, ARRAY[t0.c0, t0.c2[1]] as c2, 2 as c3 from R t0, l t1 where t0.c3 = '1' and t0.c0 = t1.c0

The t0.c3 = '1' should be t0.c3 = 1 because t0.c3 is integer column. It's not a big problem but I think fixing it would be better.

I also found an interesting thing. Assume c2 is an int4_faure type of column. If the constraint in the SQL where clause is c2 = 1(should be c2 = '1' ), the SQL runs correctly. But if uses other operators, such as c2 < 1, the SQL runs an error.

Test Case

Use case in unit_tests.py.

mudbri commented 2 years ago

This should be fixed in commit ba2eaedfd214ffed36a34bea1438574e141601f9. The issue was that operators for comparison between int type and int4_faure type were not defined. Now, they are defined and we don't need a single quote around integers even if they are being compared against int4_faure datatype. Closing issue for now. Please let me know if the problem persists