Table alias creation can lead to SQL syntax errors #850

juanpibcn2 · 2025-01-31T09:18:42Z

During the process of fetching the schema from the DB, the tables get their name and an alias.
The alias generation is handled by this class:

public class
AliasGenerator {
    private static final Random random = new Random();

    public static String getAlias(String sqlIdentifier) {
        int LENGTH = 4;
        return
                sqlIdentifier.length() > LENGTH ?
                        sqlIdentifier.substring(0, LENGTH) + "_" + random.nextInt(100)
                        : sqlIdentifier + "_" + random.nextInt(1000);

    }
}

In some particular cases like when users is joined with userprofile it can lead to bad SQL Syntax due to different tables having the same alias like in this example:

SELECT
    user_37.`auid`
	,user_37.`username`
	,user_37.`password`
	,user_37.`createdate`
	,user_37.`isActive`
	,user_37.`apid`
	,user_37.`auid`
	,user_37.`firstname`
	,user_37.`lastname`
	,user_37.`email`
	,user_37.`phone`
FROM
    wakila.users user_37

    
        INNER JOIN wakila.userprofile user_37
 
    LIMIT 100

Logs

09:33:36.854 [main] INFO org.springframework.test.context.support.AnnotationConfigContextLoaderUtils -- Could not detect default configuration classes for test class [com.homihq.db2rest.rest.mysql.MySQLCrossJoinControllerTest]: MySQLCrossJoinControllerTest does not declare any static, non-private, non-final, nested classes annotated with @Configuration.
09:33:37.002 [main] INFO org.springframework.boot.test.context.SpringBootTestContextBootstrapper -- Found @SpringBootConfiguration com.homihq.db2rest.Db2restApplication for test class com.homihq.db2rest.rest.mysql.MySQLCrossJoinControllerTest
09:33:37.163 [main] INFO org.reflections.Reflections -- Reflections took 77 ms to scan 12 urls, producing 85 keys and 397 values

    ____  ____  ____ ____  ____  _____ ____ _____  ____
   / / / |  _ \| __ )___ \|  _ \| ____/ ___|_   _| \ \ \
  | | |  | | | |  _ \ __) | |_) |  _| \___ \ | |    | | |
 < < <   | |_| | |_) / __/|  _ <| |___ ___) || |     > > >
  | | |  |____/|____/_____|_| \_\_____|____/ |_|    | | |
   \_\_\ ========================================= /_/_/
   +---------------------------------------------------+
   |:: db2rest version ::     v1.5.0-SNAPSHOT               |
   |:: Spring-Boot version ::     v3.4.1               |
   |:: Visit db2rest website :: [https://db2rest.com/] |
   +---------------------------------------------------+
2025-01-31T09:33:37.526+01:00  INFO 501729 --- [           main] c.h.d.r.m.MySQLCrossJoinControllerTest   : Starting MySQLCrossJoinControllerTest using Java 21.0.5 with PID 501729 (started by jpinasma in /home/jpinasma/open/db2rest/api-rest)
2025-01-31T09:33:37.526+01:00  INFO 501729 --- [           main] c.h.d.r.m.MySQLCrossJoinControllerTest   : The following 1 profile is active: "it-mysql"
2025-01-31T09:33:38.210+01:00  INFO 501729 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data MongoDB repositories in DEFAULT mode.
2025-01-31T09:33:38.243+01:00  INFO 501729 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 28 ms. Found 0 MongoDB repository interfaces.
2025-01-31T09:33:38.724+01:00  INFO 501729 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port 0 (http)
2025-01-31T09:33:38.732+01:00  INFO 501729 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2025-01-31T09:33:38.733+01:00  INFO 501729 --- [           main] o.apache.catalina.core.StandardEngine    : Starting Servlet engine: [Apache Tomcat/10.1.34]
2025-01-31T09:33:38.770+01:00  INFO 501729 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2025-01-31T09:33:38.770+01:00  INFO 501729 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1236 ms
2025-01-31T09:33:38.918+01:00  INFO 501729 --- [           main] org.testcontainers.images.PullPolicy     : Image pull policy will be performed by: DefaultPullPolicy()
2025-01-31T09:33:38.920+01:00  INFO 501729 --- [           main] o.t.utility.ImageNameSubstitutor         : Image name substitution will be performed by: DefaultImageNameSubstitutor (composite of 'ConfigurationFileImageNameSubstitutor' and 'PrefixingImageNameSubstitutor')
2025-01-31T09:33:38.926+01:00  INFO 501729 --- [           main] org.testcontainers.DockerClientFactory   : Testcontainers version: 1.20.4
2025-01-31T09:33:39.081+01:00  INFO 501729 --- [           main] o.t.d.DockerClientProviderStrategy       : Loaded org.testcontainers.dockerclient.UnixSocketClientProviderStrategy from ~/.testcontainers.properties, will try it first
2025-01-31T09:33:39.262+01:00  INFO 501729 --- [           main] o.t.d.DockerClientProviderStrategy       : Found Docker environment with local Unix socket (unix:///var/run/docker.sock)
2025-01-31T09:33:39.263+01:00  INFO 501729 --- [           main] org.testcontainers.DockerClientFactory   : Docker host IP address is localhost
2025-01-31T09:33:39.272+01:00  INFO 501729 --- [           main] org.testcontainers.DockerClientFactory   : Connected to docker: 
  Server Version: 26.1.3
  API Version: 1.45
  Operating System: Ubuntu 24.04.1 LTS
  Total Memory: 31528 MB
2025-01-31T09:33:39.319+01:00  INFO 501729 --- [           main] tc.testcontainers/ryuk:0.11.0            : Creating container for image: testcontainers/ryuk:0.11.0
2025-01-31T09:33:39.388+01:00  INFO 501729 --- [           main] tc.testcontainers/ryuk:0.11.0            : Container testcontainers/ryuk:0.11.0 is starting: 8b16527c0c302b6f7caa5dc8c2381eb8e218c14d45847e7eafee405e376aa674
2025-01-31T09:33:39.605+01:00  INFO 501729 --- [           main] tc.testcontainers/ryuk:0.11.0            : Container testcontainers/ryuk:0.11.0 started in PT0.286080774S
2025-01-31T09:33:39.608+01:00  INFO 501729 --- [           main] o.t.utility.RyukResourceReaper           : Ryuk started - will monitor and terminate Testcontainers containers on JVM exit
2025-01-31T09:33:39.608+01:00  INFO 501729 --- [           main] org.testcontainers.DockerClientFactory   : Checking the system...
2025-01-31T09:33:39.608+01:00  INFO 501729 --- [           main] org.testcontainers.DockerClientFactory   : ✔︎ Docker server version should be at least 1.6.0
2025-01-31T09:33:39.610+01:00  INFO 501729 --- [           main] tc.mysql:8.2                             : Creating container for image: mysql:8.2
2025-01-31T09:33:39.610+01:00  WARN 501729 --- [           main] tc.mysql:8.2                             : Reuse was requested but the environment does not support the reuse of containers
To enable reuse of containers, you must set 'testcontainers.reuse.enable=true' in a file located at /home/jpinasma/.testcontainers.properties
2025-01-31T09:33:39.644+01:00  INFO 501729 --- [           main] tc.mysql:8.2                             : Container mysql:8.2 is starting: 9134691d6a85107ab83541f038c71cdebd29e58c8641e629dbb0f50f453fb32d
2025-01-31T09:33:39.804+01:00  INFO 501729 --- [           main] tc.mysql:8.2                             : Waiting for database connection to become available at jdbc:mysql://localhost:33263/sakila using query 'SELECT 1'
2025-01-31T09:33:46.519+01:00  INFO 501729 --- [           main] tc.mysql:8.2                             : Container mysql:8.2 started in PT6.909424244S
2025-01-31T09:33:46.519+01:00  INFO 501729 --- [           main] tc.mysql:8.2                             : Container is started (JDBC URL: jdbc:mysql://localhost:33263/sakila)
2025-01-31T09:33:46.525+01:00  INFO 501729 --- [           main] org.testcontainers.ext.ScriptUtils       : Executing database script from mysql/mysql-sakila.sql
2025-01-31T09:33:46.818+01:00  INFO 501729 --- [           main] org.testcontainers.ext.ScriptUtils       : Executed database script from mysql/mysql-sakila.sql in 293 ms.
2025-01-31T09:33:46.819+01:00  INFO 501729 --- [           main] org.testcontainers.ext.ScriptUtils       : Executing database script from mysql/mysql-sakila-data.sql
2025-01-31T09:33:46.978+01:00  INFO 501729 --- [           main] org.testcontainers.ext.ScriptUtils       : Executed database script from mysql/mysql-sakila-data.sql in 158 ms.
2025-01-31T09:33:46.978+01:00  INFO 501729 --- [           main] org.testcontainers.ext.ScriptUtils       : Executing database script from mysql/mysql-wakila-all.sql
2025-01-31T09:33:47.063+01:00  INFO 501729 --- [           main] org.testcontainers.ext.ScriptUtils       : Executed database script from mysql/mysql-wakila-all.sql in 84 ms.
2025-01-31T09:33:47.254+01:00  INFO 501729 --- [           main] com.homihq.db2rest.jdbc.JdbcManager      : Attempting to load meta-data for all relational data-sources.
2025-01-31T09:33:47.262+01:00 DEBUG 501729 --- [           main] o.s.jdbc.datasource.DataSourceUtils      : Fetching JDBC Connection from DataSource
2025-01-31T09:33:47.263+01:00  INFO 501729 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2025-01-31T09:33:47.567+01:00  INFO 501729 --- [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@423d662a
2025-01-31T09:33:47.568+01:00  INFO 501729 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2025-01-31T09:33:47.573+01:00  INFO 501729 --- [           main] c.h.d.jdbc.sql.JdbcMetaDataProvider      : Preparing database meta-data - HikariProxyDatabaseMetaData@1811698326 wrapping com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema@1718de70
2025-01-31T09:33:47.575+01:00  INFO 501729 --- [           main] c.h.d.jdbc.sql.JdbcMetaDataProvider      : Product - MySQL
2025-01-31T09:33:47.575+01:00  INFO 501729 --- [           main] c.h.d.jdbc.sql.JdbcMetaDataProvider      : Version - 8.2.0
2025-01-31T09:33:47.575+01:00  INFO 501729 --- [           main] c.h.d.jdbc.sql.JdbcMetaDataProvider      : Major Version - 8
2025-01-31T09:33:47.575+01:00  INFO 501729 --- [           main] c.h.d.jdbc.sql.JdbcMetaDataProvider      : Driver Name - MySQL Connector/J
2025-01-31T09:33:47.575+01:00  INFO 501729 --- [           main] c.h.d.jdbc.sql.JdbcMetaDataProvider      : Driver Version - mysql-connector-j-9.1.0 (Revision: cf2917ea44ae2e43a4514a33771035aa99de73bf)
2025-01-31T09:33:47.575+01:00  INFO 501729 --- [           main] c.h.d.jdbc.sql.JdbcMetaDataProvider      : IncludedSchemas - null
2025-01-31T09:33:47.575+01:00  INFO 501729 --- [           main] c.h.d.jdbc.sql.JdbcMetaDataProvider      : All schema/catalog - true
2025-01-31T09:33:47.575+01:00  INFO 501729 --- [           main] c.h.d.jdbc.sql.JdbcMetaDataProvider      : Fetching meta data for selected schemas.
2025-01-31T09:33:47.584+01:00  INFO 501729 --- [           main] c.h.d.jdbc.sql.MySQLDataExtraction       : Loading meta tables for catalog - sakila
2025-01-31T09:33:47.672+01:00  INFO 501729 --- [           main] c.h.d.jdbc.sql.MySQLDataExtraction       : Loading meta tables for catalog - wakila
2025-01-31T09:33:47.692+01:00  INFO 501729 --- [           main] c.h.d.jdbc.sql.JdbcMetaDataProvider      : Completed loading database meta-data : 21 tables
2025-01-31T09:33:47.857+01:00  INFO 501729 --- [           main] c.h.d.mongo.config.MongoConfiguration    : *** No MongoDB configured.
2025-01-31T09:33:48.557+01:00  INFO 501729 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring TestDispatcherServlet ''
2025-01-31T09:33:48.557+01:00  INFO 501729 --- [           main] o.s.t.web.servlet.TestDispatcherServlet  : Initializing Servlet ''
2025-01-31T09:33:48.567+01:00  INFO 501729 --- [           main] o.s.b.a.e.web.EndpointLinksResolver      : Exposing 3 endpoints beneath base path '/actuator'
2025-01-31T09:33:48.580+01:00  INFO 501729 --- [           main] o.s.t.web.servlet.TestDispatcherServlet  : Completed initialization in 22 ms
2025-01-31T09:33:48.711+01:00  INFO 501729 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port 34411 (http) with context path '/'
2025-01-31T09:33:48.727+01:00  INFO 501729 --- [           main] c.h.d.r.m.MySQLCrossJoinControllerTest   : Started MySQLCrossJoinControllerTest in 11.398 seconds (process running for 12.561)
Mockito is currently self-attaching to enable the inline-mock-maker. This will no longer work in future releases of the JDK. Please add Mockito as an agent to your build what is described in Mockito's documentation: https://javadoc.io/doc/org.mockito/mockito-core/latest/org/mockito/Mockito.html#0.3
WARNING: A Java agent has been loaded dynamically (/space/pipeline/net/bytebuddy/byte-buddy-agent/1.15.11/byte-buddy-agent-1.15.11.jar)
WARNING: If a serviceability tool is in use, please run with -XX:+EnableDynamicAgentLoading to hide this warning
WARNING: If a serviceability tool is not in use, please run with -Djdk.instrument.traceUsage for more information
WARNING: Dynamic loading of agents will be disallowed by default in a future release
OpenJDK 64-Bit Server VM warning: Sharing is only supported for boot loader classes because bootstrap classpath has been appended
2025-01-31T09:33:49.258+01:00  INFO 501729 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring TestDispatcherServlet ''
2025-01-31T09:33:49.258+01:00  INFO 501729 --- [           main] o.s.t.web.servlet.TestDispatcherServlet  : Initializing Servlet ''
2025-01-31T09:33:49.259+01:00  INFO 501729 --- [           main] o.s.t.web.servlet.TestDispatcherServlet  : Completed initialization in 1 ms
2025-01-31T09:33:49.331+01:00 DEBUG 501729 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL query
2025-01-31T09:33:49.331+01:00 DEBUG 501729 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [
SELECT
    user_37.`auid`
	,user_37.`username`
	,user_37.`password`
	,user_37.`createdate`
	,user_37.`isActive`
	,user_37.`apid`
	,user_37.`auid`
	,user_37.`firstname`
	,user_37.`lastname`
	,user_37.`email`
	,user_37.`phone`
FROM
    wakila.users user_37

    
        INNER JOIN wakila.userprofile user_37
 
 
    




    LIMIT 100
    

]
2025-01-31T09:33:49.331+01:00 DEBUG 501729 --- [           main] o.s.jdbc.datasource.DataSourceUtils      : Fetching JDBC Connection from DataSource
2025-01-31T09:33:49.511+01:00 ERROR 501729 --- [           main] c.h.d.jdbc.core.service.JdbcReadService  : Error in read op : 

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [
SELECT
    user_37.`auid`
	,user_37.`username`
	,user_37.`password`
	,user_37.`createdate`
	,user_37.`isActive`
	,user_37.`apid`
	,user_37.`auid`
	,user_37.`firstname`
	,user_37.`lastname`
	,user_37.`email`
	,user_37.`phone`
FROM
    wakila.users user_37

    
        INNER JOIN wakila.userprofile user_37
 
 
    




    LIMIT 100
    

]
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:103) ~[spring-jdbc-6.2.1.jar:6.2.1]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107) ~[spring-jdbc-6.2.1.jar:6.2.1]
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1556) ~[spring-jdbc-6.2.1.jar:6.2.1]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677) ~[spring-jdbc-6.2.1.jar:6.2.1]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723) ~[spring-jdbc-6.2.1.jar:6.2.1]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748) ~[spring-jdbc-6.2.1.jar:6.2.1]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:804) ~[spring-jdbc-6.2.1.jar:6.2.1]
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:218) ~[spring-jdbc-6.2.1.jar:6.2.1]
	at com.homihq.db2rest.jdbc.JdbcOperationService.read(JdbcOperationService.java:42) ~[classes/:na]
	at com.homihq.db2rest.jdbc.core.service.JdbcReadService.findAll(JdbcReadService.java:37) ~[classes/:na]
	at com.homihq.db2rest.jdbc.rest.read.ReadController.find(ReadController.java:83) ~[classes/:na]
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:580) ~[na:na]
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:257) ~[spring-web-6.2.1.jar:6.2.1]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:190) ~[spring-web-6.2.1.jar:6.2.1]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:118) ~[spring-webmvc-6.2.1.jar:6.2.1]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:986) ~[spring-webmvc-6.2.1.jar:6.2.1]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:891) ~[spring-webmvc-6.2.1.jar:6.2.1]
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-6.2.1.jar:6.2.1]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1088) ~[spring-webmvc-6.2.1.jar:6.2.1]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:978) ~[spring-webmvc-6.2.1.jar:6.2.1]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014) ~[spring-webmvc-6.2.1.jar:6.2.1]
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:914) ~[spring-webmvc-6.2.1.jar:6.2.1]
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:590) ~[tomcat-embed-core-10.1.34.jar:6.0]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:885) ~[spring-webmvc-6.2.1.jar:6.2.1]
	at org.springframework.test.web.servlet.TestDispatcherServlet.service(TestDispatcherServlet.java:72) ~[spring-test-6.2.1.jar:6.2.1]
	at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658) ~[tomcat-embed-core-10.1.34.jar:6.0]
	at org.springframework.mock.web.MockFilterChain$ServletFilterProxy.doFilter(MockFilterChain.java:165) ~[spring-test-6.2.1.jar:6.2.1]
	at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:132) ~[spring-test-6.2.1.jar:6.2.1]
	at org.springframework.test.web.servlet.MockMvc.perform(MockMvc.java:201) ~[spring-test-6.2.1.jar:6.2.1]
	at com.homihq.db2rest.rest.mysql.MySQLCrossJoinControllerTest.testCrossJoin(MySQLCrossJoinControllerTest.java:49) ~[test-classes/:na]
	at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:580) ~[na:na]
	at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:767) ~[junit-platform-commons-1.11.4.jar:1.11.4]
	at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:156) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:147) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:86) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(InterceptingExecutableInvoker.java:103) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.lambda$invoke$0(InterceptingExecutableInvoker.java:93) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:92) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.jupiter.engine.execution.InterceptingExecutableInvoker.invoke(InterceptingExecutableInvoker.java:86) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$8(TestMethodTestDescriptor.java:217) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:213) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:138) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:68) ~[junit-jupiter-engine-5.11.4.jar:5.11.4]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:156) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:146) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:144) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:143) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:100) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596) ~[na:na]
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:160) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:146) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:144) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:143) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:100) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596) ~[na:na]
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:160) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:146) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:144) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:143) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:100) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54) ~[junit-platform-engine-1.11.4.jar:1.11.4]
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:198) ~[junit-platform-launcher-1.11.4.jar:1.11.4]
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:169) ~[junit-platform-launcher-1.11.4.jar:1.11.4]
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:93) ~[junit-platform-launcher-1.11.4.jar:1.11.4]
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:58) ~[junit-platform-launcher-1.11.4.jar:1.11.4]
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:141) ~[junit-platform-launcher-1.11.4.jar:1.11.4]
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:57) ~[junit-platform-launcher-1.11.4.jar:1.11.4]
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:103) ~[junit-platform-launcher-1.11.4.jar:1.11.4]
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:85) ~[junit-platform-launcher-1.11.4.jar:1.11.4]
	at org.junit.platform.launcher.core.DelegatingLauncher.execute(DelegatingLauncher.java:47) ~[junit-platform-launcher-1.11.4.jar:1.11.4]
	at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:63) ~[junit-platform-launcher-1.11.4.jar:1.11.4]
	at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:57) ~[junit5-rt.jar:na]
	at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38) ~[junit-rt.jar:na]
	at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11) ~[idea_rt.jar:na]
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35) ~[junit-rt.jar:na]
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:232) ~[junit-rt.jar:na]
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55) ~[junit-rt.jar:na]
Caused by: java.sql.SQLSyntaxErrorException: Not unique table/alias: 'user_37'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:112) ~[mysql-connector-j-9.1.0.jar:9.1.0]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:114) ~[mysql-connector-j-9.1.0.jar:9.1.0]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:988) ~[mysql-connector-j-9.1.0.jar:9.1.0]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1056) ~[mysql-connector-j-9.1.0.jar:9.1.0]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-5.1.0.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-5.1.0.jar:na]
	at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:732) ~[spring-jdbc-6.2.1.jar:6.2.1]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658) ~[spring-jdbc-6.2.1.jar:6.2.1]
	... 95 common frames omitted


