[Libreoffice-bugs] [Bug 139600] Editing SQL correct query generate SQL error if inserting a parameter

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=139600

--- Comment #1 from Diego <diego.ercolani at gmail.com> ---
Created attachment 168872
  --> https://bugs.documentfoundation.org/attachment.cgi?id=168872&action=edit
"partial" test bench with HSQLDB embedded database

Here it is the document I promised... the problem is that Here I have another
kind of problem as Here, when I try to run the query obtain:

Accesso negato: GREATEST in statement [SELECT "MediaId", "VolumeName", "Slot",
"VolStatus", "Enabled", "VolBytes", "VolFiles", "VolRetention", "Recycle",
"MediaType", "VolType", "VolParts", "LastWritten", "ExpiresIn" FROM ( SELECT
"MediaId", CAST( "VolumeName" AS VARCHAR ( 20 ) ) AS "VolumeName", "VolStatus",
"Enabled", "VolByte", "VolFiles", "VolRetention", "Recycle", CONCAT( CASE WHEN
"Slot" < 13 THEN 'Left ' ELSE 'Right ' END, "Slot" ) AS "Slot", CAST(
"MediaType" AS VARCHAR ( 10 ) ) AS "MediaType", "VolType", "VolParts",
"LastWritten", GREATEST( 0, CAST( UNIX_TIMESTAMP( "LastWritten" ) AS INTEGER )
+ CAST( "VolRetention" AS INTEGER ) - CAST( UNIX_TIMESTAMP( NOW( ) ) AS INTEGER
) ) AS "ExpiresIn" FROM "Media2createdmanually" WHERE "InChanger" = 1 ORDER BY
"ExpiresIn" DESC LIMIT 6 ) AS "InsiemeNastri" ORDER BY "Slot"]

