You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Satheesh Bandaram (JIRA)" <de...@db.apache.org> on 2005/06/24 23:23:57 UTC
[jira] Created: (DERBY-392) Disable creating indexes on long varchar for bit data. Long varchar column doesn't allow creating indexes already.
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
Priority: Minor
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
--
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
[jira] Assigned: (DERBY-392) Disable creating indexes on long varchar for bit data. Long varchar column doesn't allow creating indexes already.
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-392?page=all ]
Satheesh Bandaram reassigned DERBY-392:
---------------------------------------
Assign To: Satheesh Bandaram
> 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
> Assignee: Satheesh Bandaram
> Priority: Minor
> Attachments: patch
>
> 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
[jira] Closed: (DERBY-392) Disable creating indexes on long varchar for bit data. Long varchar column doesn't allow creating indexes already.
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-392?page=all ]
Satheesh Bandaram closed DERBY-392:
-----------------------------------
I have verified the fix addresses the problem in 10.2 build.
> 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
> 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
[jira] Updated: (DERBY-392) Disable creating indexes on long varchar for bit data. Long varchar column doesn't allow creating indexes already.
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-392?page=all ]
Satheesh Bandaram updated DERBY-392:
------------------------------------
Attachment: Derby392
Updated patch with master updates. I will merge this into trunk and then port to 10.1 soon.
> 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
> 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
[jira] Commented: (DERBY-392) Disable creating indexes on long varchar for bit data. Long varchar column doesn't allow creating indexes already.
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-392?page=comments#action_12331421 ]
Satheesh Bandaram commented on DERBY-392:
-----------------------------------------
This fix has been submitted.
Sending java\engine\org\apache\derby\iapi\types\TypeId.java
Sending java\testing\org\apache\derbyTesting\functionTests\master\aggbuiltin.out
Sending java\testing\org\apache\derbyTesting\functionTests\master\bit.out
Sending java\testing\org\apache\derbyTesting\functionTests\master\groupBy.out
Sending java\testing\org\apache\derbyTesting\functionTests\master\implicitConversions.out
Transmitting file data .....
Committed revision 295070.
> 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
> 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
[jira] Closed: (DERBY-392) Disable creating indexes on long varchar for bit data. Long varchar column doesn't allow creating indexes already.
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-392?page=all ]
Satheesh Bandaram closed DERBY-392:
-----------------------------------
Fix Version: 10.1.1.2
Resolution: Fixed
> 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
[jira] Commented: (DERBY-392) Disable creating indexes on long varchar for bit data. Long varchar column doesn't allow creating indexes already.
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-392?page=comments#action_12331427 ]
Satheesh Bandaram commented on DERBY-392:
-----------------------------------------
Ported fix from TRUNK to 10.1 branch.
Sending java\engine\org\apache\derby\iapi\types\TypeId.java
Sending java\testing\org\apache\derbyTesting\functionTests\master\aggbuiltin.out
Sending java\testing\org\apache\derbyTesting\functionTests\master\bit.out
Sending java\testing\org\apache\derbyTesting\functionTests\master\groupBy.out
Sending java\testing\org\apache\derbyTesting\functionTests\master\implicitConversions.out
Transmitting file data .....
Committed revision 295076.
> 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
> 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
[jira] Commented: (DERBY-392) Disable creating indexes on long varchar for bit data. Long varchar column doesn't allow creating indexes already.
Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-392?page=comments#action_12314431 ]
Daniel John Debrunner commented on DERBY-392:
---------------------------------------------
Not sure why we would remove a working feature, applications may be dependent on it.
Now it may be that such indexes are not really working beause the btree has a limit on key length that would be make them not useable if the key value was over 1/2 page length.
Or maybe if long varchar for bit data is not comparable then obviously they can't be used and so it's a bug allowing such an index.
> 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
> Priority: Minor
>
> 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
--
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
[jira] Resolved: (DERBY-392) Disable creating indexes on long varchar for bit data. Long varchar column doesn't allow creating indexes already.
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-392?page=all ]
Satheesh Bandaram resolved DERBY-392:
-------------------------------------
Fix Version: 10.2.0.0
10.1.2.0
Resolution: Fixed
> 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
> 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
[jira] Reopened: (DERBY-392) Disable creating indexes on long varchar for bit data. Long varchar column doesn't allow creating indexes already.
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ 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
[jira] Updated: (DERBY-392) Disable creating indexes on long varchar for bit data. Long varchar column doesn't allow creating indexes already.
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-392?page=all ]
Satheesh Bandaram updated DERBY-392:
------------------------------------
Attachment: (was: patch)
> 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
> 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
[jira] Updated: (DERBY-392) Disable creating indexes on long varchar for bit data. Long varchar column doesn't allow creating indexes already.
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-392?page=all ]
Satheesh Bandaram updated DERBY-392:
------------------------------------
Attachment: patch
Submitting a patch for review. I think we need to document that Derby doesn't support indexing, group by, order by on long varchar for bit data anymore in release notes, in case some one is already using this incorrect behavior.
I think it would be good to port this fix to 10.1, so we can prevent more incorrect dependence on this behavior.
> 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
> Attachments: patch
>
> 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
Re: [jira] Updated: (DERBY-392) Disable creating indexes on long
varchar for bit data. Long varchar column doesn't allow creating indexes
already.
Posted by Mike Matrigali <mi...@sbcglobal.net>.
"ineffective" is an understatement. Inserting a value that is bigger
than 1/2 of a page will cause the insert to fail - with some sort of key
too big error. Current btree mechanism does not support keys bigger
than 1/2 of a page.
Many systems get around this by only indexing the first N bytes/chars (I
think 512 bytes is the mySQL value of N), of the datatype. Derby does
not have this support, but could be implemented. Building and
maintaining such an index would be easy. Some work would have to be
done in the language layer to teach it that the value in the index key
could only be used for indexing into the actual value in the base row,
it could never be used as a covering key. No changes would be necessary
at the store btree level, as it does not really know what it is storing.
Satheesh Bandaram (JIRA) wrote:
> [ 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
>
>
[jira] Updated: (DERBY-392) Disable creating indexes on long varchar for bit data. Long varchar column doesn't allow creating indexes already.
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ 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