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
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
