Oracle Error ORA-00904: "TRUE": invalid identifier

[fairking]

Oracle: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production SQLKata: 2.3.3 .Net Core 3.1

C#:

 var subQuery = Db.Query(userAlias)
                    .Take(10)
                    .Select(bean)
                    .WhereFalse(userAlias.Col(x => x.IsDeleted))
                    .WhereTrue(userAlias.Col(x => x.IsEnabled))
                    .OrderBy(userAlias.Col(x => x.CreatedDate));

Compiled SQL Query:

could not execute query
[ SELECT * FROM (SELECT usr.email Email, usr.user_name UserName FROM MATEUSZ.core_users usr WHERE usr.discriminator = :p0 AND usr.is_deleted = false AND usr.is_enabled = true ORDER BY usr.created_date) WHERE ROWNUM <= :p1 ]
Positional parameters:  #0>AppUser #1>1000

Please note the usr.is_deleted = false AND usr.is_enabled = true is wrong, instead it must be usr.is_deleted = 0 AND usr.is_enabled = 1.

Error:

ORA-00904: "TRUE": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 1 Column: 175

However if I change as follows:

 var subQuery = Db.Query(userAlias)
                    .Take(10)
                    .Select(bean)
                    .Where(userAlias.Col(x => x.IsDeleted), false)
                    .Where(userAlias.Col(x => x.IsEnabled), true)
                    .OrderBy(userAlias.Col(x => x.CreatedDate));

the error disappears.

[ahmad-moussawi]

Can you provide a relevant technical doc from Oracle?

[fairking]

Sorry, there is no false or true type in Oracle but rather 0 or 1.

Disscussion: https://stackoverflow.com/questions/3726758/is-there-any-boolean-type-in-oracle-databases

In my opinion the methods WhereTrue|WhereFalse don't make any sense and must be removed from SQLKata because Oracle users can use anything in regarding the boolean type ('Y'/'N', 1/0, '1'/'0', 'True'/'False'). So SQLKata cannot predictate which type needs to be used in query. Or there must be some additional setting on the Oracle Compiler which each developer can amend in case of difference.

In my case I use NUMBER(1) type to use oracle column as a boolean. NHibernate has no issues with that column type having on the entity side bool type on the property passing 1 or 0 in the following query .Where("is_enabled", true).