atifiu opened a new issue, #57773: URL: https://github.com/apache/doris/issues/57773
### 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 master (observed on recent commits as well as earlier releases) ### What's Wrong? Temporary table names are internally constructed by concatenating an id, a separator (TEMPORARY_TABLE_SIGN; i.e. "_#TEMP#_"), and the table name. When lower_case_table_names = 1, Doris internally normalizes table names to lower-case in some functions, but TEMPORARY_TABLE_SIGN and some name lookups remain upper-case sensitive. This causes problems locating internal temp tables when their name casing is changed by config (e.g., storage looks for "_#temp#_", code expects "_#TEMP#_", or vice versa). This causes errors such as Unknown table '..._#TEMP#_foo'. Example error outputs: --- SHOW FRONTEND CONFIG like '%case%'; ``` Key |Value|Type|IsMutable|MasterOnly|Comment| ----------------------+-----+----+---------+----------+-------+ lower_case_table_names|1 |int |false |true | | ``` ```sql create temporary table contacts_test_gkl like emp_analytics_dev_public.contacts; INSERT INTO contacts_test_gkl (CLIENT_CODE, CLIENT_NAME, CLIENT_RANK, id, name, LOW_CONFIDENCE_FLAG, ME_ID, PERSON_ID, MASTERED_PERSON_ID, HOUSEHOLD_ID, EVENT_TYPE, CHANNEL_GRAIN, CHANNEL_GRAIN_RN, CHANNEL_NAME, CHANNEL_TYPE, CHANNEL_RANK, CONTACT_DATE, CONTACT_YM, CONTACT_TYPE, CONTROL_GROUP, NEW_FORMER, BIRTH_DATE, CONTACT_AGE, CONTACT_AGE_RANGE, GENDER, LANGUAGE, INCOME, ZIP_CODE, CITY, STATE, PAYOR_CATEGORY, CHILDREN_PRESENT, MARITAL_STATUS, NICHE, LKP_ID, LINKED_PERSON_ID, LINKED_MASTERED_PERSON_ID, UNIVERSAL_PERSON_ID, PERSON_TYPE) SELECT 'C001', 'HealthCare Corp', null, 'CAM001', 'Summer Health Campaign', 'Yes', 'ME104', 'C001_123', '123', 'HH001', 'form_submit', 'Print', null, 'Print', '1. Pull', 2, '2024-03-05', '2024-03', 'Secondary Contact', 'No', 'Existing', '2000-08-10', 24, '18-24', 'Non-binary', 'French', 'Low', '12345', 'City C', 'ST', 'Self', 'Yes', 'Legally Separated', 'Niche C', null, null, null, '123', 'FMP'; ``` Expected result: insert should be successful Current output: ``` SQL Error [1105] [HY000]: errCode = 2, detailMessage = (dev-dorisdbphys1.internetbrands.com) [RUNTIME_ERROR]TStatus: create partition failed. error:errCode = 2, detailMessage = Unknown table 'b03741ef-ff46-4956-8a97-5404be511858_#TEMP#_contacts_test_gkl' ``` --- ```sql create temporary table test123_emp PROPERTIES('replication_num' = '1') as select 1; insert into test123_emp values(1); INSERT OVERWRITE table test123_emp VALUES (2); ``` Expected result: insert should be successful Current output: ``` SQL Error [1105] [HY000]: errCode = 2, detailMessage = Failed to ADD PARTITION iot_temp_1110_test123_emp LIKE test123_emp. Reason: errCode = 2, detailMessage = Unknown table 'b03741ef-ff46-4956-8a97-5404be511858_#TEMP#_test123_emp' ``` ### What You Expected? Temporary table sign usage and matching should be made case-consistent with lower_case_table_names setting: - Validate user table names with a case-insensitive check for TEMPORARY_TABLE_SIGN, so user tables can't hide/overlap temp names regardless of case. - When constructing/looking up internal temp table names, TEMPORARY_TABLE_SIGN and table name both must be lower-case (TEMPORARY_TABLE_SIGN.toLowerCase(Locale.ROOT) + tableName.toLowerCase(Locale.ROOT)) when lower_case_table_names=1. Otherwise, use as-is. - All detection/stripping helpers (generateTempTableInnerName, getTempTableDisplayName, getTempTableSessionId, isTempTable, etc) should handle case-insensitively, using TEMPORARY_TABLE_SIGN_LOWER for all comparisons. - SQL expressions (show table status etc.) should use LOWER(TABLE_NAME) for extracting display names. - Add/adjust regression tests for lower_case_table_names = 0 and = 1. **Tests to add or adjust:** 1. Create temporary table; insert, insert overwrite, add partition, and select under both lower_case_table_names = 0 and = 1. 2. Attempt to create a user table with a name containing TEMPORARY_TABLE_SIGN (any case) and check that it is rejected. 3. Validate all "show" commands (show tablets, show table stats, show column stats, show data skew, analyze, etc) work consistently for temp tables under different lower_case_table_names settings. 4. Validate error messages are consistent and easy to debug when name conflicts occur. See the suggested file/line changes and code snippets at the end of this issue for suggested fix locations. ### How to Reproduce? 1. Set up a Doris cluster. 2. Set lower_case_table_names to 1 on the Frontend(s); restart FE. 3. Create a table and a temporary table. 4. Try inserting, overwriting, or creating/using partitions on a temp table. 5. Observe that operations can fail with Unknown Table errors where the temp table name includes '_#TEMP#_' or '_#temp#_' in mismatched case between lookup and creation. You can also reproduce by patching in debug code to print/compare the internal temp table names on both creation and lookup paths, then see they are no longer matching if table-name normalization is in effect. Concrete code locations to patch (from code search): - org.apache.doris.common.FeNameFormat (TEMPORARY_TABLE_SIGN, add .toLowerCase, update checkTableName) - org.apache.doris.common.util.Util (generateTempTableInnerName, getTempTableDisplayName, getTempTableSessionId, isTempTable, isTempTableInCurrentSession) - org.apache.doris.nereids.trees.plans.commands.ShowTableStatusCommand (NAME field SQL expr) (see further regression tests for update hints) ### Anything Else? Root cause: Temp/internal names use a case-sensitive separator, not normalized with lower_case_table_names, leading to failures in code when finding tables. See the message thread and examples for detailed analysis and code links. Recommended code fix is ready; can provide concrete file/line changes and patches upon request. Willing to submit a PR to address this issue with full suite of code/test changes. For reviewers: Detailed diffs and rationale available, please ask if you need them again. ### 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
