Strange WARN message not related to my index.sql or other sql files.

[Joe1962]

On running sqlpage.bin it gives this 'WARN' message on the terminal. None of this is related to the index.sql or other sql files used in this instance. The site works as intended though:

WARN  sqlpage::webserver::database::sql] Failed to prepare "WITH nums (x) AS (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) SELECT a.x || ' times ' || b.x AS title, CASE a.x % 4 WHEN 0 THEN 'red' WHEN 1 THEN 'green' WHEN 3 THEN 'yellow' ELSE 'blue' END AS color, a.x || ' x ' || b.x || ' = ' || (a.x * b.x) AS description, 'This is basic math' AS footer, '?x=' || a.x AS link FROM nums AS a, nums AS b WHERE CAST(? AS CHAR) IS NULL OR b.x = CAST(CAST(? AS CHAR) AS DECIMAL) ORDER BY a.x, b.x": Failed to prepare SQL statement: 'WITH nums (x) AS (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) SELECT a.x || ' times ' || b.x AS title, CASE a.x % 4 WHEN 0 THEN 'red' WHEN 1 THEN 'green' WHEN 3 THEN 'yellow' ELSE 'blue' END AS color, a.x || ' x ' || b.x || ' = ' || (a.x * b.x) AS description, 'This is basic math' AS footer, '?x=' || a.x AS link FROM nums AS a, nums AS b WHERE CAST(? AS CHAR) IS NULL OR b.x = CAST(CAST(? AS CHAR) AS DECIMAL) ORDER BY a.x, b.x': error returned from database: 1054 (42S22): Unknown column 'b.x' in 'field list': 1054 (42S22): Unknown column 'b.x' in 'field list'

There appears to be a "somewhat" similar closed issue, but this had a very different message: #23

EDIT: sorry, forgot to add that this was detected on version 0.10.0 and just confirmed on 0.10.2.

[lovasoa]

Hello and thank you for the report! This looks like a problem with our default index.sql that displays the "it works" page. Which database server and version are you using?

[Joe1962]

Thanks for the quick reply! MariaDB 10.4.12 on CentOS-7.

[lovasoa]

The latest version of MariaDB works, but indeed, I get this message with old versions too. I'll see what I can do.

[lovasoa]

The problem can be reduced to the following sql statement:

WITH nums(x) AS (SELECT 1 UNION SELECT 2) SELECT a.x, b.x FROM nums a, nums b

It works in Microsoft SQL server, SQLite, Postgres, recent versions of MySQL and MariaDB, but fails in MariaDB 10.4.12.

I think I'll just remove this example from the "it works" default page.