[jira] [Commented] (DERBY-1576) Extend the CASE expression syntax for simple case

2014-05-30 Thread ASF subversion and git services (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14013372#comment-14013372
 ] 

ASF subversion and git services commented on DERBY-1576:


Commit 1598472 from [~knutanders] in branch 'code/trunk'
[ https://svn.apache.org/r1598472 ]

DERBY-1576: Extend the CASE expression syntax for simple case

Allow untyped parameters in the case operand.

 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
Assignee: Knut Anders Hatlen
Priority: Minor
  Labels: derby_triage10_11
 Attachments: d1576-1a.diff, d1576-2a.diff, d1576-3a.diff, 
 d1576-4a.diff, simple-simple.diff


 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

2014-05-30 Thread ASF subversion and git services (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14013470#comment-14013470
 ] 

ASF subversion and git services commented on DERBY-1576:


Commit 1598529 from [~knutanders] in branch 'code/trunk'
[ https://svn.apache.org/r1598529 ]

DERBY-1576: Fix javadoc warning

 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
Assignee: Knut Anders Hatlen
Priority: Minor
  Labels: derby_triage10_11
 Attachments: d1576-1a.diff, d1576-2a.diff, d1576-3a.diff, 
 d1576-4a.diff, simple-simple.diff


 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

2014-05-28 Thread ASF subversion and git services (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14011029#comment-14011029
 ] 

ASF subversion and git services commented on DERBY-1576:


Commit 1597979 from [~knutanders] in branch 'code/trunk'
[ https://svn.apache.org/r1597979 ]

DERBY-1576: Extend the CASE expression syntax for simple case

Cache the case operand so that it is only evaluated once per
evaluation of the CASE expression.

 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
Assignee: Knut Anders Hatlen
Priority: Minor
  Labels: derby_triage10_11
 Attachments: d1576-1a.diff, d1576-2a.diff, d1576-3a.diff, 
 simple-simple.diff


 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

2014-05-23 Thread ASF subversion and git services (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14007124#comment-14007124
 ] 

ASF subversion and git services commented on DERBY-1576:


Commit 1597082 from [~knutanders] in branch 'code/trunk'
[ https://svn.apache.org/r1597082 ]

DERBY-1576: Extend the CASE expression syntax for simple case

Add test cases for subqueries.

 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
Assignee: Knut Anders Hatlen
Priority: Minor
  Labels: derby_triage10_11
 Attachments: d1576-1a.diff, d1576-2a.diff, simple-simple.diff


 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

2014-05-21 Thread ASF subversion and git services (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14004422#comment-14004422
 ] 

ASF subversion and git services commented on DERBY-1576:


Commit 1596492 from [~knutanders] in branch 'code/trunk'
[ https://svn.apache.org/r1596492 ]

DERBY-1576: Extend the CASE expression syntax for simple case

Forbid untyped parameters in the case operand.

 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
Assignee: Knut Anders Hatlen
Priority: Minor
  Labels: derby_triage10_11
 Attachments: d1576-1a.diff, d1576-2a.diff, simple-simple.diff


 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

2014-05-19 Thread ASF subversion and git services (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14001486#comment-14001486
 ] 

ASF subversion and git services commented on DERBY-1576:


Commit 1595778 from [~knutanders] in branch 'code/trunk'
[ https://svn.apache.org/r1595778 ]

DERBY-1576: Extend the CASE expression syntax for simple case

Implement simple case as described by the SQL:1999 standard, including the
SQL:2003 extension F262 Extended CASE expression, and the SQL:2011
extension F263 Comma-separated predicates in simple CASE expression.

 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
Assignee: Knut Anders Hatlen
Priority: Minor
  Labels: derby_triage10_11
 Attachments: d1576-1a.diff, simple-simple.diff


 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

2014-05-19 Thread Knut Anders Hatlen (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14001492#comment-14001492
 ] 

Knut Anders Hatlen commented on DERBY-1576:
---

Thanks, Dag. I've committed the 1a patch as a first increment.

 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
Assignee: Knut Anders Hatlen
Priority: Minor
  Labels: derby_triage10_11
 Attachments: d1576-1a.diff, simple-simple.diff


 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

2014-05-19 Thread Christian d'Heureuse (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14001516#comment-14001516
 ] 

Christian d'Heureuse commented on DERBY-1576:
-

Thanks, Knut. It has been 8 years since I created this Jira issue, but now we 
have got more than I asked for... :)

 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
Assignee: Knut Anders Hatlen
Priority: Minor
  Labels: derby_triage10_11
 Attachments: d1576-1a.diff, simple-simple.diff


 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

2014-05-16 Thread Dag H. Wanvik (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13999869#comment-13999869
 ] 

Dag H. Wanvik commented on DERBY-1576:
--

Thanks, Knut! Nice cleanup and improvement; +1

 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
Assignee: Knut Anders Hatlen
Priority: Minor
  Labels: derby_triage10_11
 Attachments: d1576-1a.diff, simple-simple.diff


 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

2014-05-15 Thread Knut Anders Hatlen (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13992572#comment-13992572
 ] 

Knut Anders Hatlen commented on DERBY-1576:
---

There is also a difference between the simple case syntax in SQL:2003 and 
SQL:2011. In the SQL:2003 standard, a simple when clause can only have one 
when operand, whereas SQL:2011 allows the simple when clause to contain a 
when operand list. The example I gave in my 30/Apr/14 comment, used the 2011 
syntax.

I agree that it would be fine to only implement the SQL:1999 syntax in this 
issue, as that would be a useful improvement on its own, and that's the version 
of the standard specified in the original request.

 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
 Attachments: simple-simple.diff


 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

2014-04-30 Thread Lukas Eder (JIRA) 

[ 
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

2014-04-30 Thread Knut Anders Hatlen (JIRA) 

[ 
https://issues.apache.org/jira/browse/DERBY-1576?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=13985417#comment-13985417
 ] 

Knut Anders Hatlen commented on DERBY-1576:
---

I don't think there's any technical reason why this hasn't been done, just that 
no one has felt strongly enough about it to actually do the work yet. You may 
be right that simple case can be implemented mostly in the parser. The simple 
case syntax in the SQL standard is somewhat more involved than what's the issue 
description, though, and allows expressions such as:

{noformat}
  CASE 'test'
 WHEN IS NULL THEN 0
 WHEN BETWEEN('abc', 'def'), LIKE 'x%', 'zzz' THEN 1
 ELSE 2
{noformat}

 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

2014-04-30 Thread Lukas Eder (JIRA) 

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