Table name is not being quoted in "join/on" and "where"

Description

Let's say I have two tables in one-to-many relationship. There is session with id, and there is thing with session_id.

I know how to compute table name with all the namespacing etc. For the sake of simplicity here, let's assume their names are AO_123_SESSION and AO_123_THING.

I want to run some "advanced" queries with joins, such as:

1 SELECT COUNT(*) FROM AO_123_SESSION JOIN AO_123_THING ON AO_123_SESSION.ID = AO_123_THING.SESSION_ID
1 2 3 4 5 activeObjects.count( Session.class, Query .select() .join(Thing.class, "AO_123_SESSION.ID = AO_123_THING.SESSION_ID"));

Or:

1 DELETE FROM AO_123_THING WHERE SESSION_ID IN (SELECT AO_123_SESSION.ID FROM AO_123_SESSION)
1 activeObjects.deleteWithSQL(Thing.class, "SESSION_ID IN (SELECT AO_123_SESSION.ID FROM AO_123_SESSION)");

In both cases I would expect the generated SQL to be correctly quoted, in whatever way the SQL dialect expects (quotes, square brackets, whatever). However, the SQL generated from these Java calls in a dialect that quotes with " looks like:

1 SELECT COUNT(*) FROM "AO_123_SESSION" JOIN "AO_123_THING" ON AO_123_SESSION."ID" = AO_123_THING."SESSION_ID"
1 DELETE FROM "AO_123_THING" WHERE "SESSION_ID" IN (SELECT AO_123_SESSION."ID" FROM AO_123_SESSION)

As it is, the only way seems to be to load all the data for one table to memory, then construct a query doing the count or deletion with the right IDs. It's even more painful because there can be a large number of these IDs. Then you run into issues with limited number of arguments in prepared statement, so you have to do batching... ARGH.

I can think of at least two solutions:

  • Since table names are so special, just look for them in the string and quote. Something close to "AO_[0-9A-F]{6,6}_[\w]+", I guess.

  • Assume that the name before the field name and dot is a table name, and quote it.

Environment

None

Testing Notes

Add notes...

Status

Assignee

Unassigned

Reporter

Konrad Garus

Labels

None

Add-on Type

None

Team

None

CC

None

Risk factor

None

QA Kickoff Status

None

QA Demo Status

None

Priority

Major
Configure