MockHttpServletRequest:
      HTTP Method = POST
      Request URI = /v1/rdbms/mysqldb/users/_expand
       Parameters = {}
          Headers = [Content-Type:"application/json", Accept:"application/json", Content-Length:"25"]
             Body = <no character encoding set>
    Session Attrs = {}

Handler:
             Type = com.homihq.db2rest.jdbc.rest.read.ReadController
           Method = com.homihq.db2rest.jdbc.rest.read.ReadController#find(String, String, String, String, String, List, int, long, List)

Async:
    Async started = false
     Async result = null

Resolved Exception:
             Type = com.homihq.db2rest.core.exception.GenericDataAccessException

ModelAndView:
        View name = null
             View = null
            Model = null

FlashMap:
       Attributes = null

MockHttpServletResponse:
           Status = 400
    Error message = null
          Headers = [Content-Type:"application/problem+json"]
     Content type = application/problem+json
             Body = {"type":"https://db2rest.com/error/generic-error","title":"Generic Data Access Error","status":400,"detail":"Not unique table/alias: 'user_37'","instance":"/v1/rdbms/mysqldb/users/_expand","errorCategory":"Data-access-error","timestamp":"2025-01-31T08:33:49.515007183Z"}
    Forwarded URL = null
   Redirected URL = null
          Cookies = []

