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.

Reply via email to