You wrote: In [4]: query = (test2.type_marker=='object') | (test2.type_marker==None) & (test1.type_marker=='person') ...:
In [5]: db().select(db.test.ALL) Out[5]: <Rows (3)> So in [5] you did not use the query definition, its db() AND the result is 3 Rows, it should be 2! Thats why I asked for the query you will use to get exactly this result: test1.title test2.title Peter None Paul Laptop I haven't seen any query in your output with this result. Andreas Am Mittwoch, 7. März 2018 20:08:05 UTC+1 schrieb Richard: > > That why I think you wrongly initialize you db in the first place while > onboarding the record versioning feature... > > Please try on your side to create new app drop the model and fixture I > include in one of my last email and start web2py shell and launch the > various commands of yours... > > You should get the same output number of record... > > If you don't it might happen that there was a bug in 2.15.4 web2py version > / pyDAL... > > Try new version... > > On Wed, Mar 7, 2018 at 2:05 PM, Richard Vézina <[email protected] > <javascript:>> wrote: > >> In [4]: query = (test2.type_marker=='object') | (test2.type_marker==None) >> & (test1.type_marker=='person') >> ...: >> >> In [5]: db().select(db.test.ALL) >> Out[5]: <Rows (3)> >> >> I did there... >> >> Same result... >> >> That my point... >> >> On Wed, Mar 7, 2018 at 1:47 PM, 'Awe' via web2py-users < >> [email protected] <javascript:>> wrote: >> >>> Hello Richard, >>> you are right, the use case is parent child relation. >>> All I want to point out is if you use this query: >>> "query = (test2.type_marker=='object') | (test2.type_marker==None) & >>> (test1.type_marker=='person')" >>> you do not get the same result with record versioning enabled or >>> disabled. >>> >>> Why do I use this query definition because I want to retrieve exactly >>> this result: >>> test1.title test2.title >>> Peter None >>> Paul Laptop >>> >>> If you use this query instead: >>> (test2.type_marker=='object') & (test1.type_marker=='person') >>> you do not get the result I need. >>> >>> The " | (test2.type_marker==None) " is necessary, due to parent/child >>> relationship. >>> >>> Please try exactly the query mentioned using versioning enabled/disabled >>> and see what happens. >>> >>> Many thanks for your patience. >>> >>> Am Mittwoch, 7. März 2018 19:34:41 UTC+1 schrieb Richard: >>>> >>>> I don't get it, I don't understand what is the issue... >>>> >>>> I just demonstrate that with and without record versioning DAL returns >>>> the same set of result which what it should be doing no?? >>>> >>>> So my understanding of the issue is that you don't get the same query >>>> output when record versioning is on and when it off which to me should is >>>> the issue you point us... I migth not understand the exact problem you >>>> have. >>>> >>>> But to me this "query = (test2.type_marker=='object') | >>>> (test2.type_marker==None) & (test1.type_marker=='person')" >>>> >>>> or this "query = (test2.type_marker=='object') & >>>> (test1.type_marker=='person')" with the set of inputs you have will lead >>>> to >>>> the same output as there is not type_marker NULL value... >>>> >>>> But I guess I miss something with related to what you try to achieve >>>> with this aliasing thing that you do... >>>> >>>> Also the use case is not obvious for me as why are you doing these >>>> aliases... I guess to retrieve some sort of genealogy/record parent-child >>>> relation... >>>> >>>> Richard >>>> >>>> >>>> On Wed, Mar 7, 2018 at 12:17 PM, 'Awe' via web2py-users < >>>> [email protected]> wrote: >>>> >>>>> Hello Richard, >>>>> have read your post 3 times, but I can't find the right query result. >>>>> Could you tell me where to find in your test the query which leads to: >>>>> test1.title test2.title >>>>> Peter None >>>>> Paul Laptop >>>>> >>>>> Thank you. >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> Am Mittwoch, 7. März 2018 17:33:33 UTC+1 schrieb Richard: >>>>> >>>>>> Here some tests : >>>>>> >>>>>> *WITH record versioning* >>>>>> >>>>>> In [1]: db(db.test.id < 0).select() >>>>>> Out[1]: <Rows (0)> >>>>>> >>>>>> In [2]: db(db.test.id > 0).select() >>>>>> Out[2]: <Rows (3)> >>>>>> >>>>>> In [3]: test1 = db.test.with_alias('test1') >>>>>> >>>>>> In [4]: test2 = db.test.with_alias('test2') >>>>>> >>>>>> In [5]: query = (test2.type_marker=='object') | >>>>>> (test2.type_marker==None) & (test1.type_marker=='person') >>>>>> >>>>>> In [6]: db().select(current.db.test.ALL) >>>>>> >>>>>> --------------------------------------------------------------------------- >>>>>> NameError Traceback (most recent call >>>>>> last) >>>>>> /web2py_master_trunk/web2py/applications/test_versioning_issue/models/menu.py >>>>>> >>>>>> in <module>() >>>>>> ----> 1 db().select(current.db.test.ALL) >>>>>> >>>>>> NameError: name 'current' is not defined >>>>>> >>>>>> In [7]: db().select(db.test.ALL) >>>>>> Out[7]: <Rows (3)> >>>>>> >>>>>> In [8]: query = (test2.type_marker=='object') & >>>>>> (test1.type_marker=='person') >>>>>> >>>>>> In [9]: db().select(db.test.ALL) >>>>>> Out[9]: <Rows (3)> >>>>>> >>>>>> In [10]: db(query).select(test1.title, test2.title, left=test2.on( >>>>>> test1.id==test2.granny)) >>>>>> Out[10]: <Rows (1)> >>>>>> >>>>>> In [11]: db(query)._select(test1.title, test2.title, left=test2.on( >>>>>> test1.id==test2.granny)) >>>>>> Out[11]: '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\') 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\')));' >>>>>> >>>>>> In [12]: print db().select(db.test.ALL) >>>>>> 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,<NULL>,person,True,2018-03-07 11:10:08,<NULL>,2018-03-07 >>>>>> 11:10:08,<NULL> >>>>>> 2,Paul,<NULL>,person,True,2018-03-07 11:10:08,<NULL>,2018-03-07 >>>>>> 11:10:08,<NULL> >>>>>> 3,Laptop,2,object,True,2018-03-07 11:10:08,<NULL>,2018-03-07 >>>>>> 11:10:08,<NULL> >>>>>> >>>>>> >>>>>> *WITHOUT record versioning* >>>>>> >>>>>> In [1]: db(db.test.id > 0).select() >>>>>> Out[1]: <Rows (3)> >>>>>> >>>>>> In [2]: test1 = db.test.with_alias('test1') >>>>>> >>>>>> In [3]: test2 = db.test.with_alias('test2') >>>>>> >>>>>> In [4]: query = (test2.type_marker=='object') | >>>>>> (test2.type_marker==None) & (test1.type_marker=='person') >>>>>> ...: >>>>>> >>>>>> In [5]: db().select(db.test.ALL) >>>>>> Out[5]: <Rows (3)> >>>>>> >>>>>> In [6]: query = (test2.type_marker=='object') & >>>>>> (test1.type_marker=='person') >>>>>> >>>>>> In [7]: db().select(db.test.ALL) >>>>>> Out[7]: <Rows (3)> >>>>>> >>>>>> In [8]: db(query).select(test1.title, test2.title, left=test2.on( >>>>>> test1.id==test2.granny)) >>>>>> Out[8]: <Rows (1)> >>>>>> >>>>>> In [9]: db(query)._select(test1.title, test2.title, left=test2.on( >>>>>> test1.id==test2.granny)) >>>>>> Out[9]: 'SELECT "test1"."title", "test2"."title" FROM "test" AS >>>>>> "test1" LEFT JOIN "test" AS "test2" ON ("test1"."id" = "test2"."granny") >>>>>> WHERE (("test2"."type_marker" = \'object\') AND ("test1"."type_marker" = >>>>>> \'person\'));' >>>>>> >>>>>> In [10]: print db().select(db.test.ALL) >>>>>> 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,<NULL>,person,True,2018-03-07 11:10:08,<NULL>,2018-03-07 >>>>>> 11:10:08,<NULL> >>>>>> 2,Paul,<NULL>,person,True,2018-03-07 11:10:08,<NULL>,2018-03-07 >>>>>> 11:10:08,<NULL> >>>>>> 3,Laptop,2,object,True,2018-03-07 11:10:08,<NULL>,2018-03-07 >>>>>> 11:10:08,<NULL> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> *I have this in db.py of a newly created app with web2py trunk/master >>>>>> :* >>>>>> >>>>>> ```python >>>>>> db.define_table('test', >>>>>> Field('title', 'string'), >>>>>> Field('granny', 'reference test'), # convenience >>>>>> - topmost >>>>>> Field('type_marker', 'string'), >>>>>> auth.signature, >>>>>> ) >>>>>> >>>>>> db.test._enable_record_versioning(archive_db=db, >>>>>> archive_name='test_archive', >>>>>> current_record='current_record', >>>>>> is_active='is_active' >>>>>> ) >>>>>> >>>>>> if db(db.test.id > 0).count() == 0: >>>>>> db.test.insert(title='Peter', granny=None, type_marker='person') >>>>>> db.test.insert(title='Paul', granny=None, type_marker='person') >>>>>> db.test.insert(title='Laptop', granny=2, type_marker='object') >>>>>> ``` >>>>>> You can start the web2py shell like this : >>>>>> >>>>>> python web2py.py -a 'DUMMYPWD' -i 127.0.0.1 -p 8001 -S >>>>>> test_versioning_issue -M >>>>>> >>>>>> >>>>>> >>>>>> I don't notice any discrepency between both result, I mean I recover >>>>>> 3 records in both case... It makes sens that the raw query change to >>>>>> make >>>>>> sure it select only active records... >>>>>> >>>>>> It mays happen that you experiment an issue with the web2py version >>>>>> that you use 2.15.4... >>>>>> >>>>>> You may consider trying the above with a freshly create dummy app >>>>>> with the db.py additions above and see by yourself... If you get the >>>>>> same >>>>>> results it means that your own app may have been wrongly initialized in >>>>>> some way... If yous till experiment the issue, try with the stable >>>>>> web2py >>>>>> version from the web2py.com download page... >>>>>> >>>>>> Please report here your progress... >>>>>> >>>>>> If you determine that the issue is related to your old version of >>>>>> web2py, please consider upgrade to the newer stable or wait for the next >>>>>> release that Massimo's want to push soon. >>>>>> >>>>>> Thanks >>>>>> >>>>>> Richard >>>>>> >>>>>> On Wed, Mar 7, 2018 at 5:35 AM, 'Awe' via web2py-users < >>>>>> [email protected]> wrote: >>>>>> >>>>>>> Great, many thanks for testing it. >>>>>>> Andreas >>>>>>> >>>>>>> Am Dienstag, 6. März 2018 22:30:59 UTC+1 schrieb Richard: >>>>>>>> >>>>>>>> Can you make a model definition with a fixture for loading the >>>>>>>> table, I will make some test with trunk and various version to >>>>>>>> determine if >>>>>>>> it a regression... Nevermind I just thought you provide that in the >>>>>>>> first >>>>>>>> email... Let me have a look at that... >>>>>>>> >>>>>>>> Richard >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> On Mon, Mar 5, 2018 at 2:02 PM, 'Awe' via web2py-users < >>>>>>>> [email protected]> wrote: >>>>>>>> >>>>>>>>> Hello Richard, >>>>>>>>> it is not a problem of deleted or changed records. As shown in the >>>>>>>>> example, there is the table and versioning defined. After that 3 >>>>>>>>> records >>>>>>>>> are inserted and then the query is executed. >>>>>>>>> The defined query: >>>>>>>>> query = (test2.type_marker=='object') | >>>>>>>>> (test2.type_marker==None) & (test1.type_marker=='person') >>>>>>>>> rows = current.db(query).select(test1.title, test2.title, >>>>>>>>> left=test2.on(test1.id==test2.granny)) >>>>>>>>> does not give the right result. >>>>>>>>> I appreciate your help, maybe the problem gets easier to >>>>>>>>> understand if you try to test the given example. >>>>>>>>> Many thanks, >>>>>>>>> Andreas >>>>>>>>> >>>>>>>>> >>>>>>>>> Am Montag, 5. März 2018 16:05:26 UTC+1 schrieb Richard: >>>>>>>>>> >>>>>>>>>> Did you set the actual record that have been deleted to is_active >>>>>>>>>> = False?? Or all the records that haven't be deleted yet to TRUE?? >>>>>>>>>> You have >>>>>>>>>> to go in your backend and do an update there >>>>>>>>>> >>>>>>>>>> UPDATE stored_item >>>>>>>>>> SET is_active = TRUE >>>>>>>>>> WHERE is_active IS NULL >>>>>>>>>> >>>>>>>>>> If you didn't delete any record yet... >>>>>>>>>> >>>>>>>>>> You can also do it from web2py I guess with this command : >>>>>>>>>> >>>>>>>>>> db(db.stored_item.is_active == None).update(is_active=True) >>>>>>>>>> db.commit() >>>>>>>>>> >>>>>>>>>> Then you both query should return the same thing... Or at least >>>>>>>>>> they should... >>>>>>>>>> >>>>>>>>>> It important to initialize archive table and parent table >>>>>>>>>> properly when you add audit trail table to an already existing table >>>>>>>>>> containing record. >>>>>>>>>> >>>>>>>>>> I encourage you to read the book about record versioning : >>>>>>>>>> http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Record-versioning >>>>>>>>>> >>>>>>>>>> Richard >>>>>>>>>> >>>>>>>>>> On Sun, Mar 4, 2018 at 12:53 PM, 'Awe' via web2py-users < >>>>>>>>>> [email protected]> wrote: >>>>>>>>>> >>>>>>>>>>> Hello Richard, many thanks for analyzing. Everything you wrote >>>>>>>>>>> is completely right. But I still do not understand the behaviour >>>>>>>>>>> explained >>>>>>>>>>> before. >>>>>>>>>>> If you look at the table posted before, all is_acitve Flags are >>>>>>>>>>> TRUE. >>>>>>>>>>> to get the result I need, I have defined: >>>>>>>>>>> query = (test2.type_marker=='object') | >>>>>>>>>>> (test2.type_marker==None) & (test1.type_marker=='person') >>>>>>>>>>> rows = current.db(query).select(test1.title, test2.title, >>>>>>>>>>> left=test2.on(test1.id==test2.granny)) >>>>>>>>>>> >>>>>>>>>>> the raw sql of this query WITHOUT record versioning is fine. >>>>>>>>>>> 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'))); >>>>>>>>>>> And the result is the way it suposed to be: >>>>>>>>>>> >>>>>>>>>>> test1.title test2.title >>>>>>>>>>> Peter None >>>>>>>>>>> Paul Laptop >>>>>>>>>>> >>>>>>>>>>> The result with record versioning enabled is different: >>>>>>>>>>> test1.title test2.title >>>>>>>>>>> Paul Laptop >>>>>>>>>>> >>>>>>>>>>> Even when the is_acitve Field is defined like that: >>>>>>>>>>> >>>>>>>>>>> Field('is_active', 'boolean', writable=False, readable=False, >>>>>>>>>>> default=True,required=True, requires=IS_NOT_EMPTY()), >>>>>>>>>>> >>>>>>>>>>> So maybe I miss something? At the moment I still wonder why the >>>>>>>>>>> identical query comes to different results. >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Best regards, an many thanks for your help! >>>>>>>>>>> Andreas >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> Am Freitag, 2. März 2018 16:59:35 UTC+1 schrieb Richard: >>>>>>>>>>>> >>>>>>>>>>>> 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 < >>>>>>>>>>>> [email protected]> 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 [email protected]. >>>>>>>>>>>>> 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 [email protected]. >>>>>>>>>>> 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 [email protected]. >>>>>>>>> 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 [email protected]. >>>>>>> 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 [email protected]. >>>>> 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 [email protected] <javascript:>. >>> 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
