| Summary: | EDITING Query: SQL Parser drops partition, order by and frame clauses from window definition | ||
|---|---|---|---|
| Product: | LibreOffice | Reporter: | MGChristensen <alphageek777> |
| Component: | Base | Assignee: | Julien Nabet <serval2412> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | lionel, serval2412 |
| Priority: | medium | ||
| Version: | 6.3.5.2 release | ||
| Hardware: | x86-64 (AMD64) | ||
| OS: | Linux (All) | ||
| Whiteboard: | target:7.0.0 target:6.4.4 | ||
| Crash report or crash signature: | Regression By: | ||
| Attachments: | reproduction case (with Firebird embedded) | ||
On pc Debian x86-64 with master sources updated today, I could reproduce this. I gave a try with https://gerrit.libreoffice.org/c/core/+/92894 Created attachment 159951 [details]
reproduction case (with Firebird embedded)
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. Thank you Lionel for your help on this one. 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. |
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