[jira] [Commented] (DERBY-7148) SQL Error [20000] [38000]: The exception 'java.lang.NoSuchMethodError: 'boolean org.apache.derby.iapi.types.DataValueDescriptor.equals(boolean)'' was thrown while evalu
[
https://issues.apache.org/jira/browse/DERBY-7148?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17635335#comment-17635335
]
Lukas Eder commented on DERBY-7148:
---
A workaround is to cast the COALESCE expression to BOOLEAN:
{code}
SELECT 1
FROM sysibm.SYSDUMMY1
WHERE 1 IS NULL OR CAST(COALESCE((1 = 2), false) AS BOOLEAN);
{code}
> SQL Error [2] [38000]: The exception 'java.lang.NoSuchMethodError:
> 'boolean org.apache.derby.iapi.types.DataValueDescriptor.equals(boolean)''
> was thrown while evaluating an expression.
>
>
> Key: DERBY-7148
> URL: https://issues.apache.org/jira/browse/DERBY-7148
> Project: Derby
> Issue Type: Bug
> Components: SQL
>Affects Versions: 10.16.1.1
>Reporter: Lukas Eder
>Priority: Major
>
> Run this query:
> {code}
> SELECT 1
> FROM sysibm.SYSDUMMY1
> WHERE 1 IS NULL OR COALESCE((1 = 2), false);
> {code}
> It produces this error:
> {quote}SQL Error [2] [38000]: The exception 'java.lang.NoSuchMethodError:
> 'boolean org.apache.derby.iapi.types.DataValueDescriptor.equals(boolean)''
> was thrown while evaluating an expression.{quote}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (DERBY-7148) SQL Error [20000] [38000]: The exception 'java.lang.NoSuchMethodError: 'boolean org.apache.derby.iapi.types.DataValueDescriptor.equals(boolean)'' was thrown while evaluat
Lukas Eder created DERBY-7148:
-
Summary: SQL Error [2] [38000]: The exception
'java.lang.NoSuchMethodError: 'boolean
org.apache.derby.iapi.types.DataValueDescriptor.equals(boolean)'' was thrown
while evaluating an expression.
Key: DERBY-7148
URL: https://issues.apache.org/jira/browse/DERBY-7148
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.16.1.1
Reporter: Lukas Eder
Run this query:
{code}
SELECT 1
FROM sysibm.SYSDUMMY1
WHERE 1 IS NULL OR COALESCE((1 = 2), false);
{code}
It produces this error:
{quote}SQL Error [2] [38000]: The exception 'java.lang.NoSuchMethodError:
'boolean org.apache.derby.iapi.types.DataValueDescriptor.equals(boolean)'' was
thrown while evaluating an expression.{quote}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Updated] (DERBY-7146) NullPointerException when executing MERGE statement with NULL value in INSERT clause
[
https://issues.apache.org/jira/browse/DERBY-7146?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Lukas Eder updated DERBY-7146:
--
Description:
Try this:
{code:sql}
create table x (
i int,
c varchar(10),
primary key (i)
);
merge into x
using SYSIBM.SYSDUMMY1
on i = 1
when matched then update set c = null
when not matched then insert (i, c)
values (1, null);
{code}
It fails with
{code}
Caused by: java.sql.SQLException: Java exception: ':
java.lang.NullPointerException'.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:115)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:141)
at org.apache.derby.impl.jdbc.Util.seeNextException(Util.java:252)
at org.apache.derby.impl.jdbc.Util.javaException(Util.java:274)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:444)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:360)
at
org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2405)
at
org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:88)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:697)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:637)
at
org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:329)
at
org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.lambda$0(JDBCStatementImpl.java:131)
at
org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapDriverActions(SecurityManagerUtils.java:94)
at
org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
... 12 more
Caused by: ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
at
org.apache.derby.shared.common.error.StandardException.newException(StandardException.java:300)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory.java:170)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:75)
... 25 more
Caused by: java.lang.NullPointerException
at
org.apache.derby.impl.sql.compile.CharTypeCompiler.convertible(CharTypeCompiler.java:47)
at
org.apache.derby.impl.sql.compile.CharTypeCompiler.storable(CharTypeCompiler.java:93)
at
org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(ResultColumn.java:1041)
at
org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(ResultColumn.java:1033)
at
org.apache.derby.impl.sql.compile.ResultColumnList.checkStorableExpressions(ResultColumnList.java:1087)
at
org.apache.derby.impl.sql.compile.InsertNode.bindStatement(InsertNode.java:474)
at
org.apache.derby.impl.sql.compile.MatchingClauseNode.bindInsert(MatchingClauseNode.java:839)
at
org.apache.derby.impl.sql.compile.MatchingClauseNode.bind(MatchingClauseNode.java:214)
at
org.apache.derby.impl.sql.compile.MergeNode.bindStatement(MergeNode.java:387)
at
org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:401)
at
org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1114)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:689)
... 17 more
{code}
As a workaround, this works (removing the explicit NULL value from the INSERT
clause):
{code:sql}
merge into x
using SYSIBM.SYSDUMMY1
on i = 1
when matched then update set c = null
when not matched then insert (i)
values (1);
{code}
Using a CAST also helps work around the problem:
{code:sql}
merge into x
using SYSIBM.SYSDUMMY1
on i = 1
when matched then update set c = null
when not matched then insert (i, c)
values (1, cast(null as varchar(10)));
{code}
(Sorry, I didn't try the latest version, because I didn't get it to work in
Dbeaver)
was:
Try this:
{code:sql}
create table x (
i int,
c varchar(10),
primary key (i)
);
merge into x
using SYSIBM.SYSDUMMY1
on i = 1
when matched then update set c = null
when not matched then insert (i, c)
values (1, null);
{code}
It fails with
{{Caused by: java.sql.SQLException: Java exception: ':
java.lang.NullPointerException'.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:115)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:141)
at org.apache.derby.impl.jdbc.Util.seeNextException(Util.java:252)
at
[jira] [Created] (DERBY-7146) NullPointerException when executing MERGE statement with NULL value in INSERT clause
Lukas Eder created DERBY-7146:
-
Summary: NullPointerException when executing MERGE statement with
NULL value in INSERT clause
Key: DERBY-7146
URL: https://issues.apache.org/jira/browse/DERBY-7146
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.15.2.0
Reporter: Lukas Eder
Try this:
{code:sql}
create table x (
i int,
c varchar(10),
primary key (i)
);
merge into x
using SYSIBM.SYSDUMMY1
on i = 1
when matched then update set c = null
when not matched then insert (i, c)
values (1, null);
{code}
It fails with
{{Caused by: java.sql.SQLException: Java exception: ':
java.lang.NullPointerException'.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:115)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:141)
at org.apache.derby.impl.jdbc.Util.seeNextException(Util.java:252)
at org.apache.derby.impl.jdbc.Util.javaException(Util.java:274)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:444)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:360)
at
org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2405)
at
org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:88)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:697)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:637)
at
org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:329)
at
org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.lambda$0(JDBCStatementImpl.java:131)
at
org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapDriverActions(SecurityManagerUtils.java:94)
at
org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
... 12 more
Caused by: ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
at
org.apache.derby.shared.common.error.StandardException.newException(StandardException.java:300)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory.java:170)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:75)
... 25 more
Caused by: java.lang.NullPointerException
at
org.apache.derby.impl.sql.compile.CharTypeCompiler.convertible(CharTypeCompiler.java:47)
at
org.apache.derby.impl.sql.compile.CharTypeCompiler.storable(CharTypeCompiler.java:93)
at
org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(ResultColumn.java:1041)
at
org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(ResultColumn.java:1033)
at
org.apache.derby.impl.sql.compile.ResultColumnList.checkStorableExpressions(ResultColumnList.java:1087)
at
org.apache.derby.impl.sql.compile.InsertNode.bindStatement(InsertNode.java:474)
at
org.apache.derby.impl.sql.compile.MatchingClauseNode.bindInsert(MatchingClauseNode.java:839)
at
org.apache.derby.impl.sql.compile.MatchingClauseNode.bind(MatchingClauseNode.java:214)
at
org.apache.derby.impl.sql.compile.MergeNode.bindStatement(MergeNode.java:387)
at
org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:401)
at
org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1114)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:689)
... 17 more}}
As a workaround, this works (removing the explicit NULL value from the INSERT
clause):
{code:sql}
merge into x
using SYSIBM.SYSDUMMY1
on i = 1
when matched then update set c = null
when not matched then insert (i)
values (1);
{code}
Using a CAST also helps work around the problem:
{code:sql}
merge into x
using SYSIBM.SYSDUMMY1
on i = 1
when matched then update set c = null
when not matched then insert (i, c)
values (1, cast(null as varchar(10)));
{code}
(Sorry, I didn't try the latest version, because I didn't get it to work in
Dbeaver)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
[jira] [Created] (DERBY-7139) Boolean expressions cannot be passed to COALESCE function
Lukas Eder created DERBY-7139:
-
Summary: Boolean expressions cannot be passed to COALESCE function
Key: DERBY-7139
URL: https://issues.apache.org/jira/browse/DERBY-7139
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.15.2.0
Reporter: Lukas Eder
While this parses just fine:
{code}
values(
nullif(
1 = 0,
exists(values(1))
)
);
{code}
This doesn't work:
{code}
values(
coalesce(
1 = 0,
exists(values(1))
)
);
{code}
Resulting in:
bq. SQL Error [3] [42X01]: Syntax error: Encountered "=" at line 3, column
7.
The workaround is to wrap both arguments in parentheses
{code}
values(
coalesce(
(1 = 0),
(exists(values(1)))
)
);
{code}
I don't think there's a good reason for this limitation, so I'm guessing it's a
parser bug?
--
This message was sent by Atlassian Jira
(v8.20.7#820007)
[jira] [Commented] (DERBY-2072) Implement CAST from INTEGER (and other numeric types) to VARCHAR
[
https://issues.apache.org/jira/browse/DERBY-2072?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17154473#comment-17154473
]
Lukas Eder commented on DERBY-2072:
---
For the record, this can be circumvented by casting to CHAR first:
{code}
VALUES CAST(CAST(123 AS CHAR(38)) AS VARCHAR(10))
{code}
> Implement CAST from INTEGER (and other numeric types) to VARCHAR
>
>
> Key: DERBY-2072
> URL: https://issues.apache.org/jira/browse/DERBY-2072
> Project: Derby
> Issue Type: Improvement
> Components: SQL
>Reporter: Christian d'Heureuse
>Priority: Major
> Labels: derby_triage10_11
>
> While CASTs from DATE/TIME to VARCHAR and from INTEGER to CHAR are allowed,
> the conversion from INTEGER to VARCHAR is not supported.
> The statement
> VALUES CAST(123 as VARCHAR(10))
> produces the error
> Cannot convert types 'INTEGER' to 'VARCHAR'.
> The function
> org.apache.derby.impl.sql.compile.BaseTypeCompiler.numberConvertible()
> returns false for a conversion from INTEGER to VARCHAR. This is documented
> within a comment in the source code, but it's not documented why.this is so.
> (Another documented rule in numberConvertible() is that floating point types
> can only be converted to CHAR by the CHAR() function and not with a CAST.
> There is also no indication of why this is so.)
> See also DERBY-1289, DERBY-1306 and DERBY-1804.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (DERBY-7009) DECIMAL(33, 3) Type cannot be used in CAST expression, but can appear in views
[
https://issues.apache.org/jira/browse/DERBY-7009?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16633688#comment-16633688
]
Lukas Eder commented on DERBY-7009:
---
{quote}Other than being puzzling, can you think of any harm caused by the
behavior you have discovered?
{quote}
Well, there's always some "harm" caused by inconsistencies like these. For
instance, what's the point of being able to declare such a constant, and then
not use it in any way? E.g., while it is possible to have such a literal:
{code:java}
SELECT 123456789012345678901234567890.012345678901234567890123456789
FROM (VALUES(1)) t(a){code}
I cannot do any arithmetic with it, even the kind of arithmetic that seems
should work:
{code:java}
SELECT 123456789012345678901234567890.012345678901234567890123456789 * 1
FROM (VALUES(1)) t(a){code}
This causes:
{quote}SQL Error [3] [22003]: The resulting value is outside the range for
the data type DECIMAL/NUMERIC(31,30).
{quote}
I think that for consistency reasons, the data type limitation should apply
everywhere, because then it can be expected. If it is applied only in "some
cases", then it seems much more annoying. At least, that's my opinion.
> DECIMAL(33, 3) Type cannot be used in CAST expression, but can appear in views
> --
>
> Key: DERBY-7009
> URL: https://issues.apache.org/jira/browse/DERBY-7009
> Project: Derby
> Issue Type: Bug
> Components: SQL
>Affects Versions: 10.14.2.0
>Reporter: Lukas Eder
>Priority: Major
>
> In the manual, there is a claim that the maximum precision for the DECIMAL
> data type is 31:
> {quote}The _precision_ must be between 1 and 31. The _scale_ must be less
> than or equal to the precision.
> {quote}
> [https://db.apache.org/derby/docs/10.14/ref/rrefsqlj15260.html]
> This can be confirmed via the following failing statements:
> {{CREATE TABLE test (i decimal(33, 3));}}
> {{SELECT cast(123.45 AS decimal(33, 3)) FROM sysibm.SYSDUMMY1;}}
> However, this statement works:
> {{CREATE VIEW x(a, b) AS}}
> {{select 123456789012345678901234567890.001,
> 123456789012345678901234567890.001 from SYSIBM.SYSDUMMY1;}}
> And a quick query against the dictionary views shows that the type is
> definitely DECIMAL(33, 3):
> {{SELECT COLUMNDATATYPE}}
> {{FROM SYS.SYSCOLUMNS}}
> {{WHERE COLUMNNAME IN ('A', 'B');}}
> Yielding:
> {{COLUMNDATATYPE |}}
> {{---|}}
> {{DECIMAL(33,3) NOT NULL |}}
> {{DECIMAL(33,3) NOT NULL |}}
> For consistency reasons, the latter should fail just like the former two.
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Updated] (DERBY-7009) DECIMAL(33, 3) Type cannot be used in CAST expression, but can appear in views
[
https://issues.apache.org/jira/browse/DERBY-7009?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Lukas Eder updated DERBY-7009:
--
Description:
In the manual, there is a claim that the maximum precision for the DECIMAL data
type is 31:
{quote}The _precision_ must be between 1 and 31. The _scale_ must be less than
or equal to the precision.
{quote}
[https://db.apache.org/derby/docs/10.14/ref/rrefsqlj15260.html]
This can be confirmed via the following failing statements:
{{CREATE TABLE test (i decimal(33, 3));}}
{{SELECT cast(123.45 AS decimal(33, 3)) FROM sysibm.SYSDUMMY1;}}
However, this statement works:
{{CREATE VIEW x(a, b) AS}}
{{select 123456789012345678901234567890.001, 123456789012345678901234567890.001
from SYSIBM.SYSDUMMY1;}}
And a quick query against the dictionary views shows that the type is
definitely DECIMAL(33, 3):
{{SELECT COLUMNDATATYPE}}
{{FROM SYS.SYSCOLUMNS}}
{{WHERE COLUMNNAME IN ('A', 'B');}}
Yielding:
{{COLUMNDATATYPE |}}
{{---|}}
{{DECIMAL(33,3) NOT NULL |}}
{{DECIMAL(33,3) NOT NULL |}}
For consistency reasons, the latter should fail just like the former two.
was:
In the manual, there is a claim that the maximum precision for the DECIMAL data
type is 31:
{quote}The _precision_ must be between 1 and 31. The _scale_ must be less than
or equal to the precision.
{quote}
[https://db.apache.org/derby/docs/10.14/ref/rrefsqlj15260.html]
This can be confirmed via the following failing statements:
{{CREATE TABLE test (i decimal(33, 3));}}
{{SELECT cast(123.45 AS decimal(33, 3)) FROM sysibm.SYSDUMMY1;}}
However, this statement works:
{{CREATE VIEW x(a, b) AS }}
{{select 123456789012345678901234567890.001, 123456789012345678901234567890.001
from SYSIBM.SYSDUMMY1;}}
And a quick query against the dictionary views shows that the type is
definitely DECIMAL(33, 3):
{{SELECT COLUMNDATATYPE}}
{{FROM SYS.SYSCOLUMNS}}
{{WHERE COLUMNNAME IN ('A', 'B');}}
Yielding:
{{COLUMNDATATYPE |}}
{{---|}}
{{DECIMAL(33,3) NOT NULL |}}
{{DECIMAL(33,3) NOT NULL |}}
For consistency reasons, the latter should fail just like the former two.
> DECIMAL(33, 3) Type cannot be used in CAST expression, but can appear in views
> --
>
> Key: DERBY-7009
> URL: https://issues.apache.org/jira/browse/DERBY-7009
> Project: Derby
> Issue Type: Bug
> Components: SQL
>Affects Versions: 10.14.2.0
>Reporter: Lukas Eder
>Priority: Major
>
> In the manual, there is a claim that the maximum precision for the DECIMAL
> data type is 31:
> {quote}The _precision_ must be between 1 and 31. The _scale_ must be less
> than or equal to the precision.
> {quote}
> [https://db.apache.org/derby/docs/10.14/ref/rrefsqlj15260.html]
> This can be confirmed via the following failing statements:
> {{CREATE TABLE test (i decimal(33, 3));}}
> {{SELECT cast(123.45 AS decimal(33, 3)) FROM sysibm.SYSDUMMY1;}}
> However, this statement works:
> {{CREATE VIEW x(a, b) AS}}
> {{select 123456789012345678901234567890.001,
> 123456789012345678901234567890.001 from SYSIBM.SYSDUMMY1;}}
> And a quick query against the dictionary views shows that the type is
> definitely DECIMAL(33, 3):
> {{SELECT COLUMNDATATYPE}}
> {{FROM SYS.SYSCOLUMNS}}
> {{WHERE COLUMNNAME IN ('A', 'B');}}
> Yielding:
> {{COLUMNDATATYPE |}}
> {{---|}}
> {{DECIMAL(33,3) NOT NULL |}}
> {{DECIMAL(33,3) NOT NULL |}}
> For consistency reasons, the latter should fail just like the former two.
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (DERBY-7009) DECIMAL(33, 3) Type cannot be used in CAST expression, but can appear in views
Lukas Eder created DERBY-7009:
-
Summary: DECIMAL(33, 3) Type cannot be used in CAST expression,
but can appear in views
Key: DERBY-7009
URL: https://issues.apache.org/jira/browse/DERBY-7009
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.14.2.0
Reporter: Lukas Eder
In the manual, there is a claim that the maximum precision for the DECIMAL data
type is 31:
{quote}The _precision_ must be between 1 and 31. The _scale_ must be less than
or equal to the precision.
{quote}
[https://db.apache.org/derby/docs/10.14/ref/rrefsqlj15260.html]
This can be confirmed via the following failing statements:
{{CREATE TABLE test (i decimal(33, 3));}}
{{SELECT cast(123.45 AS decimal(33, 3)) FROM sysibm.SYSDUMMY1;}}
However, this statement works:
{{CREATE VIEW x(a, b) AS }}
{{select 123456789012345678901234567890.001, 123456789012345678901234567890.001
from SYSIBM.SYSDUMMY1;}}
And a quick query against the dictionary views shows that the type is
definitely DECIMAL(33, 3):
{{SELECT COLUMNDATATYPE}}
{{FROM SYS.SYSCOLUMNS}}
{{WHERE COLUMNNAME IN ('A', 'B');}}
Yielding:
{{COLUMNDATATYPE |}}
{{---|}}
{{DECIMAL(33,3) NOT NULL |}}
{{DECIMAL(33,3) NOT NULL |}}
For consistency reasons, the latter should fail just like the former two.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (DERBY-7008) Add support for COMMENT ON [ object ] IS '...' statements
Lukas Eder created DERBY-7008:
-
Summary: Add support for COMMENT ON [ object ] IS '...' statements
Key: DERBY-7008
URL: https://issues.apache.org/jira/browse/DERBY-7008
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.14.2.0
Reporter: Lukas Eder
A lot of RDBMS support some way of commenting on stored objects. For instance,
Oracle has this syntax:
{{COMMENT ON IS 'some comment'}}
This is very useful when navigating an unknown schema, for instance to explain
the meaning of a column with some encoded values, etc. Also, there is tool
support when these comments are exposed through the dictionary views and/or
through JDBC's DatabaseMetaData
I personally prefer Oracle's syntax over e.g. MySQL's (inline comments in
CREATE TABLE) or SQL Server's (some system function).
Ideally, all objects can receive comments, including:
* Tables (and views, etc.)
* Columns
* Routines
* Schemas
* Indexes
* etc.
But I think the most important ones are tables and columns
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (DERBY-6983) Support SELECT * for IN predicate subqueries when it is "obvious" that the asterisk expands to exactly one column
[
https://issues.apache.org/jira/browse/DERBY-6983?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16381758#comment-16381758
]
Lukas Eder commented on DERBY-6983:
---
I see, thanks a lot for the clarification. Indeed, it did not occur to me that
your comment could have been intended in this way. In any case, no offense at
all!
> Support SELECT * for IN predicate subqueries when it is "obvious" that the
> asterisk expands to exactly one column
> -
>
> Key: DERBY-6983
> URL: https://issues.apache.org/jira/browse/DERBY-6983
> Project: Derby
> Issue Type: Improvement
> Components: SQL
>Affects Versions: 10.14.1.0
>Reporter: Lukas Eder
>Priority: Major
>
> The following query is not allowed in Derby:
> {code:java}
> SELECT *
> FROM sys.systables
> WHERE tablename IN (
> SELECT *
> FROM (
> SELECT 'SYSTABLES' t
> FROM sysibm.sysdummy1
> ) t
> ){code}
> The error I'm getting is:
> {code:java}
> 'SELECT *' only allowed in EXISTS and NOT EXISTS subqueries.{code}
> In this case, it is "obvious" that the asterisk (also a qualified asterisk:
> t.*) expands to exactly one column and the query should be perfectly fine.
> I'm not aware of any other databases with such a restriction.
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (DERBY-6983) Support SELECT * for IN predicate subqueries when it is "obvious" that the asterisk expands to exactly one column
[
https://issues.apache.org/jira/browse/DERBY-6983?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16377397#comment-16377397
]
Lukas Eder commented on DERBY-6983:
---
Thanks for your comment, Rick. I'm well aware of the fact that the inner
asterisk is the one the error message is talking about ;)
This issue is a request to expand that asterisk to the column list that it
represents, and then allow for the syntax in case it expands to only one column.
You've marked this to be a deviation of the standard. Would you mind pointing
out the section of the standard that forbids asterisks in subqueries? I only
see (in SQL:2016):
{quote}*7.16 *
...
4) Case:
a) If the “*” is simply contained in a that is
immediately contained in an
, then the is equivalent to a that is an arbitrary
.
{color:#FF}b) Otherwise, the “*” is equivalent to a sequence in which each is a column reference that references a column of T
and each column of T is referenced{color}
{color:#FF}exactly once. The columns are referenced in the ascending
sequence of their ordinal position within{color}
{color:#FF}T.{color}
{quote}
No mention of this being forbidden in arbitrary subqueries.
> Support SELECT * for IN predicate subqueries when it is "obvious" that the
> asterisk expands to exactly one column
> -
>
> Key: DERBY-6983
> URL: https://issues.apache.org/jira/browse/DERBY-6983
> Project: Derby
> Issue Type: Improvement
> Components: SQL
>Affects Versions: 10.14.1.0
>Reporter: Lukas Eder
>Priority: Major
>
> The following query is not allowed in Derby:
> {code:java}
> SELECT *
> FROM sys.systables
> WHERE tablename IN (
> SELECT *
> FROM (
> SELECT 'SYSTABLES' t
> FROM sysibm.sysdummy1
> ) t
> ){code}
> The error I'm getting is:
> {code:java}
> 'SELECT *' only allowed in EXISTS and NOT EXISTS subqueries.{code}
> In this case, it is "obvious" that the asterisk (also a qualified asterisk:
> t.*) expands to exactly one column and the query should be perfectly fine.
> I'm not aware of any other databases with such a restriction.
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (DERBY-6984) Cannot use UNION in correlated subquery or derived table when first UNION subquery is parenthesised
Lukas Eder created DERBY-6984:
-
Summary: Cannot use UNION in correlated subquery or derived table
when first UNION subquery is parenthesised
Key: DERBY-6984
URL: https://issues.apache.org/jira/browse/DERBY-6984
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.14.1.0
Reporter: Lukas Eder
This is a correct SQL query in Derby:
{code:java}
(
SELECT tablename
FROM sys.systables
ORDER BY tablename
FETCH FIRST ROW ONLY
)
UNION ALL
(
SELECT tablename
FROM sys.systables
ORDER BY tablename DESC
FETCH FIRST ROW ONLY
){code}
Now, if I want to put this query in a derived table or correlated subquery, it
doesn't work. The expectedly correct syntax should be:
{code:java}
SELECT *
FROM (
(
SELECT tablename
FROM sys.systables
ORDER BY tablename
FETCH FIRST ROW ONLY
)
UNION ALL
(
SELECT tablename
FROM sys.systables
ORDER BY tablename DESC
FETCH FIRST ROW ONLY
)
) t{code}
The error I'm getting is
{code:java}
Syntax error: Encountered "UNION" at line 10, column 3.{code}
In this case, I cannot remove the parentheses around the first union subquery
because of the ORDER BY / FETCH clauses. These are workarounds:
*Wrapping the first subquery in a derived table:*
{code:java}
SELECT *
FROM (
SELECT tablename
FROM (
SELECT tablename
FROM sys.systables
ORDER BY tablename
FETCH FIRST ROW ONLY
) t
UNION ALL
(
SELECT tablename
FROM sys.systables
ORDER BY tablename DESC
FETCH FIRST ROW ONLY
)
) t{code}
*Prepending a dummy union subquery:*
{code:java}
SELECT *
FROM (
SELECT '' tablename
FROM sysibm.sysdummy1
WHERE 1 = 0
UNION ALL
(
SELECT tablename
FROM sys.systables
ORDER BY tablename
FETCH FIRST ROW ONLY
)
UNION ALL
(
SELECT tablename
FROM sys.systables
ORDER BY tablename DESC
FETCH FIRST ROW ONLY
)
) t{code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Created] (DERBY-6983) Support SELECT * for IN predicate subqueries when it is "obvious" that the asterisk expands to exactly one column
Lukas Eder created DERBY-6983:
-
Summary: Support SELECT * for IN predicate subqueries when it is
"obvious" that the asterisk expands to exactly one column
Key: DERBY-6983
URL: https://issues.apache.org/jira/browse/DERBY-6983
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.14.1.0
Reporter: Lukas Eder
The following query is not allowed in Derby:
{code:java}
SELECT *
FROM sys.systables
WHERE tablename IN (
SELECT *
FROM (
SELECT 'SYSTABLES' t
FROM sysibm.sysdummy1
) t
){code}
The error I'm getting is:
{code:java}
'SELECT *' only allowed in EXISTS and NOT EXISTS subqueries.{code}
In this case, it is "obvious" that the asterisk (also a qualified asterisk:
t.*) expands to exactly one column and the query should be perfectly fine. I'm
not aware of any other databases with such a restriction.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (DERBY-4842) Support "IF [NOT] EXISTS" in CREATE TABLE and "IF EXISTS" in DROP TABLE
[ https://issues.apache.org/jira/browse/DERBY-4842?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16265485#comment-16265485 ] Lukas Eder commented on DERBY-4842: --- In many years of working with SQL, I have never felt the need of using CREATE OR REPLACE with "data objects", such as tables. However, indeed, when working with "program objects", such as views, procedures, functions, packages, it is very useful - in fact more useful than IF NOT EXISTS, because usually there's a new version involved that I want to apply to the database regardless if the object exists. With data objects, this is different. IF [ NOT ] EXISTS is super powerful, because migration scripts tend to: - be very hard to write in a correct way - crash in the middle. With IF [ NOT ] EXISTS, they can often be simply re-run, in case the database doesn't support transactional DDL > Support "IF [NOT] EXISTS" in CREATE TABLE and "IF EXISTS" in DROP TABLE > - > > Key: DERBY-4842 > URL: https://issues.apache.org/jira/browse/DERBY-4842 > Project: Derby > Issue Type: Improvement > Components: SQL >Reporter: Adrian Tarau > Labels: derby_triage10_10 > > It would be really great if Derby will support these additions similar with > what MySQL has. > Also a way to disable constraints when dropping the whole database, so you > would not be forced to put DROPs in such an order that constraints will not > fail. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (DERBY-4842) Support "IF [NOT] EXISTS" in CREATE TABLE and "IF EXISTS" in DROP TABLE
[ https://issues.apache.org/jira/browse/DERBY-4842?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16264302#comment-16264302 ] Lukas Eder commented on DERBY-4842: --- This would be really useful. PostgreSQL and MySQL went quite far with such support. This doesn't just extend to tables, but to any object: * DROP [ IF EXISTS ] * ALTER [ IF EXISTS ] ... * ALTER [ IF EXISTS ] ADD [ IF NOT EXISTS ] * ALTER [ IF EXISTS ] DROP [ IF EXISTS ] * CREATE [ IF NOT EXISTS ] > Support "IF [NOT] EXISTS" in CREATE TABLE and "IF EXISTS" in DROP TABLE > - > > Key: DERBY-4842 > URL: https://issues.apache.org/jira/browse/DERBY-4842 > Project: Derby > Issue Type: Improvement > Components: SQL >Reporter: Adrian Tarau > Labels: derby_triage10_10 > > It would be really great if Derby will support these additions similar with > what MySQL has. > Also a way to disable constraints when dropping the whole database, so you > would not be forced to put DROPs in such an order that constraints will not > fail. -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (DERBY-6952) Regression on Statement.getGeneratedKeys() on UPDATE statements
[
https://issues.apache.org/jira/browse/DERBY-6952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16095985#comment-16095985
]
Lukas Eder commented on DERBY-6952:
---
Thanks for linking. Of course, the soundest solution that would make using
getGeneratedKeys() obsolete would be to support the SQL standard OLD TABLE () /
NEW TABLE () / FINAL TABLE () syntaxes as currently supported by DB2 (or,
alternatively, the RETURNING clause as currently implemented by Oracle PL/SQL,
PostgreSQL, Firebird):
{code}
SELECT *
FROM FINAL TABLE (
UPDATE test SET val = 2 WHERE id = 1
)
{code}
I've created a separate feature request for the
feature: https://issues.apache.org/jira/browse/DERBY-6953
> Regression on Statement.getGeneratedKeys() on UPDATE statements
> ---
>
> Key: DERBY-6952
> URL: https://issues.apache.org/jira/browse/DERBY-6952
> Project: Derby
> Issue Type: Bug
> Components: JDBC
>Affects Versions: 10.13.1.1
>Reporter: Lukas Eder
>
> Consider this program:
> {code}
> import java.sql.Connection;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.Statement;
> import java.util.Properties;
> import org.apache.derby.jdbc.EmbeddedDriver;
> public class Derby {
> public static void main(String[] args) throws Exception {
> try (Connection c1 = new
> EmbeddedDriver().connect("jdbc:derby:memory:test;create=true", new
> Properties());
> Statement s = c1.createStatement()) {
> s.execute(
> "CREATE TABLE test ("
> + "id INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS
> IDENTITY,"
> + "val INT)");
> try {
> s.executeUpdate("INSERT INTO test (val) VALUES (1)");
> try (PreparedStatement p = c1.prepareStatement("UPDATE test
> SET val = 2 WHERE id = 1", Statement.RETURN_GENERATED_KEYS)) {
> p.executeUpdate();
> try (ResultSet rs = p.getGeneratedKeys()) {
> while (rs.next())
> System.out.println(rs.getString(1));
> }
> }
> }
> finally {
> s.execute("DROP TABLE test");
> }
> }
> }
> }
> {code}
> Running this with Derby version 10.12.1.1 yields
> 1
> With 10.13.1.1, it yields:
> 0
> I'm not sure if this is (supposed to be) working at all, but the change is
> certainly a bit confusing.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Created] (DERBY-6953) Support the SQL Standard
Lukas Eder created DERBY-6953:
-
Summary: Support the SQL Standard
Key: DERBY-6953
URL: https://issues.apache.org/jira/browse/DERBY-6953
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.13.1.1
Reporter: Lukas Eder
The SQL standard supports an interesting syntax that can be used as a :
{code}
::=
TABLE
::=
|
|
|
::=
FINAL
| NEW
| OLD
{code}
This is currently supported by DB2. Databases like Firebird, Oracle (in
PL/SQL), PostgreSQL support an alternative syntax through the RETURNING keyword
that can be appended to . SQL Server has an OUTPUT
keyword that can be placed in the middle of a .
These statements are incredibly useful to retrieve generated ID values but also
trigger-generated values after a DML operation for an arbitrary number of
inserted / updated / deleted / merged rows.
It would allow people to bypass the many problems that are currently still open
related to Statement.getGeneratedKeys(). Quite likely, if these clauses were
made available, Statement.getGeneratedKeys() could be implemented by patching
the user-defined SQL to be wrapped with a clause.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Created] (DERBY-6952) Regression on Statement.getGeneratedKeys() on UPDATE statements
Lukas Eder created DERBY-6952:
-
Summary: Regression on Statement.getGeneratedKeys() on UPDATE
statements
Key: DERBY-6952
URL: https://issues.apache.org/jira/browse/DERBY-6952
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.13.1.1
Reporter: Lukas Eder
Consider this program:
{code}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import org.apache.derby.jdbc.EmbeddedDriver;
public class Derby {
public static void main(String[] args) throws Exception {
try (Connection c1 = new
EmbeddedDriver().connect("jdbc:derby:memory:test;create=true", new
Properties());
Statement s = c1.createStatement()) {
s.execute(
"CREATE TABLE test ("
+ "id INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS
IDENTITY,"
+ "val INT)");
try {
s.executeUpdate("INSERT INTO test (val) VALUES (1)");
try (PreparedStatement p = c1.prepareStatement("UPDATE test SET
val = 2 WHERE id = 1", Statement.RETURN_GENERATED_KEYS)) {
p.executeUpdate();
try (ResultSet rs = p.getGeneratedKeys()) {
while (rs.next())
System.out.println(rs.getString(1));
}
}
}
finally {
s.execute("DROP TABLE test");
}
}
}
}
{code}
Running this with Derby version 10.12.1.1 yields
1
With 10.13.1.1, it yields:
0
I'm not sure if this is (supposed to be) working at all, but the change is
certainly a bit confusing.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (DERBY-6948) INSERT .. SELECT produces NULL for getGeneratedKeys()
[
https://issues.apache.org/jira/browse/DERBY-6948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16077722#comment-16077722
]
Lukas Eder commented on DERBY-6948:
---
I see, thanks [~rhillegas]. Unfortunately, I cannot make that single user
assumption...
> INSERT .. SELECT produces NULL for getGeneratedKeys()
> -
>
> Key: DERBY-6948
> URL: https://issues.apache.org/jira/browse/DERBY-6948
> Project: Derby
> Issue Type: Bug
> Components: JDBC
>Affects Versions: 10.13.1.1
>Reporter: Lukas Eder
>
> The following code:
> {code}
> import java.sql.Connection;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.Statement;
> import org.apache.derby.jdbc.EmbeddedDataSource;
> public class Derby {
> public static void main(String[] args) throws Exception {
> EmbeddedDataSource ds;
> ds = new EmbeddedDataSource();
> ds.setDatabaseName("memory:test;create=true");
> try (Connection con = ds.getConnection();
> Statement s = con.createStatement()) {
> s.execute(
> "CREATE TABLE test ("
> + "id INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,"
> + "name VARCHAR(255))");
> try (PreparedStatement ps = con.prepareStatement("insert into
> test (name) select 'a' from sysibm.sysdummy1",
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (PreparedStatement ps = con.prepareStatement("insert into
> test (name) select 'a' from sysibm.sysdummy1",
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (ResultSet rs = s.executeQuery("select id from test")) {
> while (rs.next())
> System.out.println("ID: " + rs.getObject(1));
> }
> }
> }
> }
> {code}
> Produces this output:
> {code}
> GEN_ID: null
> GEN_ID: null
> ID: 1
> ID: 2
> ID: 3
> {code}
> The expected output would be:
> {code}
> GEN_ID: 1
> GEN_ID: 2
> GEN_ID: 3
> ID: 1
> ID: 2
> ID: 3
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (DERBY-6948) INSERT .. SELECT produces NULL for getGeneratedKeys()
[
https://issues.apache.org/jira/browse/DERBY-6948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16075961#comment-16075961
]
Lukas Eder commented on DERBY-6948:
---
I see, thanks for linking, Rick. Is there any SQL-based workaround for the
missing functionality, e.g. along the lines of the PostgreSQL INSERT ..
RETURNING statement, or along DB2's (and the SQL standard's) SELECT * FROM
FINAL TABLE (INSERT ..)?
> INSERT .. SELECT produces NULL for getGeneratedKeys()
> -
>
> Key: DERBY-6948
> URL: https://issues.apache.org/jira/browse/DERBY-6948
> Project: Derby
> Issue Type: Bug
> Components: JDBC
>Affects Versions: 10.13.1.1
>Reporter: Lukas Eder
>
> The following code:
> {code}
> import java.sql.Connection;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.Statement;
> import org.apache.derby.jdbc.EmbeddedDataSource;
> public class Derby {
> public static void main(String[] args) throws Exception {
> EmbeddedDataSource ds;
> ds = new EmbeddedDataSource();
> ds.setDatabaseName("memory:test;create=true");
> try (Connection con = ds.getConnection();
> Statement s = con.createStatement()) {
> s.execute(
> "CREATE TABLE test ("
> + "id INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,"
> + "name VARCHAR(255))");
> try (PreparedStatement ps = con.prepareStatement("insert into
> test (name) select 'a' from sysibm.sysdummy1",
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (PreparedStatement ps = con.prepareStatement("insert into
> test (name) select 'a' from sysibm.sysdummy1",
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (ResultSet rs = s.executeQuery("select id from test")) {
> while (rs.next())
> System.out.println("ID: " + rs.getObject(1));
> }
> }
> }
> }
> {code}
> Produces this output:
> {code}
> GEN_ID: null
> GEN_ID: null
> ID: 1
> ID: 2
> ID: 3
> {code}
> The expected output would be:
> {code}
> GEN_ID: 1
> GEN_ID: 2
> GEN_ID: 3
> ID: 1
> ID: 2
> ID: 3
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Commented] (DERBY-6948) INSERT .. SELECT produces NULL for getGeneratedKeys()
[
https://issues.apache.org/jira/browse/DERBY-6948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16074772#comment-16074772
]
Lukas Eder commented on DERBY-6948:
---
>From the description, I wasn't sure if this issue is the same as
>https://issues.apache.org/jira/browse/DERBY-3439 or
>https://issues.apache.org/jira/browse/DERBY-4015
> INSERT .. SELECT produces NULL for getGeneratedKeys()
> -
>
> Key: DERBY-6948
> URL: https://issues.apache.org/jira/browse/DERBY-6948
> Project: Derby
> Issue Type: Bug
> Components: JDBC
>Affects Versions: 10.13.1.1
>Reporter: Lukas Eder
>
> The following code:
> {code}
> import java.sql.Connection;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.Statement;
> import org.apache.derby.jdbc.EmbeddedDataSource;
> public class Derby {
> public static void main(String[] args) throws Exception {
> EmbeddedDataSource ds;
> ds = new EmbeddedDataSource();
> ds.setDatabaseName("memory:test;create=true");
> try (Connection con = ds.getConnection();
> Statement s = con.createStatement()) {
> s.execute(
> "CREATE TABLE test ("
> + "id INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,"
> + "name VARCHAR(255))");
> try (PreparedStatement ps = con.prepareStatement("insert into
> test (name) select 'a' from sysibm.sysdummy1",
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (PreparedStatement ps = con.prepareStatement("insert into
> test (name) select 'a' from sysibm.sysdummy1",
> Statement.RETURN_GENERATED_KEYS)) {
> ps.executeUpdate();
> try (ResultSet rs = ps.getGeneratedKeys()) {
> while (rs.next())
> System.out.println("GEN_ID: " + rs.getObject(1));
> }
> }
> try (ResultSet rs = s.executeQuery("select id from test")) {
> while (rs.next())
> System.out.println("ID: " + rs.getObject(1));
> }
> }
> }
> }
> {code}
> Produces this output:
> {code}
> GEN_ID: null
> GEN_ID: null
> ID: 1
> ID: 2
> ID: 3
> {code}
> The expected output would be:
> {code}
> GEN_ID: 1
> GEN_ID: 2
> GEN_ID: 3
> ID: 1
> ID: 2
> ID: 3
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Created] (DERBY-6948) INSERT .. SELECT produces NULL for getGeneratedKeys()
Lukas Eder created DERBY-6948:
-
Summary: INSERT .. SELECT produces NULL for getGeneratedKeys()
Key: DERBY-6948
URL: https://issues.apache.org/jira/browse/DERBY-6948
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.13.1.1
Reporter: Lukas Eder
The following code:
{code}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import org.apache.derby.jdbc.EmbeddedDataSource;
public class Derby {
public static void main(String[] args) throws Exception {
EmbeddedDataSource ds;
ds = new EmbeddedDataSource();
ds.setDatabaseName("memory:test;create=true");
try (Connection con = ds.getConnection();
Statement s = con.createStatement()) {
s.execute(
"CREATE TABLE test ("
+ "id INT PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,"
+ "name VARCHAR(255))");
try (PreparedStatement ps = con.prepareStatement("insert into test
(name) select 'a' from sysibm.sysdummy1", Statement.RETURN_GENERATED_KEYS)) {
ps.executeUpdate();
try (ResultSet rs = ps.getGeneratedKeys()) {
while (rs.next())
System.out.println("GEN_ID: " + rs.getObject(1));
}
}
try (PreparedStatement ps = con.prepareStatement("insert into test
(name) select 'a' from sysibm.sysdummy1", Statement.RETURN_GENERATED_KEYS)) {
ps.executeUpdate();
try (ResultSet rs = ps.getGeneratedKeys()) {
while (rs.next())
System.out.println("GEN_ID: " + rs.getObject(1));
}
}
try (ResultSet rs = s.executeQuery("select id from test")) {
while (rs.next())
System.out.println("ID: " + rs.getObject(1));
}
}
}
}
{code}
Produces this output:
{code}
GEN_ID: null
GEN_ID: null
ID: 1
ID: 2
ID: 3
{code}
The expected output would be:
{code}
GEN_ID: 1
GEN_ID: 2
GEN_ID: 3
ID: 1
ID: 2
ID: 3
{code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Updated] (DERBY-6947) Simple query runs forever
[
https://issues.apache.org/jira/browse/DERBY-6947?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Lukas Eder updated DERBY-6947:
--
Description:
{code}
select
X1.A as A1,
X2.A as A2
from (
select *
from (
select 1 as A
from SYSIBM.SYSDUMMY1
) x
union all
select *
from (
select 2 as A
from SYSIBM.SYSDUMMY1
) x
) as X1
join (
select *
from (
select 2 as A
from SYSIBM.SYSDUMMY1
) x
union all
select *
from (
select 3 as A
from SYSIBM.SYSDUMMY1
) x
) as X2
on X1.A = X2.A
order by A1
{code}
was:
select
X1.A as A1,
X2.A as A2
from (
select *
from (
select 1 as A
from SYSIBM.SYSDUMMY1
) x
union all
select *
from (
select 2 as A
from SYSIBM.SYSDUMMY1
) x
) as X1
join (
select *
from (
select 2 as A
from SYSIBM.SYSDUMMY1
) x
union all
select *
from (
select 3 as A
from SYSIBM.SYSDUMMY1
) x
) as X2
on X1.A = X2.A
order by A1
> Simple query runs forever
> -
>
> Key: DERBY-6947
> URL: https://issues.apache.org/jira/browse/DERBY-6947
> Project: Derby
> Issue Type: Bug
> Components: SQL
>Affects Versions: 10.13.1.1
>Reporter: Lukas Eder
>
> {code}
> select
> X1.A as A1,
> X2.A as A2
> from (
> select *
> from (
> select 1 as A
> from SYSIBM.SYSDUMMY1
> ) x
> union all
> select *
> from (
> select 2 as A
> from SYSIBM.SYSDUMMY1
> ) x
> ) as X1
> join (
> select *
> from (
> select 2 as A
> from SYSIBM.SYSDUMMY1
> ) x
> union all
> select *
> from (
> select 3 as A
> from SYSIBM.SYSDUMMY1
> ) x
> ) as X2
> on X1.A = X2.A
> order by A1
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
[jira] [Created] (DERBY-6947) Simple query runs forever
Lukas Eder created DERBY-6947: - Summary: Simple query runs forever Key: DERBY-6947 URL: https://issues.apache.org/jira/browse/DERBY-6947 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.13.1.1 Reporter: Lukas Eder select X1.A as A1, X2.A as A2 from ( select * from ( select 1 as A from SYSIBM.SYSDUMMY1 ) x union all select * from ( select 2 as A from SYSIBM.SYSDUMMY1 ) x ) as X1 join ( select * from ( select 2 as A from SYSIBM.SYSDUMMY1 ) x union all select * from ( select 3 as A from SYSIBM.SYSDUMMY1 ) x ) as X2 on X1.A = X2.A order by A1 -- This message was sent by Atlassian JIRA (v6.4.14#64029)
[jira] [Commented] (DERBY-6813) Undocumented ON DELETE SET DEFAULT is parsed, accepted, but not enforced
[
https://issues.apache.org/jira/browse/DERBY-6813?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14567613#comment-14567613
]
Lukas Eder commented on DERBY-6813:
---
[~bryanpendleton]: Thanks for the hints. I'm just here to do the reporting :)
Undocumented ON DELETE SET DEFAULT is parsed, accepted, but not enforced
Key: DERBY-6813
URL: https://issues.apache.org/jira/browse/DERBY-6813
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.11.1.1
Reporter: Lukas Eder
Priority: Minor
The REFERENCES clause implements almost all SQL standard actions for ON
DELETE and ON UPDATE:
https://db.apache.org/derby/docs/10.11/ref/rrefsqlj16357.html#rrefsqlj16357
ON DELETE SET DEFAULT is not explicitly supported by Derby (although, it
would be nice). But the Derby parser accepts it, but doesn't enforce it as
can be seen in this script
{code}
create table t1(v int not null)
create table t2(w int)
alter table t1 add constraint pk primary key (v)
insert into t1 (v) values (1), (2)
insert into t2 (w) values (1)
alter table t2 alter w set default 2
alter table t2 add constraint t2_fk foreign key (w) references t1
(v) on delete set default
delete from t1 where v = 1 -- this fails, but it shouldn't fail
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Created] (DERBY-6812) Make RESTRICT an optional keyword in DROP SEQUENCE
Lukas Eder created DERBY-6812: - Summary: Make RESTRICT an optional keyword in DROP SEQUENCE Key: DERBY-6812 URL: https://issues.apache.org/jira/browse/DERBY-6812 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.11.1.1 Reporter: Lukas Eder Priority: Minor Most SQL dialects that distinguish between RESTRICT and CASCADE in DDL statements default to RESTRICT, if the flag is not explicitly specified. Derby makes using RESTRICT mandatory in the DROP SEQUENCE statement: https://db.apache.org/derby/docs/10.11/ref/rrefsqljdropsequence.html It would be useful for better interoperability to make it optional as well, given that it doesn't really add any value in this particular case. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (DERBY-6813) Undocumented ON DELETE SET DEFAULT is parsed, accepted, but not enforced
Lukas Eder created DERBY-6813:
-
Summary: Undocumented ON DELETE SET DEFAULT is parsed, accepted,
but not enforced
Key: DERBY-6813
URL: https://issues.apache.org/jira/browse/DERBY-6813
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.11.1.1
Reporter: Lukas Eder
Priority: Minor
The REFERENCES clause implements almost all SQL standard actions for ON DELETE
and ON UPDATE:
https://db.apache.org/derby/docs/10.11/ref/rrefsqlj16357.html#rrefsqlj16357
ON DELETE SET DEFAULT is not explicitly supported by Derby (although, it would
be nice). But the Derby parser accepts it, but doesn't enforce it as can be
seen in this script
{code}
create table t1(v int not null)
create table t2(w int)
alter table t1 add constraint pk primary key (v)
insert into t1 (v) values (1), (2)
insert into t2 (w) values (1)
alter table t2 alter w set default 2
alter table t2 add constraint t2_fk foreign key (w) references t1 (v)
on delete set default
delete from t1 where v = 1 -- this fails, but it shouldn't fail
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (DERBY-6757) Driver.connect() returns null when using file-based URL
[
https://issues.apache.org/jira/browse/DERBY-6757?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14173365#comment-14173365
]
Lukas Eder commented on DERBY-6757:
---
Hmm, interesting - I had realised my mistake and I believe I had then closed
the issue, but it is still open.
Driver.connect() returns null when using file-based URL
---
Key: DERBY-6757
URL: https://issues.apache.org/jira/browse/DERBY-6757
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.11.1.1
Reporter: Lukas Eder
The following program can be used to reproduce this issue:
{code}
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Properties;
public class Test {
public static void main(String[] args) throws Exception {
Driver d = (Driver)
Class.forName(org.apache.derby.jdbc.ClientDriver).newInstance();
Properties properties = new Properties();
properties.setProperty(user, TEST);
properties.setProperty(password, TEST);
System.out.println(d.connect(jdbc:derby:C:/data/derby/test;create=true,
properties));
System.out.println(DriverManager.getConnection(jdbc:derby:C:/data/derby/test;create=true,
properties));
}
}
{code}
When run, the above program will output something like:
{monospace}
null
org.apache.derby.impl.jdbc.EmbedConnection@1226204845 (XID = 18681),
(SESSIONID = 1), (DATABASE = C:/data/derby/test), (DRDAID = null)
{monospace}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Closed] (DERBY-6757) Driver.connect() returns null when using file-based URL
[
https://issues.apache.org/jira/browse/DERBY-6757?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Lukas Eder closed DERBY-6757.
-
Resolution: Not a Problem
Driver.connect() returns null when using file-based URL
---
Key: DERBY-6757
URL: https://issues.apache.org/jira/browse/DERBY-6757
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.11.1.1
Reporter: Lukas Eder
The following program can be used to reproduce this issue:
{code}
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Properties;
public class Test {
public static void main(String[] args) throws Exception {
Driver d = (Driver)
Class.forName(org.apache.derby.jdbc.ClientDriver).newInstance();
Properties properties = new Properties();
properties.setProperty(user, TEST);
properties.setProperty(password, TEST);
System.out.println(d.connect(jdbc:derby:C:/data/derby/test;create=true,
properties));
System.out.println(DriverManager.getConnection(jdbc:derby:C:/data/derby/test;create=true,
properties));
}
}
{code}
When run, the above program will output something like:
{monospace}
null
org.apache.derby.impl.jdbc.EmbedConnection@1226204845 (XID = 18681),
(SESSIONID = 1), (DATABASE = C:/data/derby/test), (DRDAID = null)
{monospace}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Created] (DERBY-6758) Driver.connect() returns null when using file-based URL
Lukas Eder created DERBY-6758:
-
Summary: Driver.connect() returns null when using file-based URL
Key: DERBY-6758
URL: https://issues.apache.org/jira/browse/DERBY-6758
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.11.1.1
Reporter: Lukas Eder
The following program can be used to reproduce this issue:
{code}
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Properties;
public class Test {
public static void main(String[] args) throws Exception {
Driver d = (Driver)
Class.forName(org.apache.derby.jdbc.ClientDriver).newInstance();
Properties properties = new Properties();
properties.setProperty(user, TEST);
properties.setProperty(password, TEST);
System.out.println(d.connect(jdbc:derby:C:/data/derby/test;create=true,
properties));
System.out.println(DriverManager.getConnection(jdbc:derby:C:/data/derby/test;create=true,
properties));
}
}
{code}
When run, the above program will output something like:
{monospace}
null
org.apache.derby.impl.jdbc.EmbedConnection@1226204845 (XID = 18681), (SESSIONID
= 1), (DATABASE = C:/data/derby/test), (DRDAID = null)
{monospace}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Created] (DERBY-6757) Driver.connect() returns null when using file-based URL
Lukas Eder created DERBY-6757:
-
Summary: Driver.connect() returns null when using file-based URL
Key: DERBY-6757
URL: https://issues.apache.org/jira/browse/DERBY-6757
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.11.1.1
Reporter: Lukas Eder
The following program can be used to reproduce this issue:
{code}
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Properties;
public class Test {
public static void main(String[] args) throws Exception {
Driver d = (Driver)
Class.forName(org.apache.derby.jdbc.ClientDriver).newInstance();
Properties properties = new Properties();
properties.setProperty(user, TEST);
properties.setProperty(password, TEST);
System.out.println(d.connect(jdbc:derby:C:/data/derby/test;create=true,
properties));
System.out.println(DriverManager.getConnection(jdbc:derby:C:/data/derby/test;create=true,
properties));
}
}
{code}
When run, the above program will output something like:
{monospace}
null
org.apache.derby.impl.jdbc.EmbedConnection@1226204845 (XID = 18681), (SESSIONID
= 1), (DATABASE = C:/data/derby/test), (DRDAID = null)
{monospace}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (DERBY-6758) Driver.connect() returns null when using file-based URL
[
https://issues.apache.org/jira/browse/DERBY-6758?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14160341#comment-14160341
]
Lukas Eder commented on DERBY-6758:
---
Oh man. I just now realised that I wasn't using
{code}org.apache.derby.jdbc.EmbeddedDriver{code}, which I should have. Sorry
about the noise!
Driver.connect() returns null when using file-based URL
---
Key: DERBY-6758
URL: https://issues.apache.org/jira/browse/DERBY-6758
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.11.1.1
Reporter: Lukas Eder
The following program can be used to reproduce this issue:
{code}
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Properties;
public class Test {
public static void main(String[] args) throws Exception {
Driver d = (Driver)
Class.forName(org.apache.derby.jdbc.ClientDriver).newInstance();
Properties properties = new Properties();
properties.setProperty(user, TEST);
properties.setProperty(password, TEST);
System.out.println(d.connect(jdbc:derby:C:/data/derby/test;create=true,
properties));
System.out.println(DriverManager.getConnection(jdbc:derby:C:/data/derby/test;create=true,
properties));
}
}
{code}
When run, the above program will output something like:
{monospace}
null
org.apache.derby.impl.jdbc.EmbedConnection@1226204845 (XID = 18681),
(SESSIONID = 1), (DATABASE = C:/data/derby/test), (DRDAID = null)
{monospace}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Closed] (DERBY-6758) Driver.connect() returns null when using file-based URL
[
https://issues.apache.org/jira/browse/DERBY-6758?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Lukas Eder closed DERBY-6758.
-
Resolution: Invalid
Driver.connect() returns null when using file-based URL
---
Key: DERBY-6758
URL: https://issues.apache.org/jira/browse/DERBY-6758
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.11.1.1
Reporter: Lukas Eder
The following program can be used to reproduce this issue:
{code}
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Properties;
public class Test {
public static void main(String[] args) throws Exception {
Driver d = (Driver)
Class.forName(org.apache.derby.jdbc.ClientDriver).newInstance();
Properties properties = new Properties();
properties.setProperty(user, TEST);
properties.setProperty(password, TEST);
System.out.println(d.connect(jdbc:derby:C:/data/derby/test;create=true,
properties));
System.out.println(DriverManager.getConnection(jdbc:derby:C:/data/derby/test;create=true,
properties));
}
}
{code}
When run, the above program will output something like:
{monospace}
null
org.apache.derby.impl.jdbc.EmbedConnection@1226204845 (XID = 18681),
(SESSIONID = 1), (DATABASE = C:/data/derby/test), (DRDAID = null)
{monospace}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Comment Edited] (DERBY-1576) Extend the CASE expression syntax for simple case
[ https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13985333#comment-13985333 ] Lukas Eder edited comment on DERBY-1576 at 4/30/14 9:48 AM: Hmm, apart from MS Access (which doesn't have a CASE expression at all), Derby is the only database among the 16 databases supported by http://www.jooq.org, which doesn't support this syntax. Is there any particular reason why this was omitted so far? I suspect that a canonical implementation would be merely syntactic sugar, implemented in the parser... was (Author: lukas.eder): Hmm, apart from MS Access (which doesn't have a CASE expression at all), Derby is the only database among the 16 databases supported by a href=http://www.jooq.org;jOOQ/a, which doesn't support this syntax. Is there any particular reason why this was omitted so far? I suspect that a canonical implementation would be merely syntactic sugar, implemented in the parser... Extend the CASE expression syntax for simple case --- Key: DERBY-1576 URL: https://issues.apache.org/jira/browse/DERBY-1576 Project: Derby Issue Type: Improvement Components: SQL Reporter: Christian d'Heureuse Priority: Minor Labels: derby_triage10_11 The ISO/IEC 9075-2:1999 SQL standard describes two kinds of CASE expressions: simple case and searched case. The current Derby version supports searched case but not simple case. The syntax for simple case is: CASE Expression WHEN Expression THEN Expression [ WHEN Expression THEN Expression ] ... ELSE ElseExpression END Example: VALUES CASE 4 WHEN 1 THEN 'one' WHEN 2 THEN 'two' WHEN 3 THEN 'three' ELSE 'many' END -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Commented] (DERBY-1576) Extend the CASE expression syntax for simple case
[ https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13985333#comment-13985333 ] Lukas Eder commented on DERBY-1576: --- Hmm, apart from MS Access (which doesn't have a CASE expression at all), Derby is the only database among the 16 databases supported by a href=http://www.jooq.org;jOOQ/a, which doesn't support this syntax. Is there any particular reason why this was omitted so far? I suspect that a canonical implementation would be merely syntactic sugar, implemented in the parser... Extend the CASE expression syntax for simple case --- Key: DERBY-1576 URL: https://issues.apache.org/jira/browse/DERBY-1576 Project: Derby Issue Type: Improvement Components: SQL Reporter: Christian d'Heureuse Priority: Minor Labels: derby_triage10_11 The ISO/IEC 9075-2:1999 SQL standard describes two kinds of CASE expressions: simple case and searched case. The current Derby version supports searched case but not simple case. The syntax for simple case is: CASE Expression WHEN Expression THEN Expression [ WHEN Expression THEN Expression ] ... ELSE ElseExpression END Example: VALUES CASE 4 WHEN 1 THEN 'one' WHEN 2 THEN 'two' WHEN 3 THEN 'three' ELSE 'many' END -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Commented] (DERBY-1576) Extend the CASE expression syntax for simple case
[
https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13985423#comment-13985423
]
Lukas Eder commented on DERBY-1576:
---
Very nice, I wasn't aware of this interpretation of the SQL standard's when
operand, thanks for pointing that out. This appears to have been added in
SQL:2003, only - so this issue could be split into two:
- Implementing the SQL-1999 simple CASE expression
- Implementing the SQL-2003 simple CASE expression extensions
I had given this some more thought. I suspect that the simple CASE expression
is actually not equivalent to the searched CASE expression. Consider something
like:
{code}
-- Assuming random() or some other non-deterministic function exists
CASE random(0, 2)
WHEN 0 THEN 'a'
WHEN 1 THEN 'b'
WHEN 2 THEN 'c'
END
{code}
The above wouldn't be equivalent to:
{code}
CASE
WHEN random(0, 2) = 0 THEN 'a'
WHEN random(0, 2) = 1 THEN 'b'
WHEN random(0, 2) = 2 THEN 'c'
END
{code}
On the other hand, the standard says:
{quote}
6.12 case expression
2) If a case specification specifies a simple case, then let CO be the
case operand.
a) CO shall not generally contain a routine invocation whose subject
routine is an SQL-invoked routine that is possibly non-deterministic or that
possibly modifies SQL-data.
{quote}
... so calling random() would be illegal anyway
Extend the CASE expression syntax for simple case
---
Key: DERBY-1576
URL: https://issues.apache.org/jira/browse/DERBY-1576
Project: Derby
Issue Type: Improvement
Components: SQL
Reporter: Christian d'Heureuse
Priority: Minor
Labels: derby_triage10_11
The ISO/IEC 9075-2:1999 SQL standard describes two kinds of CASE expressions:
simple case and searched case.
The current Derby version supports searched case but not simple case.
The syntax for simple case is:
CASE Expression
WHEN Expression THEN Expression
[ WHEN Expression THEN Expression ]
...
ELSE ElseExpression
END
Example:
VALUES
CASE 4
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
WHEN 3 THEN 'three'
ELSE 'many'
END
--
This message was sent by Atlassian JIRA
(v6.2#6252)
[jira] [Created] (DERBY-6489) Add support for the SQL Standard ALTER SEQUENCE statement
Lukas Eder created DERBY-6489: - Summary: Add support for the SQL Standard ALTER SEQUENCE statement Key: DERBY-6489 URL: https://issues.apache.org/jira/browse/DERBY-6489 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.10.1.1 Reporter: Lukas Eder It would be nice to support the SQL standard alter sequence generator statement. -- This message was sent by Atlassian JIRA (v6.1.5#6160)
[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-tabpanelfocusedCommentId=13903113#comment-13903113
]
Lukas Eder commented on DERBY-896:
--
That is great, thank you very much, guys!
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
Assignee: Knut Anders Hatlen
Labels: derby_triage10_5_2
Fix For: 10.11.0.0
Attachments: allow-casts-with-tests.diff, allow-casts.diff, 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-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-tabpanelfocusedCommentId=13899283#comment-13899283
]
Lukas Eder commented on DERBY-896:
--
I can reproduce this issue with
{code}
select current_date as d, cast(current_date as timestamp) as ts from
SYSIBM.SYSDUMMY1
{code}
Note that according to the Derby manual, this should be possible:
http://db.apache.org/derby/docs/10.2/ref/rrefsqlj33562.html
(I know, I'm just repeating what Oleksandr already said)
Just to get some new life in this issue, which is probably easy to fix, which
pulls its own weight, and which is a bit surprising :-)
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] [Created] (DERBY-6444) Add support for INSERT INTO table DEFAULT VALUES syntax
Lukas Eder created DERBY-6444: - Summary: Add support for INSERT INTO table DEFAULT VALUES syntax Key: DERBY-6444 URL: https://issues.apache.org/jira/browse/DERBY-6444 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.10.1.1 Reporter: Lukas Eder Priority: Minor Many other databases support specific syntax to create an empty or default record in a table: INSERT INTO table DEFAULT VALUES The above syntax is equivalent to this: INSERT INTO table (v1, v2, ..., vn) VALUES (DEFAULT, DEFAULT, ..., DEFAULT) Just to name a few databases that support this syntax: - CUBRID - Firebird - H2 - HSQLDB - Ingres - PostgreSQL - SQLite - SQL Server - Sybase For convenience it would be great if the SQL-92 Standard (http://www.andrew.cmu.edu/user/shadow/sql/sql1992.txt) would be implemented: 13.8 insert statement Function Create new rows in a table. Format insert statement ::= INSERT INTO table name insert columns and source insert columns and source ::= [ left paren insert column list right paren ] query expression | DEFAULT VALUES insert column list ::= column name list -- This message was sent by Atlassian JIRA (v6.1.5#6160)
[jira] [Created] (DERBY-6228) DisconnectException when executing an SELECT [LOB column] ORDER BY [...] statement with TYPE_SCROLL_[IN]SENSITIVE and CONCUR_UPDATABLE
Lukas Eder created DERBY-6228: - Summary: DisconnectException when executing an SELECT [LOB column] ORDER BY [...] statement with TYPE_SCROLL_[IN]SENSITIVE and CONCUR_UPDATABLE Key: DERBY-6228 URL: https://issues.apache.org/jira/browse/DERBY-6228 Project: Derby Issue Type: Bug Components: Network Client Affects Versions: 10.10.1.1, 10.8.3.0 Reporter: Lukas Eder Here's a minimal program to reproduce the issue: Connection c = DriverManager.getConnection( jdbc:derby://localhost:1527/test;create=true, TEST, TEST); Statement s = c.createStatement(); s.executeUpdate( CREATE TABLE t( + id INT NOT NULL, + c CLOB + )); s.executeUpdate(INSERT INTO t VALUES (1, null)); s.executeUpdate(INSERT INTO t VALUES (2, null)); PreparedStatement stmt = c.prepareStatement( SELECT * FROM t ORDER BY id, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery(); rs.next(); The above leads to this exception: java.sql.SQLNonTransientConnectionException: Netzwerkprotokollausnahme: DSS-Länge ist beim Beenden des Parsing-Vorgangs der ID-Kette größer als 0. Die Verbindung wurde beendet. at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source) at org.apache.derby.client.am.ResultSet.next(Unknown Source) at org.jooq.test._.testcases.KeepResultSetTests.testKeepRSWithUpdateOnChangeLazy(KeepResultSetTests.java:330) at org.jooq.test.jOOQAbstractTest.testKeepRSWithUpdateOnChangeLazy(jOOQAbstractTest.java:2240) ... Caused by: org.apache.derby.client.am.DisconnectException: Netzwerkprotokollausnahme: DSS-Länge ist beim Beenden des Parsing-Vorgangs der ID-Kette größer als 0. Die Verbindung wurde beendet. at org.apache.derby.client.net.Reply.endOfSameIdChainData(Unknown Source) at org.apache.derby.client.net.NetResultSetReply.readPositioningFetch(Unknown Source) at org.apache.derby.client.net.ResultSetReply.readPositioningFetch(Unknown Source) at org.apache.derby.client.net.NetResultSet.readPositioningFetch_(Unknown Source) at org.apache.derby.client.am.ResultSet.getRowCount(Unknown Source) at org.apache.derby.client.am.ResultSet.resultSetContainsNoRows(Unknown Source) at org.apache.derby.client.am.ResultSet.getNextRowset(Unknown Source) at org.apache.derby.client.am.ResultSet.nextX(Unknown Source) ... 30 more To reproduce the above, all of the following things seem relevant: 1. There is at least one BLOB or CLOB column being selected 2. An ORDER BY clause is added 3. ResultSet.TYPE_SCROLL_SENSITIVE or ResultSet.TYPE_SCROLL_INSENSITIVE is set 4. ResultSet.CONCUR_UPDATABLE is set -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-4138) Adding an ORDER BY clause breaks updatable result sets
[ https://issues.apache.org/jira/browse/DERBY-4138?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13666467#comment-13666467 ] Lukas Eder commented on DERBY-4138: --- See also DERBY-6228, where adding an ORDER BY clause to a SELECT [LOB column] query terminates the connection Adding an ORDER BY clause breaks updatable result sets Key: DERBY-4138 URL: https://issues.apache.org/jira/browse/DERBY-4138 Project: Derby Issue Type: Improvement Components: JDBC Affects Versions: 10.4.2.0 Environment: Solaris Reporter: Alan Burlison Priority: Minor The following doesn't work: ps = c.prepareStatement(select a, b, c from tab where a = ? order by b desc, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); : : rs.updateInt(3, 123); and throws the following exception: 'updateInt' not allowed because the ResultSet is not an updatable ResultSet. [XJ083] Removing the 'order by b desc' clause makes the problem go away. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (DERBY-5893) Add support for the SQL:2008 standard IS [ NOT ] DISTINCT FROM predicate
Lukas Eder created DERBY-5893: - Summary: Add support for the SQL:2008 standard IS [ NOT ] DISTINCT FROM predicate Key: DERBY-5893 URL: https://issues.apache.org/jira/browse/DERBY-5893 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.9.1.0 Reporter: Lukas Eder Priority: Minor The SQL:1999 standard specifies the IS [ NOT ] DISTINCT FROM predicate in chapter 8.15 distinct predicate: distinct predicate ::= row value predicand 3 distinct predicate part 2 distinct predicate part 2 ::= IS [ NOT ] DISTINCT FROM row value predicand 4 row value predicand 3 ::= row value predicand row value predicand 4 ::= row value predicand This predicate is supported by at least these databases: - http://www.postgresql.org/docs/9.1/static/functions-comparison.html - http://www.h2database.com/html/grammar.html#condition_right_hand_side - http://hsqldb.org/doc/guide/ch05.html#N11BB0 - http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html#operator_equal-to (with a different syntax) - http://dcx.sybase.com/1200/en/dbreference/is-distinct-from-search-condition.html It would probably make sense for the Derby database to implement it as well. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Comment Edited] (DERBY-5893) Add support for the SQL:2008 standard IS [ NOT ] DISTINCT FROM predicate
[
https://issues.apache.org/jira/browse/DERBY-5893?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13428647#comment-13428647
]
Lukas Eder edited comment on DERBY-5893 at 8/4/12 5:39 PM:
---
Yes, it's different in the way it handles NULLs. For example:
- (1 != NULL) yields NULL, whereas (1 IS DISTINCT FROM NULL) yields TRUE
- (NULL != NULL) yields NULL, whereas (NULL IS DISTINCT FROM NULL) yields FALSE
Here's how you could translate IS [ NOT ] DISTINCT FROM into a equivalent CASE
expressions:
-- A IS DISTINCT FROM B:
CASE WHEN A IS NULL AND B IS NULL THEN 0
WHEN A IS NULL AND B IS NOT NULL THEN 1
WHEN A IS NOT NULL AND B IS NULL THEN 1
WHEN A = B THEN 0
ELSE 1
END
-- A IS NOT DISTINCT FROM B:
CASE WHEN A IS NULL AND B IS NULL THEN 1
WHEN A IS NULL AND B IS NOT NULL THEN 0
WHEN A IS NOT NULL AND B IS NULL THEN 0
WHEN A = B THEN 1
ELSE 0
END
Alternatively, replace 1 and 0 by TRUE and FALSE.
was (Author: lukas.eder):
Yes, it's different in the way it handles NULLs. For example:
- (1 != NULL) yields NULL, whereas (1 IS DISTINCT FROM NULL) yields TRUE
- (NULL != NULL) yields NULL, whereas (NULL IS DISTINCT FROM NULL) yields FALSE
Here's how you could translate IS [ NOT ] DISTINCT FROM into a equivalent CASE
expressions:
{code}
-- A IS DISTINCT FROM B:
CASE WHEN A IS NULL AND B IS NULL THEN 0
WHEN A IS NULL AND B IS NOT NULL THEN 1
WHEN A IS NOT NULL AND B IS NULL THEN 1
WHEN A = B THEN 0
ELSE 1
END
-- A IS NOT DISTINCT FROM B:
CASE WHEN A IS NULL AND B IS NULL THEN 1
WHEN A IS NULL AND B IS NOT NULL THEN 0
WHEN A IS NOT NULL AND B IS NULL THEN 0
WHEN A = B THEN 1
ELSE 0
END
{code}
Alternatively, replace 1 and 0 by TRUE and FALSE.
Add support for the SQL:2008 standard IS [ NOT ] DISTINCT FROM predicate
Key: DERBY-5893
URL: https://issues.apache.org/jira/browse/DERBY-5893
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.9.1.0
Reporter: Lukas Eder
Priority: Minor
Labels: features
The SQL:1999 standard specifies the IS [ NOT ] DISTINCT FROM predicate in
chapter 8.15 distinct predicate:
distinct predicate ::=
row value predicand 3 distinct predicate part 2
distinct predicate part 2 ::=
IS [ NOT ] DISTINCT FROM row value predicand 4
row value predicand 3 ::=
row value predicand
row value predicand 4 ::=
row value predicand
This predicate is supported by at least these databases:
- http://www.postgresql.org/docs/9.1/static/functions-comparison.html
- http://www.h2database.com/html/grammar.html#condition_right_hand_side
- http://hsqldb.org/doc/guide/ch05.html#N11BB0
-
http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html#operator_equal-to
(with a different syntax)
-
http://dcx.sybase.com/1200/en/dbreference/is-distinct-from-search-condition.html
It would probably make sense for the Derby database to implement it as well.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Comment Edited] (DERBY-5893) Add support for the SQL:2008 standard IS [ NOT ] DISTINCT FROM predicate
[ https://issues.apache.org/jira/browse/DERBY-5893?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13428647#comment-13428647 ] Lukas Eder edited comment on DERBY-5893 at 8/4/12 5:39 PM: --- Yes, it's different in the way it handles NULLs. For example: - (1 != NULL) yields NULL, whereas (1 IS DISTINCT FROM NULL) yields TRUE - (NULL != NULL) yields NULL, whereas (NULL IS DISTINCT FROM NULL) yields FALSE Here's how you could translate IS [ NOT ] DISTINCT FROM into equivalent CASE expressions: -- A IS DISTINCT FROM B: CASE WHEN A IS NULL AND B IS NULL THEN 0 WHEN A IS NULL AND B IS NOT NULL THEN 1 WHEN A IS NOT NULL AND B IS NULL THEN 1 WHEN A = B THEN 0 ELSE 1 END -- A IS NOT DISTINCT FROM B: CASE WHEN A IS NULL AND B IS NULL THEN 1 WHEN A IS NULL AND B IS NOT NULL THEN 0 WHEN A IS NOT NULL AND B IS NULL THEN 0 WHEN A = B THEN 1 ELSE 0 END Alternatively, replace 1 and 0 by TRUE and FALSE. was (Author: lukas.eder): Yes, it's different in the way it handles NULLs. For example: - (1 != NULL) yields NULL, whereas (1 IS DISTINCT FROM NULL) yields TRUE - (NULL != NULL) yields NULL, whereas (NULL IS DISTINCT FROM NULL) yields FALSE Here's how you could translate IS [ NOT ] DISTINCT FROM into a equivalent CASE expressions: -- A IS DISTINCT FROM B: CASE WHEN A IS NULL AND B IS NULL THEN 0 WHEN A IS NULL AND B IS NOT NULL THEN 1 WHEN A IS NOT NULL AND B IS NULL THEN 1 WHEN A = B THEN 0 ELSE 1 END -- A IS NOT DISTINCT FROM B: CASE WHEN A IS NULL AND B IS NULL THEN 1 WHEN A IS NULL AND B IS NOT NULL THEN 0 WHEN A IS NOT NULL AND B IS NULL THEN 0 WHEN A = B THEN 1 ELSE 0 END Alternatively, replace 1 and 0 by TRUE and FALSE. Add support for the SQL:2008 standard IS [ NOT ] DISTINCT FROM predicate Key: DERBY-5893 URL: https://issues.apache.org/jira/browse/DERBY-5893 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.9.1.0 Reporter: Lukas Eder Priority: Minor Labels: features The SQL:1999 standard specifies the IS [ NOT ] DISTINCT FROM predicate in chapter 8.15 distinct predicate: distinct predicate ::= row value predicand 3 distinct predicate part 2 distinct predicate part 2 ::= IS [ NOT ] DISTINCT FROM row value predicand 4 row value predicand 3 ::= row value predicand row value predicand 4 ::= row value predicand This predicate is supported by at least these databases: - http://www.postgresql.org/docs/9.1/static/functions-comparison.html - http://www.h2database.com/html/grammar.html#condition_right_hand_side - http://hsqldb.org/doc/guide/ch05.html#N11BB0 - http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html#operator_equal-to (with a different syntax) - http://dcx.sybase.com/1200/en/dbreference/is-distinct-from-search-condition.html It would probably make sense for the Derby database to implement it as well. -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5893) Add support for the SQL:2008 standard IS [ NOT ] DISTINCT FROM predicate
[
https://issues.apache.org/jira/browse/DERBY-5893?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13428647#comment-13428647
]
Lukas Eder commented on DERBY-5893:
---
Yes, it's different in the way it handles NULLs. For example:
- (1 != NULL) yields NULL, whereas (1 IS DISTINCT FROM NULL) yields TRUE
- (NULL != NULL) yields NULL, whereas (NULL IS DISTINCT FROM NULL) yields FALSE
Here's how you could translate IS [ NOT ] DISTINCT FROM into a equivalent CASE
expressions:
{code}
-- A IS DISTINCT FROM B:
CASE WHEN A IS NULL AND B IS NULL THEN 0
WHEN A IS NULL AND B IS NOT NULL THEN 1
WHEN A IS NOT NULL AND B IS NULL THEN 1
WHEN A = B THEN 0
ELSE 1
END
-- A IS NOT DISTINCT FROM B:
CASE WHEN A IS NULL AND B IS NULL THEN 1
WHEN A IS NULL AND B IS NOT NULL THEN 0
WHEN A IS NOT NULL AND B IS NULL THEN 0
WHEN A = B THEN 1
ELSE 0
END
{code}
Alternatively, replace 1 and 0 by TRUE and FALSE.
Add support for the SQL:2008 standard IS [ NOT ] DISTINCT FROM predicate
Key: DERBY-5893
URL: https://issues.apache.org/jira/browse/DERBY-5893
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.9.1.0
Reporter: Lukas Eder
Priority: Minor
Labels: features
The SQL:1999 standard specifies the IS [ NOT ] DISTINCT FROM predicate in
chapter 8.15 distinct predicate:
distinct predicate ::=
row value predicand 3 distinct predicate part 2
distinct predicate part 2 ::=
IS [ NOT ] DISTINCT FROM row value predicand 4
row value predicand 3 ::=
row value predicand
row value predicand 4 ::=
row value predicand
This predicate is supported by at least these databases:
- http://www.postgresql.org/docs/9.1/static/functions-comparison.html
- http://www.h2database.com/html/grammar.html#condition_right_hand_side
- http://hsqldb.org/doc/guide/ch05.html#N11BB0
-
http://dev.mysql.com/doc/refman/5.6/en/comparison-operators.html#operator_equal-to
(with a different syntax)
-
http://dcx.sybase.com/1200/en/dbreference/is-distinct-from-search-condition.html
It would probably make sense for the Derby database to implement it as well.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators:
https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (DERBY-5837) Add support for SQL standard DATE, TIME, TIMESTAMP literals
Lukas Eder created DERBY-5837: - Summary: Add support for SQL standard DATE, TIME, TIMESTAMP literals Key: DERBY-5837 URL: https://issues.apache.org/jira/browse/DERBY-5837 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.8.2.2 Reporter: Lukas Eder Priority: Minor The SQL standard 1992 specifies datetime literals as such: datetime literal ::= date literal | time literal | timestamp literal date literal ::= DATE date string time literal ::= TIME time string timestamp literal ::= TIMESTAMP timestamp string date string ::= quote date value quote time string ::= quote time value [ time zone interval ] quote timestamp string ::= quote date value space time value [ time zone interval ] quote Taken from: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt This seems not to be supported directly by Derby. Instead, Derby supports functions for constructing DATE, TIME, TIMESTAMP values. For example: http://db.apache.org/derby/docs/dev/ref/rreftimestampfunc.html For increased compatibility, it would be nice if literals were implemented according to the standard. In essence, the function's parentheses could be made optional -- This message is automatically generated by JIRA. If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (DERBY-5320) Support for RPAD, LPAD, REPEAT string functions
Support for RPAD, LPAD, REPEAT string functions --- Key: DERBY-5320 URL: https://issues.apache.org/jira/browse/DERBY-5320 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.8.1.2 Reporter: Lukas Eder Priority: Minor Some users might find it useful, if Derby officially supported RPAD, LPAD, and REPEAT functions. I know these functions can be implemented with user defined functions, too. But many other RDBMS have built-in support for them -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5005) Error when fully qualifying a field from a view in an ORDER BY clause
[ https://issues.apache.org/jira/browse/DERBY-5005?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13033611#comment-13033611 ] Lukas Eder commented on DERBY-5005: --- Works for me in 10.8. Thanks guys Error when fully qualifying a field from a view in an ORDER BY clause - Key: DERBY-5005 URL: https://issues.apache.org/jira/browse/DERBY-5005 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1 Environment: Windows 7 Reporter: Lukas Eder Assignee: Dag H. Wanvik Priority: Minor Labels: order, sql, syntax, views Fix For: 10.5.3.2, 10.6.2.3, 10.7.1.4, 10.8.1.2 Attachments: 5005.sql, derby-5005.diff, derby-5005.stat, derby-5005_10_5_diff.txt, derby-5005b.diff, derby-5005b.stat I have a strange issue that can be reproduced easily with the following objects in schema test: create table a (a integer); insert into a (a) values(1); create view v as select * from a; This works: select test.a.a from test.a where test.a.a 2 order by test.a.a asc; This doesn't work: select test.v.a from test.v where test.v.a 2 order by test.v.a asc; But this does: select test.v.a from test.v where test.v.a 2 order by v.a asc; This is the error I get: Error: 'TEST.V' is not an exposed table name in the scope in which it appears. SQLState: 42X10 ErrorCode: -1 I've tried quite a few SELECT clauses, and I think the ORDER BY clause is the only one having this issue. -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Closed] (DERBY-5005) Error when fully qualifying a field from a view in an ORDER BY clause
[ https://issues.apache.org/jira/browse/DERBY-5005?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Lukas Eder closed DERBY-5005. - Error when fully qualifying a field from a view in an ORDER BY clause - Key: DERBY-5005 URL: https://issues.apache.org/jira/browse/DERBY-5005 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1 Environment: Windows 7 Reporter: Lukas Eder Assignee: Dag H. Wanvik Priority: Minor Labels: order, sql, syntax, views Fix For: 10.5.3.2, 10.6.2.3, 10.7.1.4, 10.8.1.2 Attachments: 5005.sql, derby-5005.diff, derby-5005.stat, derby-5005_10_5_diff.txt, derby-5005b.diff, derby-5005b.stat I have a strange issue that can be reproduced easily with the following objects in schema test: create table a (a integer); insert into a (a) values(1); create view v as select * from a; This works: select test.a.a from test.a where test.a.a 2 order by test.a.a asc; This doesn't work: select test.v.a from test.v where test.v.a 2 order by test.v.a asc; But this does: select test.v.a from test.v where test.v.a 2 order by v.a asc; This is the error I get: Error: 'TEST.V' is not an exposed table name in the scope in which it appears. SQLState: 42X10 ErrorCode: -1 I've tried quite a few SELECT clauses, and I think the ORDER BY clause is the only one having this issue. -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (DERBY-5214) Make DATETIME arithmetic functions easier to use
Make DATETIME arithmetic functions easier to use
Key: DERBY-5214
URL: https://issues.apache.org/jira/browse/DERBY-5214
Project: Derby
Issue Type: Improvement
Components: JDBC, SQL
Affects Versions: 10.8.1.2
Reporter: Lukas Eder
Priority: Minor
Quite a few functions are supported in Derby's proprietary JDBC escape syntax:
http://db.apache.org/derby/docs/10.8/ref/rrefjdbc88908.html
Most of those functions can also be used without that syntax, e.g.
SELECT {fn abs(FIELD)}, abs(FIELD) FROM TABLE
will return two times the same value.
This doesn't hold true for TIMESTAMPADD and TIMESTAMPDIFF, which are not
available in the regular syntax according to:
http://db.apache.org/derby/docs/10.8/ref/rrefsqlj29026.html
It would probably be a lot simpler for most users, not to get used to the {fn
...} syntax.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-3893) Implement two-argument functions POWER, ROUND, and TRUNCATE
[ https://issues.apache.org/jira/browse/DERBY-3893?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13028205#comment-13028205 ] Lukas Eder commented on DERBY-3893: --- I think so too, this would be very useful Implement two-argument functions POWER, ROUND, and TRUNCATE --- Key: DERBY-3893 URL: https://issues.apache.org/jira/browse/DERBY-3893 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.4.2.0 Reporter: Kim Haase Priority: Minor DERBY-3063 allowed Derby to implement two-argument numeric functions, but only ATAN2 and MOD seem to have been implemented so far. The remaining functions listed in Section C.1 of the JDBC specification should be provided: POWER(number, power) ROUND(number, places) TRUNCATE(number, places) -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Created] (DERBY-5216) Add support for GREATEST and LEAST functions
Add support for GREATEST and LEAST functions Key: DERBY-5216 URL: https://issues.apache.org/jira/browse/DERBY-5216 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.8.1.2 Reporter: Lukas Eder Priority: Minor A lot of RDMBS support GREATEST and LEAST functions with a variable number of parameters. The underlying RDBMS will then return the greatest/least of n values: 5 = GREATEST(1, 2, 3, 4, 5) 1 = LEAST(1, 2, 3) I think this would be a nice enhancement for Derby, too -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-4379) Let´s add comments to Derby
[ https://issues.apache.org/jira/browse/DERBY-4379?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13011803#comment-13011803 ] Lukas Eder commented on DERBY-4379: --- I am missing this feature as well. Many databases support the Oracle-style COMMENT ON syntax as originally suggested by Rami Ojares. MySQL's syntax seems a bit inconsistent to me, as the syntax for commenting on tables is quite different from the one for commenting on columns. That's not the case with COMMENT ON [object] IS [comment]. Since the reserved word COMMENT is at the beginning of a new command, I don't see how that would interfere with the current Derby syntax definition. It should be possible to introduce that keyword without causing trouble to commands like: CREATE TABLE COMMENT (COMMENT VARCHAR(100)); Let´s add comments to Derby --- Key: DERBY-4379 URL: https://issues.apache.org/jira/browse/DERBY-4379 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 11.0.0.0 Environment: N/A Reporter: Rami Ojares Fix For: 11.0.0.0 I could not find any previous issue about adding comments to Derby. I found one suggestion about it on the web somewhere but not here in Jira. DB2 and Oracle seem to have a separate COMMENT ON clause Eg. COMMENT ON TABLE EMPLOYEE IS 'Reflects first quarter 2000 reorganization' COMMENT ON COLUMN mytable.primarykey IS 'Unique ID from Sequence SEQ_MASTER' MySql on the other hand has a more compact syntax CREATE TABLE FOO (A COMMENT 'This col is A') COMMENT='And here is the table comment' I quess SQL standard does not talk about commenting objects like tables columns etc. (Although I am not sure, maybe someone could prove me wrong here). So I propose we start with syntax like CREATE TABLE TBL_NAME (coldefinition COMMENT 'colcomment' ...) COMMENT ' tablecomment' Column comment could appear anywhere where Column-level-constraint can and the same would apply for table comment. View comment could come after the query in view definition. We would only need to add reserved word COMMENT. (Although it is a common word and most certainly is used by someone as a column or tanle name). It might be that there is already a spot for comments (or should we say remarks) because the DatabaseMetadata returns a column with that name for every attribute. It is always empty now. This feature could take the self-documenting property of derby databases to the next level. I could code this feature but now I would like to know what people think about this issue in here and since I have not been coding Derby before then perhaps a few pointers would be helpful from someone who knows the soucecode of Derby well. -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-5130) Allow for LONG VARCHAR to be compared to LONG VARCHAR
[
https://issues.apache.org/jira/browse/DERBY-5130?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13007057#comment-13007057
]
Lukas Eder commented on DERBY-5130:
---
Hmm, you're right. I agree that it is consistent with the idea of ordering
contexts... I guess you can reject this issue.
Allow for LONG VARCHAR to be compared to LONG VARCHAR
-
Key: DERBY-5130
URL: https://issues.apache.org/jira/browse/DERBY-5130
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.7.1.1
Reporter: Lukas Eder
Priority: Minor
Labels: comparison, derby_triage10_8, sql, typesystem
Interestingly, this is not possible:
select 1 from SYSIBM.SYSDUMMY1 where cast('2' as long varchar) = cast('2'
as long varchar)
Whereas these statements are executable:
select 1 from SYSIBM.SYSDUMMY1 where '2' = '2'
select 1 from SYSIBM.SYSDUMMY1 where cast(cast('2' as long varchar) as
varchar(1)) = cast(cast('2' as long varchar) as varchar(1))
According to the documentation that is the correct behaviour:
http://db.apache.org/derby/docs/10.7/ref/rrefsqlj58560.html
Nevertheless, if casting is possible between LONG VARCHAR and VARCHAR, and
assignment too, then I don't understand why LONG VARCHAR cannot even be
compared to LONG VARCHAR
Note: A similar issue has been open for a long time:
https://issues.apache.org/jira/browse/DERBY-342
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Created: (DERBY-5130) Allow for LONG VARCHAR to be compared to LONG VARCHAR
Allow for LONG VARCHAR to be compared to LONG VARCHAR
-
Key: DERBY-5130
URL: https://issues.apache.org/jira/browse/DERBY-5130
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.7.1.1
Reporter: Lukas Eder
Priority: Minor
Interestingly, this is not possible:
select 1 from SYSIBM.SYSDUMMY1 where cast('2' as long varchar) = cast('2' as
long varchar)
Whereas these statements are executable:
select 1 from SYSIBM.SYSDUMMY1 where '2' = '2'
select 1 from SYSIBM.SYSDUMMY1 where cast(cast('2' as long varchar) as
varchar(1)) = cast(cast('2' as long varchar) as varchar(1))
According to the documentation that is the correct behaviour:
http://db.apache.org/derby/docs/10.7/ref/rrefsqlj58560.html
Nevertheless, if casting is possible between LONG VARCHAR and VARCHAR, and
assignment too, then I don't understand why LONG VARCHAR cannot even be
compared to LONG VARCHAR
Note: A similar issue has been open for a long time:
https://issues.apache.org/jira/browse/DERBY-342
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-5005) Error when fully qualifying a field from a view in an ORDER BY clause
[ https://issues.apache.org/jira/browse/DERBY-5005?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12994245#comment-12994245 ] Lukas Eder commented on DERBY-5005: --- Should I verify the correctness of the fix before closing it? I'd have to wait for a release (10.8, I guess) before I could close it. What's your preferred process? Error when fully qualifying a field from a view in an ORDER BY clause - Key: DERBY-5005 URL: https://issues.apache.org/jira/browse/DERBY-5005 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1 Environment: Windows 7 Reporter: Lukas Eder Assignee: Dag H. Wanvik Priority: Minor Labels: order, sql, syntax, views Fix For: 10.8.0.0 Attachments: 5005.sql, derby-5005.diff, derby-5005.stat, derby-5005b.diff, derby-5005b.stat I have a strange issue that can be reproduced easily with the following objects in schema test: create table a (a integer); insert into a (a) values(1); create view v as select * from a; This works: select test.a.a from test.a where test.a.a 2 order by test.a.a asc; This doesn't work: select test.v.a from test.v where test.v.a 2 order by test.v.a asc; But this does: select test.v.a from test.v where test.v.a 2 order by v.a asc; This is the error I get: Error: 'TEST.V' is not an exposed table name in the scope in which it appears. SQLState: 42X10 ErrorCode: -1 I've tried quite a few SELECT clauses, and I think the ORDER BY clause is the only one having this issue. -- This message is automatically generated by JIRA. - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-5005) Error when fully qualifying a field from a view in an ORDER BY clause
[ https://issues.apache.org/jira/browse/DERBY-5005?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12992506#comment-12992506 ] Lukas Eder commented on DERBY-5005: --- Excellent guys! That was really quick! :-) Error when fully qualifying a field from a view in an ORDER BY clause - Key: DERBY-5005 URL: https://issues.apache.org/jira/browse/DERBY-5005 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.7.1.1 Environment: Windows 7 Reporter: Lukas Eder Assignee: Dag H. Wanvik Priority: Minor Labels: order, sql, syntax, views Attachments: 5005.sql, derby-5005.diff, derby-5005.stat I have a strange issue that can be reproduced easily with the following objects in schema test: create table a (a integer); insert into a (a) values(1); create view v as select * from a; This works: select test.a.a from test.a where test.a.a 2 order by test.a.a asc; This doesn't work: select test.v.a from test.v where test.v.a 2 order by test.v.a asc; But this does: select test.v.a from test.v where test.v.a 2 order by v.a asc; This is the error I get: Error: 'TEST.V' is not an exposed table name in the scope in which it appears. SQLState: 42X10 ErrorCode: -1 I've tried quite a few SELECT clauses, and I think the ORDER BY clause is the only one having this issue. -- This message is automatically generated by JIRA. - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-5006) Handle NULL literals like any other database
[ https://issues.apache.org/jira/browse/DERBY-5006?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12991400#comment-12991400 ] Lukas Eder commented on DERBY-5006: --- Hi Rick! Thanks for your feedback. I'm happy if I can help you guys. I'm planning to create more systematic integration tests for these things in jOOQ before the next release, to be sure I can provide a stable integration of Derby with jOOQ. I'll let you know if I find any more inference problems. Handle NULL literals like any other database Key: DERBY-5006 URL: https://issues.apache.org/jira/browse/DERBY-5006 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.7.1.1 Reporter: Lukas Eder Priority: Minor Labels: null, typesystem Most RDMBS allow for typeless NULL values / literals just about anywhere in a SQL statement. I am not aware of SQL standards explicitly encouraging the enforcement of casts on NULL values and literals. Even DB2, the mother of strongly typed databases has finally given up type enforcement on NULL values / literals in version 9.7: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.wn.doc/doc/i0054263.html I wonder, whether this would be a nice way to go for Derby as well? I am asking this from the perspective of a database abstraction library developer (http://jooq.sourceforge.net). I find it extremely difficult to cast NULL values to the correct type in every occasion, as in Java, null does not have a type. A good example for this is: SELECT cast(null as ...) FROM SYSIBM.SYSDUMMY1 From the Java application's point of view, any type is OK, because it won't matter. But then, why would it matter to Derby? Another good example to me is this: INSERT INTO X (ID, NAME, NUMBER) SELECT (10, null, null) FROM SYSIBM.SYSDUMMY1 This won't work. I'll need to rewrite it to something like this: INSERT INTO X (ID, NAME, NUMBER) SELECT (10, CAST(null AS VARCHAR(100)), CAST(null AS INTEGER)) FROM SYSIBM.SYSDUMMY1 When actually Derby could infer the types of null. Type inference is done sometimes. This will work: INSERT INTO X (ID, NAME, NUMBER) VALUES (10, null, null) What do you guys think? -- This message is automatically generated by JIRA. - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-5006) Handle NULL literals like any other database
[ https://issues.apache.org/jira/browse/DERBY-5006?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12991490#comment-12991490 ] Lukas Eder commented on DERBY-5006: --- Thanks for the insight! I am sure that SQL is a beast, internally. It looks so simple... just infer the type, c'mon... :) Handle NULL literals like any other database Key: DERBY-5006 URL: https://issues.apache.org/jira/browse/DERBY-5006 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.7.1.1 Reporter: Lukas Eder Priority: Minor Labels: null, typesystem Most RDMBS allow for typeless NULL values / literals just about anywhere in a SQL statement. I am not aware of SQL standards explicitly encouraging the enforcement of casts on NULL values and literals. Even DB2, the mother of strongly typed databases has finally given up type enforcement on NULL values / literals in version 9.7: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.wn.doc/doc/i0054263.html I wonder, whether this would be a nice way to go for Derby as well? I am asking this from the perspective of a database abstraction library developer (http://jooq.sourceforge.net). I find it extremely difficult to cast NULL values to the correct type in every occasion, as in Java, null does not have a type. A good example for this is: SELECT cast(null as ...) FROM SYSIBM.SYSDUMMY1 From the Java application's point of view, any type is OK, because it won't matter. But then, why would it matter to Derby? Another good example to me is this: INSERT INTO X (ID, NAME, NUMBER) SELECT (10, null, null) FROM SYSIBM.SYSDUMMY1 This won't work. I'll need to rewrite it to something like this: INSERT INTO X (ID, NAME, NUMBER) SELECT (10, CAST(null AS VARCHAR(100)), CAST(null AS INTEGER)) FROM SYSIBM.SYSDUMMY1 When actually Derby could infer the types of null. Type inference is done sometimes. This will work: INSERT INTO X (ID, NAME, NUMBER) VALUES (10, null, null) What do you guys think? -- This message is automatically generated by JIRA. - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Created: (DERBY-5006) Handle NULL literals like any other database
Handle NULL literals like any other database Key: DERBY-5006 URL: https://issues.apache.org/jira/browse/DERBY-5006 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.7.1.1 Reporter: Lukas Eder Priority: Minor Most RDMBS allow for typeless NULL values / literals just about anywhere in a SQL statement. I am not aware of SQL standards explicitly encouraging the enforcement of casts on NULL values and literals. Even DB2, the mother of strongly typed databases has finally given up type enforcement on NULL values / literals in version 9.7: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.wn.doc/doc/i0054263.html I wonder, whether this would be a nice way to go for Derby as well? I am asking this from the perspective of a database abstraction library developer (http://jooq.sourceforge.net). I find it extremely difficult to cast NULL values to the correct type in every occasion, as in Java, null does not have a type. A good example for this is: SELECT cast(null as ...) FROM SYSIBM.SYSDUMMY1 From the Java application's point of view, any type is OK, because it won't matter. But then, why would it matter to Derby? Another good example to me is this: INSERT INTO X (ID, NAME, NUMBER) SELECT (10, null, null) FROM SYSIBM.SYSDUMMY1 This won't work. I'll need to rewrite it to something like this: INSERT INTO X (ID, NAME, NUMBER) SELECT (10, CAST(null AS VARCHAR(100)), CAST(null AS INTEGER)) FROM SYSIBM.SYSDUMMY1 When actually Derby could infer the types of null. Type inference is done sometimes. This will work: INSERT INTO X (ID, NAME, NUMBER) VALUES (10, null, null) What do you guys think? -- This message is automatically generated by JIRA. - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Commented: (DERBY-2374) UNION PROBLEM WITH PARENTHESIS
[ https://issues.apache.org/jira/browse/DERBY-2374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12990938#comment-12990938 ] Lukas Eder commented on DERBY-2374: --- Thanks for all the detailed analyses. This would be a nice to have fix. In the mean-time, I'll try to create workarounds for that. UNION PROBLEM WITH PARENTHESIS -- Key: DERBY-2374 URL: https://issues.apache.org/jira/browse/DERBY-2374 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.1.3.3, 10.2.2.0, 10.3.3.1, 10.4.2.1, 10.5.3.1, 10.6.1.0 Reporter: Kenneth Gee Priority: Minor Attachments: DERBY2374_UnionWithParentheseOnLeftRS_diff_patch1.txt The following query shows the error using the Derby demo toursDB: SELECT FLIGHT_ID , SEGMENT_NUMBER , ARRIVE_TIME FROM ( (SELECT HW.FLIGHT_ID , HW.SEGMENT_NUMBER , HW.ARRIVE_TIME FROM FLIGHTS HW) UNION (SELECT SW.FLIGHT_ID , SW.SEGMENT_NUMBER , SW.ARRIVE_TIME FROM FLIGHTS SW) ) SRC; ERROR 42X01: Syntax error: Encountered UNION at line 4, column 12. The following query works: SELECT FLIGHT_ID , SEGMENT_NUMBER , ARRIVE_TIME FROM ( SELECT HW.FLIGHT_ID , HW.SEGMENT_NUMBER , HW.ARRIVE_TIME FROM FLIGHTS HW UNION SELECT SW.FLIGHT_ID , SW.SEGMENT_NUMBER , SW.ARRIVE_TIME FROM FLIGHTS SW ) SRC; -- This message is automatically generated by JIRA. - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Issue Comment Edited: (DERBY-2374) UNION PROBLEM WITH PARENTHESIS
[ https://issues.apache.org/jira/browse/DERBY-2374?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=12990938#comment-12990938 ] Lukas Eder edited comment on DERBY-2374 at 2/5/11 8:41 AM: --- Thanks for all the detailed analyses. This would be a nice to have fix. In the mean-time, I'll try to create workarounds for that. Please note that it also affects version 10.7.1.1 was (Author: lukas.eder): Thanks for all the detailed analyses. This would be a nice to have fix. In the mean-time, I'll try to create workarounds for that. UNION PROBLEM WITH PARENTHESIS -- Key: DERBY-2374 URL: https://issues.apache.org/jira/browse/DERBY-2374 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.1.3.3, 10.2.2.0, 10.3.3.1, 10.4.2.1, 10.5.3.1, 10.6.1.0 Reporter: Kenneth Gee Priority: Minor Attachments: DERBY2374_UnionWithParentheseOnLeftRS_diff_patch1.txt The following query shows the error using the Derby demo toursDB: SELECT FLIGHT_ID , SEGMENT_NUMBER , ARRIVE_TIME FROM ( (SELECT HW.FLIGHT_ID , HW.SEGMENT_NUMBER , HW.ARRIVE_TIME FROM FLIGHTS HW) UNION (SELECT SW.FLIGHT_ID , SW.SEGMENT_NUMBER , SW.ARRIVE_TIME FROM FLIGHTS SW) ) SRC; ERROR 42X01: Syntax error: Encountered UNION at line 4, column 12. The following query works: SELECT FLIGHT_ID , SEGMENT_NUMBER , ARRIVE_TIME FROM ( SELECT HW.FLIGHT_ID , HW.SEGMENT_NUMBER , HW.ARRIVE_TIME FROM FLIGHTS HW UNION SELECT SW.FLIGHT_ID , SW.SEGMENT_NUMBER , SW.ARRIVE_TIME FROM FLIGHTS SW ) SRC; -- This message is automatically generated by JIRA. - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Created: (DERBY-5005) Error when fully qualifying a field from a view in an ORDER BY clause
Error when fully qualifying a field from a view in an ORDER BY clause - Key: DERBY-5005 URL: https://issues.apache.org/jira/browse/DERBY-5005 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.7.1.1 Environment: Windows 7 Reporter: Lukas Eder Priority: Minor I have a strange issue that can be reproduced easily with the following objects in schema test: create table a (a integer); insert into a (a) values(1); create view v as select * from a; This works: select test.a.a from test.a where test.a.a 2 order by test.a.a asc; This doesn't work: select test.v.a from test.v where test.v.a 2 order by test.v.a asc; But this does: select test.v.a from test.v where test.v.a 2 order by v.a asc; This is the error I get: Error: 'TEST.V' is not an exposed table name in the scope in which it appears. SQLState: 42X10 ErrorCode: -1 I've tried quite a few SELECT caluses, and I think the ORDER BY clause is the only one having this issue. -- This message is automatically generated by JIRA. - For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-5005) Error when fully qualifying a field from a view in an ORDER BY clause
[ https://issues.apache.org/jira/browse/DERBY-5005?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Lukas Eder updated DERBY-5005: -- Description: I have a strange issue that can be reproduced easily with the following objects in schema test: create table a (a integer); insert into a (a) values(1); create view v as select * from a; This works: select test.a.a from test.a where test.a.a 2 order by test.a.a asc; This doesn't work: select test.v.a from test.v where test.v.a 2 order by test.v.a asc; But this does: select test.v.a from test.v where test.v.a 2 order by v.a asc; This is the error I get: Error: 'TEST.V' is not an exposed table name in the scope in which it appears. SQLState: 42X10 ErrorCode: -1 I've tried quite a few SELECT clauses, and I think the ORDER BY clause is the only one having this issue. was: I have a strange issue that can be reproduced easily with the following objects in schema test: create table a (a integer); insert into a (a) values(1); create view v as select * from a; This works: select test.a.a from test.a where test.a.a 2 order by test.a.a asc; This doesn't work: select test.v.a from test.v where test.v.a 2 order by test.v.a asc; But this does: select test.v.a from test.v where test.v.a 2 order by v.a asc; This is the error I get: Error: 'TEST.V' is not an exposed table name in the scope in which it appears. SQLState: 42X10 ErrorCode: -1 I've tried quite a few SELECT caluses, and I think the ORDER BY clause is the only one having this issue. Error when fully qualifying a field from a view in an ORDER BY clause - Key: DERBY-5005 URL: https://issues.apache.org/jira/browse/DERBY-5005 Project: Derby Issue Type: Bug Components: SQL Affects Versions: 10.7.1.1 Environment: Windows 7 Reporter: Lukas Eder Priority: Minor Labels: order, sql, syntax, views I have a strange issue that can be reproduced easily with the following objects in schema test: create table a (a integer); insert into a (a) values(1); create view v as select * from a; This works: select test.a.a from test.a where test.a.a 2 order by test.a.a asc; This doesn't work: select test.v.a from test.v where test.v.a 2 order by test.v.a asc; But this does: select test.v.a from test.v where test.v.a 2 order by v.a asc; This is the error I get: Error: 'TEST.V' is not an exposed table name in the scope in which it appears. SQLState: 42X10 ErrorCode: -1 I've tried quite a few SELECT clauses, and I think the ORDER BY clause is the only one having this issue. -- This message is automatically generated by JIRA. - For more information on JIRA, see: http://www.atlassian.com/software/jira
