Hello,
I post a simple step by step example, to explain.
Doing a left join and using a where clause like: table.field==None
Depending on _enable_record_versioning is enabled or not, I get different results when executing the identical query.
The result when versioning is DISABLED is the correct one.
2.15.4-stable+timestamp.2017.09.02.04.02.22
(läuft auf Rocket 1.2.6, Python 2.7.12)
SQLite DB
Example:
#simple table:
current.db.define_table('test',
Field('title', 'string'),
Field('granny', 'reference test'), # convenience - topmost
Field('type_marker', 'string'),
auth.signature,
)
#versioning, enable/disable it
current.db.test._enable_record_versioning(archive_db=current.db,
archive_name='test_archive',
current_record='current_record',
is_active='is_active'
)
#put some data in
#current.db.test.insert(**{'title': 'Peter', 'granny': None, 'type_marker': 'person'})
#current.db.test.insert(**{'title': 'Paul', 'granny': None, 'type_marker': 'person'})
#current.db.test.insert(**{'title': 'Laptop', 'granny': 2, 'type_marker': 'object'})
#so you get table content (enabled)t:
test.id test.title test.granny test.type_marker test.is_active test.created_on test.created_by test.modified_on test.modified_by
1 Peter None person True 2018-03-01 08:11:40 Me 2018-03-01 08:11:40 Me
2 Paul None person True 2018-03-01 08:11:40 Me 2018-03-01 08:11:40 Me
3 Laptop 2 object True 2018-03-01 08:11:40 Me 2018-03-01 08:11:40 Me
#simple controller function
def demo():
test1=current.db.test.with_alias('test1')
test2=current.db.test.with_alias('test2')
query = (test2.type_marker=='object') | (test2.type_marker==None) & (test1.type_marker=='person')
tbl = current.db().select(current.db.test.ALL)
rows = current.db(query).select(test1.title, test2.title, left=test2.on(
test1.id==test2.granny))
prn = current.db(query)._select(test1.title, test2.title, left=test2.on(
test1.id==test2.granny))
return dict(tbl=tbl, rows=rows, prn=prn)
Result/Output:
test1.title test2.title
Paul Laptop
Raw SQL:
SELECT "test1"."title", "test2"."title" FROM "test" AS "test1" LEFT JOIN "test" AS "test2" ON (("test1"."id" = "test2"."granny") AND (("test1"."is_active" = 'T') AND ("test2"."is_active" = 'T'))) WHERE (((("test2"."type_marker" = 'object') OR (("test2"."type_marker" IS NULL) AND ("test1"."type_marker" = 'person'))) AND (("test1"."is_active" = 'T') AND ("test2"."is_active" = 'T'))) AND (("test1"."is_active" = 'T') AND ("test2"."is_active" = 'T')));
NOW DISABLED Record versioning:
Result/Output:
test1.title test2.title
Peter None
Paul Laptop
Raw SQL:
SELECT "test1"."title", "test2"."title" FROM "test" AS "test1" LEFT JOIN "test" AS "test2" ON ("test1"."id" = "test2"."granny") WHERE (("test2"."type_marker" = 'object') OR (("test2"."type_marker" IS NULL) AND ("test1"."type_marker" = 'person')));
QUESTION:
The result with versioning turned off looks right.
It seems that the automatically inserted "AND... is_active" clauses are not correct.
Many thanks for your help and assistance.
Andreas