[ https://issues.apache.org/jira/browse/DERBY-6983?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16376777#comment-16376777 ]
Rick Hillegas commented on DERBY-6983: -------------------------------------- Thanks for logging this bug, Lukas. It is the inner * which bothers Derby. The following query also fails: {noformat} SELECT tablename FROM sys.systables WHERE tablename IN ( SELECT * FROM ( SELECT 'SYSTABLES' t FROM sysibm.sysdummy1 ) t ) ; {noformat} The following workaround works for me: {noformat} SELECT * FROM sys.systables WHERE tablename IN ( SELECT t FROM ( SELECT 'SYSTABLES' t FROM sysibm.sysdummy1 ) t ) ; {noformat} > 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)