Yes, it returns an error that I believe says something to the effect of "SQL Error: near 'WITH': Syntax error", in any case it's not the same error coming from the sqlite library itself (and as reported by sqlite3_errstr()).
And while depending on which neighbor you're talking about, I wish, but no it doesn't "blow" anything up.

It doesn't matter if the data is just fake/example data or not, I still get the same result either way.
Well, I have actually explained what I'm trying to achieve in my very first post. And I do not know how else I can explain it, nor why some don't seem to be able to understand it, it's really baffling to me how it's so unclear to some.

I'm sorry, but I've answered your questions, I've provided the create table statements, the fields each table has, the data types for those same fields, along with my query exactly as it was when I got the error messages, along with the actual error messages I'm getting.

Surely, I've provided enough info for someone who knows a lot more than I about databases and SQL to be able to tell me exactly where I've gone wrong and exactly what code I need instead. Surely I have done that, particularly when I've seen other questions on other forums where less info was provided, but somebody who knew enough was still able to answer it and provide a solution.

So if you want to test my query, then go right ahead and use whatever data you darn well like and do whatever tests you want/need to do.

I'm sorry, but if I was able to solve this myself, I wouldn't be here asking in the first place. Clearly I can't, that's why I'm here asking.

So with all respect, can I please just get some actual answers now that I can understand, so I can move forward? As I cannot move forward unless someone can show me exactly where I've gone wrong, and/or exactly what it is I'm missing here in terms of the actual code, because there must be something wrong somewhere, there must be. Of course it would very much help a great deal if the error messages actually told me exactly what it's taking issue with, rather than some pointless message that tells me absolutely nothing apart of the obvious... not to mention documentation that is actually understandable to someone who isn't a world-renowned expert in databases, SQL, and/or C...

Your very verbose posts contain only marginal information and some threats.

Most probably YOU have neglected to verify that your copy of DB Browser for SQLite (IIRC a third party product) and your SQLite shell/library are indeed running the same release of SQLite; this would easily explain why the former does and the latter does not support "WITH".

It is also YOUR responsibility to provide sufficient information for others to easily reproduce your alleged problem. This includes the data required to reproduce it, the excpected results and the actual results. And maybe an explanation of why your expectations are more correct than the results.

Being belligerent is not going to improve the quality of answers provided - indeed I was very tempted to just ignore your temper tantrum. Feel free to ignore this last remark, which is very pertinent to "how to get good answers" but which you will undoubtedly regard as "off topic".

Why are you so angry? What did I say that's made you so angry?

I don't know how you define "marginal information", but I have to strongly disagree with that comment. As I believe I have provided enough information regarding what I'm trying to do for one thing, along with what I've got so far, INCLUDING the actual error messages I'm getting. I even said before that if further info is required, then tell me what that is and why it's required - so I can provide whatever info that's going to lead to a solution, that's why I said that.

I would have thought that DB Browser for SQLite WAS using the same sqlite library my code calls, so don't understand why it wouldn't be. That said, how exactly do I even check that?

Again, I don't know how you define "belligerent", but clearly we once again have a very different definition, as I don't believe I was. In any case, that's certainly not what I'm trying to do, nor why I come here.
And saying things like "Feel free to ignore this last remark, which is very pertinent to "how to get good answers" but which you will undoubtedly regard as "off topic"." is just looking for and provoking an argument.
However, I'm not going to bite and buy into it.

Therefore, if you don't want to help, then don't if that's how you want to "help". As, and, at the end of the day nobody is being forced to do anything. And I'm just looking for some help, not some pointless argument that helps nobody.
So feel free to ignore this thread in that case, I'm not holding any gun to your head forcing you to have any part in this thread, so go ahead, please yourself, that's fine.

see https://sqlite.org/lang_corefunc.html#sqlite_version

SELECT sqlite_version();

Further required info:

x) Actual data to reproduce the issue
x) expected result
x) delivered result

See my response below for sqlite versions.

I've already answered three questions you've asked. Again, and I'm really not trying to be rude or whatever, but there isn't any other way I can explain it.
So, once again:

1) It does not matter what the data is, real, fake, whatever you like/anything.
2) So I only get each path record ONCE, not multiple times if there are multiple file records associated with that same path record. Where ALL path and file records are selected, WITHOUT the same path record being selected more than *once*. There isn't any other way I know of to explain that. Again, it's very baffling to me as to why that seems so difficult to understand, absolutely baffled there.
3) The same error messages from the library itself and the sqlite shell. Although the other issue I spoke of before doesn't seem to be happening now, so I don't know what that was about. In any case, it seems like the only issue now is the error messages I'm getting - except with DB Browser for SQLite, which DOES give me the result I'm after.

