[DERBY-7170] Error [30000] [42Y07]: Schema 'SA' does not exist when joining SYS.SYSTABLES to SYS.SYSSCHEMAS when aliasing the SYSCHEMAS table

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.17.1.0
• Component/s: SQL
• Urgency: Normal
• Issue & fix info: Repro attached, Workaround attached
• Bug behavior facts: Seen in production
• Assignee: Unassigned
• Reporter: Lukas Eder
• Created: 19/Sep/24 09:10
• Updated: 19/Sep/24 14:35

Description

On a fresh database connection using org.apache.derby.jdbc.EmbeddedDriver, using username "sa", and this URL, for example: "jdbc:derby:C:\Users\lukas\.data\derby\test", I'm trying to run this query in Dbeaver:

 SELECT
  a.SCHEMANAME,
  SYS.SYSTABLES.TABLENAME
FROM
  SYS.SYSTABLES
JOIN SYS.SYSSCHEMAS AS a ON
  SYS.SYSTABLES.SCHEMAID = a.SCHEMAID

It fails with this error:

> SQL Error [30000] [42Y07]: Schema 'SA' does not exist

Avoiding the alias is a valid workaround:

SELECT
  SYS.SYSSCHEMAS.SCHEMANAME,
  SYS.SYSTABLES.TABLENAME
FROM
  SYS.SYSTABLES
JOIN SYS.SYSSCHEMAS ON
  SYS.SYSTABLES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID