Do you have a test case that demonstrates the problem?

I built the v3.23.1 CLI and submitted this input:

create table Lefty(name text, occupation text);
create table Righty(name text, age int);
insert into Lefty values ('Ike', 'general'), ('Santa', 'deliverer');
insert into Righty values ('Ike', 131);
select l.name, l.occupation, r.age from
 Lefty l left join Righty r on l.name=r.name;

This is the result:

Ike|general|131
Santa|deliverer|

As far as I can see, this is correct. Perhaps you could share a minimal example of some table definitions, some data for them, and a query which demonstrates the "incorrect left join behavior" you report.

Wonder what could be an issue.

In the column(s) that you are joining with, do the column(s) in the left table

1. Have null values?
2. Non unique values?
3. Is your left table defined such that it is case insensitive?

None of the above is relevant.

If the case of a column being joined on is immaterial, why aren't these two results identical?

sqlite> with
   ...> LEFTY (store,area) AS (Values('S1','A1'),('S2','A1'),('S3','A1'),('S4','A2'),('S5','A1')),
   ...> RIGHTY(area, sku, price) AS (Values('a1','P1',10),('A1','P3',11),('A2','P5',13),('A2','P7', 15))
   ...> select
   ...> l.store, l.area, r.sku, r.area, r.price
   ...> from  Lefty l left join Righty r on l.area=r.area;
store  area  sku  area  price
-----  ----  ---  ----  -----
S1     A1    P3   A1    11
S2     A1    P3   A1    11
S3     A1    P3   A1    11
S4     A2    P5   A2    13
S4     A2    P7   A2    15
S5     A1    P3   A1    11
sqlite> with
   ...> LEFTY (store,area) AS (Values('S1','A1'),('S2','A1'),('S3','A1'),('S4','A2'),('S5','A1')),
   ...> RIGHTY(area, sku, price) AS (Values('A1','P1',10),('A1','P3',11),('A2','P5',13),('A2','P7', 15))
   ...> select
   ...> l.store, l.area, r.sku, r.area, r.price
   ...> from  Lefty l left join Righty r on l.area=r.area;
store  area  sku  area  price
-----  ----  ---  ----  -----
S1     A1    P1   A1    10
S1     A1    P3   A1    11
S2     A1    P1   A1    10
S2     A1    P3   A1    11
S3     A1    P1   A1    10
S3     A1    P3   A1    11
S4     A2    P5   A2    13
S4     A2    P7   A2    15
S5     A1    P1   A1    10
S5     A1    P3   A1    11
sqlite>

The difference in the scripts is the first row value of the first column on the second table.

Neither of the queries has anything to do with LEFT JOIN. Try omitting LEFT, there will be no changes.

True, it has to do with

l.area=r.area;

where equality fails because of case.

Try the following with and without the COLLATE NOCASE to see the difference of lower/upper case influence:

sqlite> .mode qbox
sqlite> with
   ...>  LEFTY (store,area) AS (Values('S1','A1'),('S2','A1'),('S3','A1'),('S4','A2'),('S5','A1')),
   ...>  RIGHTY(area, sku, price) AS (Values('a1','P1',10),('A1','P3',11),('A2','P5',13),('A2','P7', 15))
   ...> select
   ...> l.store, l.area, r.sku, r.area, r.price
   ...> from  Lefty l 
   ...> join Righty r on l.area=r.area COLLATE NOCASE;
┌───────┬──────┬──────┬──────┬───────┐
│ store │ area │ sku  │ area │ price │
├───────┼──────┼──────┼──────┼───────┤
│ 'S1'  │ 'A1' │ 'P1' │ 'a1' │ 10    │
│ 'S1'  │ 'A1' │ 'P3' │ 'A1' │ 11    │
│ 'S2'  │ 'A1' │ 'P1' │ 'a1' │ 10    │
│ 'S2'  │ 'A1' │ 'P3' │ 'A1' │ 11    │
│ 'S3'  │ 'A1' │ 'P1' │ 'a1' │ 10    │
│ 'S3'  │ 'A1' │ 'P3' │ 'A1' │ 11    │
│ 'S4'  │ 'A2' │ 'P5' │ 'A2' │ 13    │
│ 'S4'  │ 'A2' │ 'P7' │ 'A2' │ 15    │
│ 'S5'  │ 'A1' │ 'P1' │ 'a1' │ 10    │
│ 'S5'  │ 'A1' │ 'P3' │ 'A1' │ 11    │
└───────┴──────┴──────┴──────┴───────┘

That is why I asked 3. Is your left table defined such that it is case insensitive? only to be met with None of the above is relevant.

Not sure if you are being obtuse on purpose or honestly not following the clear explanations.

The OP's problem had to do with Left-Hand table rows in a LEFT-Join not showing up, which, by definition MUST all show up (which is what it means to be a LEFT-join), otherwise it is just a normal (inner) join.

You then proposed several reasons why the join condition may fail - which would have been very helpful if the OP's problem was that the join-condition is failing, but that was not the problem - LEFT-hand table rows must all show up, regardless of the JOIN-condition, so helping with the join-condition, however helpful that may be, is not relevant to the question - which was pointed out.

It's a bit like someone asking about hooking a trailer, but their car doesn't seem to have a tow-bar. You can then say many things pointing out the best towing principles and tow-bar coupling techniques, all probably helpful, but irrelevant to the problem at hand.

Also let me me be clear - Please don't take anything personal - Gunter (or anyone else) pointing out something you said is not relevant or otherwise wrong or of no consequence, is not to pick a fight or to one-up you, it's so that the person asking the question is not confused by irrelevant stuff or led astray leaving them with hours of wild-goose-chasing. This is valuable for people asking questions trying to get to the bottom of a real niggle, and we've all been "corrected" (except maybe Igor Tandetnik, he's never been wrong). :)

From https://sqlite.org/lang_select.html

"If the join-operator is a "LEFT JOIN" or "LEFT OUTER JOIN", then after the ON or USING filtering clauses have been applied, an extra row is added to the output for each row in the original left-hand input dataset that corresponds to no rows at all in the composite dataset"

In your example, each and every LHS (S) row has two RHS (A) rows that satisfy the join condition (which happens to have a typo - there is no column out_area - from posting redacted output instead of true output).

If you had a row (S6,A3) then you would obtain a result row (S6,A3,NULL,NULL,NULL) as long as there is no A3 row the RHS table.