Download it now!
Bug 132385 - EDITING Query: SQL Parser drops partition, order by and frame clauses from window definition
Summary: EDITING Query: SQL Parser drops partition, order by and frame clauses from wi...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.3.5.2 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Julien Nabet
URL:
Whiteboard: target:7.0.0 target:6.4.4
Keywords:
Depends on:
Blocks:
 
Reported: 2020-04-24 20:46 UTC by MGChristensen
Modified: 2020-04-26 11:06 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
reproduction case (with Firebird embedded) (3.28 KB, application/vnd.oasis.opendocument.database)
2020-04-26 09:31 UTC, Lionel Elie Mamane
Details

Note You need to log in before you can comment on or make changes to this bug.
Description MGChristensen 2020-04-24 20:46:37 UTC
Description:
Given a table named "product" in a PostgreSQL database named "groceries":

At the console window (ssh into the database server) using psql with groceries as the current database:

groceries=# SELECT product_id, product, ROW_NUMBER () OVER ( ORDER BY product ) FROM product;

produces the correct result, which is 196 rows, sorted alphabetically on product, with the addition of a row_number column where each row is assigned the correct row number, 1 to 196.

Example output:
 product_id |                                   product                                    | row_number 
------------+------------------------------------------------------------------------------+------------
        173 | 21 Whole Grains and Seeds "Dave's Killer Bread"                              |          1
        192 | Abbott Vanilla                                            |          2
         33 | Albacore Solid White Tuna in Water                                           |          3
         30 | Albers Quick Grits                                                           |          4
        118 | All with Stainlifters                                                        |          5
         15 | All with stainlifters Free Clear                                             |          6
         77 | Aluminum Foil                                                                |          7
        176 | Angel Food Cake                                                              |          8
          1 | B-100 Complex                                                                |          9
        155 | Bacon, Uncured Center Cut Applewood Smoked                                   |         10
        174 | Bagels, Franz Everything Premium                                             |         11
.
.
.
         43 | Whole Peeled Tomatoes                                                        |        191
         79 | Whole Wheat Spagetti 100% (Simple Truth)                                     |        192
        136 | Windex Original Refill                                                       |        193
        177 | Yakitori Chicken With Japanese-Style Fried Rice (6 9-oz bags)                |        194
        125 | Ziploc Gallon Freezer Bags                                                   |        195
        124 | Ziploc Quart Freezer Bags                                                    |        196
(196 rows)

In Base, create a new query using "Create Query in SQL View..." and enter the exact same query language as above, but with quite different results.

Base mangles the SQL to:
SELECT "product_id", "product", ROW_NUMBER ( ) OVER ( ) FROM "product"
and of course the results are not properly sorted and row_number has duplicate entries.

Unfortunately, under Severity (below), there is no choice "Show-Stopper"

Note: Base is on my workstation, Postgresql and the back-end database run on a server running FreeBSD.

Steps to Reproduce:
1.Create new query in Base using "Create Query in SQL View..."
2.Enter legal and working query which includes the SQL function ROW_NUMBER()
3.Save query.

Actual Results:
Mangled SQL not what I entered.
Incorrect query results

Expected Results:
What should have happened: same query results in Base as produced from entering query using psql in console.


Reproducible: Always


User Profile Reset: Yes


OpenGL enabled: Yes

Additional Info:
Version: 6.3.5.2
Build ID: 6.3.5.2-5.fc31
CPU threads: 16; OS: Linux 5.5; UI render: default; VCL: kde5; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US
Calc: threaded

Server: FreeBSD 11.3, Dell R710
Workstation Fedora 31 KDE x86_64
Comment 1 Julien Nabet 2020-04-25 13:30:03 UTC
On pc Debian x86-64 with master sources updated today, I could reproduce this.
Comment 2 Julien Nabet 2020-04-25 13:37:45 UTC
I gave a try with https://gerrit.libreoffice.org/c/core/+/92894
Comment 3 Lionel Elie Mamane 2020-04-26 09:31:04 UTC
Created attachment 159951 [details]
reproduction case (with Firebird embedded)
Comment 4 Commit Notification 2020-04-26 10:56:49 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/016a58d2f586d05fa0b0df433a7695ab2952a37b

tdf#132385: sql parser: don't drop clauses from window specification

It will be available in 7.0.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.
Comment 5 Julien Nabet 2020-04-26 11:05:50 UTC
Thank you Lionel for your help on this one.
Comment 6 Commit Notification 2020-04-26 11:06:24 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-4":

https://git.libreoffice.org/core/commit/4310b8b67ed9d0d32d4fd468b370bd3cfed07975

tdf#132385: sql parser: don't drop clauses from window specification

It will be available in 6.4.4.

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.