When using CAST to UNSIGNED as in "WHERE product_nr = CAST(20 AS UNSIGNED)" the syntax filter gives: Unknown data type: 'UNSIGNED' This is however part of the MySQL syntax definition. https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html
@Julien, any opinion on this ?
On pc Debian x86-64 with master sources updated today, here what I did: 1) create a table with: id : int (primary key) product_nr : int 2) add 2 lines in the table 3) Open Execute SQL Command dialog and type: select * from a.tdf155431 WHERE product_nr = CAST(20 AS UNSIGNED) => I can reproduce this BUT: if I click on "Run SQL command directly", I don't reproduce this.
I gave a try with https://gerrit.libreoffice.org/c/core/+/152128
No problem here to execute such a query in direct SQL. Isn't part of GUI. By the way: What do you want to get when setting UNSIGNED in a query? If the numeric value is unsigned it will return this value. If it is, for example, -20, it returns totally nonsense here: CAST(-20 AS UNSIGNED) gives 18446744073709600000 with JDBC connection. CAST(-20 AS UNSIGNED) gives 0 with direct connection. Isn't ABS(-20) what gives the expected value in a right way? And this will work in GUI.
Julien Nabet committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/408a51a964d06e86c12d3a938c1295c75eecea63 tdf#155431: SQL Editor does not recognize UNSIGNED It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Sorry, my patch is wrong.
Julien Nabet committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/007ef352c2e6a061c52a45a35d3930d318b8ca8b Revert "tdf#155431: SQL Editor does not recognize UNSIGNED" It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Lionel: when I remade the tests, I used HSQLDB and this database doesn't seem to accept this syntax. So I tested again with Mysql and with initial patch I had done or with this one: diff --git a/connectivity/source/parse/sqlbison.y b/connectivity/source/parse/sqlbison.y index 8263535dc82b..4ed7db24ac23 100644 --- a/connectivity/source/parse/sqlbison.y +++ b/connectivity/source/parse/sqlbison.y @@ -139,7 +139,7 @@ using namespace connectivity; %token <pParseNode> SQL_TOKEN_SCHEMA SQL_TOKEN_SELECT SQL_TOKEN_SET SQL_TOKEN_SIZE SQL_TOKEN_SMALLINT SQL_TOKEN_SOME SQL_TOKEN_SQLCODE SQL_TOKEN_SQLERROR SQL_TOKEN_SUM %token <pParseNode> SQL_TOKEN_TABLE SQL_TOKEN_TIME SQL_TOKEN_TIMESTAMP SQL_TOKEN_TIMEZONE_HOUR SQL_TOKEN_TIMEZONE_MINUTE SQL_TOKEN_TO SQL_TOKEN_TRAILING SQL_TOKEN_TRANSLATE SQL_TOKEN_TRIM SQL_TOKEN_TRUE SQL_TOKEN_UNION -%token <pParseNode> SQL_TOKEN_UNIQUE SQL_TOKEN_UNKNOWN SQL_TOKEN_UPDATE SQL_TOKEN_UPPER SQL_TOKEN_USAGE SQL_TOKEN_USER SQL_TOKEN_USING SQL_TOKEN_VALUES SQL_TOKEN_VIEW +%token <pParseNode> SQL_TOKEN_UNIQUE SQL_TOKEN_UNKNOWN SQL_TOKEN_UNSIGNED SQL_TOKEN_UPDATE SQL_TOKEN_UPPER SQL_TOKEN_USAGE SQL_TOKEN_USER SQL_TOKEN_USING SQL_TOKEN_VALUES SQL_TOKEN_VIEW %token <pParseNode> SQL_TOKEN_WHERE SQL_TOKEN_WITH SQL_TOKEN_WORK SQL_TOKEN_ZONE /* ODBC KEYWORDS */ @@ -2719,6 +2719,7 @@ cast_operand: cast_target: table_node | data_type + | SQL_TOKEN_UNSIGNED ; cast_spec: SQL_TOKEN_CAST '(' cast_operand SQL_TOKEN_AS cast_target ')' diff --git a/connectivity/source/parse/sqlflex.l b/connectivity/source/parse/sqlflex.l index 34a4067ea21c..cd395cbe26bb 100644 --- a/connectivity/source/parse/sqlflex.l +++ b/connectivity/source/parse/sqlflex.l @@ -394,6 +394,7 @@ UNBOUNDED {SQL_NEW_KEYWORD(SQL_TOKEN_UNBOUNDED); } UNION {SQL_NEW_KEYWORD(SQL_TOKEN_UNION); } UNIQUE {SQL_NEW_KEYWORD(SQL_TOKEN_UNIQUE); } UNKNOWN {SQL_NEW_KEYWORD(SQL_TOKEN_UNKNOWN); } +UNSIGNED {SQL_NEW_KEYWORD(SQL_TOKEN_UNSIGNED); } UPDATE {SQL_NEW_KEYWORD(SQL_TOKEN_UPDATE); } UPPER {SQL_NEW_KEYWORD(SQL_TOKEN_UPPER); } USAGE {SQL_NEW_KEYWORD(SQL_TOKEN_USAGE); } (notice SQL_TOKEN_UNSIGNED is now used in "cast_target" rule, I got the same result in output: 2,20, table "product_nr" contains 1,2 2,20 and its structure is: int id (primary key),int product_nr Then, thinking about tdf#118817 (WEEK, WEEKDAY, ... for EXTRACT), I tried with "Create Query in SQL View..." and here it doesn't work indeed if I don't add the token in "cast_target" rule. I'll submit a new patch then.
(In reply to Boudi from comment #0) > When using CAST to UNSIGNED as in "WHERE product_nr = CAST(20 AS UNSIGNED)" > the syntax filter gives: Unknown data type: 'UNSIGNED' > This is however part of the MySQL syntax definition. > https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html Actually, having just an isolated "UNSIGNED" there is not allowed by the syntax defined in the page you link to. It is not generally allowed anywhere a data type is expected, e.g. CREATE TEMPORARY TABLE a (a unsigned); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'unsigned)' at line 1 It is indeed accepted within a CAST, see https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast Now, I find that "UNSIGNED" business rather messy on a syntax level. The data type is called "INTEGER UNSIGNED"; one must do CREATE TEMPORARY TABLE d (a INTEGER UNSIGNED); the following fails with an error CREATE TEMPORARY TABLE d (a UNSIGNED INTEGER); but, but, but within a CAST, it must be "UNSIGNED INTEGER", and "INTEGER UNSIGNED" fails. <facepalm> As an aside (not relevant for this bug), I find the syntax is misleading, CAST(foo AS UNSIGNED INTEGER) actually casts foo to BIGINT UNSIGNED, not to INTEGER UNSIGNED. The general answer is that while this is syntax recognised by MySQL, it is not standard SQL syntax. It is technically possible to make our SQL parser some kind of hybrid that accepts some superset of various SQL dialects (SQL, MySQL, PostgreSQL, Oracle, Microsoft SQL, HSQLDB, Firebird, ...) so if someone implements it, sure, OK. One has to be aware, though, that this will not add support for unsigned types in UNO, IDL, SDBC, etc and will not by itself allow to use unsigned types at a semantic level within LibreOffice and/or the APIs in LibreOffice. I believe the MySQL/MariaDB SDBC (LibreOffice) driver deals with this by mapping unsigned types into a bigger signed type... which is probably not possible for BIGINT UNSIGNED :-| so that one is probably not supported insofar as any value exceeds the range of a BIGINT SIGNED.
(In reply to Julien Nabet from comment #8) > Lionel: when I remade the tests, I used HSQLDB and this database doesn't > seem to accept this syntax. Well, yeah, it is pretty much a MySQL-only thing.