Bad SQL syntax in class.dcactionposts.php

[actatux]

The new dotclear version 2.6.1 does not work as expected with SQLite backend.

In files inc/admin/actions/class.dcaction{comments,posts}.php, the following SELECT statement is not compatible with SQLite:

sqlite> SELECT blog_id FROM dc_blog WHERE false;
Error: no such column: false

See  http://www.sqlite.org/syntaxdiagrams.html#select-core and  http://www.sqlite.org/syntaxdiagrams.html#expr.

Because of this syntax error, new post, posts and comments pages are broken in the admin panel.

The SQLite support is broken with dotclear 2.6.x.

[czk]

I confirm this. Just just today I have tried upgrading to 2.6.1 and I get the same error. SQLite support works fine for me on 2.5.3.

I chose Dotclear specifically for the reason it supported SQLite. What are there chances the SQLite support will be restored and not become broken in another release? Is there some pre-release testing procedure that should catch such bugs, or do you depend just on community feedback to decide if the software is release-ready, say after you have released a couple of RCs?

A full error message when trying to access the blog "Entries" link in the admin panel:

Fatal error: Uncaught exception 'Exception' with message 'no such column: false (1)' in /var/www/dotclear/inc/libs/clearbricks/dblayer/class.sqlite.php:123 Stack trace: #0 /var/www/dotclear/inc/libs/clearbricks/dblayer/class.sqlite.php(85): sqliteConnection->db_query(Object(PDO), 'SELECT blog_id ...') #1 /var/www/dotclear/inc/admin/actions/class.dcactionposts.php(94): sqliteConnection->select('SELECT blog_id ...') #2 /var/www/dotclear/inc/admin/actions/class.dcaction.php(311): dcPostsActionsPage->fetchEntries(Object(ArrayObject)) #3 /var/www/dotclear/admin/posts.php(116): dcActionsPage->process() #4 {main} thrown in /var/www/dotclear/inc/libs/clearbricks/dblayer/class.sqlite.php on line 123

[actatux]

Workaround patch (#1)

[actatux]

I just attached a patch with the workaround. HTH.

[czk]

Do I get it right that this patch works with SQLite, but will break Postgres/MySQL compatibility?

[actatux]

I cannot say. I just gave a look at SQL-92 and SQL-99 BNF Grammars and the "WHERE NULL" clause does not appear to be valid, nor the "WHERE FALSE" clause. (see  http://savage.net.au/SQL/sql-99.bnf.html#where%20clause)

However, by looking at the SQL-99 standard (SQL/Foundation ISO/IEC 9075-2:1999, section 4.6 Boolean types), NULL is the third boolean value for the state "UNKNOWN", and "every SQL <predicate>, <search condition>, and <boolean value expression> may be considered as an operator that returns a boolean result." (section 4.6.2.2).

From my understanding, the "WHERE NULL" clause seems to be valid in SQL-99.

PostgreSQL supports this third state :  http://www.postgresql.org/docs/9.3/static/datatype-boolean.html

MySQL does not have a boolean datatype.

I think this patch should work at least for PostrgeSQL. However, keep in mind this is a workaround for this SQLite issue, which is critical because it breaks the most important feature of a blog: publish articles.

[Dsls]

(In [9d1adf085d11]) Fixed "where false" sqlite incompatibility, fixes #1910