R13 - LEFT JOIN over materialized subquery uses RHS collation for range seek

[Pavan-Nambi]

CREATE TABLE l(id INTEGER, txt TEXT);
CREATE TABLE r(id INTEGER, txt TEXT COLLATE NOCASE);

INSERT INTO l VALUES (2, 'a');
INSERT INTO r VALUES (11, 'A');

SELECT 'anti', l.id
FROM l
WHERE NOT EXISTS (
  SELECT 1
  FROM r
  WHERE l.txt > r.txt
)

UNION ALL

SELECT 'left', l.id
FROM l
LEFT JOIN (
  SELECT r.*, 1 AS marker
  FROM r
) AS r
ON l.txt > r.txt
WHERE r.marker IS NULL
ORDER BY 1, 2;

Turso output:

left|2

SQLite output:

-- no rows

[Pavan-Nambi]

i'm assuming this is same bug.

  CREATE TABLE l(txt TEXT);
  CREATE TABLE r(txt TEXT COLLATE NOCASE);
  CREATE TABLE aux(txt TEXT);

  INSERT INTO l VALUES ('space ');
  INSERT INTO r VALUES ('zz'), ('ZZ');
  INSERT INTO aux VALUES ('zz');

  SELECT 'semi', r.txt
  FROM (
    SELECT r.txt
    FROM r
    JOIN aux ON r.txt = aux.txt
  ) AS r
  WHERE EXISTS (
    SELECT 1
    FROM l
    WHERE l.txt < r.txt
  )

  UNION ALL

  SELECT 'inner', r.txt
  FROM l
  JOIN (
    SELECT r.txt
    FROM r
    JOIN aux ON r.txt = aux.txt
  ) AS r
  ON l.txt < r.txt

  ORDER BY 1, 2;

Turso output:

inner|zz
inner|ZZ
semi|zz

SQLite output:

inner|zz
semi|zz