Other 2 things, here it is the table definition under MariaDB:
CREATE TABLE `Media` (
  `MediaId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `VolumeName` tinyblob NOT NULL,
  `Slot` int(11) DEFAULT 0,
  `PoolId` int(10) unsigned DEFAULT 0,
  `MediaType` tinyblob NOT NULL,
  `MediaTypeId` int(10) unsigned DEFAULT 0,
  `LabelType` tinyint(4) DEFAULT 0,
  `FirstWritten` datetime DEFAULT NULL,
  `LastWritten` datetime DEFAULT NULL,
  `LabelDate` datetime DEFAULT NULL,
  `VolJobs` int(10) unsigned DEFAULT 0,
  `VolFiles` int(10) unsigned DEFAULT 0,
  `VolBlocks` int(10) unsigned DEFAULT 0,
  `VolMounts` int(10) unsigned DEFAULT 0,
  `VolBytes` bigint(20) unsigned DEFAULT 0,
  `VolType` int(10) unsigned DEFAULT 0,
  `VolErrors` int(10) unsigned DEFAULT 0,
  `VolWrites` bigint(20) unsigned DEFAULT NULL,
  `VolCapacityBytes` bigint(20) unsigned DEFAULT 0,
  `VolStatus`
enum('Full','Archive','Append','Recycle','Purged','Read-Only','Disabled','Error','Busy','Used','Cleaning')
NOT NULL,
  `Enabled` tinyint(4) DEFAULT 1,
  `Recycle` tinyint(4) DEFAULT 0,
  `ActionOnPurge` tinyint(4) DEFAULT 0,
  `VolRetention` bigint(20) unsigned DEFAULT 0,
  `VolUseDuration` bigint(20) unsigned DEFAULT 0,
  `MaxVolJobs` int(10) unsigned DEFAULT 0,
  `MaxVolFiles` int(10) unsigned DEFAULT 0,
  `MaxVolBytes` bigint(20) unsigned DEFAULT 0,
  `InChanger` tinyint(4) DEFAULT 0,
  `StorageId` int(10) unsigned DEFAULT 0,
  `DeviceId` int(10) unsigned DEFAULT 0,
  `MediaAddressing` tinyint(4) DEFAULT 0,
  `VolReadTime` bigint(20) unsigned DEFAULT 0,
  `VolWriteTime` bigint(20) unsigned DEFAULT 0,
  `EndFile` int(10) unsigned DEFAULT 0,
  `EndBlock` int(10) unsigned DEFAULT 0,
  `LocationId` int(10) unsigned DEFAULT 0,
  `RecycleCount` int(10) unsigned DEFAULT 0,
  `InitialWrite` datetime DEFAULT NULL,
  `ScratchPoolId` int(10) unsigned DEFAULT 0,
  `RecyclePoolId` int(10) unsigned DEFAULT 0,
  `Comment` blob DEFAULT NULL,
  `VolABytes` bigint(20) unsigned DEFAULT 0,
  `VolAPadding` bigint(20) unsigned DEFAULT 0,
  `VolHoleBytes` bigint(20) unsigned DEFAULT 0,
  `VolHoles` int(10) unsigned DEFAULT 0,
  `VolParts` int(11) DEFAULT 0,
  `VolCloudParts` int(11) DEFAULT 0,
  `LastPartBytes` bigint(20) DEFAULT 0,
  `CacheRetention` bigint(20) DEFAULT 0,
  PRIMARY KEY (`MediaId`),
  UNIQUE KEY `inx8` (`VolumeName`(128)),
  KEY `PoolId` (`PoolId`)
) ENGINE=MyISAM AUTO_INCREMENT=121 DEFAULT CHARSET=latin1

And Here it is my translation:
CREATE TABLE "Media" (
  "MediaId" int  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  "VolumeName" varbinary NOT NULL,
  "Slot" int DEFAULT 0,
  "PoolId" int  DEFAULT 0,
  "MediaType" varbinary NOT NULL,
  "MediaTypeId" int  DEFAULT 0,
  "LabelType" int DEFAULT 0,
  "FirstWritten" datetime DEFAULT NULL,
  "LastWritten" datetime DEFAULT NULL,
  "LabelDate" datetime DEFAULT NULL,
  "VolJobs" int  DEFAULT 0,
  "VolFiles" int DEFAULT 0,
  "VolBlocks" int DEFAULT 0,
  "VolMounts" int DEFAULT 0,
  "VolBytes" bigint  DEFAULT 0,
  "VolType" int  DEFAULT 0,
  "VolErrors" int  DEFAULT 0,
  "VolWrites" bigint  DEFAULT NULL,
  "VolCapacityBytes" bigint  DEFAULT 0,
  "VolStatus" varchar(50)  NOT NULL,
  "Enabled" tinyint DEFAULT 1,
  "Recycle" tinyint DEFAULT 0,
  "ActionOnPurge" tinyint DEFAULT 0,
  "VolRetention" bigint  DEFAULT 0,
  "VolUseDuration" bigint  DEFAULT 0,
  "MaxVolJobs" int  DEFAULT 0,
  "MaxVolFiles" int  DEFAULT 0,
  "MaxVolBytes" bigint  DEFAULT 0,
  "InChanger" tinyint DEFAULT 0,
  "StorageId" int  DEFAULT 0,
  "DeviceId" int  DEFAULT 0,
  "MediaAddressing" tinyint DEFAULT 0,
  "VolReadTime" bigint DEFAULT 0,
  "VolWriteTime" bigint DEFAULT 0,
  "EndFile" int DEFAULT 0,
  "EndBlock" int DEFAULT 0,
  "LocationId" int DEFAULT 0,
  "RecycleCount" int DEFAULT 0,
  "InitialWrite" datetime DEFAULT NULL,
  "ScratchPoolId" int DEFAULT 0,
  "RecyclePoolId" int DEFAULT 0,
  "Comment" varbinary DEFAULT NULL,
  "VolABytes" bigint DEFAULT 0,
  "VolAPadding" bigint DEFAULT 0,
  "VolHoleBytes" bigint DEFAULT 0,
  "VolHoles" int DEFAULT 0,
  "VolParts" int DEFAULT 0,
  "VolCloudParts" int DEFAULT 0,
  "LastPartBytes" bigint DEFAULT 0,
  "CacheRetention" bigint DEFAULT 0,
   Constraint CK_VolStatus CHECK ( "VolStatus" in
('Full','Archive','Append','Recycle','Purged','Read-Only','Disabled','Error','Busy','Used','Cleaning'))
);

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20210114/ac089d96/attachment-0001.htm>