Using case-insensitive table parsing on inserts does not work properly
----------------------------------------------------------------------
Key: SEQUOIA-1112
URL: https://forge.continuent.org/jira/browse/SEQUOIA-1112
Project: Sequoia
Type: Bug
Components: Core
Versions: sequoia 2.10.10
Environment: Windows XP Professional
Apache Derby
Reporter: Jonathan Stockho
If you have case-insensitive monitoring and you try to do an insert with the
following format : "insert into SCHEMA.TABLENAME values (1);" You will receive
an unknown table error if the schema name or the table name is not entirely
lowercase.
The underlying reason is that when doing the insert, Sequoia parses the query
and tries to do a lookup on a hash map that contains entries for each table.
The entries all exist twice, once with the schema name and once without. For
example, if you have a DB with the schema SCHEMA1 and the table TABLE1, the
hash map would contain two copies of the TABLE1 DatabaseTable object. One
would have the key "SCHEMA1.TABLE1" and the other would have the key "TABLE1".
Here's the sequence of events in the code:
In org.continuent.sequoia.controller.requests.InsertRequest, on line 213 (in
the 2.10.10 build) you'll see:
if (!isCaseSensitive)
insertTable = insertTable.toLowerCase();
This converts the name of the table being accessed to all lowercase. At line
226, you have the following:
DatabaseTable t = schema.getTable(insertTable, isCaseSensitive);
The method getTable exists in
org.continuent.sequoia.common.sql.schema.DatabaseSchema
In that class, on line 389 there is a getTable(tablename) call. The getTable
method calls get() on the hash map "tables". This get() fails if the name of
the schema or table has any uppercase letters in it. This is because when the
hash map is created, the keys are generated with the exact value from the
database schema. If you are using case-insensitive monitoring, the hash map is
still using case-sensitive names. And, the get() method is simply Java's own
HashMap.get() method. This uses .equals(), which is case-sensitive for String
objects.
The code then checks for null (line 340 in DatabaseSchema). It then tries to
strip the db name off the front if there (in this case it isn't) and then tries
to check for different types of quotes around the table name. Again, here this
is irrelevant.
After failing to match the tablename with a key in the hash map, null is
returned.
On line 390 in DatabaseSchema it checks for this null and checks for case
sensitivity. So, it looks like someone might expect this outcome. Again, the
db name is checked and stripped off. Then there is some code which I consider
extremely dangerous and should never be called. An iterator loops through the
values of the hash map (rather than the keys) and tries to match the
insertTable name with the name of the table object. This could cause a match
with the wrong table if you're using schema-based table names because it might
find another table with the same name, but in a different schema.
This check also fails because now it's trying to match "schema1.table1" to
"table1".
If you take out the schema name from the query it works, but not like it
should. Let's say I try "insert into TABLE1 values(1)"; This goes through the
hash map, trying to match "table1" (which is forced to lowercase by
InsertRequest) to "SCHEMA1.TABLE1" or to "TABLE1". Both are keys in the hash
map, but the match fails. Then it tries to match "table1" to a
case-insensitive lookup of each value in the hash map. Since there is a
DatabaseTable object with the name "TABLE1" this lookup succeeds while
iterating through the values.
Even though the above example works, it shouldn't because it was matching the
values instead of the keys in the hash map.
My suggestion is to either create the hash map with all lowercase keys (if
you're using case-insensitive matching of table names) or to overload the get()
method for the table hash map so it can handle a case-insensitive comparison.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
https://forge.continuent.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
_______________________________________________
Sequoia mailing list
[email protected]
https://forge.continuent.org/mailman/listinfo/sequoia
