Re: Negative Integers Escaping

Hi, Oswoldo. Thanks for reply.

> Is a good rule to always put spaces between operators

I agree. It's a good rule but it's neither in SQL nor in Postrges syntax
rules. And psycopg should guarantee a valid escaping of parameters according
to all possible and valid syntax rules.

On Fri, May 27, 2011 at 10:29 PM, Oswaldo <listas(at)soft-com(dot)es> wrote:

> El 27/05/2011 19:42, Maxim Avanov escribió:
>
> Hello everyone!
>>
>> There is an unclear behaviour in negative integers escaping when they
>> are being passed to specific SQL queries.
>> Here are some examples:
>>
>> CREATE TABLE testdb (testval integer not null default 0);
>>
>> >>> import psycopg2 as p
>> >>> p.__version__
>> '2.4 (dt dec pq3 ext)'
>> >>> c = p.connect(...)
>> >>> cr = c.cursor()
>> >>> cr.execute("insert into testdb(testval) values(9)")
>> >>> c.commit()
>> >>> cr.execute("select testval from testdb")
>> >>> cr.fetchall()
>> [(9,)]
>>
>> >>> # Ok, we know about required parentheses here because we explicitly
>> type the negative value
>> >>> cr.execute("update testdb set testval=testval-(-2)")
>> >>> c.commit()
>> >>> cr.execute("select testval from testdb")
>> >>> cr.fetchall()
>> [(11,)]
>>
>> >>> # Here we'll get a correct expression but the wrong result caused
>> by the comment sequence '--'
>> >>> cr.execute("update testdb set testval=testval-%s", (-2,))
>> >>> c.commit()
>> >>> cr.execute("select testval from testdb")
>> >>> cr.fetchall()
>> [(11,)]
>>
>> >>> # So we got to explicitly ident or to frame the placeholder with
>> parentheses
>> >>> cr.execute("update testdb set testval=testval - %s", (-2,))
>> >>> c.commit()
>> >>> cr.execute("select testval from testdb")
>> >>> cr.fetchall()
>> [(13,)]
>>
>> >>> # The same behaviour with named placeholders
>> >>> cr.execute("update testdb set testval=testval-%(val)s", {'val':-2})
>> >>> c.commit()
>> >>> cr.execute("select testval from testdb")
>> >>> cr.fetchall()
>> [(13,)]
>>
>> I found no strict rules about this case in DBAPI2 specification. So how
>> negative integers escaping should behave?
>>
>>
> When you do:
>
> cr.execute("update testdb set testval=testval-%s", (-2,))
>
> Postgresql receive:
>
> update testdb set testval=testval--2
>
> The double dash is treated as begin sql comment and only execute:
>
>
> update testdb set testval=testval
>
> Is a good rule to always put spaces between operators
>
> Regards
>
> --
> Oswaldo Hernández
>
> --
> Sent via psycopg mailing list (psycopg(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/psycopg
>