I don't think pacman is reporting from the same library you are actually using. Both 3.45.x and 3.46.x know how to handle WITH, whereas 2.8.17 (from December 2005) clearly cannot

It would be so much easier if you would just SHOW rather than stubbornly insisting on EXPLAINING. It is not my job to invent test data for your schema and query.

OS: Manjaro Linux (with the latest updates)
SQLite library version: 3.46.1-1 (according to pacman)

So it looks like I'm a version(s) ahead of your system as far as the sqlite library itself is concerned.

DB Browser for SQLite sqlite version: 3.45.3
sqlite shell sqlite version: 2.8.17
sqlite library version: 3.46.1-1 (as already posted and once again according to pacman)

So it seems that while version 3.45.3 doesn't have an issue with my SQL query, the other versions do. So that at least does explain why it works in DB Browser for SQLite, but not the sqlite shell nor the sqlite library itself.

So what's changed that's broken my SQL query between versions 3.45.3 and 3.46.1?

As I feel that's where the answer is, and that could lead to a solution.
Thoughts?

I was shocked too. And while I'm not sure what the point of your post was (other than to state the bleeding obvious, that doesn't offer any new information - let alone actually help anyone); I'm not a Manjaro Linux developer, nor am I a Manjaro Linux package maintainer, so how is it my fault they've decided to use such an old version sqlite in the sqlite shell? Well, excuse me, but I have NO say or part in that. So I'd suggest you talk to them about that, as I'm just a humble user of Manjaro.

Sorry, just saw your post.

Well, it looks like that's what I'm going to have to do - although I did not know it was using such an old version in the first place until I made the post giving the version of sqlite used in my copy of the sqlite shell.

In any case, your post is rather helpful and thank you for your suggestion.

I've checked again, including with ldd, and my library most certainly is using sqlite version 3.46.1.
So I don't know what you were looking at, but pacman isn't lying to me.

Given that John Dennis reports that the same code works without error on sqlite 3.45.1, then something has clearly changed between 3.45.1 (or the very next version) and the latest version.
I have no idea what that is and I'll leave that up to Richard to figure out what's going on here.

It looks very much to me like it's some issue with the sqlite library itself, rather than my SQL code, but again, I'll leave that up the Richard decide.

ldd shows which libraries the image is asking for (when loaded by the current user).

To show the actual images loaded by the application (which may be running under a different user and loading different libraries), read the /proc/[pid]/maps file.

The assertions "running with 3.46.1" and "not recognizing WITH" tend to be mutually exclusive (unless the library is compiled with SQLITE_OMIT_CTE).

Again, this is still you EXPLAINING instead of SHOWING.

I didn't know I was even making a bug report in the first place. The same as I don't automatically blame the language itself (be that SQL, C, etc) if I have a problem, because usually when I have a problem it's because I've done something wrong, or haven't done something that I needed to. The same as I don't just blame someone else's library (like the C library) when I have a problem - for the same reason as already stated.

Therefore, as far as I knew, it was myself at fault, not the sqlite library itself.

I posted the various versions of things here because I was asked to, and it looks like it's directly lead to the answer as to why my SQL code is either working or not working depending on what I use to run it through.

In any case, can you confirm that this issue is the result of a bug in the sqlite library itself that I've ran into here, and it's NOT my SQL code that's the issue?

If I'm right in the above statement, then take all the time you need to figure it out, that's absolutely fine. In fact, I'd rather it takes more time if it means that the right solution to fix the issue can be implemented, so take all the time you need, not a problem.

While I don't know I'll get the time today, if there is a way to send you an sql file with the SQL I've posted so far along with some insert statements; I can write you an SQL file with all of that, that should reproduce the issue in whatever you what to test it in, if you want.

In any case, at least now I can move forward, and you're far better placed than I to figure out what exactly is going on here.

