polling messages with jdbc message store can fetch messages from wrong QueueChannel

Hi,

we have encountered problems with the jdbc message store, when polling concurrently from two different queues.

The sql statement looks like this:

SELECT
MESSAGE.MESSAGE_ID, MESSAGE.MESSAGE_BYTES
from
MESSAGE
where
MESSAGE.MESSAGE_ID =
(
SELECT min(MESSAGE_ID) from MESSAGE where CREATED_DATE = (
SELECT min(CREATED_DATE) from MESSAGE, GROUP_TO_MESSAGE
where MESSAGE.MESSAGE_ID = GROUP_TO_MESSAGE.MESSAGE_ID
and GROUP_TO_MESSAGE.GROUP_KEY = ? and MESSAGE.REGION = ?
)
)

and does something like: look for message ids that where created at a date that the youngest message
in the requested queue was create. if more than one id, take the min. The intention is fifo behaviour.

BUT: if two messages were created at the same time (e.g. by a splitter) and routed to different queues, there
is a good chance that a poller polling on queue1 fetches a messages from queue2, because the messages
where created at the same time.

The "outer" where clause is missing the joining "GROUP_KEY = ?" condition.

I think this is a bug. Even more it behaves non deterministic and depends on various timing conditions.

The provided demo shows the error. description see forum thread ..