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 "Shawn Aucoin (JIRA)" <ji...@apache.org> on 2008/05/06 05:07:56 UTC
[jira] Created: (DERBY-3664) Trailing white space in varchar field
is trimmed off.
Trailing white space in varchar field is trimmed off.
-----------------------------------------------------
Key: DERBY-3664
URL: https://issues.apache.org/jira/browse/DERBY-3664
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.4.1.3, 10.3.2.1, 10.3.1.4
Environment: Win XP, Derby 10.4.1.3
Reporter: Shawn Aucoin
When executing an insert statement with a varchar field containing trailing whitespace, the trailing whitespace is removed. This results in unique constraint issues. If index is not unique, select * from login where login = 'first_test' will return both records. Preceeding whitespace doesn't appear to be affected. I tested this from IJ and from java api. I included an example below
CREATE TABLE LOGIN
(
LOGIN_ID INT PRIMARY KEY,
LOGIN VARCHAR(60),
);
CREATE UNIQUE INDEX LOGIN_LOGIN_IDX
ON LOGIN (LOGIN DESC);
insert into login (LOGIN_ID, LOGIN) values (1, 'first_test');
insert into login (LOGIN_ID, LOGIN) values (2, 'first_test ');
Now, i fully admit that there is no logical reason for trailing whitespace, but it is client data that i am dealing with and I may not be able to get it changed. Thanks, derby is a sweet product!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3664) Trailing white space in varchar
field is trimmed off.
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3664?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12648220#action_12648220 ]
Kathey Marsden commented on DERBY-3664:
---------------------------------------
Should this be closed as invalid, since Derby conforms to the standard?
> Trailing white space in varchar field is trimmed off.
> -----------------------------------------------------
>
> Key: DERBY-3664
> URL: https://issues.apache.org/jira/browse/DERBY-3664
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.3.1.4, 10.3.2.1, 10.4.1.3
> Environment: Win XP, Derby 10.4.1.3
> Reporter: Shawn Aucoin
>
> When executing an insert statement with a varchar field containing trailing whitespace, the trailing whitespace is removed. This results in unique constraint issues. If index is not unique, select * from login where login = 'first_test' will return both records. Preceeding whitespace doesn't appear to be affected. I tested this from IJ and from java api. I included an example below
> CREATE TABLE LOGIN
> (
> LOGIN_ID INT PRIMARY KEY,
> LOGIN VARCHAR(60)
> );
> CREATE UNIQUE INDEX LOGIN_LOGIN_IDX
> ON LOGIN (LOGIN DESC);
> insert into login (LOGIN_ID, LOGIN) values (1, 'first_test');
> insert into login (LOGIN_ID, LOGIN) values (2, 'first_test ');
> Now, i fully admit that there is no logical reason for trailing whitespace, but it is client data that i am dealing with and I may not be able to get it changed. Thanks, derby is a sweet product!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3664) Trailing white space in varchar
field is trimmed off.
Posted by "Shawn Aucoin (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3664?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12649085#action_12649085 ]
Shawn Aucoin commented on DERBY-3664:
-------------------------------------
I think it was decided that it would be a configurable option. I agree that it conforms to the spec.
Shawn Aucoin
shawn_aucoin@hotmail.com
_________________________________________________________________
Access your email online and on the go with Windows Live Hotmail.
http://windowslive.com/Explore/Hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_access_112008
> Trailing white space in varchar field is trimmed off.
> -----------------------------------------------------
>
> Key: DERBY-3664
> URL: https://issues.apache.org/jira/browse/DERBY-3664
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.3.1.4, 10.3.2.1, 10.4.1.3
> Environment: Win XP, Derby 10.4.1.3
> Reporter: Shawn Aucoin
>
> When executing an insert statement with a varchar field containing trailing whitespace, the trailing whitespace is removed. This results in unique constraint issues. If index is not unique, select * from login where login = 'first_test' will return both records. Preceeding whitespace doesn't appear to be affected. I tested this from IJ and from java api. I included an example below
> CREATE TABLE LOGIN
> (
> LOGIN_ID INT PRIMARY KEY,
> LOGIN VARCHAR(60)
> );
> CREATE UNIQUE INDEX LOGIN_LOGIN_IDX
> ON LOGIN (LOGIN DESC);
> insert into login (LOGIN_ID, LOGIN) values (1, 'first_test');
> insert into login (LOGIN_ID, LOGIN) values (2, 'first_test ');
> Now, i fully admit that there is no logical reason for trailing whitespace, but it is client data that i am dealing with and I may not be able to get it changed. Thanks, derby is a sweet product!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3664) Trailing white space in varchar
field is trimmed off.
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3664?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12648603#action_12648603 ]
Dag H. Wanvik commented on DERBY-3664:
--------------------------------------
This one is labelled "New Feature" (to implement NO PAD characteristic), so I guess it is valid.
The issue title is misleading though, so maybe it would be good to close this and open a separate
issue for that improvement (or rename this to "Implement NO PAD SPACE characteristic for CHAR comparison").
> Trailing white space in varchar field is trimmed off.
> -----------------------------------------------------
>
> Key: DERBY-3664
> URL: https://issues.apache.org/jira/browse/DERBY-3664
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.3.1.4, 10.3.2.1, 10.4.1.3
> Environment: Win XP, Derby 10.4.1.3
> Reporter: Shawn Aucoin
>
> When executing an insert statement with a varchar field containing trailing whitespace, the trailing whitespace is removed. This results in unique constraint issues. If index is not unique, select * from login where login = 'first_test' will return both records. Preceeding whitespace doesn't appear to be affected. I tested this from IJ and from java api. I included an example below
> CREATE TABLE LOGIN
> (
> LOGIN_ID INT PRIMARY KEY,
> LOGIN VARCHAR(60)
> );
> CREATE UNIQUE INDEX LOGIN_LOGIN_IDX
> ON LOGIN (LOGIN DESC);
> insert into login (LOGIN_ID, LOGIN) values (1, 'first_test');
> insert into login (LOGIN_ID, LOGIN) values (2, 'first_test ');
> Now, i fully admit that there is no logical reason for trailing whitespace, but it is client data that i am dealing with and I may not be able to get it changed. Thanks, derby is a sweet product!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3664) Trailing white space in varchar
field is trimmed off.
Posted by "Shawn Aucoin (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3664?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12594462#action_12594462 ]
Shawn Aucoin commented on DERBY-3664:
-------------------------------------
I changed LOGIN VARCHAR(60) to LOGIN CHAR(60) and it still behaves the same.
Again, think i can get the data changed so this isn't a show stopper, but i think the idea of making it configurable is awesome.
Thanks for all your help.
> Trailing white space in varchar field is trimmed off.
> -----------------------------------------------------
>
> Key: DERBY-3664
> URL: https://issues.apache.org/jira/browse/DERBY-3664
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.1.4, 10.3.2.1, 10.4.1.3
> Environment: Win XP, Derby 10.4.1.3
> Reporter: Shawn Aucoin
>
> When executing an insert statement with a varchar field containing trailing whitespace, the trailing whitespace is removed. This results in unique constraint issues. If index is not unique, select * from login where login = 'first_test' will return both records. Preceeding whitespace doesn't appear to be affected. I tested this from IJ and from java api. I included an example below
> CREATE TABLE LOGIN
> (
> LOGIN_ID INT PRIMARY KEY,
> LOGIN VARCHAR(60)
> );
> CREATE UNIQUE INDEX LOGIN_LOGIN_IDX
> ON LOGIN (LOGIN DESC);
> insert into login (LOGIN_ID, LOGIN) values (1, 'first_test');
> insert into login (LOGIN_ID, LOGIN) values (2, 'first_test ');
> Now, i fully admit that there is no logical reason for trailing whitespace, but it is client data that i am dealing with and I may not be able to get it changed. Thanks, derby is a sweet product!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3664) Trailing white space in varchar field
is trimmed off.
Posted by "Shawn Aucoin (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3664?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Shawn Aucoin updated DERBY-3664:
--------------------------------
Description:
When executing an insert statement with a varchar field containing trailing whitespace, the trailing whitespace is removed. This results in unique constraint issues. If index is not unique, select * from login where login = 'first_test' will return both records. Preceeding whitespace doesn't appear to be affected. I tested this from IJ and from java api. I included an example below
CREATE TABLE LOGIN
(
LOGIN_ID INT PRIMARY KEY,
LOGIN VARCHAR(60)
);
CREATE UNIQUE INDEX LOGIN_LOGIN_IDX
ON LOGIN (LOGIN DESC);
insert into login (LOGIN_ID, LOGIN) values (1, 'first_test');
insert into login (LOGIN_ID, LOGIN) values (2, 'first_test ');
Now, i fully admit that there is no logical reason for trailing whitespace, but it is client data that i am dealing with and I may not be able to get it changed. Thanks, derby is a sweet product!
was:
When executing an insert statement with a varchar field containing trailing whitespace, the trailing whitespace is removed. This results in unique constraint issues. If index is not unique, select * from login where login = 'first_test' will return both records. Preceeding whitespace doesn't appear to be affected. I tested this from IJ and from java api. I included an example below
CREATE TABLE LOGIN
(
LOGIN_ID INT PRIMARY KEY,
LOGIN VARCHAR(60),
);
CREATE UNIQUE INDEX LOGIN_LOGIN_IDX
ON LOGIN (LOGIN DESC);
insert into login (LOGIN_ID, LOGIN) values (1, 'first_test');
insert into login (LOGIN_ID, LOGIN) values (2, 'first_test ');
Now, i fully admit that there is no logical reason for trailing whitespace, but it is client data that i am dealing with and I may not be able to get it changed. Thanks, derby is a sweet product!
removed extra comma in table definition
> Trailing white space in varchar field is trimmed off.
> -----------------------------------------------------
>
> Key: DERBY-3664
> URL: https://issues.apache.org/jira/browse/DERBY-3664
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.1.4, 10.3.2.1, 10.4.1.3
> Environment: Win XP, Derby 10.4.1.3
> Reporter: Shawn Aucoin
>
> When executing an insert statement with a varchar field containing trailing whitespace, the trailing whitespace is removed. This results in unique constraint issues. If index is not unique, select * from login where login = 'first_test' will return both records. Preceeding whitespace doesn't appear to be affected. I tested this from IJ and from java api. I included an example below
> CREATE TABLE LOGIN
> (
> LOGIN_ID INT PRIMARY KEY,
> LOGIN VARCHAR(60)
> );
> CREATE UNIQUE INDEX LOGIN_LOGIN_IDX
> ON LOGIN (LOGIN DESC);
> insert into login (LOGIN_ID, LOGIN) values (1, 'first_test');
> insert into login (LOGIN_ID, LOGIN) values (2, 'first_test ');
> Now, i fully admit that there is no logical reason for trailing whitespace, but it is client data that i am dealing with and I may not be able to get it changed. Thanks, derby is a sweet product!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3664) Trailing white space in varchar
field is trimmed off.
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3664?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12594913#action_12594913 ]
Dag H. Wanvik commented on DERBY-3664:
--------------------------------------
Shawn> I changed LOGIN VARCHAR(60) to LOGIN CHAR(60) and it still behaves the same.
The docs for CHAR datatype says:
"When comparison boolean operators are applied to CHARs, the shorter
string is padded with spaces to the length of the longer string."
so I guess thats to be expected as well.
The SQL standard allows this behavior to be controlled for collations
with the <pad characteristic> when a collation is defined. It is
implementation defined what the pad characteristic is for the default
collation, see section 10.5, general rule 3) in ISO/IEC 9075-2:2003.
So Derby seems to have the PAD SPACE characteristic.
It would be good to add the possibility of NO PAD also! Any takers? :)
> Trailing white space in varchar field is trimmed off.
> -----------------------------------------------------
>
> Key: DERBY-3664
> URL: https://issues.apache.org/jira/browse/DERBY-3664
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.1.4, 10.3.2.1, 10.4.1.3
> Environment: Win XP, Derby 10.4.1.3
> Reporter: Shawn Aucoin
>
> When executing an insert statement with a varchar field containing trailing whitespace, the trailing whitespace is removed. This results in unique constraint issues. If index is not unique, select * from login where login = 'first_test' will return both records. Preceeding whitespace doesn't appear to be affected. I tested this from IJ and from java api. I included an example below
> CREATE TABLE LOGIN
> (
> LOGIN_ID INT PRIMARY KEY,
> LOGIN VARCHAR(60)
> );
> CREATE UNIQUE INDEX LOGIN_LOGIN_IDX
> ON LOGIN (LOGIN DESC);
> insert into login (LOGIN_ID, LOGIN) values (1, 'first_test');
> insert into login (LOGIN_ID, LOGIN) values (2, 'first_test ');
> Now, i fully admit that there is no logical reason for trailing whitespace, but it is client data that i am dealing with and I may not be able to get it changed. Thanks, derby is a sweet product!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3664) Trailing white space in varchar
field is trimmed off.
Posted by "Shawn Aucoin (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3664?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12594458#action_12594458 ]
Shawn Aucoin commented on DERBY-3664:
-------------------------------------
Hmm...interesting. I have a deep oracle development back ground and I know that Oracle doesn't treat it this way. It is interesting and to me would seem to be a bug, because 'first_test ' and first_test' are not equal.
However, i am not going to moan about it as I think i can get the data changed, but i would suggest that maybe this could become a future enhancement as a space is really a valid character (even trailing ones) and should be included in comparisons, although I fully acknowledge that the data should not have trailing spaces, and I am hard pressed to think of a valid scenario where it would be appropriate to have trailing spaces in a field.
Being a developer, I don't particularly like it when something does something that i didn't ask it too...;)
Again, thanks for the great product!
> Trailing white space in varchar field is trimmed off.
> -----------------------------------------------------
>
> Key: DERBY-3664
> URL: https://issues.apache.org/jira/browse/DERBY-3664
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.1.4, 10.3.2.1, 10.4.1.3
> Environment: Win XP, Derby 10.4.1.3
> Reporter: Shawn Aucoin
>
> When executing an insert statement with a varchar field containing trailing whitespace, the trailing whitespace is removed. This results in unique constraint issues. If index is not unique, select * from login where login = 'first_test' will return both records. Preceeding whitespace doesn't appear to be affected. I tested this from IJ and from java api. I included an example below
> CREATE TABLE LOGIN
> (
> LOGIN_ID INT PRIMARY KEY,
> LOGIN VARCHAR(60)
> );
> CREATE UNIQUE INDEX LOGIN_LOGIN_IDX
> ON LOGIN (LOGIN DESC);
> insert into login (LOGIN_ID, LOGIN) values (1, 'first_test');
> insert into login (LOGIN_ID, LOGIN) values (2, 'first_test ');
> Now, i fully admit that there is no logical reason for trailing whitespace, but it is client data that i am dealing with and I may not be able to get it changed. Thanks, derby is a sweet product!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3664) Trailing white space in varchar field
is trimmed off.
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3664?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-3664:
---------------------------------
Issue Type: New Feature (was: Bug)
Derby's behavior appears to conform to the SQL standard. I am changing this issue's type from Bug to Feature.
> Trailing white space in varchar field is trimmed off.
> -----------------------------------------------------
>
> Key: DERBY-3664
> URL: https://issues.apache.org/jira/browse/DERBY-3664
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Affects Versions: 10.3.1.4, 10.3.2.1, 10.4.1.3
> Environment: Win XP, Derby 10.4.1.3
> Reporter: Shawn Aucoin
>
> When executing an insert statement with a varchar field containing trailing whitespace, the trailing whitespace is removed. This results in unique constraint issues. If index is not unique, select * from login where login = 'first_test' will return both records. Preceeding whitespace doesn't appear to be affected. I tested this from IJ and from java api. I included an example below
> CREATE TABLE LOGIN
> (
> LOGIN_ID INT PRIMARY KEY,
> LOGIN VARCHAR(60)
> );
> CREATE UNIQUE INDEX LOGIN_LOGIN_IDX
> ON LOGIN (LOGIN DESC);
> insert into login (LOGIN_ID, LOGIN) values (1, 'first_test');
> insert into login (LOGIN_ID, LOGIN) values (2, 'first_test ');
> Now, i fully admit that there is no logical reason for trailing whitespace, but it is client data that i am dealing with and I may not be able to get it changed. Thanks, derby is a sweet product!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3664) Trailing white space in varchar
field is trimmed off.
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3664?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12594459#action_12594459 ]
Bryan Pendleton commented on DERBY-3664:
----------------------------------------
I think it's a good suggestion to allow the behavior to be configurable,
thanks for logging the suggestion!
I think that if you make the datatype CHAR rather than VARCHAR, then
the trailing blanks are treated as significant.
I'm glad you're enjoying Derby! I hope you continue to use it.
> Trailing white space in varchar field is trimmed off.
> -----------------------------------------------------
>
> Key: DERBY-3664
> URL: https://issues.apache.org/jira/browse/DERBY-3664
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.1.4, 10.3.2.1, 10.4.1.3
> Environment: Win XP, Derby 10.4.1.3
> Reporter: Shawn Aucoin
>
> When executing an insert statement with a varchar field containing trailing whitespace, the trailing whitespace is removed. This results in unique constraint issues. If index is not unique, select * from login where login = 'first_test' will return both records. Preceeding whitespace doesn't appear to be affected. I tested this from IJ and from java api. I included an example below
> CREATE TABLE LOGIN
> (
> LOGIN_ID INT PRIMARY KEY,
> LOGIN VARCHAR(60)
> );
> CREATE UNIQUE INDEX LOGIN_LOGIN_IDX
> ON LOGIN (LOGIN DESC);
> insert into login (LOGIN_ID, LOGIN) values (1, 'first_test');
> insert into login (LOGIN_ID, LOGIN) values (2, 'first_test ');
> Now, i fully admit that there is no logical reason for trailing whitespace, but it is client data that i am dealing with and I may not be able to get it changed. Thanks, derby is a sweet product!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3664) Trailing white space in varchar
field is trimmed off.
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3664?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12594456#action_12594456 ]
Bryan Pendleton commented on DERBY-3664:
----------------------------------------
I believe that this behavior of VARCHAR datatypes is documented:
http://db.apache.org/derby/docs/10.4/ref/rrefsqlj41207.html
When comparison boolean operators are applied to VARCHARs,
the lengths of the operands are not altered, and spaces at the
end of the values are ignored.
> Trailing white space in varchar field is trimmed off.
> -----------------------------------------------------
>
> Key: DERBY-3664
> URL: https://issues.apache.org/jira/browse/DERBY-3664
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.1.4, 10.3.2.1, 10.4.1.3
> Environment: Win XP, Derby 10.4.1.3
> Reporter: Shawn Aucoin
>
> When executing an insert statement with a varchar field containing trailing whitespace, the trailing whitespace is removed. This results in unique constraint issues. If index is not unique, select * from login where login = 'first_test' will return both records. Preceeding whitespace doesn't appear to be affected. I tested this from IJ and from java api. I included an example below
> CREATE TABLE LOGIN
> (
> LOGIN_ID INT PRIMARY KEY,
> LOGIN VARCHAR(60)
> );
> CREATE UNIQUE INDEX LOGIN_LOGIN_IDX
> ON LOGIN (LOGIN DESC);
> insert into login (LOGIN_ID, LOGIN) values (1, 'first_test');
> insert into login (LOGIN_ID, LOGIN) values (2, 'first_test ');
> Now, i fully admit that there is no logical reason for trailing whitespace, but it is client data that i am dealing with and I may not be able to get it changed. Thanks, derby is a sweet product!
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.