Ошибка оператор не существует integer boolean

I am having problem with NamedQuery when project is deployed to tomcat server on Linux machine. I have environment as:

1. CentOS 6.3
2. Postgres 8.4
3. JPA 2.1
4. Hibernate 3.2.8

NamedQuery is

Select dv FROM DocumentVersion dv where dv.isPublished=false

When this query is executed on windows machine, It works fine but when the project is deployed on server with above configuration, the pages display the exception with the following error:

ERROR 2015-01-04 22:30:33
org.hibernate.engine.jdbc.spi.SqlExceptionHelper:146 — ERROR: operator
does not exist: boolean = integer Hint: No operator matches the
given name and argument type(s). You might need to add explicit type
casts. Position: 649

When I change the query to :

Select dv FROM DocumentVersion dv where dv.isPublished =:isPublished

i.e. passing boolean as parameter to NamedQuery, it works fine on both platform.

I want to know if I need to add/update any configuration on linux machine or I am doing something wrong.

Table Schema

CREATE TABLE DocumentVersion (
  id SERIAL NOT NULL,
  uuid varchar(100) NOT NULL,
  displayName varchar(100) NOT NULL,
  fileName varchar(100) NOT NULL,
  fileDescription varchar(255) DEFAULT NULL,
  filePath varchar(2048) DEFAULT NULL,
  mimeType varchar(255) NOT NULL,
  version INTEGER NOT NULL,
  fileSize BIGINT NOT NULL,
  isPublished boolean NOT NULL DEFAULT false,
  PRIMARY KEY (id)
);

I am having problem with NamedQuery when project is deployed to tomcat server on Linux machine. I have environment as:

1. CentOS 6.3
2. Postgres 8.4
3. JPA 2.1
4. Hibernate 3.2.8

NamedQuery is

Select dv FROM DocumentVersion dv where dv.isPublished=false

When this query is executed on windows machine, It works fine but when the project is deployed on server with above configuration, the pages display the exception with the following error:

ERROR 2015-01-04 22:30:33
org.hibernate.engine.jdbc.spi.SqlExceptionHelper:146 — ERROR: operator
does not exist: boolean = integer Hint: No operator matches the
given name and argument type(s). You might need to add explicit type
casts. Position: 649

When I change the query to :

Select dv FROM DocumentVersion dv where dv.isPublished =:isPublished

i.e. passing boolean as parameter to NamedQuery, it works fine on both platform.

I want to know if I need to add/update any configuration on linux machine or I am doing something wrong.

Table Schema

CREATE TABLE DocumentVersion (
  id SERIAL NOT NULL,
  uuid varchar(100) NOT NULL,
  displayName varchar(100) NOT NULL,
  fileName varchar(100) NOT NULL,
  fileDescription varchar(255) DEFAULT NULL,
  filePath varchar(2048) DEFAULT NULL,
  mimeType varchar(255) NOT NULL,
  version INTEGER NOT NULL,
  fileSize BIGINT NOT NULL,
  isPublished boolean NOT NULL DEFAULT false,
  PRIMARY KEY (id)
);

How about this?

CREATE OR REPLACE FUNCTION equal_int_bool(x int, y bool) 
RETURNS BOOLEAN AS $$ 
begin
    return x = y::int;
end;
$$ LANGUAGE PLPGSQL IMMUTABLE STRICT;

CREATE OPERATOR = ( 
  leftarg = INTEGER, 
  rightarg = BOOLEAN,
  procedure = equal_int_bool);

CREATE OR REPLACE FUNCTION equal_bool_int(x bool, y int) 
RETURNS BOOLEAN AS $$ 
begin
    return x::int = y;
end;
$$ LANGUAGE PLPGSQL IMMUTABLE STRICT;

CREATE OPERATOR = ( 
  leftarg = BOOLEAN, 
  rightarg = INTEGER,
  procedure = equal_bool_int);

Example truths:

> select 0=false a, false=0 b, 1=true c, true=1 d;
 a | b | c | d
---+---+---+---
 t | t | t | t

Expect false for all other comparisons:

 > select 1=false a, 0=true b, 2=true c, -1=true d, true=-1 e;
 a | b | c | d | e
---+---+---+---+---
 f | f | f | f | f

Note: I’m a relative Postgresql novice, and am not recommending you create tons of random implicit conversions in your database, but thought it interesting that this was possible. Feel free to warn of dangers.

P.S. I’m deliberating whether a better semantic is 0=false, non-zero=true. Depends on your use case.

Recently I’ve tried using the plugin and I had some problems about its use.
I added the annotation on the top of my domain class, created the field at my database with default value false, but when I was trying to execute «MyDomain.findById()» I got an error like this:

ERROR util.JDBCExceptionReporter — ERROR: operator does not exist: boolean = integer
Dica: No operator matches the given name and argument type(s). You might need to add explicit type casts.

