guiyanakuang opened a new issue, #14790: URL: https://github.com/apache/doris/issues/14790
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues. ### Version The current version, more precisely related to this pr [\[fix\](agg)having clause should use column name first then alias](https://github.com/apache/doris/pull/14536). ### What's Wrong? The having clause should not use the column name first then the alias, otherwise it will be incompatible with mysql. This will prevent migration to doris using a compatible mysql engine. Let me explain the problem by looking at the mysql jdbc source code (5.1.x branch). This is a public api used to search for table names. ```java public ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) throws SQLException ``` https://github.com/mysql/mysql-connector-j/blob/ad86f36e100e104cd926c6b81c8cab9565750116/src/com/mysql/jdbc/DatabaseMetaDataUsingInfoSchema.java#L1128-L1158 This can be understood by using the following sql to query `INFORMATION_SCHEMA.TABLES`. ```sql SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, CASE WHEN TABLE_TYPE = 'BASE TABLE' THEN CASE WHEN TABLE_SCHEMA = 'mysql' OR TABLE_SCHEMA = 'performance_schema' THEN 'SYSTEM TABLE' ELSE 'TABLE' END WHEN TABLE_TYPE = 'TEMPORARY' THEN 'LOCAL_TEMPORARY' ELSE TABLE_TYPE END AS TABLE_TYPE, TABLE_COMMENT AS REMARKS, NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME, NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE ? AND TABLE_NAME LIKE ? HAVING TABLE_TYPE IN (?, ?) ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME; ``` `HAVING TABLE_TYPE IN (? , ?) ` is intended to filter the alias field TABLE_TYPE, not the field of the `NFORMATION_SCHEMA.TABLES` table. The real mysql works the same way. After this [pr](https://github.com/apache/doris/pull/14536) merge, the above api no longer works properly. ### What You Expected? Compatible with mysql, get mysql jdbc connector working again. ### How to Reproduce? ```sql SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, CASE WHEN TABLE_TYPE = 'BASE TABLE' THEN CASE WHEN TABLE_SCHEMA = 'mysql' OR TABLE_SCHEMA = 'performance_schema' THEN 'SYSTEM TABLE' ELSE 'TABLE' END WHEN TABLE_TYPE = 'TEMPORARY' THEN 'LOCAL_TEMPORARY' ELSE TABLE_TYPE END AS TABLE_TYPE, TABLE_COMMENT AS REMARKS, NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME, NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE ? -- Fill in based on the validation environment AND TABLE_NAME LIKE ? HAVING TABLE_TYPE IN ('TABLE') -- Possible values for TABLE_TYPE: https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html#getTableTypes-- ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME; ``` ### Anything Else? _No response_ ### Are you willing to submit PR? - [X] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
