[coldbox-3.6.0] criteriaBuilder not creating the correct SQL! Arghh! What am I doing wrong????

[Dominique Kimbell]

Setup:
Using the following Entity - Drillers.cfc

<cfscript>
component persistent="true" extends="coldbox.system.orm.hibernate.ActiveEntity" entityname="Driller" table="WELL_DRILLERS"{
    property name="driller_id" fieldtype="id" ormType="integer" generator="sequence" params="{sequence='MLWWELTS.SEQ_WELL_DRILLERS'}";
   
    property name="company_name" ormType="string";
   
    property name="first_name" ormType="string";
    property name="middle_name" ormType="string";    
    property name="last_name" ormType="string";
    property name="address" ormType="string";
    property name="city" ormType="string";
    property name="state" ormType="string";
    property name="zip" ormType="string";  
    property name="day_phone" ormType="string";
    property name="alt_phone" ormType="string";  
    property name="fax_phone" ormType="string";
    property name="email" ormType="string";
    property name="date_registered" ormType="string";
    property name="verified" ormType="string" setter="false";
    property name="date_verified" ormType="string";
    property name="completed" ormType="string" setter="false";
    property name="d_uid" ormType="string";
    property name="d_pwd" ormType="string";
     
    // validation
    this.constraints = {
    };
}
</cfscript>

And using the following LoginService.cfc

component singleton{
    property name="drillerEntity" inject="entityService:Driller";
       
    public Driller function init(){
        return this;
    }
   
    any function login(required string username, required string password){

        try {
            var user = drillerEntity.newCriteria();
            var propertyNames = ArrayToList(drillerEntity.getPropertyNames());
            user = user.and(
                    user.restrictions.isEQ("d_uid", arguments.username),
                    user.restrictions.isEQ("d_pwd", arguments.password)
                ).withProjections(property=propertyNames).list(asQuery=false);                      
        }catch (any e){
            writedump(e);abort;
        }    
    }
}
</cfscript>

If I call the login() function of LoginService with a username and password
Coldfusion/ORM/Hibernate builds the following SQL

SELECT this_.COMPANY_NAME AS y0_,
  this_.FIRST_NAME        AS y1_,
  this_.MIDDLE_NAME       AS y2_,
  this_.LAST_NAME         AS y3_,
  this_.ADDRESS           AS y4_,
  this_.CITY              AS y5_,
  this_."STATE"           AS y6_,
  this_.ZIP               AS y7_,
  this_.DAY_PHONE         AS y8_,
  this_.ALT_PHONE         AS y9_,
  this_.FAX_PHONE         AS y10_,
  this_.EMAIL             AS y11_,
  this_.DATE_REGISTERED   AS y12_,
  this_.VERIFIED          AS y13_,
  this_.DATE_VERIFIED     AS y14_,
  this_.COMPLETED         AS y15_,
  this_.D_UID             AS y16_,
  this_.D_PWD             AS y17_,
  this_.DRILLER_ID        AS y18_
FROM MLWWELTS.WELL_DRILLERS this_
WHERE (y16_=?
AND y17_   =?)

AND THROWS THE FOLLOWING ERROR!

[Macromedia][Oracle JDBC Driver][Oracle]ORA-00904: "Y17_": invalid identifier
                                                                                                                           

NOTE: Yes we are using the Oracle JDBC Driver...

Why does the Criteria in the login function not build a correct SQL Statement?
I could do this with two subcriterias, but then I have a SQL statement that is twice and long as it needs to be...

This has been plaguing me since I started using the CriteriaBuilder to create my code...

Is it me?
Coldfusion?
Hibernate?
Coldbox?
Oracle?

I would like to know why! A technical explanation would be acceptable...

Thanks for any help up front,

Dominique

[Dominique Kimbell]

Just a follow-up... I still haven't found out why this happens...
But...
The solution I came up with was to actually alias those two property names "d_uid:username" and "d_pwd:password" in the property attribute of the withProjections function.
Works but is a little hacky...

Dom

[Wesley Hampton]

I know this is old, but I ran across it and thought I'd share what Joel had posted on one of my questions.  It seems using the word "this" can be used to get around this: 

.withProjections( property="this.MyProperty:MyProperty" )

https://groups.google.com/d/msg/coldbox/3T0cDcMGGHc/iwmAvU6MaYUJ