"not in"

[matthewrobertbell]

Hi,

"not in" in a query seems to emit the exact same SQL as "in".

The last 2 outputs from the below script are:

SELECT "b"."id", "b"."name"
FROM "Blah" "b"
WHERE "b"."name" LIKE '%hello%'

It seems in your tests there is

test_declarative_sqltranslator2.py:        q = select(g for g in Group if 'John' not in g.students.name and g.dept == Department[1])

Is there a difference between your test and mine? My test below, which I ran with pony==0.4.9:

from pony.orm import *

sql_debug(True)
db = Database('sqlite', ':memory:')

class Blah(db.Entity):
    name = Required(unicode)

db.generate_mapping(create_tables=True)

with db_session:
    x = Blah(name='hello frank')
    y = Blah(name='hello john')

with db_session:
    print 'No "in"'
    print select(b for b in Blah)[:]

    print '"in"'
    print select(b for b in Blah if 'hello' in b.name)[:]

    print 'not "in"'
    print select(b for b in Blah if 'hello' not in b.name)[:]

[kozlovsky]

Thanks, fixed.

Pony translates "not in" in three different ways, depending on the operand type:

>>> from pony.orm.examples.estore import *
>>> select(p for p in Product if 'Apple' in p.name)[:]
SELECT "p"."id", "p"."name", ...
FROM "Product" "p"
WHERE "p"."name" LIKE '%Apple%'

>>> select(p for p in Product if p.name in ('Product1', 'Product2'))[:]
SELECT "p"."id", "p"."name", ...
FROM "Product" "p"
WHERE "p"."name" IN ('Product1', 'Product2')

>>> select(p for p in Product if p.name in (p2.name for p2 in Product if p2.price > 100))[:]
SELECT "p"."id", "p"."name", ...
FROM "Product" "p"
WHERE "p"."name" IN (
    SELECT "p2"."name"
    FROM "Product" "p2"
    WHERE "p2"."price" > 100
    )

There was a bug in the first type of translation, where not in is used with substrings and where LIKE / NOT LIKE should be generated.

Is there a difference between your test and mine?

The mentioned test in test_declarative_sqltranslator2.py is used for testing the presence of additional column IS NOT NULL check when NOT IN subquery is used with optional column. This test is not intended to test queries with NOT LIKE conditions. I just added missed test to test_declarative_string_mixin.py file, which stores all tests for string expressions.

Thanks again, now queries with NOT LIKE should work correctly.