[jira] [Commented] (DERBY-2212) Add "Unique where not null" to create index
[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16237348#comment-16237348 ] Oleksandr Alesinskyy commented on DERBY-2212: - Glad to see that a 10 years old ticket again brings some attention. > Add "Unique where not null" to create index > --- > > Key: DERBY-2212 > URL: https://issues.apache.org/jira/browse/DERBY-2212 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.2.1.6 >Reporter: Oleksandr Alesinskyy >Priority: Major > Labels: derby_triage10_10 > Attachments: FunctionalSpec.html, FunctionalSpecV3.html, > FunctionalSpecV3_comment.html, FunctionlaSpecv2.html, derby-2212preview.diff, > derby-2212preview2.diff > > > Derby prohibits creation of unique constraints on nullable colums (as well if > only some columns in the constraint list are nullable) and treat nulls in > unique indexes as normal values (i.e. only one row with null values in > indexed columns may be inserted into the table). This bahavior is very > restrictive, does not completely comply with SQL standards (both letter and > intent) as well as with business needs and intending meaning of NULL values > (2 null values are not considered as equal, this comparision shall return > NULL, and for selection criteria boolean null is treated as FALSE). > This behavior, as far as I can see, is modelled after DB2 (and differs from > behavior of most other major databases, like SyBase, Oracle, etc.). > But even DB2 provide some means to alleviate these restrictions, namely > "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. > It will be very good if such "UNIQUE WHERE NOT NULL" clause will be > introduced in Derby. > Regards, > Oleksandr Alesinskyy -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (DERBY-896) Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE' to 'TIMESTAMP'."
[
https://issues.apache.org/jira/browse/DERBY-896?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13899565#comment-13899565
]
Oleksandr Alesinskyy commented on DERBY-896:
H-m-m. this issue (that should require from a couple of hours to a couple of
day of work, former is much more likely) now entered its 9th year...
> Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE'
> to 'TIMESTAMP'."
>
>
> Key: DERBY-896
> URL: https://issues.apache.org/jira/browse/DERBY-896
> Project: Derby
> Issue Type: Bug
> Components: SQL
>Affects Versions: 10.1.2.1
> Environment: - Derby Information
> JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
> [D:\Programme\Derby\bin\lib\derby.jar] 10.1.2.1 - (330608)
> [D:\Programme\Derby\bin\lib\derbytools.jar] 10.1.2.1 - (330608)
>Reporter: Oleksandr Alesinskyy
> Labels: derby_triage10_5_2
> Attachments: w.sql
>
>
> Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE'
> to 'TIMESTAMP'.",
> e.g. in ij:
> select cast(cast ('2006-01-01' as date) as timestamp) from sys.sysschemas;
> or
> select cast (current_date as timestamp) from bradi_dat.lpaip01;
> The similar issue exists for time ("ERROR 42846: Cannot convert types 'TIME'
> to 'TIMESTAMP'.",
> Documentation clearly states that these conversions are allowed, see table 1
> in a description of CAST function in the Derby Reference Manual and comment
> about "Conversion of date/time values" below that table (see below as well).
> Conversions of date/time values
> A date/time value can always be converted to and from a TIMESTAMP. If a DATE
> is
> converted to a TIMESTAMP, the TIME component of the resulting TIMESTAMP is
> always
> 00:00:00. If a TIME data value is converted to a TIMESTAMP, the DATE
> component is
> set to the value of CURRENT_DATE at the time the CAST is executed. If a
> TIMESTAMP
> is converted to a DATE, the TIME component is silently truncated. If a
> TIMESTAMP is
> converted to a TIME, the DATE component is silently truncated.
>
--
This message was sent by Atlassian JIRA
(v6.1.5#6160)
[jira] Commented: (DERBY-3609) Wrong functionality of auto-generated keys support
[ https://issues.apache.org/jira/browse/DERBY-3609?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12603985#action_12603985 ] Oleksandr Alesinskyy commented on DERBY-3609: - Exactly,. and its requires support for multirow inserts, namely "In some cases, such as in an insert select statement, more than one key may be returned. The ResultSet object returned by getGeneratedKeys will contain a row for each key that a statement generated. If no keys are generated, an empty result set will be returned." > Wrong functionality of auto-generated keys support > -- > > Key: DERBY-3609 > URL: https://issues.apache.org/jira/browse/DERBY-3609 > Project: Derby > Issue Type: Bug > Components: JDBC >Affects Versions: 10.3.2.1 > Environment: N/A >Reporter: Oleksandr Alesinskyy > Attachments: TestGeneratedKeys.java > > > Currently support for autogeneration key retrieval in JDBC driver > (java.sql.Statement) relies on IDENTITY_VAL_LOCAL function, which means that > for multi-row INSERT... VALUES statements as well as for INSERT...AS SELECT > statement wrong result are returned (key generated of last executed > single-row INSERT...VALUES statement or NULL otherwise). While this > functionality is documented in the Reference description of > IDENTITY_VAL_LOCAL it is not so clearly documented in "autogenerated keys" > subchapter of JDBC 3.0 Features (the only vague hint is "Calling > ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys > produces a ResultSetMetaData object that is similar to that returned by > IDENTITY_VAL_LOCAL."). > Moreover, as far as I understant it diverge from JDBC specification. IMHO or > this functionality shal lbe implemented completely, or exception shall be > thrown if for given statement functionality is not supported (e.g. "Feature > not supported"). -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3609) Wrong functionality of auto-generated keys support
[ https://issues.apache.org/jira/browse/DERBY-3609?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Oleksandr Alesinskyy updated DERBY-3609: Attachment: TestGeneratedKeys.java Source of above mentioned test programm > Wrong functionality of auto-generated keys support > -- > > Key: DERBY-3609 > URL: https://issues.apache.org/jira/browse/DERBY-3609 > Project: Derby > Issue Type: Bug > Components: JDBC >Affects Versions: 10.3.2.1 > Environment: N/A >Reporter: Oleksandr Alesinskyy > Attachments: TestGeneratedKeys.java > > > Currently support for autogeneration key retrieval in JDBC driver > (java.sql.Statement) relies on IDENTITY_VAL_LOCAL function, which means that > for multi-row INSERT... VALUES statements as well as for INSERT...AS SELECT > statement wrong result are returned (key generated of last executed > single-row INSERT...VALUES statement or NULL otherwise). While this > functionality is documented in the Reference description of > IDENTITY_VAL_LOCAL it is not so clearly documented in "autogenerated keys" > subchapter of JDBC 3.0 Features (the only vague hint is "Calling > ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys > produces a ResultSetMetaData object that is similar to that returned by > IDENTITY_VAL_LOCAL."). > Moreover, as far as I understant it diverge from JDBC specification. IMHO or > this functionality shal lbe implemented completely, or exception shall be > thrown if for given statement functionality is not supported (e.g. "Feature > not supported"). -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3609) Wrong functionality of auto-generated keys support
[ https://issues.apache.org/jira/browse/DERBY-3609?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12587233#action_12587233 ] Oleksandr Alesinskyy commented on DERBY-3609: - Regrettably, after INSERT...SELECT returned resultset contains not null as you stated, but key generated by last single row INSERT..VALUES on the same table. This behavior is completely misleading. It is bad enough for IDENTITY_VAL_LOCAL(), but (IMHO) absolutely unacceptable for getGeneratedKeys(). And here is log from test programm that proves my point: --- insert values has generated id 1, is null false insert as select same table has inserted 1 rows insert as select from same table has generated id 1, is null false insert as select from another table has inserted 1 rows insert as select from another has generated id 1, is null false MUMU contents id 1, is null false MUMU contents id 2, is null false MUMU contents id 3, is null false Done. -- Progam source is attached > Wrong functionality of auto-generated keys support > -- > > Key: DERBY-3609 > URL: https://issues.apache.org/jira/browse/DERBY-3609 > Project: Derby > Issue Type: Bug > Components: JDBC >Affects Versions: 10.3.2.1 > Environment: N/A >Reporter: Oleksandr Alesinskyy > > Currently support for autogeneration key retrieval in JDBC driver > (java.sql.Statement) relies on IDENTITY_VAL_LOCAL function, which means that > for multi-row INSERT... VALUES statements as well as for INSERT...AS SELECT > statement wrong result are returned (key generated of last executed > single-row INSERT...VALUES statement or NULL otherwise). While this > functionality is documented in the Reference description of > IDENTITY_VAL_LOCAL it is not so clearly documented in "autogenerated keys" > subchapter of JDBC 3.0 Features (the only vague hint is "Calling > ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys > produces a ResultSetMetaData object that is similar to that returned by > IDENTITY_VAL_LOCAL."). > Moreover, as far as I understant it diverge from JDBC specification. IMHO or > this functionality shal lbe implemented completely, or exception shall be > thrown if for given statement functionality is not supported (e.g. "Feature > not supported"). -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Created: (DERBY-3609) Wrong functionality of auto-generated keys support
Wrong functionality of auto-generated keys support -- Key: DERBY-3609 URL: https://issues.apache.org/jira/browse/DERBY-3609 Project: Derby Issue Type: Bug Components: JDBC Affects Versions: 10.3.2.1 Environment: N/A Reporter: Oleksandr Alesinskyy Currently support for autogeneration key retrieval in JDBC driver (java.sql.Statement) relies on IDENTITY_VAL_LOCAL function, which means that for multi-row INSERT... VALUES statements as well as for INSERT...AS SELECT statement wrong result are returned (key generated of last executed single-row INSERT...VALUES statement or NULL otherwise). While this functionality is documented in the Reference description of IDENTITY_VAL_LOCAL it is not so clearly documented in "autogenerated keys" subchapter of JDBC 3.0 Features (the only vague hint is "Calling ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData object that is similar to that returned by IDENTITY_VAL_LOCAL."). Moreover, as far as I understant it diverge from JDBC specification. IMHO or this functionality shal lbe implemented completely, or exception shall be thrown if for given statement functionality is not supported (e.g. "Feature not supported"). -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Created: (DERBY-3605) Remove restriction "If there is an index defined on the table, the table can be renamed."
Remove restriction "If there is an index defined on the table, the table can be renamed." - Key: DERBY-3605 URL: https://issues.apache.org/jira/browse/DERBY-3605 Project: Derby Issue Type: Improvement Affects Versions: 10.3.2.1 Environment: N/A Reporter: Oleksandr Alesinskyy Remove following restriction for "RENAME TABLE" statement: "If there is an index defined on the table, the table can be renamed." I really do not see any valid reasons (except, probably not very wise implementation) for existence this restriction. And it may come very inconvinient if table to be renamed has an index(ses) referenced by foreign keys from other tables. Regards, Oleksandr -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12544013 ] Oleksandr Alesinskyy commented on DERBY-2212: - Initial request (filed by me) was to allow "unique when not null" indexes (a'la DB2). Latest expressed intention was to allow unique constraints that permit nulls duplication (i.e. if at least one of constrained columns contains .null value, record satisfies constraint regardless which other records are already present in the table). Regards, Oleksandr > Add "Unique where not null" to create index > --- > > Key: DERBY-2212 > URL: https://issues.apache.org/jira/browse/DERBY-2212 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.2.1.6 >Reporter: Oleksandr Alesinskyy >Assignee: Anurag Shekhar > Attachments: derby-2212preview.diff, derby-2212preview2.diff, > FunctionalSpec.html, FunctionalSpecV3.html, FunctionalSpecV3_comment.html, > FunctionlaSpecv2.html > > > Derby prohibits creation of unique constraints on nullable colums (as well if > only some columns in the constraint list are nullable) and treat nulls in > unique indexes as normal values (i.e. only one row with null values in > indexed columns may be inserted into the table). This bahavior is very > restrictive, does not completely comply with SQL standards (both letter and > intent) as well as with business needs and intending meaning of NULL values > (2 null values are not considered as equal, this comparision shall return > NULL, and for selection criteria boolean null is treated as FALSE). > This behavior, as far as I can see, is modelled after DB2 (and differs from > behavior of most other major databases, like SyBase, Oracle, etc.). > But even DB2 provide some means to alleviate these restrictions, namely > "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. > It will be very good if such "UNIQUE WHERE NOT NULL" clause will be > introduced in Derby. > Regards, > Oleksandr Alesinskyy -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12543859 ] Oleksandr Alesinskyy commented on DERBY-2212: - >functional spec shouldn't have any reference index specially because it talks >about the feature related to unique constraint. >How it is implemented shouldn't be mentioned in this document. (Please check >Mike's feed back on the first version) I have seen, Mike's feedback, but I do not agree with it. Yes, implementation details shall be clearly separated from specification itself, but, if they are important and affect interaction with other parts of the system (as in this case) they may and shall be mentioned while clearly marked. I.e. specification structure may be as follows: Functional specification 1. Specification itself Bla-bla-bla 2. Implementation notes Bla-bla-bla > Add "Unique where not null" to create index > --- > > Key: DERBY-2212 > URL: https://issues.apache.org/jira/browse/DERBY-2212 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.2.1.6 >Reporter: Oleksandr Alesinskyy >Assignee: Anurag Shekhar > Attachments: derby-2212preview.diff, derby-2212preview2.diff, > FunctionalSpec.html, FunctionalSpecV3.html, FunctionlaSpecv2.html > > > Derby prohibits creation of unique constraints on nullable colums (as well if > only some columns in the constraint list are nullable) and treat nulls in > unique indexes as normal values (i.e. only one row with null values in > indexed columns may be inserted into the table). This bahavior is very > restrictive, does not completely comply with SQL standards (both letter and > intent) as well as with business needs and intending meaning of NULL values > (2 null values are not considered as equal, this comparision shall return > NULL, and for selection criteria boolean null is treated as FALSE). > This behavior, as far as I can see, is modelled after DB2 (and differs from > behavior of most other major databases, like SyBase, Oracle, etc.). > But even DB2 provide some means to alleviate these restrictions, namely > "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. > It will be very good if such "UNIQUE WHERE NOT NULL" clause will be > introduced in Derby. > Regards, > Oleksandr Alesinskyy -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12540728 ] Oleksandr Alesinskyy commented on DERBY-2212: - Anurag, There are still hordes of typos (both in the functional specification and your last comment). I'm really afraid that patch source would be done with the same level of accuracy :( In my opinion ithe functional specification must point that for time beeing implementation depends on appropriate extension of the behavior of the unique index and refer to task of exposing of this new behavior to the user. One more point - this change for sure will affect optimizer behavior and will require modification in it, but specification completely miss this point. Regards, Oleksandr * "Anurag Shekhar (JIRA)" <[EMAIL PROTECTED]> [Wed, 7 Nov 2007 01:07:50 https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12540705 version I https://issues.apache.org/jira/browse/DERBY-2212 derby-2212preview2.diff, (as treat bahavior (both meaning differs etc.). -- Oleksandr Alesinskyy. > Add "Unique where not null" to create index > --- > > Key: DERBY-2212 > URL: https://issues.apache.org/jira/browse/DERBY-2212 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.2.1.6 >Reporter: Oleksandr Alesinskyy >Assignee: Anurag Shekhar > Attachments: derby-2212preview.diff, derby-2212preview2.diff, > FunctionalSpec.html, FunctionalSpecV3.html, FunctionlaSpecv2.html > > > Derby prohibits creation of unique constraints on nullable colums (as well if > only some columns in the constraint list are nullable) and treat nulls in > unique indexes as normal values (i.e. only one row with null values in > indexed columns may be inserted into the table). This bahavior is very > restrictive, does not completely comply with SQL standards (both letter and > intent) as well as with business needs and intending meaning of NULL values > (2 null values are not considered as equal, this comparision shall return > NULL, and for selection criteria boolean null is treated as FALSE). > This behavior, as far as I can see, is modelled after DB2 (and differs from > behavior of most other major databases, like SyBase, Oracle, etc.). > But even DB2 provide some means to alleviate these restrictions, namely > "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. > It will be very good if such "UNIQUE WHERE NOT NULL" clause will be > introduced in Derby. > Regards, > Oleksandr Alesinskyy -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12539048 ] Oleksandr Alesinskyy commented on DERBY-2212: - New version of specification looks much better, but still has some problems. 1. There are too many typos and they sometimes affect the meaning, e.g. "null,null,1 null.null,1 no exception" - what is "null.null" (dot instead of comma) "null,1,null null,null,1 no exception" - likely should be ""null,1,null null,1,null no exception" 2. After "no exception" shall be " and table contains both inserted rows" (otherwise it is legal to assume that second row may be silently swallowed) 3. Issue is named 'Add "Unique where not null" to create index', but discussion of unique indexes is completely absent from functional specification. Regards, Oleksandr > Add "Unique where not null" to create index > --- > > Key: DERBY-2212 > URL: https://issues.apache.org/jira/browse/DERBY-2212 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.2.1.6 >Reporter: Oleksandr Alesinskyy >Assignee: Anurag Shekhar > Attachments: derby-2212preview.diff, derby-2212preview2.diff, > FunctionalSpec.html, FunctionlaSpecv2.html > > > Derby prohibits creation of unique constraints on nullable colums (as well if > only some columns in the constraint list are nullable) and treat nulls in > unique indexes as normal values (i.e. only one row with null values in > indexed columns may be inserted into the table). This bahavior is very > restrictive, does not completely comply with SQL standards (both letter and > intent) as well as with business needs and intending meaning of NULL values > (2 null values are not considered as equal, this comparision shall return > NULL, and for selection criteria boolean null is treated as FALSE). > This behavior, as far as I can see, is modelled after DB2 (and differs from > behavior of most other major databases, like SyBase, Oracle, etc.). > But even DB2 provide some means to alleviate these restrictions, namely > "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. > It will be very good if such "UNIQUE WHERE NOT NULL" clause will be > introduced in Derby. > Regards, > Oleksandr Alesinskyy -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12538063 ] Oleksandr Alesinskyy commented on DERBY-2212: - This change not "would" but "may" introduce incompatibilities, as it it quite possible to avoid incompatibilities all together. > Add "Unique where not null" to create index > --- > > Key: DERBY-2212 > URL: https://issues.apache.org/jira/browse/DERBY-2212 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.2.1.6 >Reporter: Oleksandr Alesinskyy >Assignee: Anurag Shekhar > Attachments: derby-2212preview.diff, derby-2212preview2.diff, > FunctionalSpec.html > > > Derby prohibits creation of unique constraints on nullable colums (as well if > only some columns in the constraint list are nullable) and treat nulls in > unique indexes as normal values (i.e. only one row with null values in > indexed columns may be inserted into the table). This bahavior is very > restrictive, does not completely comply with SQL standards (both letter and > intent) as well as with business needs and intending meaning of NULL values > (2 null values are not considered as equal, this comparision shall return > NULL, and for selection criteria boolean null is treated as FALSE). > This behavior, as far as I can see, is modelled after DB2 (and differs from > behavior of most other major databases, like SyBase, Oracle, etc.). > But even DB2 provide some means to alleviate these restrictions, namely > "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. > It will be very good if such "UNIQUE WHERE NOT NULL" clause will be > introduced in Derby. > Regards, > Oleksandr Alesinskyy -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12537001 ] Oleksandr Alesinskyy commented on DERBY-2212: - To avoid user confusions current syntax should retain existing behavior and new and consistent syntax extension be provided for both index and constraints. > Add "Unique where not null" to create index > --- > > Key: DERBY-2212 > URL: https://issues.apache.org/jira/browse/DERBY-2212 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.2.1.6 >Reporter: Oleksandr Alesinskyy >Assignee: Anurag Shekhar > Attachments: derby-2212preview.diff, derby-2212preview2.diff > > > Derby prohibits creation of unique constraints on nullable colums (as well if > only some columns in the constraint list are nullable) and treat nulls in > unique indexes as normal values (i.e. only one row with null values in > indexed columns may be inserted into the table). This bahavior is very > restrictive, does not completely comply with SQL standards (both letter and > intent) as well as with business needs and intending meaning of NULL values > (2 null values are not considered as equal, this comparision shall return > NULL, and for selection criteria boolean null is treated as FALSE). > This behavior, as far as I can see, is modelled after DB2 (and differs from > behavior of most other major databases, like SyBase, Oracle, etc.). > But even DB2 provide some means to alleviate these restrictions, namely > "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. > It will be very good if such "UNIQUE WHERE NOT NULL" clause will be > introduced in Derby. > Regards, > Oleksandr Alesinskyy -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536931 ] Oleksandr Alesinskyy commented on DERBY-2212: - Yes, unique constraint is not the same thing as unique index but it requires unique index to be enforced (yes, it is implementation detail, but it exists for now and for foreseeable future as fas as I can tell). So we result in the 2 different behavior of the unique index depending on mode of creation - direct or indirect. As I have already pointed in this discussion it would cause a headaches for users (and major one if it would not be clearly distiguishable in dictionary views). Regards, Oleksandr > Add "Unique where not null" to create index > --- > > Key: DERBY-2212 > URL: https://issues.apache.org/jira/browse/DERBY-2212 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.2.1.6 >Reporter: Oleksandr Alesinskyy >Assignee: Anurag Shekhar > Attachments: derby-2212preview.diff, derby-2212preview2.diff > > > Derby prohibits creation of unique constraints on nullable colums (as well if > only some columns in the constraint list are nullable) and treat nulls in > unique indexes as normal values (i.e. only one row with null values in > indexed columns may be inserted into the table). This bahavior is very > restrictive, does not completely comply with SQL standards (both letter and > intent) as well as with business needs and intending meaning of NULL values > (2 null values are not considered as equal, this comparision shall return > NULL, and for selection criteria boolean null is treated as FALSE). > This behavior, as far as I can see, is modelled after DB2 (and differs from > behavior of most other major databases, like SyBase, Oracle, etc.). > But even DB2 provide some means to alleviate these restrictions, namely > "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. > It will be very good if such "UNIQUE WHERE NOT NULL" clause will be > introduced in Derby. > Regards, > Oleksandr Alesinskyy -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536515 ] Oleksandr Alesinskyy commented on DERBY-2212: - I may agree as far as we are speaking about single-column indexes, but for multicolumn-indexes when not all columns need to be null to allow duplicates things may get worse. > Add "Unique where not null" to create index > --- > > Key: DERBY-2212 > URL: https://issues.apache.org/jira/browse/DERBY-2212 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.2.1.6 >Reporter: Oleksandr Alesinskyy >Assignee: Anurag Shekhar > Attachments: derby-2212preview.diff, derby-2212preview2.diff > > > Derby prohibits creation of unique constraints on nullable colums (as well if > only some columns in the constraint list are nullable) and treat nulls in > unique indexes as normal values (i.e. only one row with null values in > indexed columns may be inserted into the table). This bahavior is very > restrictive, does not completely comply with SQL standards (both letter and > intent) as well as with business needs and intending meaning of NULL values > (2 null values are not considered as equal, this comparision shall return > NULL, and for selection criteria boolean null is treated as FALSE). > This behavior, as far as I can see, is modelled after DB2 (and differs from > behavior of most other major databases, like SyBase, Oracle, etc.). > But even DB2 provide some means to alleviate these restrictions, namely > "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. > It will be very good if such "UNIQUE WHERE NOT NULL" clause will be > introduced in Derby. > Regards, > Oleksandr Alesinskyy -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536278 ] Oleksandr Alesinskyy commented on DERBY-2212: - Keeping backward compatibility in this case shall be of much higher priority then keeping with SQL standard (BTW, each major database deviates from SQL standard at this point). So, what would we achieve if CREATE INDEX would not provide additional syntax? There are 2 possibilities 1. CREATE UNIQUE INDEX retain existing behavior. Then indexes created implicitly by UNIQUE constraint would differ in behavior from indexes created by CREATE UNIQUE INDEX. Customer headache is ensured. 2. CREATE UNIQUE INDEX creates indexes with new behavior. Then many existing application will be broken. Instead of headache customers encounter pain in the ass. Not much better. > Add "Unique where not null" to create index > --- > > Key: DERBY-2212 > URL: https://issues.apache.org/jira/browse/DERBY-2212 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.2.1.6 >Reporter: Oleksandr Alesinskyy >Assignee: Anurag Shekhar > Attachments: derby-2212preview.diff, derby-2212preview2.diff > > > Derby prohibits creation of unique constraints on nullable colums (as well if > only some columns in the constraint list are nullable) and treat nulls in > unique indexes as normal values (i.e. only one row with null values in > indexed columns may be inserted into the table). This bahavior is very > restrictive, does not completely comply with SQL standards (both letter and > intent) as well as with business needs and intending meaning of NULL values > (2 null values are not considered as equal, this comparision shall return > NULL, and for selection criteria boolean null is treated as FALSE). > This behavior, as far as I can see, is modelled after DB2 (and differs from > behavior of most other major databases, like SyBase, Oracle, etc.). > But even DB2 provide some means to alleviate these restrictions, namely > "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. > It will be very good if such "UNIQUE WHERE NOT NULL" clause will be > introduced in Derby. > Regards, > Oleksandr Alesinskyy -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536195 ] Oleksandr Alesinskyy commented on DERBY-2212: - I guess to change behaviour of existing unique indexes/constraints is a way to dangerous, many application will suffer. So syntax that allows to specify how to treat nulls is a must. Regards, Oleksandr > Add "Unique where not null" to create index > --- > > Key: DERBY-2212 > URL: https://issues.apache.org/jira/browse/DERBY-2212 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.2.1.6 >Reporter: Oleksandr Alesinskyy >Assignee: Anurag Shekhar > Attachments: derby-2212preview.diff, derby-2212preview2.diff > > > Derby prohibits creation of unique constraints on nullable colums (as well if > only some columns in the constraint list are nullable) and treat nulls in > unique indexes as normal values (i.e. only one row with null values in > indexed columns may be inserted into the table). This bahavior is very > restrictive, does not completely comply with SQL standards (both letter and > intent) as well as with business needs and intending meaning of NULL values > (2 null values are not considered as equal, this comparision shall return > NULL, and for selection criteria boolean null is treated as FALSE). > This behavior, as far as I can see, is modelled after DB2 (and differs from > behavior of most other major databases, like SyBase, Oracle, etc.). > But even DB2 provide some means to alleviate these restrictions, namely > "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. > It will be very good if such "UNIQUE WHERE NOT NULL" clause will be > introduced in Derby. > Regards, > Oleksandr Alesinskyy -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536178 ] Oleksandr Alesinskyy commented on DERBY-2212: - Hello Mike, concerning index and unique constraints - I did not completely catch your point, sorry. Do you propose to add additional syntax to CONSTRAINT clause only or just go Oracle way which treats all unique indexes the same way? And I completely agree that before implementation some specification concerning both SQL syntax and index behavior shall be agreed. In my opinion, for compatibility reasons DB2 way is better as any index created without additional syntax (does not matter in the constraint clause or in the create index command) behave exactly as before, so no existing application would be affected. And index, created with additional syntax provides new behavior. BTW, it may be considered as new, third type of index (that likely require system dictionary expansion). Regards, Oleksandr > Add "Unique where not null" to create index > --- > > Key: DERBY-2212 > URL: https://issues.apache.org/jira/browse/DERBY-2212 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.2.1.6 >Reporter: Oleksandr Alesinskyy >Assignee: Anurag Shekhar > Attachments: derby-2212preview.diff, derby-2212preview2.diff > > > Derby prohibits creation of unique constraints on nullable colums (as well if > only some columns in the constraint list are nullable) and treat nulls in > unique indexes as normal values (i.e. only one row with null values in > indexed columns may be inserted into the table). This bahavior is very > restrictive, does not completely comply with SQL standards (both letter and > intent) as well as with business needs and intending meaning of NULL values > (2 null values are not considered as equal, this comparision shall return > NULL, and for selection criteria boolean null is treated as FALSE). > This behavior, as far as I can see, is modelled after DB2 (and differs from > behavior of most other major databases, like SyBase, Oracle, etc.). > But even DB2 provide some means to alleviate these restrictions, namely > "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. > It will be very good if such "UNIQUE WHERE NOT NULL" clause will be > introduced in Derby. > Regards, > Oleksandr Alesinskyy -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12535982 ] Oleksandr Alesinskyy commented on DERBY-2212: - I'm original reporter :) DB2-like approach I have suggested for sole reason that Derby is already very DB2-like in many areas. If some other approach will be adopted that allows duplicated NULLs in unique indexes, it is Ok with me. In this very case it looks that DB2 approach is somewhat more consistent as Oracle one. Namely, Oracle treats NULLs in indexes in a special manner if and only if all columns in the index contain NULL. I. e. for create table EXAMPLE ( x number, y number );: create unique index IDX_EXAMPLE on EXAMPLE(x,y); insert into EXAMPLE values(null,null); insert into EXAMPLE values(null,null); would succeed result in 2 records with NULL values, but insert into EXAMPLE values(1,null); insert into EXAMPLE values(1,null); would cause exception on second insert. Such behavior is somewhat cumbersome. On the other hand Oracle treats unique indexes and unique constraints uniformly. > Add "Unique where not null" to create index > --- > > Key: DERBY-2212 > URL: https://issues.apache.org/jira/browse/DERBY-2212 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.2.1.6 >Reporter: Oleksandr Alesinskyy >Assignee: Anurag Shekhar > Attachments: derby-2212preview.diff, derby-2212preview2.diff > > > Derby prohibits creation of unique constraints on nullable colums (as well if > only some columns in the constraint list are nullable) and treat nulls in > unique indexes as normal values (i.e. only one row with null values in > indexed columns may be inserted into the table). This bahavior is very > restrictive, does not completely comply with SQL standards (both letter and > intent) as well as with business needs and intending meaning of NULL values > (2 null values are not considered as equal, this comparision shall return > NULL, and for selection criteria boolean null is treated as FALSE). > This behavior, as far as I can see, is modelled after DB2 (and differs from > behavior of most other major databases, like SyBase, Oracle, etc.). > But even DB2 provide some means to alleviate these restrictions, namely > "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. > It will be very good if such "UNIQUE WHERE NOT NULL" clause will be > introduced in Derby. > Regards, > Oleksandr Alesinskyy -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12512383 ] Oleksandr Alesinskyy commented on DERBY-2212: - Oracle behavior: (null,null,null) vs (null,null,null) - allowed (1,null,null) vs (null,null,null) - allowed (1,null,null) vs (1,null,null) - NOT allowed (1,2,null) vs ((1,null,null)) - allowed (1,2,null) vs (1,2,null) - NOT allowed (1,2,3) vs (1,2,null) - allowed (1,2,3) vs (1,2,3l) - NOT allowed DB2 for iSeries without "when not null" clause: (null,null,null) vs (null,null,null) - NOT allowed (1,null,null) vs (null,null,null) - allowed (1,null,null) vs (1,null,null) - NOT allowed (1,2,null) vs ((1,null,null)) - allowed (1,2,null) vs (1,2,null) - NOT allowed (1,2,3) vs (1,2,null) - allowed (1,2,3) vs (1,2,3) - NOT allowed with "when not null" clause: (null,null,null) vs (null,null,null) - allowed (1,null,null) vs (null,null,null) - allowed (1,null,null) vs (1,null,null) -allowed (1,2,null) vs ((1,null,null)) - allowed (1,2,null) vs (1,2,null) - allowed (1,2,3) vs (1,2,null) - allowed (1,2,3) vs (1,2,3l) - NOT allowed > Add "Unique where not null" to create index > --- > > Key: DERBY-2212 > URL: https://issues.apache.org/jira/browse/DERBY-2212 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.2.1.6 >Reporter: Oleksandr Alesinskyy > > Derby prohibits creation of unique constraints on nullable colums (as well if > only some columns in the constraint list are nullable) and treat nulls in > unique indexes as normal values (i.e. only one row with null values in > indexed columns may be inserted into the table). This bahavior is very > restrictive, does not completely comply with SQL standards (both letter and > intent) as well as with business needs and intending meaning of NULL values > (2 null values are not considered as equal, this comparision shall return > NULL, and for selection criteria boolean null is treated as FALSE). > This behavior, as far as I can see, is modelled after DB2 (and differs from > behavior of most other major databases, like SyBase, Oracle, etc.). > But even DB2 provide some means to alleviate these restrictions, namely > "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. > It will be very good if such "UNIQUE WHERE NOT NULL" clause will be > introduced in Derby. > Regards, > Oleksandr Alesinskyy -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2212) Add "Unique where not null" to create index
[ https://issues.apache.org/jira/browse/DERBY-2212?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12512198 ] Oleksandr Alesinskyy commented on DERBY-2212: - Just for your information - Oracle implements this feature as follows: Rows in which all indexed columns have NULL values are excluded from index, rows that has non-NULL value in at least one indexed column shall be unique. > Add "Unique where not null" to create index > --- > > Key: DERBY-2212 > URL: https://issues.apache.org/jira/browse/DERBY-2212 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.2.1.6 >Reporter: Oleksandr Alesinskyy > > Derby prohibits creation of unique constraints on nullable colums (as well if > only some columns in the constraint list are nullable) and treat nulls in > unique indexes as normal values (i.e. only one row with null values in > indexed columns may be inserted into the table). This bahavior is very > restrictive, does not completely comply with SQL standards (both letter and > intent) as well as with business needs and intending meaning of NULL values > (2 null values are not considered as equal, this comparision shall return > NULL, and for selection criteria boolean null is treated as FALSE). > This behavior, as far as I can see, is modelled after DB2 (and differs from > behavior of most other major databases, like SyBase, Oracle, etc.). > But even DB2 provide some means to alleviate these restrictions, namely > "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. > It will be very good if such "UNIQUE WHERE NOT NULL" clause will be > introduced in Derby. > Regards, > Oleksandr Alesinskyy -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-1949) locate function returns invalid value when first parameter is empty string
[
https://issues.apache.org/jira/browse/DERBY-1949?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12484820
]
Oleksandr Alesinskyy commented on DERBY-1949:
-
Yes, I definitely had misread the documentation. While it is not perfect I have
to admit that by really careful reading it is possible to understand what is
what. The misleading is usage of "search string" term. Usually, it designates a
string which is searched inside of other string and in current documentation it
is used in opposite sense.
Concerning behavior - as soon as argument meaning is clear, behavior is perfect
and need not be changed.
IJ Version 10.2
ij> connect 'd:/work/lms';
ij> values locate(
'1','');
1
---
0
1 Zeile ausgewõhlt
ij> values locate('!','"';
FEHLER 42X01: Syntaxfehler: Encountered "" at line 1, column 21.
ij> values locate('!','"');
1
---
0
1 Zeile ausgewõhlt
ij> values locate('','');
1
---
1
1 Zeile ausgewõhlt
ij> values locate('','',2);
1
---
2
1 Zeile ausgewõhlt
ij> values locate('','',2);
1
---
2
1 Zeile ausgewõhlt
ij> values locate('1','');
1
---
0
1 Zeile ausgewõhlt
ij> values locate('1','',2);
1
---
0
1 Zeile ausgewõhlt
Concerning parameters order - it is the same as by DB2 and differs from SyBase
and Oracle does not support LOCATE at all (INSTR is used instead). In my
opinion, it is better to keep the current order.
Proposed documentation change is better then current documentation but still
not sompletely clear.
I guess, something like to
"First CharacterExpression is a string to search for.
Sexond CharacterExpression is a string to search in.
...
if first argument is an empty string then value of third argument is returned
(or 1 if it is not specified) even if the 2nd argument is an empty string.
If any of arguments has null value then result is null."
Regards,
Oleksandr
> locate function returns invalid value when first parameter is empty string
> --
>
> Key: DERBY-1949
> URL: https://issues.apache.org/jira/browse/DERBY-1949
> Project: Derby
> Issue Type: Bug
> Components: SQL
>Affects Versions: 10.1.3.1, 10.2.1.6
> Environment: N/A
>Reporter: Oleksandr Alesinskyy
> Assigned To: Bryan Pendleton
> Attachments: docs.diff, rrefsqlj61998.html
>
>
> locate function returns false invalid when first parameter is empty string,
> e.g.
> values(locate('','A')
> results in 1, which is obviously wrong
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-1949) locate function returns invalid value when first parameter is empty string
[
https://issues.apache.org/jira/browse/DERBY-1949?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12484543
]
Oleksandr Alesinskyy commented on DERBY-1949:
-
It seems obvious (to me :) ) that returned value shall be 0.
Derby documentation on LOCATE clearly states "If the substring is not found,
LOCATE returns 0". And there is no chance to find non-empty substring in the
empty
string .
More subtle case is when both string and substring are empty. But even in this
case
0 seems to be right value (and consistent with other DBMS as far as I know,
save the
Oracle that agains the SQL standard treats empty strings as nulls ).
Regards,
Oleksandr
> locate function returns invalid value when first parameter is empty string
> --
>
> Key: DERBY-1949
> URL: https://issues.apache.org/jira/browse/DERBY-1949
> Project: Derby
> Issue Type: Bug
> Components: SQL
>Affects Versions: 10.1.3.1, 10.2.1.6
> Environment: N/A
>Reporter: Oleksandr Alesinskyy
> Assigned To: Bryan Pendleton
>
> locate function returns false invalid when first parameter is empty string,
> e.g.
> values(locate('','A')
> results in 1, which is obviously wrong
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Created: (DERBY-2212) Add "Unique where not null" to create index
Add "Unique where not null" to create index --- Key: DERBY-2212 URL: https://issues.apache.org/jira/browse/DERBY-2212 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.2.1.6 Reporter: Oleksandr Alesinskyy Derby prohibits creation of unique constraints on nullable colums (as well if only some columns in the constraint list are nullable) and treat nulls in unique indexes as normal values (i.e. only one row with null values in indexed columns may be inserted into the table). This bahavior is very restrictive, does not completely comply with SQL standards (both letter and intent) as well as with business needs and intending meaning of NULL values (2 null values are not considered as equal, this comparision shall return NULL, and for selection criteria boolean null is treated as FALSE). This behavior, as far as I can see, is modelled after DB2 (and differs from behavior of most other major databases, like SyBase, Oracle, etc.). But even DB2 provide some means to alleviate these restrictions, namely "UNIQUE WHERE NOT NULL" clause for CREATE INDEX statement. It will be very good if such "UNIQUE WHERE NOT NULL" clause will be introduced in Derby. Regards, Oleksandr Alesinskyy -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Created: (DERBY-2203) Not all documented TIMESTAMP formats are properly recognized by TIMESTAMP() and CAST() functions
Not all documented TIMESTAMP formats are properly recognized by TIMESTAMP() and
CAST() functions
Key: DERBY-2203
URL: http://issues.apache.org/jira/browse/DERBY-2203
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.2.1.6
Reporter: Oleksandr Alesinskyy
Reference manual states
"Derby supports the following formats for TIMESTAMP:
-mm-dd hh[:mm[:ss[.nn]]]
-mm-dd-hh[.mm[.ss[.nn]]]"
There is following issue with this piece of documentation:
1.
>From this tatement follows that minutes, second and fractional seconds are
>optional.
In reality it is not so. Only fractional seconds part is treated as optional,
see below.
Note that CAST and TIMESTAMP functions report different errors (SQL states),
while, likely,
they shall be the same.
1.1 Hours only
values (TIMESTAMP('1969-10-01 00'));
SQL State = 22008 SQL Code = -1 SQL Message = '1969-10-01 00' ist ein
ungültiges Argument für die Funktion timestamp. Exception message =
java.sql.SQLException: '1969-10-01 00' ist ein ungültiges Argument für die
Funktion timestamp.
values (CAST('1969-10-01 00' as timestamp));
SQL State = 22007 SQL Code = -1 SQL Message = Die Syntax der
Zeichenfolgendarstellung eines datetime-Wertes ist fehlerhaft. Exception
message = java.sql.SQLException: Die Syntax der Zeichenfolgendarstellung eines
datetime-Wertes ist fehlerhaft.
1.2. Hours and minutes
values (TIMESTAMP('1969-10-01 00:00'));
SQL State = 22008 SQL Code = -1 SQL Message = '1969-10-01 00:00' ist ein
ungültiges Argument für die Funktion timestamp. Exception message =
java.sql.SQLException: '1969-10-01 00:00' ist ein ungültiges Argument für die
Funktion timestamp.
values (CAST('1969-10-01 00:00' as timestamp));
SQL State = 22007 SQL Code = -1 SQL Message = Die Syntax der
Zeichenfolgendarstellung eines datetime-Wertes ist fehlerhaft. Exception
message = java.sql.SQLException: Die Syntax der Zeichenfolgendarstellung eines
datetime-Wertes ist fehlerhaft.
1.3 Hours, minutes and seconds - HU-R-R-A-H!
values (TIMESTAMP('1969-10-01 00:00:00'));
Run successful
values (CAST('1969-10-01 00:00:00' as timestamp));
Run successful
2. Second of above mentioned formats exhibits exactly the same behavior.
I'm not sure if it is Derby bug or documentation bug, but, anyway, it shall be
corrected here or there.
--
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] Created: (DERBY-1950) Invalid 39004 message when function has primitive type parameter but null is passed into String type parameter
Invalid 39004 message when function has primitive type parameter but null is passed into String type parameter -- Key: DERBY-1950 URL: http://issues.apache.org/jira/browse/DERBY-1950 Project: Derby Issue Type: Bug Affects Versions: 10.1.3.1 Environment: N/A Reporter: Oleksandr Alesinskyy Priority: Minor create function countDelimitersBefore( str varchar(512), delimiter char(1), pos integer) returns integer language java parameter style java external name 'de.ntec.common.util.DBStringUtils.countDelimitersBefore'; select countDelimitersBefore(someColumn,'/',1) from someTable; where value of SomeColumn is NULL inappropriatly results in SQL State = 39004 SQL Code = -1 SQL Message = A NULL value cannot be passed to a method which takes a parameter of primitive type 'int'. Exception message = org.apache.derby.client.am.SqlException: A NULL value cannot be passed to a method which takes a parameter of primitive type 'int'. As far as I can see in 10.2.1.6 this bug is fixed. -- 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] Created: (DERBY-1949) locate function returns invalid value when first parameter is empty string
locate function returns invalid value when first parameter is empty string
--
Key: DERBY-1949
URL: http://issues.apache.org/jira/browse/DERBY-1949
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.2.1.6, 10.1.3.1
Environment: N/A
Reporter: Oleksandr Alesinskyy
locate function returns false invalid when first parameter is empty string,
e.g.
values(locate('','A')
results in 1, which is obviously wrong
--
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-896) Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE' to 'TIMESTAMP'."
[
http://issues.apache.org/jira/browse/DERBY-896?page=comments#action_12364754 ]
Oleksandr Alesinskyy commented on DERBY-896:
Casting from TIMESTAMP to DATE (as well as to TIME) is supported by Derby by
means of normal CAST function, so built-in DATE(TIMESTAMP) and TIME(TIMESTAMP)
are superflous (but probably have to be maintained for backward compatibility).
e.g.
CAST(current_timestamp as date) works
CAST(current_date as timestamp) does NOT work
> Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE'
> to 'TIMESTAMP'."
>
>
> Key: DERBY-896
> URL: http://issues.apache.org/jira/browse/DERBY-896
> Project: Derby
> Type: Bug
> Versions: 10.1.2.1
> Environment: - Derby Information
> JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
> [D:\Programme\Derby\bin\lib\derby.jar] 10.1.2.1 - (330608)
> [D:\Programme\Derby\bin\lib\derbytools.jar] 10.1.2.1 - (330608)
> Reporter: Oleksandr Alesinskyy
>
> Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE'
> to 'TIMESTAMP'.",
> e.g. in ij:
> select cast(cast ('2006-01-01' as date) as timestamp) from sys.sysschemas;
> or
> select cast (current_date as timestamp) from bradi_dat.lpaip01;
> The similar issue exists for time ("ERROR 42846: Cannot convert types 'TIME'
> to 'TIMESTAMP'.",
> Documentation clearly states that these conversions are allowed, see table 1
> in a description of CAST function in the Derby Reference Manual and comment
> about "Conversion of date/time values" below that table (see below as well).
> Conversions of date/time values
> A date/time value can always be converted to and from a TIMESTAMP. If a DATE
> is
> converted to a TIMESTAMP, the TIME component of the resulting TIMESTAMP is
> always
> 00:00:00. If a TIME data value is converted to a TIMESTAMP, the DATE
> component is
> set to the value of CURRENT_DATE at the time the CAST is executed. If a
> TIMESTAMP
> is converted to a DATE, the TIME component is silently truncated. If a
> TIMESTAMP is
> converted to a TIME, the DATE component is silently truncated.
>
--
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] Created: (DERBY-896) Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE' to 'TIMESTAMP'."
Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE' to
'TIMESTAMP'."
Key: DERBY-896
URL: http://issues.apache.org/jira/browse/DERBY-896
Project: Derby
Type: Bug
Versions: 10.1.2.1
Environment: - Derby Information
JRE - JDBC: J2SE 1.4.2 - JDBC 3.0
[D:\Programme\Derby\bin\lib\derby.jar] 10.1.2.1 - (330608)
[D:\Programme\Derby\bin\lib\derbytools.jar] 10.1.2.1 - (330608)
Reporter: Oleksandr Alesinskyy
Cast date to timestamp results in "ERROR 42846: Cannot convert types 'DATE' to
'TIMESTAMP'.",
e.g. in ij:
select cast(cast ('2006-01-01' as date) as timestamp) from sys.sysschemas;
or
select cast (current_date as timestamp) from bradi_dat.lpaip01;
The similar issue exists for time ("ERROR 42846: Cannot convert types 'TIME' to
'TIMESTAMP'.",
Documentation clearly states that these conversions are allowed, see table 1 in
a description of CAST function in the Derby Reference Manual and comment about
"Conversion of date/time values" below that table (see below as well).
Conversions of date/time values
A date/time value can always be converted to and from a TIMESTAMP. If a DATE is
converted to a TIMESTAMP, the TIME component of the resulting TIMESTAMP is
always
00:00:00. If a TIME data value is converted to a TIMESTAMP, the DATE component
is
set to the value of CURRENT_DATE at the time the CAST is executed. If a
TIMESTAMP
is converted to a DATE, the TIME component is silently truncated. If a
TIMESTAMP is
converted to a TIME, the DATE component is silently truncated.
--
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
