Issue with SQL's WITH clause

[Chrisy15]

Idk what I'm expecting to come of this but the terms are too ambiguous for the search bar to be useful so I might as well ask anyway.

I was trying to populate a table with binary numbers using SQL's recursion because I'm a showoff who wants to use this basic understanding of the mechanic at whatever opportunity.

CREATE TABLE IF NOT EXISTS DMS_BitsTable(num INTEGER UNIQUE DEFAULT 0);

WITH RECURSIVE C15_BinaryView(val) AS (SELECT 1 UNION ALL SELECT val + val FROM C15_BinaryView LIMIT 8)
INSERT OR REPLACE INTO DMS_BitsTable 
        (num) 
SELECT     val 
FROM C15_BinaryView ORDER BY val;

This should create a temporary table containing the 8-bit values that can then be used for concatenated dummy buildings later on. However, when the game tries to execute the code it throws an error:

[433286.062] near "WITH": syntax error

The error looks simple enough, but what doesn't make sense is that when the code (and the entire file's code at that) is run through SQLSpy it works perfectly fine.

Spoiler :

The same issue happens with much simpler WITH statements too, like

WITH tTest(col) AS (SELECT 1) INSERT INTO Buildings (Type) SELECT col FROM tTest;

They run perfectly fine in SQLSpy, but when executed through the game it doesn't recognise the WITH as valid.

A final odd twist to this issue is that the WITH clause works perfectly fine in VI. I wrote this simple enough piece for Pok the other day

CREATE TABLE IF NOT EXISTS PopulationReference     (     Size INT     );     WITH RECURSIVE t(val) AS (SELECT 1 UNION ALL SELECT val + 1 FROM t LIMIT 30) INSERT INTO PopulationReference (Size) SELECT val FROM t;

and it worked perfectly fine for him, while I got this upgrade-tree-navigating query working at some point even if I don't have the finalised version on this laptop

WITH RECURSIVE GetSamuraiUpgradePath(UnitType) AS (VALUES('UNIT_JAPANESE_SAMURAI') UNION ALL SELECT a.UpgradeUnit FROM UnitUpgrades a, GetSamuraiUpgradePath b WHERE a.Unit = b.UnitType) SELECT * FROM GetSamuraiUpgradePath;

Is it a known issue that V can't use the WITH clause? Is there a reason why this could be the case? Has anyone else ever actually tried to use this before or is it just me?

 

[Troller0001]

I did try to use a WITH-statement inside a Lua-file using DB.Query(...) and got the same error message (while it also ran perfectly fine in SQLiteSpy). I suspect that the same system runs behind the execution of that SQL.
Quite unfortunate, since WITH-statements can be very useful from time to time, but I think we're out of luck...

----

As to how I 'solved' the issue, I simply stored each WITH as a separate statement in a variable, and simply concatenated it with the main statement before passing it onto DB.Query.
For the example, this:

sQuery = [[WITH tTest(col) AS (SELECT 1)
INSERT INTO Buildings (Type)
SELECT col FROM tTest]]

for row in DB.Query(sQuery) do
--w/e
end

became

stTest = [[SELECT 1 AS col]]
sQuery = [[
INSERT INTO Buildings (Type)
SELECT col FROM (]]..stTest..[[)
]]

for row in DB.Query(sQuery) do
--w/e
end

This is unfortunately not really helpful for your purpose as you're trying to do this outside of the Lua environment.

 

[Chrisy15]

I guess this means that the game must run its own syntax checking on SQL statements before inserting them into the database, and it so happens that this checking doesn't support Common Table Expressions at least, because if the syntax checking was localised to the database itself we wouldn't have the ambiguity. I guess it does make sense that the program interacting with the database would have to handle the error checking, as the database itself is just a file and so presumably can't do things...

 

[Nutty]

The game doesn't do it's own syntax checking. If it did, we'd get nice error messages like those that come out of the XML parser.

I'm not sure what version of SQLite Civ5 uses, but WITH clause support wasn't added to SQLite until 3.8.3 in 2014, so it's not supported by Civ5, since even BNW was released prior to that.

 

[Chrisy15]

Well that does make more sense, yeah. I guess I was just hoping the page would give a date or version number when it was added if it was added after release.

 

[LeeS]

We have to bear in mind what an civ5 is in terms of software versionings. I've seen the occassional thread here on lua where someone is attempting a function inherent in lua from a version number much higher than 5.1, and being confused as to why it simply does not work.

Or being confused as to why civ6's table.count does not work when attempted in Civ5.