Changeset 344

Timestamp:

06/11/12 18:05:15 (12 years ago)

Author:

Kris Deugau

Message:

/trunk

Work around bizarre SQL un-bug where:

SELECT <stuff> UNION (SELECT <stuff> ORDER BY ...)

not only didn't essentially glue the results of the two SELECTs together
serially (mildly confusing but a nonissue), but the ORDER BY was not obeyed
properly and resulted in records returned in the wrong relative order
overall (a problem when further processing required that relative ordering
of the records from the second SELECT).

This caused PTR template entries in reverse zones to not stack/overlay/cascade
properly on export, because larger netblocks returned first (against the ORDER
BY) prevented the smaller, more specific blocks from being expanded.

See #26

File:

• trunk/DNSDB.pm (modified) (1 diff)

trunk/DNSDB.pm

@@ -4193 +4193 @@
   my $revsth = $dbh->prepare("SELECT rdns_id,revnet,status FROM revzones WHERE status=1 ".
         "ORDER BY masklen(revnet) DESC");
+# For reasons unknown, we can't sanely UNION these statements.  Feh.
+# Supposedly it should work though (note last 3 lines):
+## PG manual
+#UNION Clause
+#
+#The UNION clause has this general form:
+#
+#    select_statement UNION [ ALL ] select_statement
+#
+#select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR UPDATE, or FOR SHARE clause. (ORDER BY
+#and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these
+#clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)
+
+  my $soasth = $dbh->prepare("SELECT host,type,val,distance,weight,port,ttl,record_id ".
+        "FROM records WHERE rdns_id=? AND type=6");
   $recsth = $dbh->prepare("SELECT host,type,val,distance,weight,port,ttl,record_id ".
-        "FROM records WHERE rdns_id=? AND type=6 UNION ".
-        "(SELECT host,type,val,distance,weight,port,ttl,record_id ".
         "FROM records WHERE rdns_id=? AND not type=6 ".
-        "ORDER BY masklen(CAST(val AS inet)) DESC, CAST(val AS inet))");
+        "ORDER BY masklen(CAST(val AS inet)) DESC, CAST(val AS inet)");
   $revsth->execute();
   while (my ($revid,$revzone,$revstat) = $revsth->fetchrow_array) {
-    $recsth->execute($revid, $revid);
+    $soasth->execute($revid);
+    my (@zsoa) = $soasth->fetchrow_array();
+    _printrec_tiny($datafile,'y',\%recflags,$revzone,
+        $zsoa[0],$zsoa[1],$zsoa[2],$zsoa[3],$zsoa[4],$zsoa[5],$zsoa[6],'','');
+
+    $recsth->execute($revid);
     while (my ($host,$type,$val,$dist,$weight,$port,$ttl,$recid) = $recsth->fetchrow_array) {
       next if $recflags{$recid};