Boolean type always show `false` in OpenGaussDB

[AndrewDi]

Description

Boolean type display error。

Your environment

When I use pgcli connect to OpenGaussDB，boolean type always show false。

create table t2(a int,b boolean);
insert into t2 values(1,false);
insert into t2 values(1,true);
select * from t2;
+---+-------+
| a | b     |
|---+-------|
| 1 | False |
| 1 | False |
+---+-------+

• [x] Please provide your OS and version information： MacOS 14.4.1
• [x] Please provide your CLI version. 4.0.1

[AndrewDi]

But when I use 3.3.1 version pgcli, no such problem.

[dbaty]

Thanks for the report. By any chance, could you try to reproduce with the following versions: 3.4.1 and 3.5.0? My guess is that it might work with pgcli 3.4.1, but fail with 3.5.0. The culprit could be the migration from psycopg2 to psycopg3 (a dependency of pgcli), which landed in pgcli 3.5.0. If you can then try to connect directly with pyscopg2 and psycopg3, that would help pinpoint the source of the issue (which could then be reported to the psycopg project itself).

[AndrewDi]

I use mac brew，it‘s not easy to rollback pgcli version。

[AndrewDi]

I have do some test，sample code will always show false with psycopy 3.1.18.

#!/bin/python
import psycopg

if __name__ == '__main__':
    with psycopg.connect("dbname=testdb user=test host=192.168.64.5 port=5432") as conn:
        with conn.cursor() as cur:
            cur.execute('select true')
            for record in cur.fetchall():
                print(record)

@dbaty

[AndrewDi]

when I change version to psycopg2, new error rise.

Traceback (most recent call last):
  File "/Users/xxx/Developer/psycopytest/testpsycopg.py", line 8, in <module>
    for record in cur.fetchall():
psycopg2.InterfaceError: can't parse boolean: '1'

[j-bennet]

Newer versions of pgcli switched from psycopg2 to psysopg (also known as psycopg3). Those two libraries are not. compatible.

[AndrewDi]

It's weird, same version with same code, original pgsql display right result, opengauss display wrong result, but opengauss's client gsql do not have such problem. @j-bennet

[vimiix]

when I change version to psycopg2, new error rise.

Traceback (most recent call last):
  File "/Users/xxx/Developer/psycopytest/testpsycopg.py", line 8, in <module>
    for record in cur.fetchall():
psycopg2.InterfaceError: can't parse boolean: '1'

@AndrewDi Psycopg2 dose have this problem. If you use 'B' compatibility database in opengauss, the Boolean values will return 0 or 1 at the protocol layer, the pg driver is not supported. I once fixed this in this issue.

[j-bennet]

You do not want to switch to psycopg2, we switched to psycopg as of 3.5.0.

[AndrewDi]

bug already fixed, close this issue,thx!