The databse I use is postgres version 1.20.0 (Jul 13 2015, rev: REL-1_20_0) and grails 2.3.6.
After searching on the internet and reading the plugin’s code I figure out the problem should be at the line 26 at the file LogicalDeleteGrailsPlugin.groovy, I just changed

from:
logicDeleteHibernateFilter(FilterDefinitionFactoryBean) {
defaultFilterCondition = «deleted = 0»
}
to:
logicDeleteHibernateFilter(FilterDefinitionFactoryBean) {
defaultFilterCondition = «deleted = ‘0’» //single quotes surround 0
}

Although it worked for me until now, I need to make more tests to be sure about that modification.

I’m writing that issue to ask if you guys have faced the same problem with other databases and if my modification should be a solution to that problem.

Recently I’ve tried using the plugin and I had some problems about its use.
I added the annotation on the top of my domain class, created the field at my database with default value false, but when I was trying to execute «MyDomain.findById()» I got an error like this:

ERROR util.JDBCExceptionReporter — ERROR: operator does not exist: boolean = integer
Dica: No operator matches the given name and argument type(s). You might need to add explicit type casts.

The databse I use is postgres version 1.20.0 (Jul 13 2015, rev: REL-1_20_0) and grails 2.3.6.
After searching on the internet and reading the plugin’s code I figure out the problem should be at the line 26 at the file LogicalDeleteGrailsPlugin.groovy, I just changed

from:
logicDeleteHibernateFilter(FilterDefinitionFactoryBean) {
defaultFilterCondition = «deleted = 0»
}
to:
logicDeleteHibernateFilter(FilterDefinitionFactoryBean) {
defaultFilterCondition = «deleted = ‘0’» //single quotes surround 0
}

Although it worked for me until now, I need to make more tests to be sure about that modification.

I’m writing that issue to ask if you guys have faced the same problem with other databases and if my modification should be a solution to that problem.

У меня проблема с NamedQuery, когда проект развертывается на сервере tomcat на машине Linux. У меня есть среда как:

1. CentOS 6.3
2. Postgres 8.4
3. JPA 2.1
4. Спящий режим 3.2.8

NamedQuery — это

Select dv FROM DocumentVersion dv where dv.isPublished=false

Когда этот запрос выполняется на машине Windows, он отлично работает, но когда проект развертывается на сервере с указанной выше конфигурацией, на страницах отображается исключение со следующей ошибкой:

ERROR 2015-01-04 22:30:33 org.hibernate.engine.jdbc.spi.SqlExceptionHelper: 146 — ERROR: оператор не существует: boolean = integer Подсказка: оператор не соответствует указанному имени и типу аргументов. Возможно, вам придется добавлять явные типы. Должность: 649

Когда я меняю запрос на:

Select dv FROM DocumentVersion dv where dv.isPublished =:isPublished

т.е. передавая boolean как параметр в NamedQuery, он отлично работает на обеих платформах.

Я хочу знать, нужно ли мне добавлять/обновлять любую конфигурацию на Linux-машине, или я делаю что-то неправильно.

Схема таблицы

CREATE TABLE DocumentVersion (
  id SERIAL NOT NULL,
  uuid varchar(100) NOT NULL,
  displayName varchar(100) NOT NULL,
  fileName varchar(100) NOT NULL,
  fileDescription varchar(255) DEFAULT NULL,
  filePath varchar(2048) DEFAULT NULL,
  mimeType varchar(255) NOT NULL,
  version INTEGER NOT NULL,
  fileSize BIGINT NOT NULL,
  isPublished boolean NOT NULL DEFAULT false,
  PRIMARY KEY (id)
);

После переноса базы данных mysql v5 на postgres v12 приложение Java Spring показывает ошибку ниже: ОШИБКА: оператор не существует: boolean = integer Подсказка: ни один оператор не соответствует заданному имени и типам аргументов. Возможно, вам потребуется добавить явное приведение типов.

2 ответа

Лучший ответ

select * from Order where active = 1

select * from Order where active = true

psql (12.4)
Type "help" for help.

postgres=# select true = 1;
ERROR:  operator does not exist: boolean = integer
LINE 1: select true = 1;
                    ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
postgres=# 

-- you'll need to write your own `bit_to_boolean()` function
CREATE CAST (BIT AS BOOLEAN)
  WITH FUNCTION bit_to_boolean(BIT)
  AS IMPLICIT;

postgres=# select true = 1;
ERROR:  operator does not exist: boolean = integer
LINE 1: select true = 1;
                    ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
postgres=# select true = '1';
 ?column? 
----------
 t
(1 row)

postgres=# select true = 't';
 ?column? 
----------
 t
(1 row)

postgres=# select true = 'f';
 ?column? 
----------
 f
(1 row)

postgres=# select true = '0';
 ?column? 
----------
 f
(1 row)

-- do this
SELECT * FROM table WHERE bool_col = <true/false>;

-- instead of this
SELECT * FROM table WHERE bool_col = <val>;