The median function behaves inconsistently

[LLMTesting-Ojiken]

Here's an example:

DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS v0;

CREATE TABLE t0
(
    c0 INT
);

CREATE TABLE v0
(
    c0 INT
);

INSERT INTO t0 (c0)
VALUES (NULL),
       (0.0280020140016927),
       (NULL);

INSERT INTO v0 (c0)
VALUES (1);

select median(col)
from (
         SELECT DISTINCT (json_quote(COALESCE(t0.c0, v0.c0))) as col
         FROM v0,
              t0
     );

If you run this code on the SQlite3 website, select will not be searchable and will throw an error.
Running it against the project's jar would produce an output of 0.5, but even that would be wrong; the correct answer would be 0.514001007000846.

[LLMTesting-Ojiken]

Let's add an example:

DROP TABLE IF EXISTS rt0;
DROP TABLE IF EXISTS rt1;

CREATE TABLE rt0 (
c0 INT,
c1 INT,
c2 INT
);

CREATE TABLE rt1 (
c0 INT,
c1 REAL,
c2 REAL
);

INSERT INTO rt0 (c0, c1, c2) VALUES
(2, 0, 1223944844),
(-1683817647, 0, 923566474),
(0, 0, 0),
(661783771, -1690752644, 0)
(661783772, 0, 0),
(1429326726, 0, 0),
374654478, 0, 0),
(-1995059026, 0, 947454331)
(1429326727, 0, 610255341),
(1429326728, 0, 0),
(1429326729, 0, 0),
(772973077, 0, 0),
(1429326730, -1672795388, -1217651817),
(1429326731, 0, 0);

INSERT INTO rt1 (c0, c1, c2) VALUES
(1, 0.0, 0.0),
(2, 0.0, 0.0),
(0, 0.0, 0.0),
(1624396833, 0.0, 0.0),
(1624396834, 0.0, 0.0);



SELECT median(col)
FROM (
SELECT (SUM('-765715140') OVER ()) AS col
FROM rt0, rt1
);

SELECT should result in -53600059800 (I ran it on SQLite3), but it outputs -2060452248.

[LLMTesting-Ojiken]

There seems to be a lot of inconsistency in this function, and I have a lot of examples here......

[LLMTesting-Ojiken]

DROP TABLE IF EXISTS vt0;

CREATE TABLE vt0 (
c0 NONE
);

INSERT INTO vt0 (c0) VALUES
('ej9'),
('ej9'),
('ej9'),
(NULL),
(NULL),
(' [*),
('ej9'),
('ej9'),
('ej9');


SELECT median(col)
FROM (
SELECT DISTINCT
LEAD(
(
((vt0.c0) NOT BETWEEN (vt0.c0) AND (vt0.c0))
IN (
((vt0.c0) NOT BETWEEN (vt0.c0) AND (vt0.c0)),
((vt0.c0) NOT BETWEEN (vt0.c0) AND (vt0.c0))
)
),
CAST((NOT (vt0.c0)) AS NUMERIC),
0.2980441957797325
) OVER ( ROWS '' PRECEDING EXCLUDE GROUP) AS col
FROM vt0
WHERE ('1074839690')
);

The result of the subquery is
| 1 |
│ NULL │
│ 0.298044195779733 |

So the correct median is 0.649022097889866 (verified on SQlite), not 0.5 (in the jar).

[gotson]

please check your scripts againt the sqlite cli, they don't work and produce errors.

[LLMTesting-Ojiken]

please check your scripts againt the sqlite cli, they don't work and produce errors.

Here's the script that I've confirmed works. You can check it out. This one is 0.649022097889866 in SQLite3 Fiddle, but when implemented based on this jar, it's 0.5.

DROP TABLE IF EXISTS vt0;

CREATE TABLE vt0 (
c0 NONE
);

INSERT INTO vt0 (c0) VALUES
('ej9'),
('ej9'),
(NULL);


SELECT median(col)
FROM (
SELECT DISTINCT
LEAD(
(
((vt0.c0) NOT BETWEEN (vt0.c0) AND (vt0.c0))
IN (
((vt0.c0) NOT BETWEEN (vt0.c0) AND (vt0.c0)),
((vt0.c0) NOT BETWEEN (vt0.c0) AND (vt0.c0))
)
),
CAST((NOT (vt0.c0)) AS NUMERIC),
0.2980441957797325
) OVER ( ROWS '' PRECEDING EXCLUDE GROUP) AS col
FROM vt0
WHERE ('1074839690')
);

[gotson]

So after checking this quickly, the website mentions (emphasis mine):

median(X)
The median() function returns the median value of all non-NULL X within a group. The median(X) function is equivalent to percentile_cont(X,0.5). See separate documentation on the percentile extension for additional information.
The median() function is available since SQLite version 3.51.0 (2025-11-04) if the amalgamation is compiled using -DSQLITE_ENABLE_PERCENTILE, or as a loadable extension in prior versions of SQLite.

The embedded native library is not compiled using this option, thus the median() function used is the one from the extensions (also from SQLite).

I did try to recompile the native lib using the aforementioned option, and remove the function from the extension, and i get the same result as the CLI.

[dddddddcoconut]

请对照你的脚本与 sqlite CLI 对照，它们无法工作并产生错误。

这是我确认有效的脚本。你可以去看看。这个在 SQLite3 Fiddle 中是 0.649022097889866，但基于这个 jar 实现时，是 0.5。

DROP TABLE IF EXISTS vt0;

CREATE TABLE vt0 (
c0 NONE
);

INSERT INTO vt0 (c0) VALUES
('ej9'),
('ej9'),
(NULL);

SELECT median(col)
FROM (
SELECT DISTINCT
LEAD(
(
((vt0.c0) NOT BETWEEN (vt0.c0) AND (vt0.c0))
IN (
((vt0.c0) NOT BETWEEN (vt0.c0) AND (vt0.c0)),
((vt0.c0) NOT BETWEEN (vt0.c0) AND (vt0.c0))
)
),
CAST((NOT (vt0.c0)) AS NUMERIC),
0.2980441957797325
) OVER ( ROWS '' PRECEDING EXCLUDE GROUP) AS col
FROM vt0
WHERE ('1074839690')
);

I have also reproduced the same issue: running the query in SQLite3 Fiddle yields a result of 0.649022097889866, while the environment based on this jar implementation returns 0.5. There is indeed a discrepancy between the two results.

[github-actions]

🎉 This issue has been resolved in 3.51.1.1 (Release Notes)