More than 14 columns results in severe error.

[virk]

Hi, it's me again :-)

I have created a table like below:

table ?s ?Name ?Geburtstag ?Alter ?Alter ?Alter ?Alter ?Alter ?Alter ?Alter ?Alter ?Alter ?Alter ?Alter ?s is a: person ?s name: ?Name ?s birthday: ?Geburtstag ?s age: ?Alter ?s age: ?Alter ?s age: ?Alter ?s age: ?Alter ?s age: ?Alter ?s age: ?Alter ?s age: ?Alter ?s age: ?Alter ?s age: ?Alter ?s age: ?Alter ?s age: ?Alter

If I now add one more "?Alter", so that I get 15 columns in the output table, the result is an error as follows:

Strata storage: Failed to prepare query 'SELECT DISTINCT v0, v6, v9, v12 FROM (SELECT DISTINCT lit1, lit2, lit5, v6, lit8, v9, COALESCE(r1.v0, r2.v0) AS v0, COALESCE(r1.lit11, r2.lit11) AS lit11, COALESCE(r1.v12, r2.v12) AS v12 FROM (SELECT DISTINCT lit1, lit2, lit5, v6, lit8, v9, COALESCE(r1.v0, r2.v0) AS v0, COALESCE(r1.lit11, r2.lit11) AS lit11, COALESCE(r1.v12, r2.v12) AS v12 FROM (SELECT DISTINCT lit1, lit2, lit5, v6, lit8, v9, COALESCE(r1.v0, r2.v0) AS v0, COALESCE(r1.lit11, r2.lit11) AS lit11, COALESCE(r1.v12, r2.v12) AS v12 FROM (SELECT DISTINCT lit1, lit2, lit5, v6, lit8, v9, COALESCE(r1.v0, r2.v0) AS v0, COALESCE(r1.lit11, r2.lit11) AS lit11, COALESCE(r1.v12, r2.v12) AS v12 FROM (SELECT DISTINCT lit1, lit2, lit5, v6, lit8, v9, COALESCE(r1.v0, r2.v0) AS v0, COALESCE(r1.lit11, r2.lit11) AS lit11, COALESCE(r1.v12, r2.v12) AS v12 FROM (SELECT DISTINCT lit1, lit2, lit5, v6, lit8, v9, COALESCE(r1.v0, r2.v0) AS v0, COALESCE(r1.lit11, r2.lit11) AS lit11, COALESCE(r1.v12, r2.v12) AS v12 FROM (SELECT DISTINCT lit1, lit2, lit5, v6, lit8, v9, COALESCE(r1.v0, r2.v0) AS v0, COALESCE(r1.lit11, r2.lit11) AS lit11, COALESCE(r1.v12, r2.v12) AS v and so on and so on……………………………………………………………………………………………………………

(Of course I came along this behaviour while using a table with 14 different columns :-) )

Is there a maximum numbers of columns which can be specified?

[bwanders]

Well... If you use the SQLite backend you are limited to the limits of SQLite, which happen to be on the smallish side. I can't really help the technical limitations on the backend.

But if you really need to have more columns, there's also a MySQL and a PostgreSQL backend implemented. Both of those have better support for big queries. For information to set that up, I refer to the Strata plugin page.

Hope that helps!

[virk]

But the similar table designed with the "structured data plugin" does not react with that mistake. So IMHO it is probably not related to SQLite directly.

[bwanders]

You are right, it is not related to SQLite directly. The error is actually from SQLite, but the reason it happens has to do with how Strata converts a query to SQL. It does so by producing a lot of nested queries, which eventually reaches the expression depth limit of SQLite...

For some cases, this can be solved by reworking the query generation code. Unfortunately, I do not have time to do so at the moment.

The structured data plugin is limited as well, but they can use joins, which puts them at a limit of 64 columns in a table.

(In the future, feel free to snip repetitive error messages, but please post the last part of the error message even if you snip the middle. The end usually contains relevant information, in this case it contained the very nice parser stack overflow notice ^_^)

[virk]

One further question related to this topic: I just want strata to present a table in which 15-20 variables are present. Just a table like: Present me all the invoices, with 1) customer, 2) project, 3) number, 4)price without VAT, 5) VAT, 6)price with VAT, etc. … … 15) payment date

The only query I want to have applied is that it is an "invoice". From my point of view something like:

<table ?customer ?project ?number ….> ?s is a: ?invoice

Is this possible somehow or is 14 really a limit?

[bwanders]

That is possible, and is called a 'resource query' or 'resource view' in the code. Unfortunately, at the moment this requires writing a custom view in PHP.

The other option would still be to switch to another database backend.

On 17-9-2015 15:51, Heiner Veelken wrote:

One further question related to this topic: I just want strata to present a table in which 15-20 variables are present. Just a table like: Present me all the invoices, with 1) customer, 2) project, 3) number, 4)price without VAT, 5) VAT, 6)price with VAT, etc. … … 15) payment date

The only query I want to have applied is that it is an "invoice". From my point of view something like:

<table ?customer ?project ?number ….> ?s is a: ?invoice

Is this possible somehow or is 14 really a limit?

---

Reply to this email directly or view it on GitHub: https://github.com/bwanders/dokuwiki-strata/issues/14#issuecomment-141093160

[virk]

Thank you Brend! If you ever come to Vaals, you're invited for a beer! Tot kijk!