Bug Hunting Session
Bug 118817 - FIREBIRD: EXTRACT Function - WEEK, WEEKDAY, YEARDAY AND MILLISECOND
Summary: FIREBIRD: EXTRACT Function - WEEK, WEEKDAY, YEARDAY AND MILLISECOND
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.0.1 rc
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2018-07-18 09:01 UTC by Vilos
Modified: 2019-07-11 13:41 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Querydesigner screen shot (88.08 KB, image/png)
2018-07-23 17:42 UTC, Drew Jensen
Details
Design View for WEEKDAY and YEARDAY error and LO Version (53.41 KB, image/png)
2018-07-23 18:20 UTC, Vilos
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Vilos 2018-07-18 09:01:00 UTC
WEEK, WEEKDAY and YEARDAY do not work when entered in the Query Design dialog.

Entering the following using the Query Design dialog:

EXTRACT( WEEK FROM "DateVisit" )

Returns the following error: Expression, field name 'EXTRACT( WEEK FROM "DateVisit" )' does not exist

However, the following statement works (for WEEK, WEEKDAY and YEARDAY) when entered in Tools -> SQL

SELECT
EXTRACT( WEEK FROM "DateVisit" ) "WeekNo"
FROM "iCare"
WHERE EXTRACT( WEEK FROM "DateVisit" ) = 1
Comment 1 Drew Jensen 2018-07-20 21:20:56 UTC
Confirmed in today's build of 6.1 RC and 6.2Alpha0

The extract function will work without SQL Direct enabled but is not recognized in the designer UI.

For example: 
1 Download this example file
https://nextcloud.documentfoundation.org/s/ePAmqMj3N2Ywms4

2 Create a new query with the SQL view and enter the following Select statement.
SELECT "Country",
       COALESCE("FirstName", ' ', "LastName") As "Name", 
       EXTRACT(YEAR FROM "OrderDate") As "Year", 
       COUNT(*) As "Count"
FROM "Employees" INNER JOIN "Orders" 
    ON ("Employees"."EmployeeID"="Orders"."EmployeeID") 
    GROUP BY "Country", "Name", "Year"

3 Run the query (zero records because one of the tables is empty)

4 switch views to the designer UI

5 Unable to run the query or even switch back to SQL view.
Comment 2 Julien Nabet 2018-07-23 16:06:35 UTC
Dup of tdf#104986?
Comment 3 Vilos 2018-07-23 16:58:58 UTC
Hi. It was reported as fixed in that build, but it isn't for fixed for me. I texted it again in RC2. Still not working. Interestingly, I can no longer create a FIREBIRD db in RC2.
Comment 4 Vilos 2018-07-23 17:00:11 UTC
Just to add, that you may be pointing out that I should've replied to that previous thread. Didn't find it - sorry1
Comment 5 Xisco Faulí 2018-07-23 17:27:33 UTC
(In reply to Vilos from comment #3)
> Hi. It was reported as fixed in that build, but it isn't for fixed for me. I
> texted it again in RC2. Still not working. Interestingly, I can no longer
> create a FIREBIRD db in RC2.

You need to enable the experimental features first. Tools - Options - Advanced - Enable experimental features..

I have set the bug's status to 'NEEDINFO'. Please change it back to
'UNCONFIRMED' if the bug is still present in the latest version.
Comment 6 Drew Jensen 2018-07-23 17:42:42 UTC
Created attachment 143720 [details]
Querydesigner screen shot

Simple case is working for me on Ubuntu 18.04 and build (QueryDesigner Screenshot)
Version: 6.1.1.0.0+
Build ID: 2466ea26c4bef1e002a24f6845084633e5a058c4
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-6-1, Time: 2018-07-21_22:43:36
Locale: en-US (en_US.UTF-8); Calc: group threaded

and 6.2 Master
Comment 7 Vilos 2018-07-23 18:20:32 UTC
Created attachment 143721 [details]
Design View for WEEKDAY and YEARDAY error and LO Version

Tools - Options - Advanced - Enable experimental features set. WEEK works. WEEKDAY and YEARDAY don't. See attached image. Just to re-iterate, the SQL equivalent statements work in Tools->SQL.
Comment 8 Drew Jensen 2018-07-23 19:04:13 UTC
Sorry, twice in two days I couldn't see the tree for the forest or some such.

MILLISECOND is also not recognized by the Base SQL parser.
Comment 9 Lionel Elie Mamane 2018-12-03 16:27:31 UTC
I think the EXTRACT function needs to be added to the SQL grammar of LibreOffice