Re: Negative Integers Escaping

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/psycopg