Eli Mesika has uploaded a new change for review.
Change subject: core: do not use distinct if sort expr have func
......................................................................
core: do not use distinct if sort expr have func
A former patch made by Omer F added distinct to the search in order to
prevent duplicates.
In the case the search is in the format :
SELECT DISTINCT * FROM T .... ORDER BY f({arguments})
There is a problem since the DISTINCT is performed first and then the
sorting, so f is unknown since it is not part of the result set.
This patch checks if the sort clause has a function call and removes the
DISTINCT if it finds one.
Also added a unit test for this case.
Change-Id: I7c036b2b9ee94266b6e3df54f2c50167e454ed6a
Bug-Url: https://bugzilla.redhat.com/show_bug.cgi?id=1198506
Signed-off-by: emesika <[email protected]>
---
M
backend/manager/modules/searchbackend/src/main/java/org/ovirt/engine/core/searchbackend/SyntaxChecker.java
M
backend/manager/modules/searchbackend/src/test/java/org/ovirt/engine/core/searchbackend/SyntaxCheckerTest.java
2 files changed, 16 insertions(+), 7 deletions(-)
git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/92/38492/1
diff --git
a/backend/manager/modules/searchbackend/src/main/java/org/ovirt/engine/core/searchbackend/SyntaxChecker.java
b/backend/manager/modules/searchbackend/src/main/java/org/ovirt/engine/core/searchbackend/SyntaxChecker.java
index 0984b78..b3b7325 100644
---
a/backend/manager/modules/searchbackend/src/main/java/org/ovirt/engine/core/searchbackend/SyntaxChecker.java
+++
b/backend/manager/modules/searchbackend/src/main/java/org/ovirt/engine/core/searchbackend/SyntaxChecker.java
@@ -920,11 +920,9 @@
"SELECT * FROM %1$s WHERE ( %2$s IN (%3$s)",
mSearchObjectAC.getRelatedTableName(searchObjStr, false),
primeryKey,
- getInnerQuery(tableName, primeryKey,
fromStatement,
- wherePhrase));
+ getInnerQuery(tableName, primeryKey,
fromStatement, wherePhrase, sortExpr));
} else {
- inQuery = "(" + getInnerQuery(tableName, "*", fromStatement,
- wherePhrase);
+ inQuery = "(" + getInnerQuery(tableName, "*", fromStatement,
wherePhrase, sortExpr);
}
if (syntax.getSearchFrom() > 0) {
inQuery = StringFormat.format("%1$s and %2$s > %3$s",
inQuery, primeryKey, syntax.getSearchFrom());
@@ -945,9 +943,16 @@
return retval;
}
- private String getInnerQuery(String tableName, String primeryKey, String
fromStatement, StringBuilder wherePhrase) {
- return StringFormat.format("SELECT distinct %1$s.%2$s FROM %3$s %4$s",
tableName, primeryKey, fromStatement,
- wherePhrase);
+ private String getInnerQuery(String tableName, String primeryKey, String
fromStatement, StringBuilder wherePhrase, StringBuilder sortExpr) {
+ // prevent using distinct when the sort expression has a function call
since when distinct is used it is performed first and sorting
+ // is done on the result, so all fields in the sort clause should
appear in the result set after distinct is applied
+
+ if (sortExpr.indexOf("(") > 0) {
+ return StringFormat.format("SELECT %1$s.%2$s FROM %3$s %4$s",
tableName, primeryKey, fromStatement, wherePhrase);
+ }
+ else {
+ return StringFormat.format("SELECT distinct %1$s.%2$s FROM %3$s
%4$s", tableName, primeryKey, fromStatement, wherePhrase);
+ }
}
protected String getPagePhrase(SyntaxContainer syntax, String pageNumber) {
diff --git
a/backend/manager/modules/searchbackend/src/test/java/org/ovirt/engine/core/searchbackend/SyntaxCheckerTest.java
b/backend/manager/modules/searchbackend/src/test/java/org/ovirt/engine/core/searchbackend/SyntaxCheckerTest.java
index e3f7af3..4dec348 100644
---
a/backend/manager/modules/searchbackend/src/test/java/org/ovirt/engine/core/searchbackend/SyntaxCheckerTest.java
+++
b/backend/manager/modules/searchbackend/src/test/java/org/ovirt/engine/core/searchbackend/SyntaxCheckerTest.java
@@ -185,6 +185,10 @@
// Used to validate that searching values not in fields search all
fields
testValidSql("Vm: mac=00:1a:4a:d4:53:94",
"SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT
distinct vms_with_tags.vm_guid FROM vms_with_tags WHERE (
vms_with_tags.vm_pool_name LIKE '%mac=00:1a:4a:d4:53:94%' OR
vms_with_tags.run_on_vds_name LIKE '%mac=00:1a:4a:d4:53:94%' OR
vms_with_tags.vm_fqdn LIKE '%mac=00:1a:4a:d4:53:94%' OR vms_with_tags.tag_name
LIKE '%mac=00:1a:4a:d4:53:94%' OR vms_with_tags.guest_cur_user_name LIKE
'%mac=00:1a:4a:d4:53:94%' OR vms_with_tags.vm_name LIKE
'%mac=00:1a:4a:d4:53:94%' OR vms_with_tags.vm_description LIKE
'%mac=00:1a:4a:d4:53:94%' OR vms_with_tags.quota_name LIKE
'%mac=00:1a:4a:d4:53:94%' OR vms_with_tags.vm_host LIKE
'%mac=00:1a:4a:d4:53:94%' OR vms_with_tags.vm_ip LIKE
'%mac=00:1a:4a:d4:53:94%' OR vms_with_tags.storage_pool_name LIKE
'%mac=00:1a:4a:d4:53:94%' OR vms_with_tags.vds_group_name LIKE
'%mac=00:1a:4a:d4:53:94%' OR vms_with_tags.vm_comment LIKE
'%mac=00:1a:4a:d4:53:94%' ) )) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1)
LIMIT 0"!
);
+ // Testing that in case that function is used in the ORDER BY clause
then no DISTINCT is generated
+ testValidSql("Vms: SORTBY IP DESC",
+ "SELECT * FROM ((SELECT vms.* FROM vms ) ORDER BY
fn_get_comparable_ip_list(vm_ip) DESC NULLS LAST,vm_name ASC ) as T1 OFFSET (1
-1) LIMIT 0");
+
}
@Test
--
To view, visit https://gerrit.ovirt.org/38492
To unsubscribe, visit https://gerrit.ovirt.org/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I7c036b2b9ee94266b6e3df54f2c50167e454ed6a
Gerrit-PatchSet: 1
Gerrit-Project: ovirt-engine
Gerrit-Branch: ovirt-engine-3.5
Gerrit-Owner: Eli Mesika <[email protected]>
_______________________________________________
Engine-patches mailing list
[email protected]
http://lists.ovirt.org/mailman/listinfo/engine-patches