So while I did write an sql script and that worked on both sqlite version 3.45.3 (in DB Browser for SQLite) and 3.46.1 (SQLite shell (sqlite3),
the issue was that the sqlite library itself wasn't getting the query with a space in-between the SQL keywords.

So the query is working now when passed into the sqlite library itself.
So it wasn't any issue with the query itself or the data itself/any of the insert statements.

I would mark this as solved, but I don't know how to do that in this forum.

Thanks to all those that helped.

Thank you Rowan.

Sorry for the very late reply, as time hasn't been on my side lately as well as a number of other (unrelated) things.

I decided that the query I posted in this thread was simply too complicated and too difficult to properly understand, so I've adopted a different solution that works perfectly and gives me exactly what I want.
Also, trying to find any documentation on the "magnifier" was simply impossible, as I could not find anything on that no matter what search terms I used.

So basically, instead of the above query I've created a separate C function that gets each file record based on the pathid, where to get the paths it's just a simple SELECT * Paths query.

So, the flow basically goes like this:

* get a path record
* call my function to get all file records that have that same pathid
* get the next path record
* call my function to get all file records that have that same pathid
* so on, and so forth until there are no more path records to get

The bottom line is that there will be no need to post another thread about fixing the SQL query I posted above.
And just to be clear, there are no issues with any of my current SQL queries.

Although, and when I have a chance to make sure it is the sqlite library doing it rather than my code, there might be another issue that I might need to refer to the developers of sqlite regarding an issue where it seems the sqlite library is segfaulting where it should be returning an error, not segfaulting. But again, and when I get the time, I'll have to confirm that it's a problem in the sqlite library itself - it does seem it's the sqlite library, but I'll confirm that before I post a thread about it.

Yes, I think you maybe be right about that, my bad.

Although, since the sqlite3 shell is using the same version of sqlite as my library is, then it would likely give the same error as the library itself is returning to mine when I try and pass in that same SQL query.

So there's still some issue with the sqlite library itself by the looks of it, although I'll let Richard tell us what the story is when he gets a chance to have a look at it and investigate it.

I don't see why you need the CTE at all, it contributes nothing. So in essence you are asking

SELECT ... FROM Paths p LEFT JOIN Files f ON f.pathid=p.pathid;

For any "Path", this will produce ALL the matching "Files". If you want only 1 result row per "Path", maybe you should GROUP BY "Path"

I have no idea what you mean by "CTE" for starters, and not really sure what you mean by the rest of your post.

Can you give an actual example, as in the actual code that I can run to see if it gives me the result I need?

Again, I'm a visual learner, I need to be able to *see* it to understand it properly, otherwise it's not at all likely going to make much, if any sense, at all to me. Again, *not* trying to be rude, but I never got a choice in that, that was the cards I've been dealt in life. Nothing I can do about it.

see https://sqlite.org/lang_select.html 
see https://sqlite.org/lang_with.html

Look at the railroad syntax diagrams. Find the WITH and GROUP BY boxes.

add the following to your statement:

.. GROUP BY p.pathid;

Once you have that, you can start to think about which of the File rows you want and how to tell SQLite.

And here you are, asking for concrete examples, but absolutely refusing to give them yourself.

Please supply some sample insert statements and expected results.  Post #15 looks like a great solution.

Drop table if exists Paths;Drop Table if exists Files;
CREATE TABLE if not exists Paths (pathid INTEGER PRIMARY KEY NOT NULL, 
                            path      TEXT NOT NULL UNIQUE ON CONFLICT IGNORE, 
                            uid       INTEGER NOT NULL, 
                            atime     INTEGER, 
                            mtime     INTEGER, 
                            ctime     INTEGER);


CREATE TABLE if not exists Files (
                            fileid    INTEGER PRIMARY KEY NOT NULL,
                            pathid    INTEGER NOT NULL
                                 REFERENCES Paths(pathid) 
                                         ON DELETE CASCADE 
                                         ON UPDATE CASCADE,
                            fname     TEXT, 
                            inode     INTEGER NOT NULL, 
                            fsize     INTEGER NOT NULL, 
                            uid       INTEGER NOT NULL, 
                            atime     INTEGER, 
                            mtime     INTEGER, 
                            ctime     INTEGER, 
                            type      TEXT  
                               CONSTRAINT fi_fname_not_null NOT NULL 
                               CONSTRAINT fi_fname_not_empt CHECK (fname > ''));


INSERT INTO Paths values (1, 'path1',2,3,4,5);
INSERT INTO Files values (1, 1,'path1',2,2,2,2,2,2,'mytype');


WITH
    magnifier(idx) AS (
        VALUES (1),(2)
    )
SELECT
    COALESCE(f.fname, p.path) AS Path,
    COALESCE(f.inode, '') AS Inode,
	COALESCE(f.fsize, '') AS Size,
	COALESCE(f.uid, '') AS UID,
	COALESCE(f.atime, '') AS atime,
	COALESCE(f.mtime, '') AS mtime,
	COALESCE(f.ctime, '') AS ctime,
	COALESCE(f.type, '') AS Type
FROM Paths p
CROSS JOIN magnifier m
LEFT JOIN Files f
ON m.idx = 2 AND f.pathid = p.pathid
ORDER BY
    p.path,
    m.idx,
    f.fname,
    f.inode,
    f.fsize,
    f.uid,
    f.atime,
    f.mtime,
    f.ctime,
    f.type;

As I said above, the issue is now resolved.