Booleans confused as integers using PGSQL

[Invincibear]

Describe the bug Boolean handling in Medoo is broken. Medoo is mutating boolean $x = true into integer $x = 1, causing PGSQL to throw mismatched type errors.

Information

• Version of Medoo: 1.7.2
• Type of Database (MySQL, MSSQL, SQLite...): psql (PostgreSQL) 9.2.24
• System (Linux\Windows\Mac): CentOS 7

Detail Code Medoo turns this command:

$users = $db->select(
    'users',
    ['user_id', 'user_google_id', 'user_access_token', 'user_calendar_id'],
    ['user_sync_events' => true]
);

Into this SQL query: SELECT "user_id","user_google_id","user_access_token","user_calendar_id" FROM "warmap_users" WHERE "user_sync_events" = 1

Which results in this error:

ERROR: operator does not exist: boolean = integer LINE 2: "user_sync_events" = 1 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. SQL state: 42883 Character: 119

Expected output Results from SELECT query. Medoo not mutating booleans into integers.

[CydeSwype]

This should be fixed with my patch. Just waiting on repo maintainers to pick it up and merge the pull request. 🤞

[catfan]

@CydeSwype @Invincibear Sorry for the delay response. I'm extremely busy for other things in those days, and back to this project now.

I tested the problem again from PostgresSQL 9.4 (this is the version that Docker can support) to latest 12.1. The boolean query is still success.

Here is my test config: For 9.4:

  postgres94:
    image: postgres:9.4
    restart: always
    user: root
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: password

$database->create("account", [
    "user_id serial PRIMARY KEY",
    "user_name text",
    "is_active boolean"
]);

$database->insert("account", [
    "user_name" => "apple",
    "is_active" => true
]);

$database->insert("account", [
    "user_name" => "foo",
    "is_active" => true
]);

$database->insert("account", [
    "user_name" => "bar",
    "is_active" => false
]);

$data = $database->select("account", [
    "user_id",
    "user_name",
    "is_active"
], [
    "is_active" => true
]);

var_dump($data);

For latest 12.1

  postgres:
    image: postgres
    restart: always
    user: root
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: password

$database->create("account", [
   // Primary auto increase key for PostgresSQL 10.0+ 
    "user_id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY",
    "user_name text",
    "is_active boolean"
]);

$database->insert("account", [
    "user_name" => "apple",
    "is_active" => true
]);

$database->insert("account", [
    "user_name" => "foo",
    "is_active" => true
]);

$database->insert("account", [
    "user_name" => "bar",
    "is_active" => false
]);

$data = $database->select("account", [
    "user_id",
    "user_name",
    "is_active"
], [
    "is_active" => true
]);

var_dump($data);

Maybe you can upgrade PostgresSQL 9.4+ or latest that recommended. The version 9.2 is not officially supported anymore by PostgresSQL.

https://www.postgresql.org/docs/9.4/datatype-boolean.html