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] <javascript:>> 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] <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.
