[JIRA] (HHH-16933) JSON issue on Oracle 23c
by Christian Beikov (JIRA)
Christian Beikov ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMWM3ODIwYzJm... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16933?atlOrigin=eyJpIjoiMWM3OD... ) HHH-16933 ( https://hibernate.atlassian.net/browse/HHH-16933?atlOrigin=eyJpIjoiMWM3OD... ) JSON issue on Oracle 23c ( https://hibernate.atlassian.net/browse/HHH-16933?atlOrigin=eyJpIjoiMWM3OD... )
Issue Type: Bug Affects Versions: 6.2.6 Assignee: Unassigned Components: hibernate-core Created: 13/Jul/2023 03:52 AM Priority: Major Reporter: Christian Beikov ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... )
Not sure if the problem is in Hibernate or Oracle 23c. Copying from a conversation with Loic:
A minimal reproducer is:
create table JsonHolder (
id number(19,0) not null,
aggregate json,
primary key (id),
check (aggregate.theInt.number() is not null)
);
producing ORA-00904: "AGGREGATE"."THEINT"."NUMBER": invalid identifier
The fact is that you're using Oracle SQL dot notation that requires an alias (ideal usage being DML).
So to make your code work, you'd need to use the JSON_VALUE() function instead such as:
create table JsonHolder (
id number(19,0) not null,
aggregate json,
primary key (id),
check (json_value(aggregate, '$.theInt.number()') is not null)
);
( https://hibernate.atlassian.net/browse/HHH-16933#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16933#add-comment?atlOrigin=ey... )
Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100231- sha1:2991753 )
2 years, 9 months
[JIRA] (HHH-16932) envers audit-trail not written for jsonb column
by Christian Thiel (JIRA)
Christian Thiel ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=712020%... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiZGRjZDJhODk4... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16932?atlOrigin=eyJpIjoiZGRjZD... ) HHH-16932 ( https://hibernate.atlassian.net/browse/HHH-16932?atlOrigin=eyJpIjoiZGRjZD... ) envers audit-trail not written for jsonb column ( https://hibernate.atlassian.net/browse/HHH-16932?atlOrigin=eyJpIjoiZGRjZD... )
Change By: Christian Thiel ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=712020%... )
I’m using a Embeddable Object to store i18n in a JSONB-Column like this:
{noformat}@Table(schema = "mdata", name = "PART")
@Audited(withModifiedFlag = true)
public class Part {
@JdbcTypeCode(SqlTypes.JSON)
Translation description;
...
{noformat}
with
{noformat}@Embeddable
public class Translation {
String de;
String en;
}
{noformat}
All changes to the Part entity trigger an entry in the envers-table PART_AUD, but
* changes to the JSONB description-column are not marked as modified and the
* jsonb-values in the PART_AUD are always NULL.
I have added a Quarkus Sample-Project with a test-case.
My findings so far:
I use Flyway to create the tables → the _AUD table has a jsonb-column, like the original table.
Without flyway, if i configure the project to let hibernate create the tables, the _AUD table has separate STRING columns for each field in the embeddable! This seems odd…
In the insert-statement that envers generates the jsonb-column is missing at all (see console-log of my test):
{code:sql} insert
into
base.PART_AUD
(REVTYPE,description_MOD,name,name_MOD,stamp,stamp_MOD,REV,id)
values
(?,?,?,?,?,?,?,?){code}
may this is releated to [https://hibernate.atlassian.net/browse/HHH-16910|https://hibernate.atlass...]
( https://hibernate.atlassian.net/browse/HHH-16932#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16932#add-comment?atlOrigin=ey... )
Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100231- sha1:2991753 )
2 years, 9 months
[JIRA] (HHH-16932) envers audit-trail not written for jsonb column
by Christian Thiel (JIRA)
Christian Thiel ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=712020%... ) *created* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiZDY0YjdhNzFh... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16932?atlOrigin=eyJpIjoiZDY0Yj... ) HHH-16932 ( https://hibernate.atlassian.net/browse/HHH-16932?atlOrigin=eyJpIjoiZDY0Yj... ) envers audit-trail not written for jsonb column ( https://hibernate.atlassian.net/browse/HHH-16932?atlOrigin=eyJpIjoiZDY0Yj... )
Issue Type: Bug Affects Versions: 6.2.5 Assignee: Chris Cranford ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) Attachments: code-with-quarkus.zip Components: hibernate-envers Created: 12/Jul/2023 23:57 PM Environment: Quarkus 3.2.0
hibernate-core 6.2.5.final
hiberanate-envers-6.2.5.final Priority: Major Reporter: Christian Thiel ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=712020%... )
I’m using a Embeddable Object to store i18n in a JSONB-Column like this:
@Table(schema = "mdata", name = "PART")
@Audited(withModifiedFlag = true)
public class Part {
@JdbcTypeCode(SqlTypes.JSON)
Translation description;
...
with
@Embeddable
public class Translation {
String de;
String en;
}
All changes to the Part entity trigger an entry in the envers-table PART_AUD, but
* changes to the JSONB description-column are not marked as modified and the
* jsonb-values in the PART_AUD are always NULL.
I have added a Quarkus Sample-Project with a test-case.
My findings so far:
I use Flyway to create the tables → the _AUD table has a jsonb-column, like the original table.
Without flyway, if i configure the project to let hibernate create the tables, the _AUD table has separate STRING columns for each field in the embeddable! This seems odd…
In the insert-statement that envers generates the jsonb-column is missing at all (see console-log of my test):
insert
into
base.PART_AUD
(REVTYPE,description_MOD, name ,name_MOD,stamp,stamp_MOD,REV,id)
values
(?,?,?,?,?,?,?,?)
( https://hibernate.atlassian.net/browse/HHH-16932#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16932#add-comment?atlOrigin=ey... )
Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100231- sha1:2991753 )
2 years, 9 months