Hibernate version: latest (not sure)
Mapping documents:
Code:
<component name="keyValueMap" access="field">
<map name="parameters" access="field" table="ENVELOPE_PARAMETERS"
cascade="all-delete-orphan" lazy="true" sort="natural">
<key column="BROADCAST_ENVELOPES" not-null="true"
foreign-key="FK_ENVELOPE_PARAMETERS__BROADCAST_ENVELOPES" />
<composite-map-key class="com.amplafi.foundation.KeyValueMapKey">
<key-property name="namespace" column="NAMESPACEKEY" length="100" access="field"/>
<key-property name="key" column="SUBKEY" length="100" access="field"/>
<key-property name="index" column="IDXKEY" access="field"/>
</composite-map-key>
<element type="string" column="VALUE" length="512"/>
</map>
</component>
I think this is really a problem with MySQL, but they probably won't change it, so is this something changeable in hibernate's MySQL dialogue? I believe this type of subselect query works fine in PostgreSQL
Thanks
Code between sessionFactory.openSession() and session.close():from BroadcastMessage as BM where ('test', 'testkey', 0) in indices(BM.keyValueMap.parameters) and 10616280 in elements(BM.keyValueMap.parameters)
Full stack trace of any exception that occurs:No exception, just returns 0 results
Name and version of the database you are using: MySQL 5.0.67
The generated SQL (show_sql=true):Code:
select
broadcastm0_.* --for brevity
from
BROADCAST_MESSAGES broadcastm0_
where
(
(
'test' , 'testkey' , 0
) in (
select
parameters1_.NAMESPACEKEY,
parameters1_.SUBKEY,
parameters1_.IDXKEY
from
MESSAGE_PARAMETERS parameters1_
where
broadcastm0_.ID=parameters1_.BROADCAST_MESSAGE
)
)
and (
10616280 in (
select
parameters2_.VALUE
from
MESSAGE_PARAMETERS parameters2_
where
broadcastm0_.ID=parameters2_.BROADCAST_MESSAGE
)
)
In MySQL, this does not work, because varchars (the two columns are varchars) need to be cast to char for comparison to quoted strings. The SQL that does work is:
Code:
select
broadcastm0_.* --for brevity
from
BROADCAST_MESSAGES broadcastm0_
where
(
(
'test' , 'testkey' , 0
) in (
select
CAST(parameters1_.NAMESPACEKEY AS CHAR),
CAST(parameters1_.SUBKEY AS CHAR),
parameters1_.IDXKEY
from
MESSAGE_PARAMETERS parameters1_
where
broadcastm0_.ID=parameters1_.BROADCAST_MESSAGE
)
)
and (
10616280 in (
select
parameters2_.VALUE
from
MESSAGE_PARAMETERS parameters2_
where
broadcastm0_.ID=parameters2_.BROADCAST_MESSAGE
)
)
Login
FAQ
Search