Given a query in the form
select a from TableA a
join TableB b
on a.primarykey || '' = b.foreignkey
HXTT blows up and doesn't return.. The relative table sizes are both less than 100k rows.
Why do I need such a strange join? A combination of legacy data, hibernate, and inability to touch the schema and datasources.
In general, I suppose the hibernate optimiser should be able to pick an index for trivial optimization cases like the above...
M
|
We will test it soon.
select a from TableA a
join TableB b
on a.primarykey || '' = b.foreignkey ?
Why do you try
select a from TableA a
join TableB b
on a.primarykey= b.foreignkey ?
HXTT Aceess won't quicken a.primarykey || ''.
|
The problem is that because the SQL is generated by hibernate framework.
The real issue is the following
a.primarykey is a VARCHAR(10). BUT... the value is stored with right padded spaces.
e.g. '12345 ' not '12345'.
The problem is that b.foreignkey is also VARCHAR(10), but NOT stored with right spaces. This is moronic schema, but I am working with legacy data so I don't really have a choice to change the schema.
Basically the problem is this..
Table a
join Table b
on a.primarykey = b.primarykey returns results of the join. Which is the correct behaviour that matches access. The problem is that
len(a.primarykey) != len(b.primarykey) and the results return to java leave us with !aJavaObject.primarykey.equals(bJavaObject.foreignkey).
e.g. a.primarykey = '12345 '
b.foreignkey = '12345'
This causes big problems for Hibernate. The way to solve the problem is to append a '' to force HXTT to treat a.primarkey as a VARCHAR(10), strip the spaces and return a.primarykey = '12345'.
Hope this helps.
|
Oh... I noticed after I posed that the comment poster is stripping spaces.
should read
e.g. '12345' not '12345'
|
WTF.. Anything in the single quotes is getting striped..
should really read
e.g. '12345' || 5 MORE SPACES, not '12345'
|
I guess primarykey is an indexed column, so you can use:
select a from TableA a
join TableB b
on a.primarykey= RPAD((b.foreignkey,10,' ')
|
|
Please let us know that speed resultwith the suggested sql. If it's slow still, we will analyse and give a solution.
|
Main
webmaster@hxtt.com