java.lang.AssertionError: Status expected:<200> but was:<400>
Expected :200
Actual   :400
<Click to see difference>


	at org.springframework.test.util.AssertionErrors.fail(AssertionErrors.java:61)
	at org.springframework.test.util.AssertionErrors.assertEquals(AssertionErrors.java:128)
	at org.springframework.test.web.servlet.result.StatusResultMatchers.lambda$matcher$9(StatusResultMatchers.java:640)
	at org.springframework.test.web.servlet.MockMvc$1.andExpect(MockMvc.java:214)
	at com.homihq.db2rest.rest.mysql.MySQLCrossJoinControllerTest.testCrossJoin(MySQLCrossJoinControllerTest.java:54)
	at java.base/java.lang.reflect.Method.invoke(Method.java:580)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1596)

2025-01-31T09:33:49.562+01:00  INFO 501729 --- [ionShutdownHook] o.s.b.w.e.tomcat.GracefulShutdown        : Commencing graceful shutdown. Waiting for active requests to complete
2025-01-31T09:33:49.565+01:00  INFO 501729 --- [tomcat-shutdown] o.s.b.w.e.tomcat.GracefulShutdown        : Graceful shutdown complete

Process finished with exit code 255

Perhaps we can check if the alias already exists in the schema, and if it does, get another random int.
This can be problematic with databases with schemas in which most tables have the same naming prefix.

