<html>
<head>
<base href="https://bugs.documentfoundation.org/">
</head>
<body><table border="1" cellspacing="0" cellpadding="8">
<tr>
<th>Bug ID</th>
<td><a class="bz_bug_link
bz_status_UNCONFIRMED "
title="UNCONFIRMED - EDITING Query: LibreOffice Base changes SQL query after editing query in "Edit in SQL View...""
href="https://bugs.documentfoundation.org/show_bug.cgi?id=132385">132385</a>
</td>
</tr>
<tr>
<th>Summary</th>
<td>EDITING Query: LibreOffice Base changes SQL query after editing query in "Edit in SQL View..."
</td>
</tr>
<tr>
<th>Product</th>
<td>LibreOffice
</td>
</tr>
<tr>
<th>Version</th>
<td>6.3.5.2 release
</td>
</tr>
<tr>
<th>Hardware</th>
<td>x86-64 (AMD64)
</td>
</tr>
<tr>
<th>OS</th>
<td>Linux (All)
</td>
</tr>
<tr>
<th>Status</th>
<td>UNCONFIRMED
</td>
</tr>
<tr>
<th>Severity</th>
<td>normal
</td>
</tr>
<tr>
<th>Priority</th>
<td>medium
</td>
</tr>
<tr>
<th>Component</th>
<td>Base
</td>
</tr>
<tr>
<th>Assignee</th>
<td>libreoffice-bugs@lists.freedesktop.org
</td>
</tr>
<tr>
<th>Reporter</th>
<td>alphageek777@yahoo.com
</td>
</tr></table>
<p>
<div>
<pre>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</pre>
</div>
</p>
<hr>
<span>You are receiving this mail because:</span>
<ul>
<li>You are the assignee for the bug.</li>
</ul>
</body>
</html>