Hi,

I've spotted a regression with sqlite 3.38.0. This involves a quite complex database and request. I've done my best to simplify down to the minimum scenario I could identified.

Dump of a (simplified version) of the database (the values of the sqlite1_stat table are the ones of the full database):

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;

CREATE TABLE extent(
    auth_name TEXT NOT NULL,
    code INTEGER NOT NULL,
    south_lat FLOAT,
    CONSTRAINT pk_extent PRIMARY KEY (auth_name, code)
);
INSERT INTO extent VALUES('PROJ',1,1);

CREATE TABLE usage(
    object_table_name TEXT NOT NULL,
    object_auth_name TEXT NOT NULL,
    object_code INTEGER NOT NULL,
    extent_auth_name TEXT NOT NULL,
    extent_code INTEGER NOT NULL
);
INSERT INTO usage VALUES('other_transformation','PROJ',1,'PROJ',1);
INSERT INTO usage VALUES('other_transformation','PROJ',2,'PROJ',1);

CREATE TABLE grid_transformation(
    auth_name TEXT NOT NULL,
    code INTEGER NOT NULL,
    name TEXT NOT NULL,

    source_crs_auth_name TEXT NOT NULL,
    source_crs_code INTEGER NOT NULL,
    target_crs_auth_name TEXT NOT NULL,
    target_crs_code INTEGER NOT NULL,

    accuracy FLOAT,

    CONSTRAINT pk_grid_transformation PRIMARY KEY (auth_name, code)
);

CREATE TABLE other_transformation(
    auth_name TEXT NOT NULL,
    code INTEGER NOT NULL,
    name TEXT NOT NULL ,

    source_crs_auth_name TEXT NOT NULL,
    source_crs_code INTEGER NOT NULL,
    target_crs_auth_name TEXT NOT NULL,
    target_crs_code INTEGER NOT NULL,

    accuracy FLOAT,

    CONSTRAINT pk_other_transformation PRIMARY KEY (auth_name, code)
);

INSERT INTO other_transformation VALUES('PROJ',1,'N43 height to N60 height','EPSG',8675,'EPSG',5717,0.01);
INSERT INTO other_transformation VALUES('PROJ',2,'N60 height to N2000 height','EPSG',5717,'EPSG',3900,0.01);

CREATE TABLE supersession(
    superseded_table_name TEXT NOT NULL,
    superseded_auth_name TEXT NOT NULL,
    superseded_code INTEGER NOT NULL,
    replacement_table_name TEXT NOT NULL,
    replacement_auth_name TEXT NOT NULL,
    replacement_code INTEGER NOT NULL,
    same_source_target_crs BOOLEAN NOT NULL
);

ANALYZE sqlite_master;
INSERT INTO sqlite_stat1 VALUES('grid_transformation','grid_transformation_idx','799 267 6 6 2');
INSERT INTO sqlite_stat1 VALUES('other_transformation','other_transformation_idx','373 75 5 5 3');
INSERT INTO sqlite_stat1 VALUES('supersession','supersession_idx','1206 201 173 2');
INSERT INTO sqlite_stat1 VALUES('usage','idx_usage_object','22233 2022 530 2');
INSERT INTO sqlite_stat1 VALUES('extent','extent','4160 832 1');
CREATE INDEX idx_usage_object ON usage(object_table_name, object_auth_name, object_code);
CREATE INDEX supersession_idx ON supersession(superseded_table_name, superseded_auth_name, superseded_code);
CREATE INDEX grid_transformation_idx ON grid_transformation(source_crs_auth_name, source_crs_code, target_crs_auth_name, target_crs_code);
CREATE INDEX other_transformation_idx ON other_transformation(source_crs_auth_name, source_crs_code, target_crs_auth_name, target_crs_code);

CREATE VIEW coordinate_operation_view AS
    SELECT 'grid_transformation' AS table_name, auth_name, code, name,
           source_crs_auth_name,
           source_crs_code, target_crs_auth_name, target_crs_code,
           accuracy FROM grid_transformation
    UNION ALL
    SELECT 'other_transformation' AS table_name, auth_name, code, name,
           source_crs_auth_name,
           source_crs_code, target_crs_auth_name, target_crs_code,
           accuracy FROM other_transformation;
COMMIT;

Request:

echo "SELECT v1.accuracy AS accuracy1, v2.accuracy as accuracy2, a1.south_lat AS south_lat1, a2.south_lat AS south_lat2 FROM coordinate_operation_view v1 JOIN coordinate_operation_view v2 ON v1.target_crs_auth_name = v2.source_crs_auth_name AND v1.target_crs_code = v2.source_crs_code LEFT JOIN supersession ss1 ON ss1.superseded_table_name = v1.table_name AND ss1.superseded_auth_name = v1.auth_name AND ss1.superseded_code = v1.code AND ss1.superseded_table_name = ss1.replacement_table_name AND ss1.same_source_target_crs = 1 JOIN usage u1 ON u1.object_table_name = v1.table_name AND u1.object_auth_name = v1.auth_name AND u1.object_code = v1.code JOIN extent a1 ON a1.auth_name = u1.extent_auth_name AND a1.code = u1.extent_code JOIN usage u2 ON u2.object_table_name = v2.table_name AND u2.object_auth_name = v2.auth_name AND u2.object_code = v2.code JOIN extent a2 ON a2.auth_name = u2.extent_auth_name AND a2.code = u2.extent_code WHERE v1.source_crs_auth_name = 'EPSG' AND v1.source_crs_code = '8675' AND v2.target_crs_auth_name = 'EPSG' AND v2.target_crs_code = '3900' AND south_lat1 != 0 AND south_lat2 != 0 ORDER BY accuracy1 + accuracy2" | sqlite3 test.db

With sqlite < 3.38.0, the following correct result is returned:

With sqlite 3.38.0, no row is returned.

Doing a git bisect session on the https://github.com/sqlite/sqlite mirror spots the following commit as the offending one:

commit a11c5e22b5eb32cdf2061a6c19fd7bf4d2a36e05
Author: drh <>
Date:   Thu Dec 9 18:44:03 2021 +0000

    Enable bloom filters for the right table of a LEFT JOIN.  Fix unreachable
    branches.
    
    FossilOrigin-Name: d342ab722de1fc3f34219c9755253db9d88eb50a6fa5cc257207008b00e7fc03

I've tested also with the latest commit of the master branch at time of writing (https://github.com/sqlite/sqlite/commit/6f9d68817cddd4491a225403e8aa9125ee199fe5), and the issue is still present on it.

Best regards, Even