If we think about an extreme scenario:
A database with a schema with 1000+ tables all of them start with a common prefix that's long, in that case even searching for existing alias won't fix the issue

The text was updated successfully, but these errors were encountered:

juanpibcn2 · 2025-01-31T09:47:46Z

I have created #851 as a proof of concept to fix this issue

kdhrubo · 2025-01-31T10:02:11Z

Awesome. Will take a look into it.

…

On Fri, Jan 31, 2025, 10:48 AM juanpibcn2 ***@***.***> wrote: I have created #851 <#851> as a proof of concept to fix this issue — Reply to this email directly, view it on GitHub <#850 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAISPREF4NY74C4EXMKKVGD2NNBFRAVCNFSM6AAAAABWHEHOBCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDMMRWG44DIOBVGM> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

juanpibcn2 linked a pull request Jan 31, 2025 that will close this issue

fix(rdbms-support) Replace int with UUID for table aliasing #851

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Table alias creation can lead to SQL syntax errors #850

Table alias creation can lead to SQL syntax errors #850

juanpibcn2 commented Jan 31, 2025

Logs

juanpibcn2 commented Jan 31, 2025

kdhrubo commented Jan 31, 2025 via email

Table alias creation can lead to SQL syntax errors #850

Table alias creation can lead to SQL syntax errors #850

Comments

juanpibcn2 commented Jan 31, 2025

Logs

juanpibcn2 commented Jan 31, 2025

kdhrubo commented Jan 31, 2025 via email