Skip to content
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.

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

Bad emulation of DUAL table in HSQLDB when connecting with a DBA user on a database with more than one user #3762

Closed
stellingsimon opened this issue Nov 12, 2014 · 3 comments
Closed

Bad emulation of DUAL table in HSQLDB when connecting with a DBA user on a database with more than one user #3762

stellingsimon opened this issue Nov 12, 2014 · 3 comments
Labels
C: Functionality P: Urgent R: Fixed T: Defect
Milestone
Version 3.5.0

Comments

@stellingsimon
Copy link

We are using Jooq 3.2.6 on HSQLDB and Oracle.

On HSQLDB, the following query

dsl.select(Sequences.ORDER_NUMBER_SEQUENCE.nextval()).fetch().get(0).getValue(0, Long.class);

renders to

select next value for SCHEMA.SEQUENCE
from INFORMATION_SCHEMA.SYSTEM_USERS

which returns three rows on our system (because we have three users in the schema). The values obtained via the above JOOQ call are not dense on HSQLDB, while they are on e.g. Oracle.

This problem does not occur when rendering to the following statement:

call next value for ARIMS.ORDER_NUMBER_SEQUENCE
@lukaseder lukaseder added this to the Version 3.5.0 milestone Nov 12, 2014
@lukaseder
Copy link
Member

Thanks for reporting this. This is news to me. I thought that the INFORMATION_SCHEMA.SYSTEM_USERS table is a legitimate dummy table in HSQLDB as it is supposed to return only the current user:

Contains the list of all users in the database (when used by a DBA user), or just the current user.

From: http://hsqldb.org/doc/guide/guide.html. I wasn't aware of that DBA user part. I guess we'll need to wrap that dummy table like we do for Informix or MS Access:

select next value for SCHEMA.SEQUENCE
from (select 1 as DUAL from INFORMATION_SCHEMA.SYSTEM_USERS limit 1) as DUAL

@lukaseder
Copy link
Member

I can confirm that if we add more users to the system, our integration tests (who run as DBA) fail quite hard. The solution is indeed to add a LIMIT 1 clause and fetch the dual value from a derived table

