[ http://issues.apache.org/jira/browse/DERBY-392?page=all ] Satheesh Bandaram reopened DERBY-392: -------------------------------------
Reopening to include 10.1.1.2 fixIn. > Disable creating indexes on long varchar for bit data. Long varchar column > doesn't allow creating indexes already. > ------------------------------------------------------------------------------------------------------------------ > > Key: DERBY-392 > URL: http://issues.apache.org/jira/browse/DERBY-392 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.1.1.0, 10.0.2.2 > Environment: generic > Reporter: Satheesh Bandaram > Assignee: Satheesh Bandaram > Priority: Minor > Fix For: 10.2.0.0, 10.1.2.0, 10.1.1.2 > Attachments: Derby392 > > I guess I did not articulate my reasons for suggesting removal of index > support for 'long varchar for bit data' completely. > > 1) Long varchar types are not comparable... If they are not comparable, > it should not be possible to use them in GROUP BY, ORDER BY or allow regular > B-Tree indexes. > 2) Also, long varchar types tend to be long in size and hence the regular > B-Tree mechanism is not a suitable way to index them. Dan also mentioned they > become ineffective for keys longer than half a page size. > It should not be possible to create an index on 'long varchar for bit data' > datatypes. Derby currently doesn't allow creating indexes on 'long varchar' > datatypes and the same should apply for it's bit data equivalent too. > ij> create table longchar ( i int, c long varchar); > 0 rows inserted/updated/deleted > ij> create index longIdx on longchar(c); > ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, > ORD > ER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because > compari > sons are not supported for that type. > ij> create table longcharBitData ( i int, c long varchar for bit data); > 0 rows inserted/updated/deleted > ij> create index longIdx on longcharBitData(c); > 0 rows inserted/updated/deleted > Derby also seems to allow GROUP BY and/or ORDER BY on LONG VARCHAR FOR BIT > DATA types. I believe this is incorrect too. > select c from longcharBitData group by c; > C > -------------------------------------------------------------------------------- > ------------------------------------------------ > 0 rows selected > ij> select c from longcharBitData group by c order by c; > C > -------------------------------------------------------------------------------- > ------------------------------------------------ > 0 rows selected -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
