Regression in 8.2.2 - Prepared statements break nested queries if there are "gaps"

[megakoresh]

To reproduce

Happens when using prepared statements which are references in nested queries and there are gaps (unused values) between the sent values. Yes, very obscure, but it worked in 8.2.1 and doesn't in 8.2.2

Create tables and populate values

CREATE TABLE test_table (
  value double,
  state symbol,
  opened_at timestamp
) timestamp(opened_at) PARTITION BY YEAR;

INSERT INTO test_table(value,state,opened_at) VALUES
(1, 'OPEN', dateadd('d', -1, now())),
(4, 'CLOSED', dateadd('d', -2, now())),
(2, 'CLOSED', dateadd('d', -3, now())),
(7, 'CLOSED', dateadd('d', -4, now())),
(1, 'CLOSED', dateadd('d', -5, now()));

Run query:

WITH s AS (SELECT * from test_table WHERE value < 7) SELECT s.state, s.opened_at FROM s WHERE 1=1 AND state <> 'OPEN';

Observe as the query returns results without issues. Now open node.js repl and do this:

$ npx ts-node
> import { Pool } from "pg";
> const pool = new Pool({ host: 'localhost', port: 8812, user: 'admin', password: 'quest', database: 'quest', max: 10});
> let res = await pool.query("WITH s AS (SELECT * from test_table WHERE value < $1) SELECT s.state, s.opened_at FROM s WHERE 1=1 AND state <> $2", [7, "OPEN"]);
> res = await pool.query("WITH s AS (SELECT * from test_table WHERE value < $1) SELECT s.state, s.opened_at FROM s WHERE 1=1 AND state <> $4", [7, "", "", "OPEN"]);
> res = await pool.query("WITH s AS (SELECT * from test_table WHERE value < 7) SELECT s.state, s.opened_at FROM s WHERE 1=1 AND state <> 'OPEN'");

Observe as the one time that you have these prepared statements with a gap in between them fails with that weird exception and the rest succeed. The same code works fine in 8.2.1 and we have not changed the postgres library during the upgrade. So regression is on questdb side, plz investigate!

QuestDB version:

8.2.2

OS, in case of Docker specify Docker and the Host OS:

Ubuntu 22.04 (Docker)

File System, in case of Docker specify Host File System:

ext4

Full Name:

Stanislav Simovski

Affiliation:

Solita Oy

Have you followed Linux, MacOs kernel configuration steps to increase Maximum open files and Maximum virtual memory areas limit?

• Yes, I have

Additional context

The regression is present in 8.2.3 also

[bluestreak01]

can you try taking '7' out of the quote marks?

> res = await pool.query("WITH s AS (SELECT * from test_table WHERE value < 7) SELECT s.state, s.opened_at FROM s WHERE 1=1 AND state <> 'OPEN'");

I cannot reproduce the error if i do that. SQL with quote marks, e.g.

WITH s AS (SELECT * from test_table WHERE value < '7') SELECT s.state, s.opened_at FROM s WHERE 1=1 AND state <> 'OPEN'

gives consistent error in via PG call and Web Console. We can probably fix comparison of "double" and "character", but that's not PG specific.

[megakoresh]

@bluestreak01 yes, sorry, the quotes on the 7 were just a mistake. To be clear:

res = await pool.query("WITH s AS (SELECT * from test_table WHERE value < $1) SELECT s.state, s.opened_at FROM s WHERE 1=1 AND state <> $4", [7, "", "", "OPEN"]);

Gives error Cannot invoke "io.questdb.cairo.sql.Function.getType()" because "f" is null

and

> res = await pool.query("WITH s AS (SELECT * from test_table WHERE value < 7) SELECT s.state, s.opened_at FROM s WHERE 1=1 AND state <> 'OPEN'");

works fine. The error comes only when using prepared statements when there is a "gap" between the statements that are used. E.g. [7, "unused", "unused", "OPEN"] and the query references $1 and $4. If the prepared query parameters are sequential with no gaps, the use-case works fine also, e.g.

res = await pool.query("WITH s AS (SELECT * from test_table WHERE value < $1) SELECT s.state, s.opened_at FROM s WHERE 1=1 AND state <> $2", [7, "OPEN"]);

Works fine. Its a niche use-case and we only saw this because of our query builder which put optional parameters into fixed order in the prepared statement query. However this method of query building is not really that uncommon so if anyone tries to use a query builder with 8.2.2 and above, they are likely to run into this issue.

On 8.2.1 all examples work fine.

[bluestreak01]

thanks for the clarification, i can reproduce the issue with

res = await pool.query("WITH s AS (SELECT * from test_table WHERE value < $1) SELECT s.state, s.opened_at FROM s WHERE 1=1 AND state <> $4", [7, "", "", "OPEN"]);

The error comes only when using prepared statements when there is a "gap" between the statements that are used. E.g. [7, "unused", "unused", "OPEN"] and the query references $1 and $1.

yes, i understand the definition of "gap" now, thank you. We will fix asap

[bluestreak01]

we have a fix for this issue in the PR just above. That said, this is an anti-pattern to be aware of. Some PG clients, such as python psycopg and rust will error out on the client side if you pass sparse parameter indexes.