Hmmm... I think that if you have record versioning activated there shouldn't be any is_active row(s) with NULL value... I mean is_active flag is used to determine if the record in the "parent" table has been deleted or not, since you can truely deleted in case you use record versioning feature as all the previous record stated records contained in the versioning table reference the parent record in the parent table...
And you see in the second example here : http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer?search=record+versioning#Record-versioning That is_active is set to default=True and there is this note : Notice the hidden boolean field called is_active and defaulting to True. So if you have activated record versioning feature over an already existing table you need to set is_active to TRUE for all the existing record once you start using the versioning feature and set the is_active to be mandatory... I guess it would make sens to change seconde book example in orther that is should be more obvious that is_active is mandatory like so : db.define_table('stored_item', Field('name'), Field('quantity', 'integer'), Field('is_active', 'boolean', writable=False, readable=False, default=True, required=True, requires=IS_NOT_EMPTY())) That way you don't need to care about null value to get all the records of the parent table and DAL is not bugged... Richard On Fri, Mar 2, 2018 at 9:22 AM, 'Awe' via web2py-users < web2py@googlegroups.com> wrote: > So, I did check it again: > > It seems that DAL is not able to handle a left join using ISNULL condition > in combination with enabled record versioning. > There are two "is active" checks within the left join: OK > The next two upcoming ANDs to check "is_active" are not necessary and > actually prohibit the query to work properly. > To make this work it should be: AND (("test1"."is_active" = 'T') AND > (("test2"."is_active" = 'T' OR ("test2"."is_active" ISNULL )) > The two AND is active checks at the end of the query are not necessary at > all (my opinion). > > Could somebody please verifiy this! > Andreas > > > Am Donnerstag, 1. März 2018 13:37:59 UTC+1 schrieb Awe: >> >> 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 web2py+unsubscr...@googlegroups.com. > For more options, visit https://groups.google.com/d/optout. > -- 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 web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
