@lericol just to confirm, are you using PostgreSQL as the database?

This seems very much like #15809. It looks to be a rather odd bug. I can confirm it happens in PostgreSQL, but not in MySQL.

Exact reproduction steps

0. Use PostgreSQL database.
1. Create a new collection test.
2. In collection test, create a new string field called name.
3. Create 3 items in collection test, with names T1, T2, and T3 respectively.
4. Make a GET request to /items/test?search=710406684325 with the necessary read permission.
5. (optional) try the hooks extension shared below.

Findings

It seems like the "issue" precisely happens with this line:

as .where({ "test.id": 710406684325 }) is causing trouble in postgres, but not .whereRaw('??.?? = ??', ['test', 'id', 710406684325]). However both works perfectly for MySQL.

What's particularly odd is both syntax actually generates the exact same SQL query, at least from what I've found. Here's a sample hooks extension to further illustrate it:

module.exports = ({ init }, { database: knex }) => {
	init('app.before', async () => {
		const collection = 'test';
		const name = 'id';
		const number = 710406684325;

		const failedQuery = knex.from(collection).where({ [`${collection}.${name}`]: number }); // this is the same object syntax as apply-query.ts line 763
		// using number as the 3rd binding also doesn't work in Postgres, but works in MySQL
		// .whereRaw(`??.?? = ?`, [collection, name, number]);
		const successfulQuery = knex.from(collection).whereRaw(`??.?? = ${number}`, [collection, name]);

		// both generates the exact same query.
		// PostgreSQL: 'select * from "test" where "test"."id" = 710406684325'
		// MySQL: 'select * from `test` where `test`.`id` = 710406684325'
		console.log({ rawFailedQuery: failedQuery.toString() });
		console.log({ rawSuccessfulQuery: successfulQuery.toString() });

		// somehow for failedQuery, `710406684325` is turned into string `"710406684325"` via pg driver
		failedQuery.then((r) => console.log({ failedQuery: r })).catch((e) => console.error(e));
		successfulQuery.then((r) => console.log({ successfulQuery: r })).catch((e) => console.error(e));
	});
};

Testing with psql in the terminal also seems to indicate that the raw SQL query above should work:

• Success: raw SQL that is built by Knex

  

• Fail: intentionally add single quotes to make it throw the same error as reported

  

With the information at hand, I can only assume it's something to do with the pg driver itself, rather than Knex or Directus. However it may require more research to verify this assumption.

@lericol just to confirm, are you using PostgreSQL as the database?

This seems very much like #15809. It looks to be a rather odd bug. I can confirm it happens in PostgreSQL, but not in MySQL.

Exact reproduction steps

0. Use PostgreSQL database.

1. Create a new collection `test`.

2. In collection `test`, create a new string field called `name`.

3. Create 3 items in collection `test`, with names `T1`, `T2`, and `T3` respectively.

4. Make a GET request to `/items/test?search=710406684325` with the necessary read permission.

5. (optional) try the hooks extension shared below.

Findings

It seems like the "issue" precisely happens with this line:

directus/api/src/utils/apply-query.ts

Line 762 in 13a720b

if (!isNaN(number)) this.orWhere({ [`${collection}.${name}`]: number });

as .where({ "test.id": 710406684325 }) is causing trouble in postgres, but not .whereRaw('??.?? = ??', ['test', 'id', 710406684325]). However both works perfectly for MySQL.

What's particularly odd is both syntax actually generates the exact same SQL query, at least from what I've found. Here's a sample hooks extension to further illustrate it:

module.exports = ({ init }, { database: knex }) => {
	init('app.before', async () => {
		const collection = 'test';
		const name = 'id';
		const number = 710406684325;

		const failedQuery = knex.from(collection).where({ [`${collection}.${name}`]: number }); // this is the same object syntax as apply-query.ts line 763
		// using number as the 3rd binding also doesn't work in Postgres, but works in MySQL
		// .whereRaw(`??.?? = ?`, [collection, name, number]);
		const successfulQuery = knex.from(collection).whereRaw(`??.?? = ${number}`, [collection, name]);

		// both generates the exact same query.
		// PostgreSQL: 'select * from "test" where "test"."id" = 710406684325'
		// MySQL: 'select * from `test` where `test`.`id` = 710406684325'
		console.log({ rawFailedQuery: failedQuery.toString() });
		console.log({ rawSuccessfulQuery: successfulQuery.toString() });

		// somehow for failedQuery, `710406684325` is turned into string `"710406684325"` via pg driver
		failedQuery.then((r) => console.log({ failedQuery: r })).catch((e) => console.error(e));
		successfulQuery.then((r) => console.log({ successfulQuery: r })).catch((e) => console.error(e));
	});
};

Testing with psql in the terminal also seems to indicate that the raw SQL query above should work:

* Success: raw SQL that is built by Knex
  ![](https://user-images.githubusercontent.com/42867097/227826057-28f8e1e7-2858-4ff5-b3fd-272ea6358cea.png)

* Fail: intentionally add single quotes to make it throw the same error as reported
  ![](https://user-images.githubusercontent.com/42867097/227826118-ee7db74e-1740-4aa5-a969-cb419f5bc2c8.png)

With the information at hand, I can only assume it's something to do with the pg driver itself, rather than Knex or Directus. However it may require more research to verify this assumption.

Yes, PostgreSQL

After further investigating here are my findings , the way knex is parsing whereRaw and passing it to the PG driver is diffrent then the way it's pass the where

so i had a collection called currencies , and a int field and i init a connection with the pg library directly

const { Pool } = require('pg')
const connectionString = "postgres://************/postgres"
const pool = new Pool({
  connectionString: connectionString,
})
const collection = 'currencies';
const name = 'sort';
const number = 710406684325;

using whereRaw and orWhereRaw is the equivalent of the following code , it's will ignore the error how ever the results are actually always 0 row unless there is a diffrent orWhereRaw , so it's not really a successfulQuery , more like one that ignore errors.

const successfulQuery = "SELECT * FROM " + collection + " WHERE " + collection + "." + name + " = " + number;
pool.query(successfulQuery, function(err, rows) {
  if(err) throw err
  console.log(rows)
})

in the other hand using where ( orWhere ) is the equivalent of the following code , the code will perform checks internally using the ( libpq library ) , and show the out of range error

const failedQuery = "SELECT * FROM " + collection + " WHERE " + collection + "." + name + " = $1";
pool.query(failedQuery, [number], function(err, rows) {
  if(err) throw err
  console.log(rows)
  pool.end()
})

so using this.orWhereRaw instead of this.orWhere will be the solution

Based on #17955 (review) a potential fix addressing this issue should meet the following criteria:

• Handle this limitation of PostgreSQL in a specific helper, such that other vendors can remain with the original approach
• Add a blackbox test for this scenario (also to verify if PostgreSQL being the sole outlier here)

Would be nice to see a fix for this

I'd love to be able to use a barcode scanner on the search field :)