Omer Frenkel has uploaded a new change for review. Change subject: core: add distinct keyword to search ......................................................................
core: add distinct keyword to search currently, the search can return duplicate results, for example when template has more than one disk, or disks are on different storage domains. this patch adds a 'distinct' to the sql the search mechanism creates, in order to remove any duplicates that return from the query. Bug-Url: https://bugzilla.redhat.com/show_bug.cgi?id=1130915 Change-Id: I070900dc9e1064c98f8b342f26dbc3c23ba8c4a4 Signed-off-by: Omer Frenkel <ofren...@redhat.com> --- 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, 67 insertions(+), 65 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/76/32676/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 753099c..5552f4b 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 @@ -946,7 +946,7 @@ } private String getInnerQuery(String tableName, String primeryKey, String fromStatement, StringBuilder wherePhrase) { - return StringFormat.format("SELECT %1$s.%2$s FROM %3$s %4$s", tableName, primeryKey, fromStatement, + return StringFormat.format("SELECT distinct %1$s.%2$s FROM %3$s %4$s", tableName, primeryKey, fromStatement, wherePhrase); } 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 de677a7..e3f7af3 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 @@ -115,51 +115,51 @@ @Test public void testHost() { testValidSql("Host: sortby cpu_usage desc", - "SELECT * FROM ((SELECT vds.* FROM vds ) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vds.* FROM vds ) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); // Before: 19ms // "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags WHERE vds_with_tags.vds_name LIKE 'test1' )) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0" // Current: 5ms testValidSql("Host: name =\"test1\" sortby cpu_usage desc", - "SELECT * FROM ((SELECT vds.* FROM vds WHERE vds.vds_name LIKE test1 ) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vds.* FROM vds WHERE vds.vds_name LIKE test1 ) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); // Before: 17ms // "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags WHERE vds_with_tags.usage_cpu_percent > 80 )) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0" // Current: 5ms testValidSql("Host: CPU_USAGE > 80 sortby cpu_usage desc", - "SELECT * FROM ((SELECT vds.* FROM vds WHERE vds.usage_cpu_percent > 80 ) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vds.* FROM vds WHERE vds.usage_cpu_percent > 80 ) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); // Before: 25ms // "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags LEFT OUTER JOIN vdc_users_with_tags ON vds_with_tags.vds_id=vdc_users_with_tags.vm_guid WHERE vdc_users_with_tags.name LIKE user1 )) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0" // Current: 10ms testValidSql("Host: user.name = \"user1\" sortby cpu_usage desc", - "SELECT * FROM ((SELECT vds.* FROM vds LEFT OUTER JOIN vdc_users_with_tags ON vds.vds_id=vdc_users_with_tags.vm_guid WHERE vdc_users_with_tags.name LIKE user1 ) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vds.* FROM vds LEFT OUTER JOIN vdc_users_with_tags ON vds.vds_id=vdc_users_with_tags.vm_guid WHERE vdc_users_with_tags.name LIKE user1 ) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); // Before: 63ms // "SELECT * FROM (SELECT * FROM vds WHERE ( storage_pool_id IN (SELECT storage_pool_id FROM storage_domains WHERE storage_domains.storage_name LIKE 'pool1')) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0" // Current: 68ms testValidSql("Host: STORAGE.name = \"sd1\" sortby cpu_usage desc", - "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags LEFT OUTER JOIN storage_domains_with_hosts_view ON vds_with_tags.storage_id=storage_domains_with_hosts_view.id WHERE storage_domains_with_hosts_view.storage_name LIKE sd1 )) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT distinct vds_with_tags.vds_id FROM vds_with_tags LEFT OUTER JOIN storage_domains_with_hosts_view ON vds_with_tags.storage_id=storage_domains_with_hosts_view.id WHERE storage_domains_with_hosts_view.storage_name LIKE sd1 )) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); // Before: 23ms // "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags LEFT OUTER JOIN audit_log ON vds_with_tags.vds_id=audit_log.vds_id WHERE ( audit_log.severity = '2' AND vds_with_tags.usage_cpu_percent > 80 ))) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0" // Current: 9ms testValidSql("Host: EVENT.severity=error and CPU_USAGE > 80 sortby cpu_usage desc", - "SELECT * FROM ((SELECT vds.* FROM vds LEFT OUTER JOIN audit_log ON vds.vds_id=audit_log.vds_id WHERE ( audit_log.severity = '2' AND vds.usage_cpu_percent > 80 )) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vds.* FROM vds LEFT OUTER JOIN audit_log ON vds.vds_id=audit_log.vds_id WHERE ( audit_log.severity = '2' AND vds.usage_cpu_percent > 80 )) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("Host: EVENT.severity=error and tag=tag1 sortby cpu_usage desc", - "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags LEFT OUTER JOIN audit_log ON vds_with_tags.vds_id=audit_log.vds_id WHERE ( audit_log.severity = '2' AND vds_with_tags.tag_name IN (tag1) ))) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT distinct vds_with_tags.vds_id FROM vds_with_tags LEFT OUTER JOIN audit_log ON vds_with_tags.vds_id=audit_log.vds_id WHERE ( audit_log.severity = '2' AND vds_with_tags.tag_name IN (tag1) ))) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("Host: tag=\"tag1\"", - "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags WHERE vds_with_tags.tag_name IN (tag1) )) ORDER BY vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT distinct vds_with_tags.vds_id FROM vds_with_tags WHERE vds_with_tags.tag_name IN (tag1) )) ORDER BY vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); // Before: 22ms // "SELECT * FROM (SELECT * FROM vds WHERE ( vds_id IN (SELECT vds_with_tags.vds_id FROM vds_with_tags LEFT OUTER JOIN vms_with_tags ON vds_with_tags.vds_id=vms_with_tags.run_on_vds WHERE vms_with_tags.vm_name LIKE 'vm1' )) ORDER BY vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0" // Current: 11ms testValidSql("Host: vm.name=\"vm1\"", - "SELECT * FROM ((SELECT vds.* FROM vds LEFT OUTER JOIN vms_with_tags ON vds.vds_id=vms_with_tags.run_on_vds WHERE vms_with_tags.vm_name LIKE vm1 ) ORDER BY vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vds.* FROM vds LEFT OUTER JOIN vms_with_tags ON vds.vds_id=vms_with_tags.run_on_vds WHERE vms_with_tags.vm_name LIKE vm1 ) ORDER BY vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("Vms: cluster = default and Templates.name = template_1 and Storage.name = storage_1", - "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT vms_with_tags.vm_guid FROM vms_with_tags LEFT OUTER JOIN vm_templates_storage_domain ON vms_with_tags.vmt_guid=vm_templates_storage_domain.vmt_guid LEFT OUTER JOIN storage_domains_with_hosts_view ON vms_with_tags.storage_id=storage_domains_with_hosts_view.id WHERE ( ( vms.vds_group_name LIKE default AND vm_templates_storage_domain.name LIKE template\\_1 ) AND storage_domains_with_hosts_view.storage_name LIKE storage\\_1 ))) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT distinct vms_with_tags.vm_guid FROM vms_with_tags LEFT OUTER JOIN vm_templates_storage_domain ON vms_with_tags.vmt_guid=vm_templates_storage_domain.vmt_guid LEFT OUTER JOIN storage_domains_with_hosts_view ON vms_with_tags.storage_id=storage_domains_with_hosts_view.id WHERE ( ( vms.vds_group_name LIKE default AND vm_templates_storage_domain.name LIKE template\\_1 ) AND storage_domains_with_hosts_view.storage_name LIKE storage\\_1 ))) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("Vms: cluster = default and Templates.name = template_1 and Storage.name = storage_1 and Vnic.network_name = vnic_1", - "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT vms_with_tags.vm_guid FROM vms_with_tags LEFT OUTER JOIN vm_templates_storage_domain ON vms_with_tags.vmt_guid=vm_templates_storage_domain.vmt_guid LEFT OUTER JOIN storage_domains_with_hosts_view ON vms_with_tags.storage_id=storage_domains_with_hosts_view.id LEFT OUTER JOIN vm_interface_view ON vms_with_tags.vm_guid=vm_interface_view.vm_guid WHERE ( ( ( vms.vds_group_name LIKE default AND vm_templates_storage_domain.name LIKE template\\_1 ) AND storage_domains_with_hosts_view.storage_name LIKE storage\\_1 ) AND vm_interface_view.network_name LIKE vnic\\_1 ))) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT distinct vms_with_tags.vm_guid FROM vms_with_tags LEFT OUTER JOIN vm_templates_storage_domain ON vms_with_tags.vmt_guid=vm_templates_storage_domain.vmt_guid LEFT OUTER JOIN storage_domains_with_hosts_view ON vms_with_tags.storage_id=storage_domains_with_hosts_view.id LEFT OUTER JOIN vm_interface_view ON vms_with_tags.vm_guid=vm_interface_view.vm_guid WHERE ( ( ( vms.vds_group_name LIKE default AND vm_templates_storage_domain.name LIKE template\\_1 ) AND storage_domains_with_hosts_view.storage_name LIKE storage\\_1 ) AND vm_interface_view.network_name LIKE vnic\\_1 ))) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testHosts() { testValidSql("Hosts: sortby cpu_usage desc", - "SELECT * FROM ((SELECT vds.* FROM vds ) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vds.* FROM vds ) ORDER BY usage_cpu_percent DESC NULLS LAST,vds_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test @@ -168,109 +168,111 @@ // "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT vms_with_tags.vm_guid FROM vms_with_tags WHERE ( ( ( ( ( ( ( vms_with_tags.status = '1' OR vms_with_tags.status = '2' ) OR vms_with_tags.status = '6' ) OR vms_with_tags.status = '9' ) OR vms_with_tags.status = '10' ) OR vms_with_tags.status = '16' ) OR vms_with_tags.status = '4' ) OR vms_with_tags.status = '7' ))) ORDER BY usage_cpu_percent DESC NULLS LAST,vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0" // Current - 15ms testValidSql("Vm: status=Up or status=PoweringUp or status=MigratingTo or status=WaitForLaunch or status=RebootInProgress or status=PoweringDown or status=Paused or status=Unknown sortby cpu_usage desc", - "SELECT * FROM ((SELECT vms.* FROM vms WHERE ( ( ( ( ( ( ( vms.status = '1' OR vms.status = '2' ) OR vms.status = '6' ) OR vms.status = '9' ) OR vms.status = '10' ) OR vms.status = '16' ) OR vms.status = '4' ) OR vms.status = '7' )) ORDER BY usage_cpu_percent DESC NULLS LAST,vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vms.* FROM vms WHERE ( ( ( ( ( ( ( vms.status = '1' OR vms.status = '2' ) OR vms.status = '6' ) OR vms.status = '9' ) OR vms.status = '10' ) OR vms.status = '16' ) OR vms.status = '4' ) OR vms.status = '7' )) ORDER BY usage_cpu_percent DESC NULLS LAST,vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); // Before - 20ms // "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT vms_with_tags.vm_guid FROM vms_with_tags )) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0" // Current - 16ms testValidSql("Vm:", - "SELECT * FROM ((SELECT vms.* FROM vms ) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vms.* FROM vms ) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); // Before - 203ms // "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT vms_with_tags.vm_guid FROM vms_with_tags LEFT OUTER JOIN vdc_users_with_tags ON vms_with_tags.vm_guid=vdc_users_with_tags.vm_guid WHERE vdc_users_with_tags.name LIKE user1 )) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0" // Current - 15ms testValidSql("Vm: user.name = user1", - "SELECT * FROM ((SELECT vms.* FROM vms LEFT OUTER JOIN vdc_users_with_tags ON vms.vm_guid=vdc_users_with_tags.vm_guid WHERE vdc_users_with_tags.name LIKE user1 ) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vms.* FROM vms LEFT OUTER JOIN vdc_users_with_tags ON vms.vm_guid=vdc_users_with_tags.vm_guid WHERE vdc_users_with_tags.name LIKE user1 ) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("Vm: user.name = \"user1\" and user.tag=\"tag1\"", - "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT vms_with_tags.vm_guid FROM vms_with_tags LEFT OUTER JOIN vdc_users_with_tags ON vms_with_tags.vm_guid=vdc_users_with_tags.vm_guid WHERE ( vdc_users_with_tags.name LIKE user1 AND vdc_users_with_tags.tag_name IN (tag1) ))) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT distinct vms_with_tags.vm_guid FROM vms_with_tags LEFT OUTER JOIN vdc_users_with_tags ON vms_with_tags.vm_guid=vdc_users_with_tags.vm_guid WHERE ( vdc_users_with_tags.name LIKE user1 AND vdc_users_with_tags.tag_name IN (tag1) ))) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); // 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 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"); + "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"! ); } @Test public void testVms() { testValidSql("Vms:", - "SELECT * FROM ((SELECT vms.* FROM vms ) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vms.* FROM vms ) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("Vms: storage.name = 111", - "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT vms_with_tags.vm_guid FROM vms_with_tags LEFT OUTER JOIN storage_domains_with_hosts_view ON vms_with_tags.storage_id=storage_domains_with_hosts_view.id WHERE storage_domains_with_hosts_view.storage_name LIKE 111 )) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM (SELECT * FROM vms WHERE ( vm_guid IN (SELECT distinct vms_with_tags.vm_guid FROM vms_with_tags LEFT OUTER JOIN storage_domains_with_hosts_view ON vms_with_tags.storage_id=storage_domains_with_hosts_view.id WHERE storage_domains_with_hosts_view.storage_name LIKE 111 )) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + testValidSql("Vm: template.name = temp1", + "SELECT * FROM ((SELECT distinct vms.* FROM vms LEFT OUTER JOIN vm_templates_storage_domain ON vms.vmt_guid=vm_templates_storage_domain.vmt_guid WHERE vm_templates_storage_domain.name LIKE temp1 ) ORDER BY vm_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testEvent() { testValidSql("Event: ", - "SELECT * FROM ((SELECT audit_log.* FROM audit_log WHERE not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct audit_log.* FROM audit_log WHERE not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("Event: severity=error ", - "SELECT * FROM ((SELECT audit_log.* FROM audit_log WHERE audit_log.severity = '2' AND not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct audit_log.* FROM audit_log WHERE audit_log.severity = '2' AND not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("Event: severity=alert ", - "SELECT * FROM ((SELECT audit_log.* FROM audit_log WHERE audit_log.severity = '10' AND not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct audit_log.* FROM audit_log WHERE audit_log.severity = '10' AND not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); // Before: 11ms // "SELECT * FROM (SELECT * FROM audit_log WHERE ( audit_log_id > 0 and audit_log_id IN (SELECT audit_log.audit_log_id FROM audit_log LEFT OUTER JOIN vds_with_tags ON audit_log.vds_id=vds_with_tags.vds_id WHERE vds_with_tags.vds_name LIKE host1 ) and not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0" // Current: 9ms testValidSql("Event: host.name = \"host1\" ", - "SELECT * FROM ((SELECT audit_log.* FROM audit_log LEFT OUTER JOIN vds_with_tags ON audit_log.vds_id=vds_with_tags.vds_id WHERE vds_with_tags.vds_name LIKE host1 AND not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct audit_log.* FROM audit_log LEFT OUTER JOIN vds_with_tags ON audit_log.vds_id=vds_with_tags.vds_id WHERE vds_with_tags.vds_name LIKE host1 AND not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testEvents() { testValidSql("Events: ", - "SELECT * FROM ((SELECT audit_log.* FROM audit_log WHERE not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct audit_log.* FROM audit_log WHERE not deleted) ORDER BY audit_log_id DESC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testTemplate() { testValidSql("Template: ", - "SELECT * FROM ((SELECT vm_templates_view.* FROM vm_templates_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vm_templates_view.* FROM vm_templates_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("Template: hosts.name = fake1", - "SELECT * FROM ((SELECT vm_templates_view.* FROM vm_templates_view LEFT OUTER JOIN vms_with_tags ON vm_templates_view.vmt_guid=vms_with_tags.vmt_guid LEFT OUTER JOIN vds_with_tags ON vms_with_tags.run_on_vds=vds_with_tags.vds_id WHERE vds_with_tags.vds_name LIKE fake1 ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vm_templates_view.* FROM vm_templates_view LEFT OUTER JOIN vms_with_tags ON vm_templates_view.vmt_guid=vms_with_tags.vmt_guid LEFT OUTER JOIN vds_with_tags ON vms_with_tags.run_on_vds=vds_with_tags.vds_id WHERE vds_with_tags.vds_name LIKE fake1 ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("Templates: storage.name = 111", - "SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT vm_templates_storage_domain.vmt_guid FROM vm_templates_storage_domain LEFT OUTER JOIN vms_with_tags ON vm_templates_storage_domain.vmt_guid=vms_with_tags.vmt_guid LEFT OUTER JOIN storage_domains_with_hosts_view ON vm_templates_storage_domain.storage_id=storage_domains_with_hosts_view.id WHERE storage_domains_with_hosts_view.storage_name LIKE 111 )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM (SELECT * FROM vm_templates_view WHERE ( vmt_guid IN (SELECT distinct vm_templates_storage_domain.vmt_guid FROM vm_templates_storage_domain LEFT OUTER JOIN vms_with_tags ON vm_templates_storage_domain.vmt_guid=vms_with_tags.vmt_guid LEFT OUTER JOIN storage_domains_with_hosts_view ON vm_templates_storage_domain.storage_id=storage_domains_with_hosts_view.id WHERE storage_domains_with_hosts_view.storage_name LIKE 111 )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testTemplates() { testValidSql("Templates: ", - "SELECT * FROM ((SELECT vm_templates_view.* FROM vm_templates_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vm_templates_view.* FROM vm_templates_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testUser() { testValidSql("User:", - "SELECT * FROM (SELECT * FROM vdc_users WHERE ( user_id IN (SELECT vdc_users_with_tags.user_id FROM vdc_users_with_tags )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM (SELECT * FROM vdc_users WHERE ( user_id IN (SELECT distinct vdc_users_with_tags.user_id FROM vdc_users_with_tags )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("User: host.name=\"host1\"", - "SELECT * FROM (SELECT * FROM vdc_users WHERE ( user_id IN (SELECT vdc_users_with_tags.user_id FROM vdc_users_with_tags LEFT OUTER JOIN vms_with_tags ON vdc_users_with_tags.vm_guid=vms_with_tags.vm_guid LEFT OUTER JOIN vds_with_tags ON vms_with_tags.run_on_vds=vds_with_tags.vds_id WHERE vds_with_tags.vds_name LIKE host1 )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM (SELECT * FROM vdc_users WHERE ( user_id IN (SELECT distinct vdc_users_with_tags.user_id FROM vdc_users_with_tags LEFT OUTER JOIN vms_with_tags ON vdc_users_with_tags.vm_guid=vms_with_tags.vm_guid LEFT OUTER JOIN vds_with_tags ON vms_with_tags.run_on_vds=vds_with_tags.vds_id WHERE vds_with_tags.vds_name LIKE host1 )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testUsers() { testValidSql("Users:", - "SELECT * FROM (SELECT * FROM vdc_users WHERE ( user_id IN (SELECT vdc_users_with_tags.user_id FROM vdc_users_with_tags )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM (SELECT * FROM vdc_users WHERE ( user_id IN (SELECT distinct vdc_users_with_tags.user_id FROM vdc_users_with_tags )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testGroup() { testValidSql("Group:", - "SELECT * FROM ((SELECT ad_groups.* FROM ad_groups ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct ad_groups.* FROM ad_groups ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("Group: name=group1", - "SELECT * FROM ((SELECT ad_groups.* FROM ad_groups WHERE ad_groups.name LIKE group1 ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct ad_groups.* FROM ad_groups WHERE ad_groups.name LIKE group1 ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testGroups() { testValidSql("Groups:", - "SELECT * FROM ((SELECT ad_groups.* FROM ad_groups ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct ad_groups.* FROM ad_groups ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testPool() { testValidSql("Pool: ", - "SELECT * FROM ((SELECT vm_pools_full_view.* FROM vm_pools_full_view ) ORDER BY vm_pool_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vm_pools_full_view.* FROM vm_pools_full_view ) ORDER BY vm_pool_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testPools() { testValidSql("Pools: ", - "SELECT * FROM ((SELECT vm_pools_full_view.* FROM vm_pools_full_view ) ORDER BY vm_pool_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vm_pools_full_view.* FROM vm_pools_full_view ) ORDER BY vm_pool_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test @@ -279,9 +281,9 @@ // "SELECT * FROM (SELECT * FROM vds_groups_view WHERE ( vds_group_id IN (SELECT vds_groups_storage_domain.vds_group_id FROM vds_groups_storage_domain )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0" // Current: 1ms testValidSql("Cluster: ", - "SELECT * FROM ((SELECT vds_groups_view.* FROM vds_groups_view ) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vds_groups_view.* FROM vds_groups_view ) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("Cluster: storage.name = 111", - "SELECT * FROM (SELECT * FROM vds_groups_view WHERE ( vds_group_id IN (SELECT vds_groups_storage_domain.vds_group_id FROM vds_groups_storage_domain LEFT OUTER JOIN storage_domains_with_hosts_view ON vds_groups_storage_domain.storage_id=storage_domains_with_hosts_view.id WHERE storage_domains_with_hosts_view.storage_name LIKE 111 )) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM (SELECT * FROM vds_groups_view WHERE ( vds_group_id IN (SELECT distinct vds_groups_storage_domain.vds_group_id FROM vds_groups_storage_domain LEFT OUTER JOIN storage_domains_with_hosts_view ON vds_groups_storage_domain.storage_id=storage_domains_with_hosts_view.id WHERE storage_domains_with_hosts_view.storage_name LIKE 111 )) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0"); } @Test @@ -290,7 +292,7 @@ // "SELECT * FROM (SELECT * FROM vds_groups_view WHERE ( vds_group_id IN (SELECT vds_groups_storage_domain.vds_group_id FROM vds_groups_storage_domain )) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0" // Current: 1ms testValidSql("Clusters: ", - "SELECT * FROM ((SELECT vds_groups_view.* FROM vds_groups_view ) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct vds_groups_view.* FROM vds_groups_view ) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0"); } @Test @@ -299,25 +301,25 @@ // "SELECT * FROM (SELECT * FROM storage_pool WHERE ( id IN (SELECT storage_pool_with_storage_domain.id FROM storage_pool_with_storage_domain )) ORDER BY name,name ASC ) as T1 OFFSET (1 -1) LIMIT 0" // Current: 1ms testValidSql("DataCenter: sortby name", - "SELECT * FROM ((SELECT storage_pool.* FROM storage_pool ) ORDER BY name,name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct storage_pool.* FROM storage_pool ) ORDER BY name,name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("DataCenter: Clusters.name =Default", - "SELECT * FROM ((SELECT storage_pool.* FROM storage_pool LEFT OUTER JOIN vds_groups_storage_domain ON storage_pool.id=vds_groups_storage_domain.storage_pool_id WHERE vds_groups_storage_domain.name LIKE Default ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct storage_pool.* FROM storage_pool LEFT OUTER JOIN vds_groups_storage_domain ON storage_pool.id=vds_groups_storage_domain.storage_pool_id WHERE vds_groups_storage_domain.name LIKE Default ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testStorage() { testValidSql("Storage: ", - "SELECT * FROM ((SELECT storage_domains_for_search.* FROM storage_domains_for_search ) ORDER BY storage_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct storage_domains_for_search.* FROM storage_domains_for_search ) ORDER BY storage_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("Storage: datacenter = Default", - "SELECT * FROM ((SELECT storage_domains_for_search.* FROM storage_domains_for_search WHERE storage_domains_for_search.storage_pool_name::text LIKE Default ) ORDER BY storage_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct storage_domains_for_search.* FROM storage_domains_for_search WHERE storage_domains_for_search.storage_pool_name::text LIKE Default ) ORDER BY storage_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("Storage: host.name = fake1", - "SELECT * FROM ((SELECT storage_domains_for_search.* FROM storage_domains_for_search LEFT OUTER JOIN vds_with_tags ON storage_domains_for_search.id=vds_with_tags.storage_id WHERE vds_with_tags.vds_name LIKE fake1 ) ORDER BY storage_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct storage_domains_for_search.* FROM storage_domains_for_search LEFT OUTER JOIN vds_with_tags ON storage_domains_for_search.id=vds_with_tags.storage_id WHERE vds_with_tags.vds_name LIKE fake1 ) ORDER BY storage_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testStorages() { testValidSql("Storages: ", - "SELECT * FROM ((SELECT storage_domains_for_search.* FROM storage_domains_for_search ) ORDER BY storage_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct storage_domains_for_search.* FROM storage_domains_for_search ) ORDER BY storage_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test @@ -326,9 +328,9 @@ // "SELECT * FROM (SELECT * FROM all_disks WHERE ( disk_id IN (SELECT all_disks.disk_id FROM all_disks )) ORDER BY disk_alias ASC, disk_id ASC ) as T1 OFFSET (1 -1) LIMIT 0" // Current: 12ms testValidSql("disk: ", - "SELECT * FROM ((SELECT all_disks.* FROM all_disks ) ORDER BY disk_alias ASC, disk_id ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct all_disks.* FROM all_disks ) ORDER BY disk_alias ASC, disk_id ASC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("disk: alias=vm1", - "SELECT * FROM ((SELECT all_disks.* FROM all_disks WHERE all_disks.disk_alias LIKE vm1 ) ORDER BY disk_alias ASC, disk_id ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct all_disks.* FROM all_disks WHERE all_disks.disk_alias LIKE vm1 ) ORDER BY disk_alias ASC, disk_id ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test @@ -337,35 +339,35 @@ // "SELECT * FROM (SELECT * FROM all_disks WHERE ( disk_id IN (SELECT all_disks.disk_id FROM all_disks )) ORDER BY disk_alias ASC, disk_id ASC ) as T1 OFFSET (1 -1) LIMIT 0" // Current: 12ms testValidSql("disks: ", - "SELECT * FROM ((SELECT all_disks.* FROM all_disks ) ORDER BY disk_alias ASC, disk_id ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct all_disks.* FROM all_disks ) ORDER BY disk_alias ASC, disk_id ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testVolume() { testValidSql("volume: ", - "SELECT * FROM ((SELECT gluster_volumes_view.* FROM gluster_volumes_view ) ORDER BY vol_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct gluster_volumes_view.* FROM gluster_volumes_view ) ORDER BY vol_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("volume: name=volume1", - "SELECT * FROM ((SELECT gluster_volumes_view.* FROM gluster_volumes_view WHERE gluster_volumes_view.vol_name LIKE volume1 ) ORDER BY vol_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct gluster_volumes_view.* FROM gluster_volumes_view WHERE gluster_volumes_view.vol_name LIKE volume1 ) ORDER BY vol_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testVolumes() { testValidSql("volumes: ", - "SELECT * FROM ((SELECT gluster_volumes_view.* FROM gluster_volumes_view ) ORDER BY vol_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct gluster_volumes_view.* FROM gluster_volumes_view ) ORDER BY vol_name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testQuota() { testValidSql("quota: ", - "SELECT * FROM ((SELECT quota_view.* FROM quota_view ) ORDER BY quota_name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct quota_view.* FROM quota_view ) ORDER BY quota_name ASC) as T1 OFFSET (1 -1) LIMIT 0"); testValidSql("quota: STORAGEPOOLNAME=pool", - "SELECT * FROM ((SELECT quota_view.* FROM quota_view WHERE quota_view.storage_pool_name LIKE pool ) ORDER BY quota_name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct quota_view.* FROM quota_view WHERE quota_view.storage_pool_name LIKE pool ) ORDER BY quota_name ASC) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testQuotas() { testValidSql("quota: ", - "SELECT * FROM ((SELECT quota_view.* FROM quota_view ) ORDER BY quota_name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct quota_view.* FROM quota_view ) ORDER BY quota_name ASC) as T1 OFFSET (1 -1) LIMIT 0"); } @Test @@ -374,12 +376,12 @@ // "SELECT * FROM (SELECT * FROM network_view WHERE ( id IN (SELECT network_view.id FROM network_view )) ORDER BY storage_pool_name ASC, name ASC) as T1 OFFSET (1 -1) LIMIT 0" // Current: 1.5ms testValidSql("network: ", - "SELECT * FROM ((SELECT network_view.* FROM network_view ) ORDER BY storage_pool_name ASC, name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct network_view.* FROM network_view ) ORDER BY storage_pool_name ASC, name ASC) as T1 OFFSET (1 -1) LIMIT 0"); // Before: 5ms // "SELECT * FROM (SELECT * FROM network_view WHERE ( id IN (SELECT network_view.id FROM network_view LEFT OUTER JOIN network_cluster_view ON network_view.id=network_cluster_view.network_id WHERE ( network_cluster_view.network_name LIKE 'cluster1' AND network_view.name LIKE 'network1' ))) ORDER BY storage_pool_name ASC, name ASC) as T1 OFFSET (1 -1) LIMIT 0" // Current: 4ms testValidSql("network: CLUSTER_NETWORK.network_name=cluster1 and name=network1", - "SELECT * FROM ((SELECT network_view.* FROM network_view LEFT OUTER JOIN network_cluster_view ON network_view.id=network_cluster_view.network_id WHERE ( network_cluster_view.network_name LIKE cluster1 AND network_view.name LIKE network1 )) ORDER BY storage_pool_name ASC, name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct network_view.* FROM network_view LEFT OUTER JOIN network_cluster_view ON network_view.id=network_cluster_view.network_id WHERE ( network_cluster_view.network_name LIKE cluster1 AND network_view.name LIKE network1 )) ORDER BY storage_pool_name ASC, name ASC) as T1 OFFSET (1 -1) LIMIT 0"); } @Test @@ -388,7 +390,7 @@ // "SELECT * FROM (SELECT * FROM network_view WHERE ( id IN (SELECT network_view.id FROM network_view )) ORDER BY storage_pool_name ASC, name ASC) as T1 OFFSET (1 -1) LIMIT 0" // Current: 1.5ms testValidSql("networks: ", - "SELECT * FROM ((SELECT network_view.* FROM network_view ) ORDER BY storage_pool_name ASC, name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct network_view.* FROM network_view ) ORDER BY storage_pool_name ASC, name ASC) as T1 OFFSET (1 -1) LIMIT 0"); } @Test @@ -397,12 +399,12 @@ // "SELECT * FROM (SELECT * FROM providers WHERE ( id IN (SELECT providers.id FROM providers )) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0" // Current: 1ms testValidSql("provider: ", - "SELECT * FROM ((SELECT providers.* FROM providers ) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct providers.* FROM providers ) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0"); // Before: 1.2ms // "SELECT * FROM (SELECT * FROM providers WHERE ( id IN (SELECT providers.id FROM providers WHERE providers.name LIKE 'prov1' )) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0" // Current: 0.7ms testValidSql("provider: name=\"prov1\"", - "SELECT * FROM ((SELECT providers.* FROM providers WHERE providers.name LIKE prov1 ) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct providers.* FROM providers WHERE providers.name LIKE prov1 ) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0"); } @Test @@ -411,32 +413,32 @@ // "SELECT * FROM (SELECT * FROM providers WHERE ( id IN (SELECT providers.id FROM providers )) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0" // Current: 1ms testValidSql("providers: ", - "SELECT * FROM ((SELECT providers.* FROM providers ) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct providers.* FROM providers ) ORDER BY name ASC) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testInstanceType() { testValidSql("instancetype: ", - "SELECT * FROM ((SELECT instance_types_view.* FROM instance_types_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct instance_types_view.* FROM instance_types_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testInstanceTypes() { testValidSql("instancetype: ", - "SELECT * FROM ((SELECT instance_types_view.* FROM instance_types_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct instance_types_view.* FROM instance_types_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testImageType() { testValidSql("imagetype: ", - "SELECT * FROM ((SELECT image_types_view.* FROM image_types_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct image_types_view.* FROM image_types_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } @Test public void testImageTypes() { testValidSql("imagetypes: ", - "SELECT * FROM ((SELECT image_types_view.* FROM image_types_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); + "SELECT * FROM ((SELECT distinct image_types_view.* FROM image_types_view ) ORDER BY name ASC ) as T1 OFFSET (1 -1) LIMIT 0"); } private void testValidSql(String dynamicQuery, String exepctedSQLResult) { -- To view, visit http://gerrit.ovirt.org/32676 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: I070900dc9e1064c98f8b342f26dbc3c23ba8c4a4 Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: ovirt-engine-3.5 Gerrit-Owner: Omer Frenkel <ofren...@redhat.com> _______________________________________________ Engine-patches mailing list Engine-patches@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-patches
