Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
jirapatk opened this issue Jan 21, 2025 · 0 comments

Comments

@jirapatk
Copy link

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant