[DERBY-6983] Support SELECT * for IN predicate subqueries when it is "obvious" that the asterisk expands to exactly one column

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.14.1.0
• Component/s: SQL
• Urgency: Normal
• Issue & fix info: Workaround attached
• Bug behavior facts: Deviation from standard, Seen in production
• Assignee: Unassigned
• Reporter: Lukas Eder
• Created: 26/Feb/18 09:39
• Updated: 01/Mar/18 09:47

Description

The following query is not allowed in Derby:

SELECT *
FROM sys.systables
WHERE tablename IN (
  SELECT *
  FROM (
    SELECT 'SYSTABLES' t
    FROM sysibm.sysdummy1
  ) t
)

The error I'm getting is:

'SELECT *' only allowed in EXISTS and NOT EXISTS subqueries.

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.