I am testing out Nextcloud with CockroachDB. And I think I may have encountered …a bug in CockroachDB, but with the database module for PostgreSQL from PHP-PDO.
**Describe the problem**
It looks like CockroachDB converts/misinterprets an integer value for a string value
**To Reproduce**
To trigger the bug, just a database (in this example 'nextcloud') and connect to it and run the following command.
```sql
CREATE TABLE IF NOT EXISTS filecache (fileid INT8 NOT NULL, storage INT8 NOT NULL DEFAULT 0, path VARCHAR(4000) NULL DEFAULT NULL, path_hash VARCHAR(32) NOT NULL DEFAULT '');
INSERT INTO filecache (fileid, storage, path, path_hash) VALUES (1, 828084885788491777, 'files/New folder/auto-dark-mode/.git', b2f888b162c19ade85a80fe0928876d7);
```
Now execute this PHP script up against your CockroachDB
```php
<?php
// Connect to the database
$host = 'db';
$dbname = 'nextcloud';
$username = 'nextcloud';
$password = 'NEXTCLOUD_PASSWORD';
$conn = null;
while ($conn === null) {
try {
$conn = new PDO("pgsql:host=$host;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::ATTR_AUTOCOMMIT, FALSE);
} catch(PDOException $e) {
echo 'Error: ' . $e->getMessage();
sleep(2);
}
}
sleep(3);
// Print database before Test
$sql = "SELECT * FROM filecache";
$stmt = $conn->prepare($sql);
try {
$stmt->execute();
$result = $stmt->fetchAll();
// Print the result
print("\nPrint database before Test: ");
print_r($result);
} catch(Driver\Exception $e) {
print_r($e);
}
// Send TEST the request
// Send TEST the request
// Bad SQL request
$sql = 'UPDATE "filecache" SET "storage" = ?, "path_hash" = MD5((CAST(? AS TEXT) || CAST(SUBSTR("path", ?) AS TEXT))), "path" = (CAST(? AS TEXT) || CAST(SUBSTR("path", ?) AS TEXT)) WHERE ("storage" = ?) AND ("path" LIKE ?)';
// Good SQL request
// $sql = 'UPDATE "filecache" SET "storage" = ?, "path_hash" = MD5((CAST(? AS TEXT) || CAST(SUBSTR("path", CAST(? AS INT)) AS TEXT))), "path" = (CAST(? AS TEXT) || CAST(SUBSTR("path", CAST(? as INT)) AS TEXT)) WHERE ("storage" = ?) AND ("path" LIKE ?)';
$stmt = $conn->prepare($sql);
# Index Value Type (1 = int, 2 = string)
$stmt->bindValue(1 , 828084885788491777, 1);
$stmt->bindValue(2 , "files_trashbin/files/auto-dark-mode.d1673181254", 2);
$stmt->bindValue(3 , 32, 1);
$stmt->bindValue(4 , "files_trashbin/files/auto-dark-mode.d1673181254", 2);
$stmt->bindValue(5 , 32, 1);
$stmt->bindValue(6 , 828084885788491777, 1);
$stmt->bindValue(7 , "files/New folder/auto-dark-mode/%", 2);
try {
$stmt->execute();
print("\nIt works!\n");
} catch(Driver\Exception $e) {
print_r($e);
}
// Print database after Test
$sql = "SELECT * FROM filecache";
$stmt = $conn->prepare($sql);
try {
$stmt->execute();
$result = $stmt->fetchAll();
// Print the result
print("\nPrint database after Test: ");
print_r($result);
} catch(Driver\Exception $e) {
print_r($e);
}
// Close the connection
$conn = null;
?>
```
and it will return the following error (even through the same code works fine on PostgreSQL)
```
Fatal error: Uncaught PDOException: SQLSTATE[23502]: Not null violation: 7 ERROR: null value in column "path_hash" violates not-null constraint in /var/www/html/test-db.php:52
```
So at first, it just looks like a violation of a constraint, and it technically is, but there is more too it.
If we comment-out the "Bad SQL request" (line 43) and remove the comment from the "Good SQL request" (line 45) and try again, everything works.
So, what is the difference? Well, basically both places there
```sql
CAST(SUBSTR("path", ?) AS TEXT)
```
is used, it was changed to
```sql
CAST(SUBSTR("path", CAST(? AS INT)) AS TEXT))
```
Simply put, I make sure that the values declared in line 52 & 54 are converted to `INT`, even through in the request send to the database, they were already set to the type `INT`.
The problem with the value `32` in line 52 & 54 being considered a string by CockroachDB is also confirmed by looking in the log file `cockroach-data/logs/cockroach-sql-exec.log`.
```
I230108 16:38:08.241467 1653 9@util/log/event_log.go:32 ⋮ [n1,client=192.168.96.3:57802,user=‹nextcloud›] 277 ={
"Timestamp":1673195888238600832,
"EventType":"query_execute",
"Statement":"UPDATE ‹\"\"›.‹\"\"›.‹filecache› SET ‹storage› = $1, ‹path_hash› = md5((CAST($2 AS STRING) || CAST(substr(‹path›, $3) AS STRING))), ‹path› = (CAST($4 AS STRING) || CAST(substr(‹path›, $5) AS STRING)) WHERE (‹storage› = $6) AND (‹path› LIKE $7)",
"Tag":"UPDATE",
"User":"‹nextcloud›",
"PlaceholderValues":[
"‹828084885788491777›",
"‹'files_trashbin/files/auto-dark-mode.d1673181254'›",
"‹'32'›",
"‹'files_trashbin/files/auto-dark-mode.d1673181254'›",
"‹'32'›",
"‹828084885788491777›",
"‹'files/New folder/auto-dark-mode/%'›"
],
"ExecMode":"exec",
"SQLSTATE":"23502",
"ErrorText":"null value in column ‹\"path_hash\"› violates not-null constraint",
"Age":2.736035,
"FullTableScan":true,
"TxnCounter":5
}
```
Here we can see in the `PlaceholderValues` the value `'32'` is wrapped with `'` just like the strings `'files_trashbin/files/auto-dark-mode.d1673181254'` & `'files/New folder/auto-dark-mode/%'`, but the integer `828084885788491777` is not wrapped with `'` because it is recognized as an integer.
**Expected behavior**
I expect that the function `substr(input: varbit, start_pos: int) → varbit` described in the documented:
https://www.cockroachlabs.com/docs/stable/functions-and-operators.html#string-and-byte-functions
would inform the part of CockroachDB which interprets incoming values that the value used for `start_pos` should be interpreted as an integer. Or at the very least throw an exception saying
```
The parameter `start_pos` in the function `substr` requires an integer value not an string value
```
or something like that 😁
**Additional data / screenshots**
I have submitted all my logs and SQL queries in the reproduce section
If applicable, add screenshots to help explain your problem.
**Environment:**
- CockroachDB version: v22.2.1 (at the time latest container image)
- Container Image: nextcloud:latest
- Client app: PHP-PDO
**Additional context**
I have created a docker-compose environment which reproduces the error, using my script. I hope this will help the developer(s).
The environment can switch between using CockroachDB & PostgreSQL.
[cockroach_bug.zip](https://github.com/cockroachdb/cockroach/files/10369104/cockroach_bug.zip)
Jira issue: CRDB-23214
