db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Satheesh Bandaram (JIRA)" <derby-...@db.apache.org>
Subject [jira] Updated: (DERBY-392) Disable creating indexes on long varchar for bit data. Long varchar column doesn't allow creating indexes already.
Date Sun, 26 Jun 2005 05:54:00 GMT
     [ http://issues.apache.org/jira/browse/DERBY-392?page=all ]

Satheesh Bandaram updated DERBY-392:
------------------------------------

    Description: 
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

  was:
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


> 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.0.2.2, 10.1.1.0
>  Environment: generic
>     Reporter: Satheesh Bandaram
>     Priority: Minor

>
> 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


Mime
View raw message