JDBC call to DatabaseMetaData.getExportedKeys fails

[camarena]

Is this feature supported? I'm getting this error:

║  ║  ║ java.sql.SQLSyntaxErrorException: unsupported: join with USING(column_list) clause for complex queries
║  ║  ║ 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
║  ║  ║ 	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
║  ║  ║ 	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
║  ║  ║ 	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1009)
║  ║  ║ 	at com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema.executeMetadataQuery(DatabaseMetaDataUsingInfoSchema.java:70)
║  ║  ║ 	at com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema.getExportedKeys(DatabaseMetaDataUsingInfoSchema.java:403)

I'm using JDBC driver "mysql" % "mysql-connector-java" % "8.0.28"

[fooforge]

@camarena I would need to see the exact SQL query causing the error to assess if this is a compatibility issue or not. If you don't want to share it publicly, feel free to reach out via https://support.planetscale.com/ and we'll chat there. :-)

[camarena]

The query is generated by the JDBC driver itself. This is the way to have the issue:

    public static void main(String[] args) throws SQLException {
        Connection c = DriverManager.getConnection(url, user, password);
        DatabaseMetaData m = c.getMetaData();
        m.getExportedKeys(null, null, tableName);
    }

The error is:

Exception in thread "main" java.sql.SQLSyntaxErrorException: unsupported: join with USING(column_list) clause for complex queries
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1009)
	at com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema.executeMetadataQuery(DatabaseMetaDataUsingInfoSchema.java:70)
	at com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema.getExportedKeys(DatabaseMetaDataUsingInfoSchema.java:403)
	at com.stepzen.reproduce.Main.main(Main.java:16)

[camarena]

@fooforge ^^

[fooforge]

@camarena We were able to reproduce this. It's one of the scenarios on Vitess's list of unsupported cases for which there's currently no workaround other than altering the query.

To make it work, you would need to change the USING construct against an ON, turn the table name into a variable, and then use your own function instead of DatabaseMetaData's getExportedKeys.

Original SQL

SELECT 
  DISTINCT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT, 
  NULL AS PKTABLE_SCHEM, 
  A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, 
  A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, 
  A.TABLE_SCHEMA AS FKTABLE_CAT, 
  NULL AS FKTABLE_SCHEM, 
  A.TABLE_NAME AS FKTABLE_NAME, 
  A.COLUMN_NAME AS FKCOLUMN_NAME, 
  A.ORDINAL_POSITION AS KEY_SEQ, 
  CASE WHEN R.UPDATE_RULE = 'CASCADE' THEN 0 WHEN R.UPDATE_RULE = 'SET NULL' THEN 2 WHEN R.UPDATE_RULE = 'SET DEFAULT' THEN 4 WHEN R.UPDATE_RULE = 'RESTRICT' THEN 1 WHEN R.UPDATE_RULE = 'NO ACTION' THEN 1 ELSE 1 END AS UPDATE_RULE, 
  CASE WHEN R.DELETE_RULE = 'CASCADE' THEN 0 WHEN R.DELETE_RULE = 'SET NULL' THEN 2 WHEN R.DELETE_RULE = 'SET DEFAULT' THEN 4 WHEN R.DELETE_RULE = 'RESTRICT' THEN 1 WHEN R.DELETE_RULE = 'NO ACTION' THEN 1 ELSE 1 END AS DELETE_RULE, 
  A.CONSTRAINT_NAME AS FK_NAME, 
  TC.CONSTRAINT_NAME AS PK_NAME, 
  7 AS DEFERRABILITY 
FROM 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE A 
  JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (
    TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME
  ) 
  JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON (
    R.CONSTRAINT_NAME = B.CONSTRAINT_NAME 
    AND R.TABLE_NAME = B.TABLE_NAME 
    AND R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA
  ) 
  LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON (
    A.REFERENCED_TABLE_SCHEMA = TC.TABLE_SCHEMA 
    AND A.REFERENCED_TABLE_NAME = TC.TABLE_NAME 
    AND TC.CONSTRAINT_TYPE IN ('UNIQUE', 'PRIMARY KEY')
  ) 
WHERE 
  B.CONSTRAINT_TYPE = 'FOREIGN KEY' 
  AND A.REFERENCED_TABLE_NAME = 'dept_manager' 
ORDER BY 
  FKTABLE_NAME, 
  FKTABLE_NAME, 
  KEY_SEQ;

Updated SQL

SELECT 
  DISTINCT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT, 
  NULL AS PKTABLE_SCHEM, 
  A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, 
  A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, 
  A.TABLE_SCHEMA AS FKTABLE_CAT, 
  NULL AS FKTABLE_SCHEM, 
  A.TABLE_NAME AS FKTABLE_NAME, 
  A.COLUMN_NAME AS FKCOLUMN_NAME, 
  A.ORDINAL_POSITION AS KEY_SEQ, 
  CASE WHEN R.UPDATE_RULE = 'CASCADE' THEN 0 WHEN R.UPDATE_RULE = 'SET NULL' THEN 2 WHEN R.UPDATE_RULE = 'SET DEFAULT' THEN 4 WHEN R.UPDATE_RULE = 'RESTRICT' THEN 1 WHEN R.UPDATE_RULE = 'NO ACTION' THEN 1 ELSE 1 END AS UPDATE_RULE, 
  CASE WHEN R.DELETE_RULE = 'CASCADE' THEN 0 WHEN R.DELETE_RULE = 'SET NULL' THEN 2 WHEN R.DELETE_RULE = 'SET DEFAULT' THEN 4 WHEN R.DELETE_RULE = 'RESTRICT' THEN 1 WHEN R.DELETE_RULE = 'NO ACTION' THEN 1 ELSE 1 END AS DELETE_RULE, 
  A.CONSTRAINT_NAME AS FK_NAME, 
  TC.CONSTRAINT_NAME AS PK_NAME, 
  7 AS DEFERRABILITY 
FROM 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE A 
  JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B ON (
    A.CONSTRAINT_NAME = B.CONSTRAINT_NAME 
    AND A.TABLE_NAME = B.TABLE_NAME 
    AND A.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA
  ) 
  JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON (
    R.CONSTRAINT_NAME = B.CONSTRAINT_NAME 
    AND R.TABLE_NAME = B.TABLE_NAME 
    AND R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA
  ) 
  LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON (
    A.REFERENCED_TABLE_SCHEMA = TC.TABLE_SCHEMA 
    AND A.REFERENCED_TABLE_NAME = TC.TABLE_NAME 
    AND TC.CONSTRAINT_TYPE IN ('UNIQUE', 'PRIMARY KEY')
  ) 
WHERE 
  B.CONSTRAINT_TYPE = 'FOREIGN KEY' 
  AND A.REFERENCED_TABLE_NAME = :table
ORDER BY 
  FKTABLE_NAME, 
  FKTABLE_NAME, 
  KEY_SEQ;

Worth noting is that we have a dedicated group of people working on increasing Vitess's compatibility. And, while I'm not able to say if or when we're going to get to it at this time, I can share that we have seen several ORMs throwing exceptions for these types of queries, so we hope to be able to get to it soon.

Thanks to @jgreet and @orware who have done the heavy lifting here as my Java chops are basic at best. 😄

[camarena]

Thank for your help. I hope you get time to fix it at some point. For now we'll have to remove Vitess from the list of supported DBs. We rely on JDBC as the standard to use. We can't justify having a different jdbc driver for Vitess or to have non JDBC compliant code in our product.
Thanks again.

[fooforge]

@camarena Good news! PlanetScale should now support this query construct. If you could give this another try and report back, that would be great.