Skip to content

On binary XML storage insignificant whitespaces are removed. #880

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
lwasylow opened this issue Mar 27, 2019 · 11 comments · Fixed by #1039
Closed

On binary XML storage insignificant whitespaces are removed. #880

lwasylow opened this issue Mar 27, 2019 · 11 comments · Fixed by #1039
Labels
bug
Milestone
v3.1.10

Comments

@lwasylow
Copy link
Member

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.
@lwasylow lwasylow added the bug label Mar 27, 2019
@jgebal
Copy link
Member

jgebal commented Mar 27, 2019

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

@jgebal
Copy link
Member

jgebal commented Mar 27, 2019

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.

@jgebal
Copy link
Member

jgebal commented Mar 27, 2019

LiveSQL demo of issue:
https://livesql.oracle.com/apex/livesql/s/h5zgtdvybyr9pbyazs8na56sb

@jgebal
Copy link
Member

jgebal commented Mar 28, 2019

Aswer from ASKTom
https://asktom.oracle.com/pls/apex/asktom.search?tag=infidelity-when-storing-xmltype-data-elements-spaces

@lwasylow
Copy link
Member Author

Follow up question on XMLTABLE reading these data.
https://livesql.oracle.com/apex/livesql/s/h6ahy0vlhykp7k7xu0ua0xmw4

@lwasylow
Copy link
Member Author

lwasylow commented Apr 19, 2019 •
edited by jgebal
Loading

Some of the changes been implemented in PR #895
There are still issues regarding whitespace vs double whitespace.

@jgebal
Copy link
Member

jgebal commented Dec 29, 2019

@lwasylow - what should we do about this issue?

@lwasylow
Copy link
Member Author

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.

@jgebal
Copy link
Member

jgebal commented Dec 29, 2019

We should probably mention it in documentation.
It only applies to cursor/object/nested-table comparison - right?

@lwasylow
Copy link
Member Author

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

jgebal added a commit that referenced this issue Jan 2, 2020
@jgebal
Updated documentation for whitespace vs null compare.
d33bd80 
Updated tests o represent the issue more clearly.

Closes #880
@jgebal jgebal mentioned this issue Jan 2, 2020
Merged
@jgebal
Copy link
Member

jgebal commented Jan 4, 2020

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.

@jgebal jgebal added this to the 3.1.10 milestone Feb 23, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug
Projects
None yet
Milestone
v3.1.10
Development

Successfully merging a pull request may close this issue.

Updated documentation for whitespace vs null compare. utPLSQL/utPLSQL
2 participants
@jgebal @lwasylow