search

smi2 / phpClickHouse

php ClickHouse wrapper
MIT License
741 stars 139 forks source link

Error when inserting boolean values #208

Open bpastukh opened 8 months ago

bpastukh commented 8 months ago

Hi, i'm facing following exception when executing insert with bool value when columns is of UInt8 type after upgrading to v1.5.2. After debug i've noticed that false is converted to 'false'.

In Statement.php line 183:

[ClickHouseDB\Exception\QueryException (400)] HttpCode:400 ; ;Code: 6, e.displayText() = DB::Exception: Cannot parse string 'false' as UInt8: syntax error at begin of string. Note: there are toUInt8OrZero and toUInt8OrNull functions, which returns zer o/NULL instead of throwing exception.: while executing 'FUNCTION CAST(assumeNotNull(_dummy_0) :: 2, 'UInt8' :: 1) -> cast(assumeNotNull(_dummy_0), 'UInt8') UInt8 : 4' (version 21.2.5.5 (official build))

Same problem is with Bool type (Type bool is internally stored as UInt8.) https://clickhouse.com/docs/en/sql-reference/data-types/boolean.

I suspect this commit to be the reason of the problem https://github.com/smi2/phpClickHouse/commit/c8c90068d0d999efcf42cfbc90548896bee75d5c

mrceperka commented 6 months ago

Got the same problem

MakarMS commented 6 months ago

Hi, i'm facing following exception when executing insert with bool value when columns is of UInt8 type after upgrading to v1.5.2. After debug i've noticed that false is converted to 'false'.

In Statement.php line 183:

[ClickHouseDB\Exception\QueryException (400)] HttpCode:400 ; ;Code: 6, e.displayText() = DB::Exception: Cannot parse string 'false' as UInt8: syntax error at begin of string. Note: there are toUInt8OrZero and toUInt8OrNull functions, which returns zer o/NULL instead of throwing exception.: while executing 'FUNCTION CAST(assumeNotNull(_dummy_0) :: 2, 'UInt8' :: 1) -> cast(assumeNotNull(_dummy_0), 'UInt8') UInt8 : 4' (version 21.2.5.5 (official build))

Same problem is with Bool type (Type bool is internally stored as UInt8.) https://clickhouse.com/docs/en/sql-reference/data-types/boolean.

I suspect this commit to be the reason of the problem c8c9006

Hi. The implicitness when working with Bool type has been removed. If your column is declared as Int8, you should pass an integer, not a boolean to it, hoping for implicit type casting. If you want to pass a boolean type from PHP, then you need to use the Bool type for your column, regardless of its internal implementation in the database. This removes the implicit and makes the code more maintainable.

I agree that this should not have been in the patch version, but the change itself is correct.