[Libreoffice-bugs] [Bug 132385] New: EDITING Query: LibreOffice Base changes SQL query after editing query in "Edit in SQL View..."

[bugzilla-daemon at bugs.documentfoundation.org]

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

            Bug ID: 132385
           Summary: EDITING Query: LibreOffice Base changes SQL query
                    after editing query in "Edit in SQL View..."
           Product: LibreOffice
           Version: 6.3.5.2 release
          Hardware: x86-64 (AMD64)
                OS: Linux (All)
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Base
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: alphageek777 at yahoo.com

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

-- 
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/20200424/15b3b6df/attachment-0001.htm>