sql: support features used by Hibernate

[justinj]

There's a number of queries issued by the Hibernate test suite that we don't execute properly or cannot parse (for various different reasons), this issue tracks them as they're found and fixed. This list is incomplete and growing as we find more:

Most important:

• information_schema._pg_expandarray - sql: support Hibernate's use of _pg_expandarray #16971

Mildly more important:

• time datatype - sql: time datatype #16490
• CREATE TEMPORARY TABLE ... - sql: Add support for TEMP tables #5807
• Hibernate tries to load SERIAL types into an int, which fails if they are too large - testDirectIdPropertyAccess -sql: Hibernate tries to load SERIAL types into an int #22607
  • sql: 32 bit SERIAL type sql: 32 bit SERIAL type #24062
• int/float operations (like 1::int + float::int) - testQuotientConversion -sql: Hibernate's use of int/float operations #24063
• Adding a foreign key without having an index
  • This often doesn't actually result in the tests failing
• sql: Support ALTER TABLE __ ADD CONSTRAINT __ FOREIGN KEY __ statements sql: Support ALTER TABLE __ ADD CONSTRAINT __ FOREIGN KEY __ statements #8855

Mildly less important:

• ALL operator with subqueries (rather than just arrays) - testEqualAll - sql: ANY/ALL with subqueries #17662
• Sequences - Auto Increment function #16445
  • We can disable sequences in a dialect
• Correlated subqueries - testCollectionIsEmptyCriteria
• Types besides int8 - testConstructorResultNativeQuery - sql: support Postgres syntax names for all types #14493
  • We can remap the names to int in a dialect, but just doing this seems to break Hibernate's type inference.
• current_time - testDateTimeFunctions - sql: timetz datatype #20944
• SELECT ... FOR UPDATE - can be worked around via a dialect - sql: explicit lock syntax (SELECT FOR {SHARE,UPDATE} {skip locked,nowait}) #6583
• sql: Implement "ESCAPE" sql: Implement "ESCAPE" #23538

Other:

• sql: statement with parameters much slower than with string concatenation sql: statement with parameters much slower than with string concatenation #21463
• Run the Hibernate test suite and capture examples of failed queries Run the Hibernate test suite and capture examples of failed queries  #24741
• sql: use Hibernate's test suite sql: use Hibernate's test suite #15821

Some of them would probably be better to fix in CockroachDB itself (like temporary tables) and some would be better fixed by a custom Hibernate dialect, and some might be infeasible to fix at all at present.

[cuongdo]

Can you bucket these by how many tests are failing because of these issues? Perhaps two categories, such as "frequent failure" and "infrequent failure", would be sufficient. I believe this would help the prioritization of these issues.

[justinj]

Yeah, sounds good. At the moment I have them roughly sorted by how many failures I estimate they cause (top causing the most), but it's very rough.

[awoods187]

Do we need savepoint support (#10735) for Hibernate?

[tbg]

User @Linitha on Gitter ran into the int4/int problem: https://gitter.im/cockroachdb/cockroach?at=5b2a9a626ceffe4eba1d2e24

Hi everyone
We are planning to move from mysql to cockroachDB. Our app uses Hibernate so I ran first time with hibernate.api.hbm2ddl.auto= create and next time when I run with hibernate.api.hbm2ddl.auto= validate I get the following exception:
Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: wrong column type encountered in column [cancellationReason] in table [api_apple_store_receipt_log]; found [int8 (Types#BIGINT)], but expecting [int4 (Types#INTEGER)]
My entity class have:
private Integer cancellationReason;

[awoods187]

cc @knz

[knz]

@Linitha @tschottdorf we plan to work on that as #26925.

[tim-o]

Zendesk ticket #2504 has been linked to this issue.

[tim-o]

Comment made from Zendesk by Tim O'Brien on 2018-08-11 at 18:22:

Paging @Bram for hibernate expertise. At the moment I believe it’s not fully supported and non-trivial. Unsure whether this specific issue is known or not.

[tim-o]

Comment made from Zendesk by Tim O'Brien on 2018-08-11 at 18:23:

Hi @vladimir_shulga,

We’re actually working on just this problem right now. What happens is this is a combination of the fact that our transactions work slightly differently than postgres’ and Hibernate doesn’t know how to handle this just yet and we return some different errors.

This example should show you how to approach it without Hibernate, and we’re actively working on getting it working properly with Hibernate.

We have a number of issues still to get Hibernate working, here’s the meta issue for it: #16491

Please feel free to post your issue directly as a bug in our github. External requests help us prioritize fixes and updates.

[tim-o]

Comment made from Zendesk by Tim O'Brien on 2018-08-11 at 18:23:

Hi @Bram,

Thank you for clarifying this question out!
I’ve created the issue #27274. Do you know how much could it take to have it fixed?

[jordanlewis]

This feels like it's outlived its usefulness - cc @rafiss @solongordon maybe want to close this one?

[rafiss]

Yes we don't need this now. For anyone else looking here, please try out the CockroachDBDialect in Hibernate, and let us know if you have issues.