@lukaseder lukaseder changed the title Sequence skips values on HSQLDB Bad emulation of DUAL table in HSQLDB when connecting with a DBA user on a database with more than one user Nov 14, 2014
lukaseder added a commit that referenced this issue Nov 14, 2014
@lukaseder
[#3762] Bad emulation of DUAL table in HSQLDB when connecting with a …
de75e0e 
…DBA user on a database with more than one user
@lukaseder
Copy link
Member

This fix will be merged to 3.2.7 (#3768), 3.3.5 (#3767), and 3.4.5 (#3766)

arnaudroger pushed a commit to arnaudroger/jOOQ that referenced this issue Dec 16, 2014
@lukaseder @arnaudroger
[jOOQ#3762] Bad emulation of DUAL table in HSQLDB when connecting wit…
6268c6b 
…h a DBA user on a database with more than one user
lukaseder added a commit that referenced this issue Dec 23, 2014
@lukaseder
Version 3.2.7 - December 23, 2014
1d6a3ae 
================================================================================

This is a patch release with some useful fixes for the 3.2 branch

Bug fixes
---------
#3206 - DSL.field(Condition) doesn't correctly handle NULLs when
emulating boolean fields with a CASE expression
#3392 - Call setAccessible(true) only if really needed
#3402 - Wrong character length generated by jOOQ for Firebird
#3408 - Remove the default log4j.xml configuration file from the maven
plugin again
#3420 - NullPointerException when generating code for Oracle AQ Tables
#3427 - Internal QueryCollectorSignal exception escapes into user code
when not dealt with in ExecuteListener
#3430 - JDBC escape syntax is not correctly rendered from plain SQL
#3437 - QualifiedField does not respect RenderContext.qualify()
#3445 - Cannot run Maven code generator with professional edition
#3450 - Backslashes in SQL comments don't get escaped
#3463 - Field.in(...) methods shouldn't return trueCondition() or
falseCondition()
#3473 - java.lang.IllegalArgumentException: Field (null) is not
contained in Row
#3507 - Bad @Support annotation on Select.intersect() and
Select.except()
#3547 - DSLContext.batch(String) doesn't accept bind variables
#3571 - Converted Data Type does not propagate nullable and defaulted
properties
#3602 - Cannot INSERT into view
#3696 - OutOfMemoryException with SQL Azure, caused by long-running loop
of consuming further exceptions
#3762 - Bad emulation of DUAL table in HSQLDB when connecting with a DBA
user on a database with more than one user
#3811 - Potential NullPointerException in Cursor.fetchOne(RecordMapper)
and other methods
lukaseder added a commit that referenced this issue Feb 23, 2015
@lukaseder
Version 3.4.5 - February 23, 2015
d88e004 
================================================================================

Bug fixes
---------

#3696 - OutOfMemoryException with SQL Azure, caused by long-running loop
of consuming further exceptions
#3705 - The jOOQ "flash log" doesn't display nicely when using
java.util.logging
#3711 - Incorrect code generated for cross-schema OBJECT type references
#3718 - Exceptions on rollback hide the original exceptions causing the
rollback
#3730 - Cannot use regex COMMENTS in <forcedType/>'s <expression/>
#3758 - ExecuteListener.warning() is not listed as a method in the
Javadoc
#3762 - Bad emulation of DUAL table in HSQLDB when connecting with a DBA
user on a database with more than one user
#3811 - Potential NullPointerException in Cursor.fetchOne(RecordMapper)
and other methods
#3824 - PostgreSQL dateAdd() erroneously returns a TIMESTAMP value
#3855 - Oracle REAL and FLOAT types cause code generator to produce
BigInteger instead of Double types
#3909 - Inefficient number to number conversion in
org.jooq.tools.Convert
#3915 - Calls to Configuration.derive() create a Configuration that
indirectly references the previous Configuration instance
#3919 - Bad caching of JPA-annotated getters leads to wrong mapping into
POJOs that have @ConstructorProperties
#3924 - Four octects are sent to Postgres for each negative byte in a
byte[]
#3936 - AbstractQuery unnecessarily creates two DefaultRenderContext
instances
#3940 - Batch statements do not work when queries contain inlined bind
variables
#3946 - Replace ArrayList<ExecuteListener> by ExecuteListener[] in
ExecuteListeners to avoid excessive Iterator creation
#3947 - Avoid creating unnecessary Iterators and ArrayLists in
AbstractStore.getAttachables()
#3957 - java.lang.NoSuchMethodException: createARRAY when using Oracle
arrays with BoneCP
#3962 - The H2 emulation of SHR might produce rounding errors
#3966 - Inefficient BIT_COUNT() function for those dialects that do not
natively support SHR()
#3977 - Meta.getTables() also returns entries for indexes for PostgreSQL
#3993 - ResultQuery.fetchAny() methods Javadoc erroneously claims
throwing of InvalidResultException
#4000 - Avoid {ResultSet | CallableStatement | SQLInput).wasNull() calls
if not necessary, as they're somewhat expensive on some JDBC drivers
#4022 - NullPointerException when generating SQLite table with composite
primary key
#4028 - Loader API's "onDuplicateKeyIgnore()" executes inefficient
SELECT
#4036 - Obsolete remark in RecordListener Javadoc about batch semantics
#4056 - Query.bind(String, Object) binds only to the first occurrence of
a named bind value
#4065 - Exception when loading array results from plain SQL queries into
a Record
#4075 - Bad result from Query.getBindValues() when named Params are
repeated
lukaseder added a commit that referenced this issue Feb 23, 2015
@lukaseder
Version 3.4.5 - February 23, 2015
e0a5517 
================================================================================

Bug fixes
---------

#3696 - OutOfMemoryException with SQL Azure, caused by long-running loop
of consuming further exceptions
#3705 - The jOOQ "flash log" doesn't display nicely when using
java.util.logging
#3711 - Incorrect code generated for cross-schema OBJECT type references
#3718 - Exceptions on rollback hide the original exceptions causing the
rollback
#3730 - Cannot use regex COMMENTS in <forcedType/>'s <expression/>
#3758 - ExecuteListener.warning() is not listed as a method in the
Javadoc
#3762 - Bad emulation of DUAL table in HSQLDB when connecting with a DBA
user on a database with more than one user
#3811 - Potential NullPointerException in Cursor.fetchOne(RecordMapper)
and other methods
#3824 - PostgreSQL dateAdd() erroneously returns a TIMESTAMP value
#3855 - Oracle REAL and FLOAT types cause code generator to produce
BigInteger instead of Double types
#3909 - Inefficient number to number conversion in
org.jooq.tools.Convert
#3915 - Calls to Configuration.derive() create a Configuration that
indirectly references the previous Configuration instance
#3919 - Bad caching of JPA-annotated getters leads to wrong mapping into
POJOs that have @ConstructorProperties
#3924 - Four octects are sent to Postgres for each negative byte in a
byte[]
#3936 - AbstractQuery unnecessarily creates two DefaultRenderContext
instances
#3940 - Batch statements do not work when queries contain inlined bind
variables
#3946 - Replace ArrayList<ExecuteListener> by ExecuteListener[] in
ExecuteListeners to avoid excessive Iterator creation
#3947 - Avoid creating unnecessary Iterators and ArrayLists in
AbstractStore.getAttachables()
#3957 - java.lang.NoSuchMethodException: createARRAY when using Oracle
arrays with BoneCP
#3962 - The H2 emulation of SHR might produce rounding errors
#3966 - Inefficient BIT_COUNT() function for those dialects that do not
natively support SHR()
#3977 - Meta.getTables() also returns entries for indexes for PostgreSQL
#3993 - ResultQuery.fetchAny() methods Javadoc erroneously claims
throwing of InvalidResultException
#4000 - Avoid {ResultSet | CallableStatement | SQLInput).wasNull() calls
if not necessary, as they're somewhat expensive on some JDBC drivers
#4022 - NullPointerException when generating SQLite table with composite
primary key
#4028 - Loader API's "onDuplicateKeyIgnore()" executes inefficient
SELECT
#4036 - Obsolete remark in RecordListener Javadoc about batch semantics
#4056 - Query.bind(String, Object) binds only to the first occurrence of
a named bind value
#4065 - Exception when loading array results from plain SQL queries into
a Record
#4075 - Bad result from Query.getBindValues() when named Params are
repeated
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
C: Functionality P: Urgent R: Fixed T: Defect
Projects
None yet
Milestone
Version 3.5.0
Development

No branches or pull requests
2 participants
@lukaseder @stellingsimon