Ora-00918 Column Ambigously defined Error

[Todd Flora]

When creaing an ORM Class that references the same associate class twice, the SQL statement ends up with an ambigous column.

Please consider the following Class Definition

  [Entity]
  [Table('CUSTOMER')]
  [Sequence('Select GetSid() as Sid from dual')]
  TCustomer = class(TModelBase)
  private

    [Column('SID',[cpRequired,cpNotNull,cpPrimaryKey],19,0)]
    FSid: Int64;

    [Column('TAX_AREA_SID',[],19,0)]
    FTaxAreaSid: Nullable<Int64>;

    [Column('TAX_AREA2_SID',[],19,0)]
    FTaxArea2Sid: Nullable<Int64>;

    [Spring.Persistence.Mapping.Attributes.Transient]
    [ManyToOne(false, [ckCascadeAll], 'FTaxAreaSid')]
    FTaxArea: TTaxAreaAssociate;

    [Spring.Persistence.Mapping.Attributes.Transient]
    [ManyToOne(false, [ckCascadeAll], 'FTaxArea2Sid')]
    FTaxArea2: TTaxAreaAssociate;


    function TCustomer.GetTaxArea: TNullableString;
    begin
      Result := nil;
      if FTaxArea <> nil then
      Result := FTaxArea.Name;
    end;

    function TCustomer.GetTaxArea2: TNullableString;
    begin
      Result := nil;
      if FTaxArea2 <> nil then
        Result := FTaxArea2.Name;
    end;

  public
    constructor Create;override;
    destructor Destroy; override;
    property Sid: Int64 read FSid write SetSid;
    property TaxAreaSid: Nullable<Int64> read FTaxAreaSid write SetTaxAreaSid;
    property TaxArea2Sid: Nullable<Int64> read FTaxArea2Sid write SetTaxArea2Sid;
    property TaxArea: TNullableString read GetTaxArea write SetTaxArea;
    property TaxArea2: TNullableString read GetTaxArea2 write SetTaxArea2;
  end;

Notice that the same associate class TTaxAreaAssociate is referenced twice because there are actually two tax_area_sid fields in the customer record.

When the SQL Statement is generated though it does not treat these as two separate instances of the Tax_Area table but only one and therefore gives the same alias for both.

Here is the SQL that is generated

SELECT t2."SID",
       t2."TAX_AREA_SID",
       t2."TAX_AREA2_SID",
       t1."SID"                  AS t1$SID,
       t1."TAX_AREA_NAME"        AS t1$TAX_AREA_NAME,
       t1."SID"                  AS t1$SID,
       t1."TAX_AREA_NAME"        AS t1$TAX_AREA_NAME
  FROM CUSTOMER t2 
  LEFT OUTER JOIN TAX_AREA t1
    ON t1."SID" = t2."TAX_AREA_SID" 
  LEFT OUTER JOIN TAX_AREA t1
    ON t1."SID" = t2."TAX_AREA2_SID" 

[Todd Flora]

Seems like this is the issue.

function TAnsiSQLGenerator.GetTableNameWithAlias(const table: TSQLTable): string;
begin
  Result := table.Name + ' AS ' + TSQLAliasGenerator.GetAlias(table.Name);
end;

The TSqlAliasGenerator is just generating one alias per table.name and since the table name of course is the same for these two seperate joins then it returns the same one.

Isn't SQL Generation Fun!

[Stefan Glienke]

That is not the only code that is causing the issue (remember that this does not even work on Oracle because it does not accept the AS for a table alias?)

But what is correct is that TSQLAliasGenerator causing the problem. But the problem here originates from the TManyToOneRelation.BuildColumnName method. The alias needs to be generated for each TSQLTable instance. Unfortunately the code for many-to-one relations need to be refactored to allow that.

[Todd Flora]

Yes correct it is the TOracleSQLGenerators version of this method without the as keyword that I should have referenced.

[Todd Flora]

Is this on the radar screen? This is a showstopper for us. If not then I will need to come up with a solution myself. I would appreciate if you could just let me know if it is being worked on? Thanks,

[Stefan Glienke]

Yes, but not easy to fix - there is some issue in the architecture for many-to-one relations. Working on it in the session-refactoring branch.

[Todd Flora]

Thanks so much for this. I am integrating your changes now.

A couple if API issues. We use GetMemberValueDeep and GetClassMembers from the now defunct TRttiExplorer class.You have replaced these with two internal functions in the EntityCache.TEntityData class with:

GetMemberValueDeep - an Embedded Function GetAttributes a private Class Function

Would it be possible to make both of these public off of the TEntityData class. I have moved GetAttributes to public section and unembedded the GetMemberValueDeep function and made it public for now so that we can use these functions without duplicating the code.

Thanks again for all your hard work,

Todd.

--

[Stefan Glienke]

No, they are private for exactly this reason: preventing anyone from using them - they are implementation detail and not public API. We can change them in the future without worrying about breaking anyone's code.