Description

lwasylow

opened on Mar 27, 2019

Issue body actions

On binary XML storage insignificant whitespaces are removed.
This is default way of storing XML data post 12.1 release and causing trimming of whitespaces.
Using CLOBS is inefficient.
e.g
select * from table(ut_varchar2_list(' '))

select * from table(ut_varchar2_list(''))

are showing as equal.

Activity

1. lwasylow

   added

   bug

   on Mar 27, 2019
2. jgebal commented on Mar 27, 2019

   

   jgebal

   on Mar 27, 2019

   Member

   More actions

   Documentation note on this can be found here:
   https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adxdb/choice-of-XMLType-storage-and-indexing.html#GUID-320807FC-DB58-4600-8F92-A1247A354AE5
3. jgebal commented on Mar 27, 2019

   

   jgebal

   on Mar 27, 2019

   Member

   More actions

   Raised it with ASKTom, as documentation is not mentioning data-infidelity.
   I would expect XMLType to give full data-fidelity.

   It smells like an Oracle bug. Not much we can do about it.
   CLOB storage is absolutely not an option due to performance issues.
4. jgebal commented on Mar 27, 2019

   

   jgebal

   on Mar 27, 2019

   Member

   More actions

   LiveSQL demo of issue:
   https://livesql.oracle.com/apex/livesql/s/h5zgtdvybyr9pbyazs8na56sb
5. jgebal commented on Mar 28, 2019

   

   jgebal

   on Mar 28, 2019

   Member

   More actions

   Aswer from ASKTom
   https://asktom.oracle.com/pls/apex/asktom.search?tag=infidelity-when-storing-xmltype-data-elements-spaces
6. lwasylow commented on Mar 29, 2019

   

   lwasylow

   on Mar 29, 2019

   MemberAuthor

   More actions

   Follow up question on XMLTABLE reading these data.
   https://livesql.oracle.com/apex/livesql/s/h6ahy0vlhykp7k7xu0ua0xmw4
7. lwasylow commented on Apr 19, 2019

   

   lwasylow

   on Apr 19, 2019

   MemberAuthor

   More actions

   Some of the changes been implemented in PR #895
   There are still issues regarding whitespace vs double whitespace.
8. jgebal commented on Dec 29, 2019

   

   jgebal

   on Dec 29, 2019

   Member

   More actions

   @lwasylow - what should we do about this issue?
9. lwasylow commented on Dec 29, 2019

   

   lwasylow

   on Dec 29, 2019

   MemberAuthor

   More actions

   I think we close it for now. Only problem is a whitespace vs double space. Not much we can do and no one raised as an issue.
10. jgebal commented on Dec 29, 2019

    

    jgebal

    on Dec 29, 2019

    Member

    More actions

    We should probably mention it in documentation.
    It only applies to cursor/object/nested-table comparison - right?
11. lwasylow commented on Dec 30, 2019

    

    lwasylow

    on Dec 30, 2019

    MemberAuthor

    More actions

    Yes it's only when we use xml as part of cursor comparison. Once we stop 11. 2 we could switch to json maybe.
12. jgebal

    added a commit that references this issue on Jan 2, 2020

    Updated documentation for whitespace vs null compare.

    This issue will close once commit d33bd80 is merged into the 'develop' branch.

    d33bd80
13. jgebal commented on Jan 4, 2020

    

    jgebal

    on Jan 4, 2020

    Member

    More actions

    One more Idea that comes to my mind is to use ANYDATA.convertObject() and store XMTYPE as ANYDATA. This again will be n overhead but I wonder which one is worse to-CLOB or to-ANYDATA conversion.
14. jgebal

    closed this as completedin #1039on Jan 4, 2020
15. jgebal

    added this to the 3.1.10 milestone on Feb 23, 2020

Issue actions