Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Duplicate aliases generated for columns (1.1.3)(EclipseLink 1.1.3 can generate duplicate alias names for columns => ORA-918 column ambiguously defined)
Duplicate aliases generated for columns (1.1.3) [message #523380] Fri, 26 March 2010 05:07 Go to next message
Ed Randall is currently offline Ed Randall
Messages: 6
Registered: July 2009
Junior Member
Came across this problem yesterday, surely a bug in the way EclipseLink generates aliases for column names.

We have some entities mapping to some legacy tables:
@Entity
public class T {
    @Id 
    @Column(name="K1")
    private String key ;
    
    @Column(updatable=false)
    private String k2 = " ";

    private String a1 = " ";
    private String a2 = " ";
    private String a3 = " ";
    // ... 125 a columns
    private String a125 = " ";
    
    private double n1;
    private double n2;
    private double n3;
    // ... 95 n columns
    private double n95;
}

@Entity
public class X {
    @EmbeddedId     
    private PrimaryKeyPair key;

    private String a1;
    private String a2;
    private String a3;
    private String a4;
    private String a5;

    private double n1;
    private double n2;
    private double n3;
}


This is the named query:
SELECT t FROM T t, X x 
WHERE t.key = x.a1 
  AND x.key.k1 = :partial 
  AND x.key.k2 LIKE :partialX 
ORDER BY x.key.k2"),


The problem is that when querying this, SQL is generated with non-unique aliases - Eclipselink appends a number to the column name,
t1.N1 happens to be number 150 so gets N1150, t1.N11 happens to be number 50 and gets N1150
=> ORA-918 column ambiguously defined.


2010-03-25 17:06:04,375 INFO org.springframework.test.context.transaction.TransactionalTe stExecutionListener [main] (TransactionalTestExecutionListener.java:280) - Rolled back transaction after test execution for test context [[TestContext@8864af testClass = ImportControllerTest, locations = array<String>['classpath:/test/jpa/jpa-wss.xml', 'classpath:/test/ejb/foureyes-services.xml', 'classpath:/test/jotws/jot-connection.xml', 'classpath:/test/ejb/jmock-class-imposteriser.xml', 'classpath:/test/jotws/p4recordblocker-client.xml', 'classpath:/test/jotws/valuevariable-client.xml'], testInstance = biz.wss.jsf.staticintegration.wssimport.ImportControllerTest @16acdd1, testMethod = testImport@ImportControllerTest, testException = Exception [EclipseLink-4002] (Eclipse Persistence Services - 1.1.3.v20091002-r5404): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00918: column ambiguously defined

Error Code: 918
Call: SELECT * FROM (SELECT /*+ FIRST_ROWS */ a.*, ROWNUM rnum FROM (SELECT t1.K1 AS K11, t1.N41 AS N412, t1.N40 AS N403, t1.N45 AS N454, t1.N44 AS N445, t1.A120 AS A1206, t1.N43 AS N437, t1.A121 AS A1218, t1.N42 AS N429, t1.A123 AS A12310, t1.N48 AS N4811, t1.A122 AS A12212, t1.N49 AS N4913, t1.A125 AS A12514, t1.N46 AS N4615, t1.A124 AS A12416, t1.N47 AS N4717, t1.N30 AS N3018, t1.N32 AS N3219, t1.A110 AS A11020, t1.N31 AS N3121, t1.N34 AS N3422, t1.N33 AS N3323, t1.A119 AS A11924, t1.A114 AS A11425, t1.N35 AS N3526, t1.A113 AS A11327, t1.N36 AS N3628, t1.A112 AS A11229, t1.N37 AS N3730, t1.A111 AS A11131, t1.N38 AS N3832, t1.A118 AS A11833, t1.N39 AS N3934, t1.A117 AS A11735, t1.A116 AS A11636, t1.A115 AS A11537, t1.N23 AS N2338, t1.N22 AS N2239, t1.N21 AS N2140, t1.N20 AS N2041, t1.N28 AS N2842, t1.N29 AS N2943, t1.N26 AS N2644, t1.N27 AS N2745, t1.N24 AS N2446, t1.N25 AS N2547, t1.N10 AS N1048, t1.N12 AS N1249, t1.N11 AS N1150, t1.N17 AS N1751, t1.N18 AS N1852, t1.N19 AS N1953, t1.N13 AS N1354, t1.N14 AS N1455, t1.N15 AS N1556, t1.N16 AS N1657, t1.N80 AS N8058, t1.A98 AS A9859, t1.N81 AS N8160, t1.A99 AS A9961, t1.N84 AS N8462, t1.N85 AS N8563, t1.N82 AS N8264, t1.N83 AS N8365, t1.N88 AS N8866, t1.N89 AS N8967, t1.N86 AS N8668, t1.N87 AS N8769, t1.N70 AS N7070, t1.N71 AS N7171, t1.N72 AS N7272, t1.N73 AS N7373, t1.N74 AS N7474, t1.N75 AS N7575, t1.N76 AS N7676, t1.N77 AS N7777, t1.N78 AS N7878, t1.N79 AS N7979, t1.A76 AS A7680, t1.A77 AS A7781, t1.A78 AS A7882, t1.A79 AS A7983, t1.N66 AS N6684, t1.N67 AS N6785, t1.N64 AS N6486, t1.N65 AS N6587, t1.N62 AS N6288, t1.N63 AS N6389, t1.N60 AS N6090, t1.N61 AS N6191, t1.N69 AS N6992, t1.N68 AS N6893, t1.A80 AS A8094, t1.A82 AS A8295, t1.A81 AS A8196, t1.A84 AS A8497, t1.A83 AS A8398, t1.A86 AS A8699, t1.A85 AS A85100, t1.A89 AS A89101, t1.A87 AS A87102, t1.A88 AS A88103, t1.N53 AS N53104, t1.N54 AS N54105, t1.N55 AS N55106, t1.N56 AS N56107, t1.N50 AS N50108, t1.N51 AS N51109, t1.N52 AS N52110, t1.N58 AS N58111, t1.N57 AS N57112, t1.N59 AS N59113, t1.A93 AS A93114, t1.A92 AS A92115, t1.A91 AS A91116, t1.A90 AS A90117, t1.A97 AS A97118, t1.A96 AS A96119, t1.A95 AS A95120, t1.A94 AS A94121, t1.A63 AS A63122, t1.A64 AS A64123, t1.A61 AS A61124, t1.A62 AS A62125, t1.A60 AS A60126, t1.N92 AS N92127, t1.N91 AS N91128, t1.N90 AS N90129, t1.N95 AS N95130, t1.N94 AS N94131, t1.A59 AS A59132, t1.N93 AS N93133, t1.A58 AS A58134, t1.A57 AS A57135, t1.A56 AS A56136, t1.A55 AS A55137, t1.A54 AS A54138, t1.A72 AS A72139, t1.A73 AS A73140, t1.A74 AS A74141, t1.A75 AS A75142, t1.A70 AS A70143, t1.A71 AS A71144, t1.A69 AS A69145, t1.A66 AS A66146, t1.A65 AS A65147, t1.A68 AS A68148, t1.A67 AS A67149, t1.N1 AS N1150, t1.A41 AS A41151, t1.N5 AS N5152, t1.A42 AS A42153, t1.N4 AS N4154, t1.N3 AS N3155, t1.A40 AS A40156, t1.N2 AS N2157, t1.N9 AS N9158, t1.N8 AS N8159, t1.N7 AS N7160, t1.N6 AS N6161, t1.A9 AS A9162, t1.A1 AS A1163, t1.A35 AS A35164, t1.A2 AS A2165, t1.A34 AS A34166, t1.A3 AS A3167, t1.A33 AS A33168, t1.A4 AS A4169, t1.A32 AS A32170, t1.A5 AS A5171, t1.A39 AS A39172, t1.A6 AS A6173, t1.A38 AS A38174, t1.A7 AS A7175, t1.A37 AS A37176, t1.A8 AS A8177, t1.A36 AS A36178, t1.A50 AS A50179, t1.A51 AS A51180, t1.A52 AS A52181, t1.A53 AS A53182, t1.A44 AS A44183, t1.A43 AS A43184, t1.A46 AS A46185, t1.A45 AS A45186, t1.A48 AS A48187, t1.A47 AS A47188, t1.A49 AS A49189, t1.A20 AS A20190, t1.A16 AS A16191, t1.A17 AS A17192, t1.A14 AS A14193, t1.A15 AS A15194, t1.A12 AS A12195, t1.A13 AS A13196, t1.A10 AS A10197, t1.A11 AS A11198, t1.A18 AS A18199, t1.A19 AS A19200, t1.A31 AS A31201, t1.A30 AS A30202, t1.A25 AS A25203, t1.A26 AS A26204, t1.A27 AS A27205, t1.A28 AS A28206, t1.A21 AS A21207, t1.A22 AS A22208, t1.A23 AS A23209, t1.A24 AS A24210, t1.A29 AS A29211, t1.A102 AS A102212, t1.A103 AS A103213, t1.A100 AS A100214, t1.A101 AS A101215, t1.A106 AS A106216, t1.A107 AS A107217, t1.A104 AS A104218, t1.A105 AS A105219, t1.A108 AS A108220, t1.A109 AS A109221, t1.K2 AS K2222 FROM COREXFA t0, CORETRA t1 WHERE (((t1.K1 = t0.A1) AND (t0.K1 = ?)) AND (t0.K2 LIKE ?)) ORDER BY t0.K2 ASC) a WHERE ROWNUM <= ?) WHERE rnum > ?
bind => [001, %, 2, 0]


If EclipseLink simply used a delimiter, "_" or something, in there between column name and the number, the problem would go away.

(The situation is that we are trying to migrate from Toplink Essentials to EclipseLink.
Would like to use JPA2 but cannot use it yet, due to MappedSuperclass bugs in 1.2.0 and 2.0.1.
Therefore we will have to make a small step first, 1.1.3 should be very close to Toplink Essentials that ships with Glassfish)

Is this a known bug?
If not, can someone point me to the bit of code that generates these aliases, so we can create a private patch to 1.1.3, until 1.1.4 comes out?

Thanks

Ed
Re: Duplicate aliases generated for columns (1.1.3) [message #523452 is a reply to message #523380] Fri, 26 March 2010 10:17 Go to previous message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1016
Registered: July 2009
Senior Member
Hello Ed,

I have not seen someone file an issue for this before you filed https://bugs.eclipse.org/bugs/show_bug.cgi?id=307164

The problem is that the native pagination feature used when setting the max+first rows results uses aliases. You can turn this off by setting the shouldForceFieldNamesToUpperCase flag on the DatabasePlatform to false - best way would be through a customizer that would get the platform from the session and call setShouldUseRownumFiltering(false) on it. This will prevent EclipseLink from using this feature - instead it will use the setMaxRows feature on the JDBC statement and use the setFirstRows on the returned result set.

If you want to create a patch, the org.eclipse.persistence.internal.expressions.SQLSelectStatem ent has a generatedAlias method that creates the alias string.

Best Regards,
Chris
Previous Topic:Optimizing EclipseLink generated SQL
Next Topic:How to integrate EclipseLink into existing Client/Server application ?
Goto Forum:
  


Current Time: Thu Jul 24 21:53:25 EDT 2014

Powered by FUDForum. Page generated in 0.02396 seconds