fix bug 994

[bacula/bacula] / bacula / src / cats / sql_cmds.c

diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c
index d45a500ee5564fbbeb1fdaf41fe7fbe0bb358ccf..e953c342718f54f11330c078b64e2de7f7c31158 100644 (file)
--- a/bacula/src/cats/sql_cmds.c
+++ b/bacula/src/cats/sql_cmds.c
@@ -7,8 +7,8 @@
    many others, a complete list can be found in the file AUTHORS.
    This program is Free Software; you can redistribute it and/or
    modify it under the terms of version two of the GNU General Public
-   License as published by the Free Software Foundation plus additions
-   that are listed in the file LICENSE.
+   License as published by the Free Software Foundation and included
+   in the file LICENSE.
 
    This program is distributed in the hope that it will be useful, but
    WITHOUT ANY WARRANTY; without even the implied warranty of
@@ -33,6 +33,13 @@
  *
  *   Version $Id$
  */
+/*
+ * Note, PostgreSQL imposes some constraints on using DISTINCT and GROUP BY
+ *  for example, the following is illegal in PostgreSQL:
+ *  SELECT DISTINCT JobId FROM temp ORDER BY StartTime ASC;
+ *  because all the ORDER BY expressions must appear in the SELECT list!
+ */
+
 
 #include "bacula.h"
 #include "cats.h"
@@ -86,7 +93,6 @@ const char *select_job =
 /* Delete temp tables and indexes  */
 const char *drop_deltabs[] = {
    "DROP TABLE DelCandidates",
-   "DROP INDEX DelInx1",
    NULL};
 
 
@@ -398,23 +404,14 @@ const char *uar_inc =
    "AND FileSet.FileSet='%s' "
    "%s";
 
-#ifdef HAVE_POSTGRESQL
-/* Note, the PostgreSQL will have a much uglier looking
- * list since it cannot do GROUP BY of different values.
- */
 const char *uar_list_temp =
-   "SELECT JobId,Level,JobFiles,JobBytes,StartTime,VolumeName,StartFile"
+   "SELECT DISTINCT JobId,Level,JobFiles,JobBytes,StartTime,VolumeName"
    " FROM temp"
-   " ORDER BY StartTime,StartFile ASC";
-#else
-const char *uar_list_temp =
-   "SELECT JobId,Level,JobFiles,JobBytes,StartTime,VolumeName,StartFile"
-   " FROM temp"
-   " GROUP BY JobId ORDER BY StartTime,StartFile ASC";
-#endif
+   " ORDER BY StartTime ASC";
 
 
-const char *uar_sel_jobid_temp = "SELECT JobId FROM temp ORDER BY StartTime ASC";
+const char *uar_sel_jobid_temp = 
+   "SELECT DISTINCT JobId,StartTime FROM temp ORDER BY StartTime ASC";
 
 const char *uar_sel_all_temp1 = "SELECT * FROM temp1";
 
@@ -495,4 +492,4 @@ const char *uar_jobid_fileindex_from_dir =
  
 /* Query to get list of files from table -- presuably built by an external program */
 const char *uar_jobid_fileindex_from_table = 
-   "SELECT JobId, FileIndex from %s";
+   "SELECT JobId,FileIndex from %s";