[
https://issues.apache.org/jira/browse/DERBY-6983?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=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 <query specification>*
...
4) Case:
a) If the <select list> “*” is simply contained in a <table subquery> that is
immediately contained in an
<exists predicate>, then the <select list> is equivalent to a <value
expression> that is an arbitrary
<literal>.
{color:#FF0000}b) Otherwise, the <select list> “*” is equivalent to a <value
expression> sequence in which each <value{color}
{color:#FF0000}expression> is a column reference that references a column of T
and each column of T is referenced{color}
{color:#FF0000}exactly once. The columns are referenced in the ascending
sequence of their ordinal position within{color}
{color:#FF0000}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)
