Zenn
Closed10

CodeIgniter4.4.5 Type char casting bug PostgreSQL with updateBatch()

PHP
PostgreSQL
codeigniter
codeigniter4
naentenaente
  • PHP 7.4.33
  • CodeIgniter 4.4.5
  • PostgreSQL 9.2.24
SELECT version();

PostgreSQL 9.2.24 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

CREATE TABLE test_table
(
    id      integer,
    char    char(10),
    varchar varchar(10)
);

INSERT INTO test_table (id, char, varchar) VALUES (1, 'foo', 'foo');
INSERT INTO test_table (id, char, varchar) VALUES (2, 'bar', 'bar');
INSERT INTO test_table (id, char, varchar) VALUES (3, 'baz', 'baz');
id char varchar
1 foo foo
2 bar bar
3 baz baz
naentenaente
$ php spark make:model TestTable --suffix
app\Models\TestTableModel.php
<?php

namespace App\Models;

use CodeIgniter\Model;

class TestTableModel extends Model
{
    protected $table            = 'test_table';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = false;
    protected $returnType       = 'array';
    protected $useSoftDeletes   = false;
    protected $protectFields    = true;
    protected $allowedFields    = ['id', 'char', 'varchar'];
}
app\Controllers\TestController.php
<?php

namespace App\Controllers;

use App\Controllers\BaseController;
use App\Models\TestTableModel;

class TestController extends BaseController
{
    public function index(): string
    {
        $model = new TestTableModel();
        $data = [
            [
                'id'      => 1,
                'char'    => 'Foo',
                'varchar' => 'Foo',
            ],
            [
                'id'      => 2,
                'char'    => 'Bar',
                'varchar' => 'Bar',
            ],
            [
                'id'      => 3,
                'char'    => 'Baz',
                'varchar' => 'Baz',
            ],
        ];
        $model->updateBatch($data, 'id');
    }
}
naentenaente

実行された SQL

UPDATE "test_table"
SET "char"    = CAST(_u."char" AS CHARACTER),
    "varchar" = CAST(_u."varchar" AS CHARACTER VARYING)
FROM (SELECT 'Foo' "char", 1 "id", 'Foo' "varchar"
      UNION ALL
      SELECT 'Bar' "char", 2 "id", 'Bar' "varchar"
      UNION ALL
      SELECT 'Baz' "char", 3 "id", 'Baz' "varchar") _u
WHERE "test_table"."id" = CAST(_u."id" AS INTEGER)

実行結果

id char varchar
1 F Foo
2 B Bar
3 B Baz
naentenaente
SELECT version();

PostgreSQL 13.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

naentenaente
  • PostgreSQL 13.11
CREATE TABLE test_table
(
    id      integer,
    char    char(10),
    varchar varchar(10)
);

INSERT INTO test_table (id, char, varchar) VALUES (1, 'foo', 'foo');
INSERT INTO test_table (id, char, varchar) VALUES (2, 'bar', 'bar');
INSERT INTO test_table (id, char, varchar) VALUES (3, 'baz', 'baz');
id char varchar
1 foo foo
2 bar bar
3 baz baz 
UPDATE "test_table"
SET "char"    = CAST(_u."char" AS CHARACTER),
    "varchar" = CAST(_u."varchar" AS CHARACTER VARYING)
FROM (SELECT 'Foo' "char", 1 "id", 'Foo' "varchar"
      UNION ALL
      SELECT 'Bar' "char", 2 "id", 'Bar' "varchar"
      UNION ALL
      SELECT 'Baz' "char", 3 "id", 'Baz' "varchar") _u
WHERE "test_table"."id" = CAST(_u."id" AS INTEGER)
id char varchar
1 F Foo
2 B Bar
3 B Baz
naentenaente

対応いただいた内容を以下のファイルへ反映した。
vendor\codeigniter4\framework\system\Database\Postgre\Builder.php

実行された SQL

UPDATE "test_table"
SET "char"    = CAST(_u."char" AS CHARACTER(10)),
    "varchar" = CAST(_u."varchar" AS CHARACTER VARYING)
FROM (SELECT 'Foo' "char", 1 "id", 'Foo' "varchar"
      UNION ALL
      SELECT 'Bar' "char", 2 "id", 'Bar' "varchar"
      UNION ALL
      SELECT 'Baz' "char", 3 "id", 'Baz' "varchar") _u
WHERE "test_table"."id" = CAST(_u."id" AS INTEGER)

実行結果

id char varchar
1 Foo Foo
2 Bar Bar
3 Baz Baz
このスクラップは3ヶ月前にクローズされました