There is a problem casting from numbers to strings in 3.7.3 #2069

Open
doublemetal opened this Issue Dec 20, 2016 · 4 comments

Projects

None yet

2 participants

@doublemetal
doublemetal commented Dec 20, 2016 edited

I want to compare a numeric column to a string.
So I used the method of the Q object variable of queryDsl, stringValue().

.

like this:

QEntity.entity.sid.stringValue().contains(query);
or
QEntity.entity.sid.like(query);

However, the query is generated as below and an error occurs

select * from entity where CAST(sid as char) like '%query%';

.

char is need a length

like this:

select * from entity where CAST(sid as char(10)) like '%query%';

I think there is a problem with the STRING_CAST function instrintValue.
StringOperation.create(Ops.STRING_CAST, mixin);
Or, is there a problem with me? Or hibernate?

In my Oracle dialect(Oracle10gDialect extends Oracle9iDialect), Type.CHAR is char (1 char).
Is this relevant?

@Shredder121
Member

Could you post the JPQL as well?
Then we can see if the issue comes from us or hibernate.

@doublemetal
doublemetal commented Dec 20, 2016 edited

@Shredder121
please see this code:

QAstCatalogEntity qAstCatalogEntity = QAstCatalogEntity.astCatalogEntity;
q = q.toUpperCase();

BooleanBuilder booleanBuilder = new BooleanBuilder();
booleanBuilder.and(qAstCatalogEntity.classCd.eq(classCd));

if(!StringUtils.isEmpty(q)) {
	booleanBuilder.and(
			qAstCatalogEntity.manufacturer.toUpperCase().contains(q)
				.or(qAstCatalogEntity.model.toUpperCase().contains(q))
				.or(qAstCatalogEntity.modelDetail.toUpperCase().contains(q))
				.or(qAstCatalogEntity.sid.like(q))
			);
	}

(List<AstCatalogEntity>) repository.findAll(booleanBuilder);
select
        astcatalog0_.sid as sid1_9_,
        astcatalog0_.created_date as created_date2_9_,
        astcatalog0_.creator_id as creator_id3_9_,
        astcatalog0_.modified_date as modified_date4_9_,
        astcatalog0_.modifier_id as modifier_id5_9_,
        astcatalog0_.class_cd as class_cd6_9_,
        astcatalog0_.manufacturer as manufacturer7_9_,
        astcatalog0_.model as model8_9_,
        astcatalog0_.model_detail as model_detail9_9_,
        astcatalog0_.nams_yn as nams_yn10_9_,
        astcatalog0_.sid_line as sid_line11_9_ 
    from
        ast_catalog astcatalog0_ 
    where
        astcatalog0_.class_cd=? 
        and astcatalog0_.nams_yn=? 
        and (
            upper(astcatalog0_.manufacturer) like ? escape '!' 
            or upper(astcatalog0_.model) like ? escape '!' 
            or upper(astcatalog0_.model_detail) like ? escape '!' 
            or cast(astcatalog0_.sid as char) like ? escape '!'
        )

the astcatalog0_.sid is NumberPath<Long>

Do you need more?

@Shredder121
Member

That's the SQL that Hibernate generates.
We'll need the JPQL that comes from us, and that goes in Hibernate.

@doublemetal

Sorry.

Please look at the code below

select astCatalogEntity
from AstCatalogEntity astCatalogEntity
where 
	astCatalogEntity.classCd = ?1 and astCatalogEntity.namsYn = ?2 
and
	(upper(astCatalogEntity.manufacturer) like ?3 escape '!'
		or upper(astCatalogEntity.model) like ?3 escape '!'
		or upper(astCatalogEntity.modelDetail) like ?3 escape '!'
		or str(astCatalogEntity.sid) like ?3 escape '!')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment