Bug: Query::matchSimpleBinds index problem only toolbar.

[byazrail]

Describe the bug A clear and concise description of what the bug is.

CodeIgniter 4 version 4.1.1

Expected behavior, and steps to reproduce if appropriate

Context

• OS: [Centos 7]
• Web server [nginx]
• PHP version [7.3.27]

[paulbalandan]

Can you try the latest develop branch? There's already fixes for Query so maybe your issue has been fixed.

[byazrail]

Can you try the latest develop branch? There's already fixes for Query so maybe your issue has been fixed.

I check, I will report the result.

[byazrail]

@paulbalandan It looks like it hasn't improved yet. It gives the same error.

[byazrail]

For the workaround; In Database\Query.php::debugToolbarDisplay; $sql = $this->getQuery(); change. $sql = $this->getOriginalQuery();

[paulbalandan]

Are you passing a raw SQL query or a BaseBuilder instance? Can you share your sql code thru the builder?

[byazrail]

Are you passing a raw SQL query or a BaseBuilder instance? Can you share your sql code thru the builder?

BaseBuilder Query;

$builder = $this->db->table('search_mview p');
$builder->select('p.*');
$builder->where('p.locations_ids ?| ARRAY[\'639\']', null, false);
$builder->where('p.status', 1);
$builder->orderBy('p.id', 'ASC');
$builder->limit(10);
$builder->get();

[byazrail]

Exception Details;

[paulbalandan]

Ok, I'll try to replicate this. One question though as I am not that immersed in database operations. What does this mean?

$builder->where('p.locations_ids ?| ARRAY[\'639\']', null, false);

[paulbalandan]

I'm suspecting the framework is matching ?| as a simple bind but has nothing to bind with, causing the undefined offset.

[byazrail]

Ok, I'll try to replicate this. One question though as I am not that immersed in database operations. What does this mean?

$builder->where('p.locations_ids ?| ARRAY[\'639\']', null, false);

PostgreSQL Jsonb array filter.

https://www.postgresql.org/docs/9.5/functions-json.html

[byazrail]

I'm suspecting the framework is matching ?| as a simple bind but has nothing to bind with, causing the undefined offset.

[byazrail]

MATERIALIZED VIEW Result. Only PostgreSQL jsonb column filter; locations_ids etc.

[byazrail]

My Solution;

[paulbalandan]

After setting up my Postgres database, I'm using this script but I'm getting the expected empty result. No exceptions where thrown.

<?php

require __DIR__ . '/system/Test/bootstrap.php';

use Config\Database;

$db = Database::connect();

$builder = $db->table('search p');
$sql = $builder
    ->select('p.*')
    ->where('p.location ?| ARRAY[\'639\']', null, false)
    ->where('p.status', 1)
    ->orderBy('p.id', 'ASC')
    ->limit(10)
    ->get();

var_dump($sql);

The dump would give me, as expected:

class CodeIgniter\Database\Postgre\Result#24 (8) {
  public $connID =>
  resource(78) of type (pgsql link)
  public $resultID =>
  resource(82) of type (pgsql result)
  public $resultArray =>
  array(0) {
  }
  public $resultObject =>
  array(0) {
  }
  public $customResultObject =>
  array(0) {
  }
  public $currentRow =>
  int(0)
  protected $numRows =>
  NULL
  public $rowData =>
  NULL
}

[byazrail]

@paulbalandan hi,

The normal query is already running. Problems on Debug / Toolbar / Database Queries. As I mentioned at the beginning.

[paulbalandan]

Confirming this issue. It seems this errors when on the browser. on CLI this works as fine.