Skip to content

ER_BAD_FIELD_ERROR: Unknown column 'i.EXPRESSION' in query getConstraintColumns #342

Description

@jirapatk

I'm experiencing an issue with the database analysis tool when running a query to get constraint columns. The error message is:
Local gateway: Internal Server Error: Error ER_BAD_FIELD_ERROR on query getConstraintColumns: Unknown column 'i.EXPRESSION' in 'field list'
The query that's causing the error is:

sql

SELECT
c.CONSTRAINT_NAME AS constraint_name,
c.CONSTRAINT_TYPE AS constraint_type,
c.TABLE_SCHEMA AS table_schema,
c.TABLE_NAME AS table_name,
cc.ORDINAL_POSITION AS column_index,
cc.COLUMN_NAME AS column_name,
null AS column_expr,
cc.REFERENCED_TABLE_SCHEMA AS ref_schema,
cc.REFERENCED_TABLE_NAME AS ref_table,
cc.REFERENCED_COLUMN_NAME AS ref_column
FROM
information_schema.TABLE_CONSTRAINTS c
LEFT JOIN information_schema.KEY_COLUMN_USAGE cc
ON cc.CONSTRAINT_CATALOG = c.CONSTRAINT_CATALOG
AND cc.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA
AND cc.CONSTRAINT_NAME = c.CONSTRAINT_NAME
AND cc.TABLE_NAME = c.TABLE_NAME
WHERE
c.CONSTRAINT_TYPE != 'CHECK'
AND c.TABLE_SCHEMA = 'hqms'
UNION
SELECT
i.INDEX_NAME AS constraint_name,
"INDEX" AS constraint_type,
i.TABLE_SCHEMA AS table_schema,
i.TABLE_NAME AS table_name,
i.SEQ_IN_INDEX AS column_index,
i.COLUMN_NAME AS column_name,
i.EXPRESSION AS column_expr,
null AS ref_schema,
null AS ref_table,
null AS ref_column
FROM
information_schema.STATISTICS i
LEFT JOIN information_schema.TABLE_CONSTRAINTS c
ON c.TABLE_SCHEMA = i.TABLE_SCHEMA
AND c.TABLE_NAME = i.TABLE_NAME
AND c.CONSTRAINT_NAME = i.INDEX_NAME
WHERE
c.CONSTRAINT_TYPE IS NULL
AND i.TABLE_SCHEMA = 'hqms'
ORDER BY
table_schema, table_name, constraint_name, column_index;

Image

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions