#!/usr/bin/env perl
#
# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements.  See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership.  The ASF licenses this file
# to you under the Apache License, Version 2.0 (the
# "License"); you may not use this file except in compliance
# with the License.  You may obtain a copy of the License at
#
#   http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.
#
#
# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements.  See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership.  The ASF licenses this file
# to you under the Apache License, Version 2.0 (the
# "License"); you may not use this file except in compliance
# with the License.  You may obtain a copy of the License at
#
#   http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied.  See the License for the
# specific language governing permissions and limitations
# under the License.
#
use Pod::Usage;
use Getopt::Long;
use Data::Dumper;
use strict;
use warnings;

=head1 NAME

B<atmsort.pl> - [A] [T]est [M]echanism Sort: sort the contents of SQL log files to aid diff comparison

=head1 SYNOPSIS

B<atmsort.pl> [options] logfile [logfile...]

Options:

    -help            brief help message
    -man             full documentation
    -ignore_headers  ignore header lines in query output
    -ignore_plans    ignore explain plan content in query output
    -init <file>     load initialization file
    -do_equiv        construct or compare equivalent query regions

=head1 OPTIONS

=over 8

=item B<-help>

    Print a brief help message and exits.

=item B<-man>
    
    Prints the manual page and exits.

=item B<-ignore_headers> 

gpdiff/atmsort expect Postgresql "psql-style" output for SELECT
statements, with a two line header composed of the column names,
separated by vertical bars (|), and a "separator" line of dashes and
pluses beneath, followed by the row output.  The psql utility performs
some formatting to adjust the column widths to match the size of the
row output.  Setting this parameter causes gpdiff to ignore any
differences in the column naming and format widths globally.

=item B<-ignore_plans> 

Specify this option to ignore any explain plan diffs between the
input files. This will completely ignore any plan content in 
the input files thus masking differences in plans between the input files.

For example, for the following plan: 
explain select i from foo where i > 10;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..2.72 rows=45 width=4)
   ->  Table Scan on foo  (cost=0.00..1.55 rows=45 width=4)
         Filter: i > 10
 Settings:  optimizer=on
(4 rows)

atmsort.pl -ignore_plans will reduce this to: 

explain select i from foo where i > 10;
QUERY PLAN
___________
GP_IGNORE:{
GP_IGNORE:  'child' => [
GP_IGNORE:    {
GP_IGNORE:      'id' => 2,
GP_IGNORE:      'parent' => 1,
GP_IGNORE:      'short' => 'Table Scan on foo'
GP_IGNORE:    }
GP_IGNORE:  ],
GP_IGNORE:  'id' => 1,
GP_IGNORE:  'short' => 'Gather Motion'
GP_IGNORE:}
GP_IGNORE:(4 rows)


=item B<-init> <file>

Specify an initialization file containing a series of directives
(mainly for match_subs) that get applied to the input files.  To
specify multiple initialization files, use multiple init arguments,
eg:

  -init file1 -init file2 


=item B<-do_equiv>
    
    Choose one of the following options
    
=over 12

=item ignore_all:  

(default) ignore all content in a start_equiv/end_equiv block.

=item make:  

replace the query output of all queries in a start_equiv/end_equiv
block with the output of the first query, processed according to any
formatting directives for each query.


=item compare:  

process the query output of all queries in a start_equiv/end_equiv
block (versus prefixing the entire block with GP_IGNORE).

=back

=back

=head1 DESCRIPTION

atmsort reads sql log files from STDIN and sorts the query output for
all SELECT statements that do *not* have an ORDER BY, writing the
result to STDOUT.  This change to the log facilitates diff comparison,
since unORDERed query output does not have a guaranteed order.  Note
that for diff to work correctly, statements that do use ORDER BY must
have a fully-specified order.  The utility gpdiff.pl invokes atmsort
in order to compare the Greenplum test results against standard
Postgresql.

The log content must look something like:

 SELECT a, b, c, d 
   from foo 
   ORDER BY 1,2,3,4;
      a      |        b        |     c     |       d       
 ------------+-----------------+-----------+---------------
  1          | 1               | 1         | 1
  1          | 1               | 1         | 2
  3          | 2               | 2         | 5
 (3 rows)

The log file must contain SELECT statements, followed by the query
output in the standard Postgresql format, ie a set of named columns, a
separator line constructed of dashes and plus signs, and the rows,
followed by an "(N rows)" row count.  The SELECT statement must be
unambiguous, eg no embedded SQL keywords like INSERT, UPDATE, or
DELETE, and it must be terminated with a semicolon.  Normally, the
query output is sorted, but if the statement contains an ORDER BY
clause the query output for that query is not sorted.

=head2 EXPLAIN PLAN

atmsort can also use explain.pl to process EXPLAIN and EXPLAIN ANALYZE
output in a configuration-independent way.  It strips out all timing,
segment, and slice information, reducing the plan to a simple nested
perl structure.  For example, for the following plan:

explain analyze select * from customer;

                                     QUERY PLAN                              
------------------------------------------------------------------------
 Gather Motion 2:1  (slice1)  (cost=0.00..698.88 rows=25088 width=550)
   Rows out:  150000 rows at destination with 0.230 ms to first row, 
   386 ms to end, start offset by 8.254 ms.
   ->  Seq Scan on customer  (cost=0.00..698.88 rows=25088 width=550)
         Rows out:  Avg 75000.0 rows x 2 workers.  Max 75001 rows (seg0) 
         with 0.056 ms to first row, 26 ms to end, start offset by 7.332 ms.
 Slice statistics:
   (slice0)    Executor memory: 186K bytes.
   (slice1)    Executor memory: 130K bytes avg x 2 workers, 
               130K bytes max (seg0).
 Total runtime: 413.401 ms
(8 rows)

atmsort reduces the plan to:

                                     QUERY PLAN                              
------------------------------------------------------------------------
{
  'child' => [
              {
      'id' => 2,
      'parent' => 1,
      'short' => 'Seq Scan on customer'
      }
  ],
  'id' => 1,
  'short' => 'Gather Motion'
  }
(8 rows)


=head2 Advanced Usage 

atmsort supports several "commands" that allow finer-grained control
over the comparison process for SELECT queries.  These commands are
specified in comments in the following form:

 --
 -- order 1
 --
 SELECT a, b, c, d 
   from foo 
   ORDER BY 1;

or

 SELECT a, b, c, d 
   from foo 
   ORDER BY 1; -- order 1

The supported commands are:

=over 12

=item -- order column number[, column number...]

  The order directive is used to compare 
  "partially-ordered" query
  output.  The specified columns are assumed 
  to be ordered, and the  remaining columns are 
  sorted to allow for deterministic comparison.

=item -- ignore

The ignore directive prefixes the SELECT output with GP_IGNORE.  The
diff command can use the -I flag to ignore lines with this prefix.

=item -- mvd colnum[, colnum...] -> colnum[, colnum...] [; <additional specs>]

mvd is designed to support Multi-Value Dependencies for OLAP queries.
The syntax "col1,col2->col3,col4" indicates that the col1 and col2
values determine the col3, col4 result order.

=item -- start_ignore

Ignore all results until the next "end_ignore" directive.  The
start_ignore directive prefixes all subsequent output with GP_IGNORE,
and all other formatting directives are ignored as well.  The diff
command can use the -I flag to ignore lines with this prefix.

=item -- end_ignore

  Ends the ignored region that started with "start_ignore"

=item -- start_headers_ignore

Similar to the command-line "ignore_headers", ignore differences in
column naming and format widths.

=item -- end_headers_ignore

  Ends the "headers ignored" region that started with "start_headers_ignore"

=item -- start_equiv

Begin an "equivalent" region, and treat contents according to the
specified --do_equiv option.  Normally, the results are ignored.  The
"--do_equiv=make" option replaces the contents of all queries in the
equivalent region with the results of the first query.  If
"--do_equiv=compare" option is specified, the region is processed
according to the standard query formatting rules.

=item -- end_equiv

  Ends the equivalent region that started with "start_equiv"

=item -- copy_stdout

Marks the start of a "copy" command that writes to stdout.  In this
case, the directive must be on the same line as the command, e.g:

  copy mystuff to stdout ; -- copy_stdout

=item -- start_matchsubs

Starts a list of match/substitution expressions, where the match and
substitution are specified as perl "m" and "s" operators for a single
line of input.  atmsort will compile the expressions and use them to
process the current input file.  The format is:

    -- start_matchsubs
    --
    -- # first, a match expression
    -- m/match this/
    -- # next, a substitute expression
    -- s/match this/substitute this/
    --
    -- # and can have more matchsubs after this...
    --
    -- end_matchsubs

  Blank lines are ignored, and comments may be used if they are
  prefixed with "#", the perl comment character, eg:

    -- # this is a comment

  Multiple match and substitute pairs may be specified.  See "man
  perlre" for more information on perl regular expressions.

=item -- end_matchsubs
  
  Ends the match/substitution region that started with "start_matchsubs"

=item -- start_matchignore

Similar to matchsubs, starts a list of match/ignore expressions as a
set of perl match operators.  Each line that matches one of the
specified expressions is elided from the atmsort output.  Note that
there isn't an "ignore" expression -- just a list of individual match
operators.

=item -- end_matchignore

  Ends the match/ignore region that started with "start_matchignore"

=item -- force_explain

Normally, atmsort can detect that a SQL query is being EXPLAINed, and
the expain processing will happen automatically.  However, if the
query is complex, you may need to tag it with a comment to force the
explain.  Using this command for non-EXPLAIN statements is
inadvisable.

=back

Note that you can combine the directives for a single query, but each
directive must be on a separate line.  Multiple mvd specifications
must be on a single mvd line, separated by semicolons.  Note that 
start_ignore overrides all directives until the next end_ignore.

=head1 CAVEATS/LIMITATIONS

atmsort cannot handle "unsorted" SELECT queries where the output has
strings with embedded newlines or pipe ("|") characters due to
limitations with the parser in the "tablelizer" function.  Queries
with these characteristics must have an ORDER BY clause to avoid
potential erroneous comparison.

=head1 AUTHORS

Apache HAWQ

Address bug reports and comments to: dev@hawq.apache.org

=cut

my $glob_id = "";

# optional set of prefixes to identify sql statements, query output,
# and sorted lines (for testing purposes)
#my $apref = 'a: ';
#my $bpref = 'b: ';
#my $cpref = 'c: ';
#my $dpref = 'S: ';
my $apref = '';
my $bpref = '';
my $cpref = '';
my $dpref = '';

my $glob_compare_equiv;
my $glob_make_equiv_expected;
my $glob_ignore_headers;
my $glob_ignore_plans;
my $glob_ignore_whitespace;
my $glob_init;

my $glob_orderwarn;
my $glob_verbose;
my $glob_fqo;

# array of "expected" rows from first query of equiv region
my $equiv_expected_rows;

BEGIN
{ 
    $glob_compare_equiv       = 0;
    $glob_make_equiv_expected = 0;
    $glob_ignore_headers      = 0;
    $glob_ignore_plans        = 0;
    $glob_ignore_whitespace   = 0;
    $glob_init                = [];

	$glob_orderwarn           = 0;
	$glob_verbose             = 0;
	$glob_fqo                 = {count => 0};
}

BEGIN {
    my $man  = 0;
    my $help = 0;
    my $compare_equiv = 0;
    my $make_equiv_expected = 0;
    my $do_equiv;
    my $ignore_headers;
    my $ignore_plans;
    my @init_file;
    my $verbose;
    my $orderwarn;

    GetOptions(
               'help|?' => \$help, man => \$man, 
               'gpd_ignore_headers|gp_ignore_headers|ignore_headers' => \$ignore_headers,
               'gpd_ignore_plans|gp_ignore_plans|ignore_plans' => \$ignore_plans,
               'gpd_init|gp_init|init:s' => \@init_file,
               'do_equiv:s' => \$do_equiv,
		        'order_warn|orderwarn' => \$orderwarn,
		        'verbose' => \$verbose
               )
        or pod2usage(2);

    if (defined($do_equiv))
    {
        if ($do_equiv =~ m/^(ignore)/i)
        {
            # ignore all - default
        }
        elsif ($do_equiv =~ m/^(compare)/i)
        {
            # compare equiv region
            $compare_equiv = 1;
        }
        elsif ($do_equiv =~ m/^(make)/i)            
        {
            # make equiv expected output
            $make_equiv_expected = 1;
        }
        else
        {
            $glob_id = "unknown do_equiv option: $do_equiv\nvalid options are:\n\tdo_equiv=compare\n\tdo_equiv=make";
            $help = 1;
        }

    }

    pod2usage(-msg => $glob_id, -exitstatus => 1) if $help;
    pod2usage(-msg => $glob_id, -exitstatus => 0, -verbose => 2) if $man;

    $glob_compare_equiv       = $compare_equiv;
    $glob_make_equiv_expected = $make_equiv_expected;
    $glob_ignore_headers      = $ignore_headers;
    $glob_ignore_plans        = $ignore_plans;

    $glob_ignore_whitespace   = $ignore_headers; # XXX XXX: for now

	# ENGINF-200: allow multiple init files
    push @{$glob_init}, @init_file;

	$glob_orderwarn           = $orderwarn;
    $glob_verbose      		  = $verbose;

}

my $glob_match_then_sub_fnlist;

sub _build_match_subs
{
    my ($here_matchsubs, $whomatch) = @_;

    my $stat = [1];

     # filter out the comments and blank lines
     $here_matchsubs =~ s/^\s*\#.*$//gm;
     $here_matchsubs =~ s/^\s+$//gm;

#    print $here_matchsubs;

    # split up the document into separate lines
    my @foo = split(/\n/, $here_matchsubs);

    my $ii = 0;

    my $matchsubs_arr = [];
    my $msa;

    # build an array of arrays of match/subs pairs
    while ($ii < scalar(@foo))
    {
        my $lin = $foo[$ii];

        if ($lin =~ m/^\s*$/) # skip blanks
        {
            $ii++;
            next;
        }

        if (defined($msa))
        {
            push @{$msa}, $lin;

            push @{$matchsubs_arr}, $msa;

            undef $msa;
        }
        else
        {
            $msa = [$lin];
        }
        $ii++;
        next;
    } # end while

#    print Data::Dumper->Dump($matchsubs_arr);

    my $bigdef;

    my $fn1;

    # build a lambda function for each expression, and load it into an
    # array
    my $mscount = 1;

    for my $defi (@{$matchsubs_arr})
    {
        unless (2 == scalar(@{$defi}))
        {
            my $err1 = "bad definition: " . Data::Dumper->Dump([$defi]);
            $stat->[0] = 1;
            $stat->[1] = $err1;
            return $stat;
        }

        $bigdef = '$fn1 = sub { my $ini = shift; '. "\n";
        $bigdef .= 'if ($ini =~ ' . $defi->[0];
        $bigdef .= ') { ' . "\n";
#        $bigdef .= 'print "match\n";' . "\n";
        $bigdef .= '$ini =~ ' . $defi->[1];
        $bigdef .= '; }' . "\n";
        $bigdef .= 'return $ini; }' . "\n";

#        print $bigdef;

        if (eval $bigdef)
        {
            my $cmt = $whomatch . " matchsubs \#" . $mscount;
            $mscount++;

            # store the function pointer and the text of the function
            # definition
            push @{$glob_match_then_sub_fnlist}, 
			[$fn1, $bigdef, $cmt, $defi->[0], $defi->[1]];

			if ($glob_verbose)
			{
				print "GP_IGNORE: Defined $cmt\t$defi->[0]\t$defi->[1]\n"
			}
        }
        else
        {
            my $err1 = "bad eval: $bigdef";
            $stat->[0] = 1;
            $stat->[1] = $err1;
            return $stat;
        }

    }

#    print Data::Dumper->Dump($glob_match_then_sub_fnlist);
   
    return $stat;

} # end _build_match_subs

# list of all the match/substitution expressions
BEGIN
{
    my $here_matchsubs;
	

# construct a "HERE" document of match expressions followed by
# substitution expressions.  Embedded comments and blank lines are ok
# (they get filtered out).

    $here_matchsubs = << 'EOF_matchsubs';

# some cleanup of greenplum-specific messages
m/\s+(\W)?(\W)?\(seg.*pid.*\)/
s/\s+(\W)?(\W)?\(seg.*pid.*\)//

m/WARNING:\s+foreign key constraint \".*\" will require costly sequential scans/
s/\".*\"/\"dummy key\"/

m/CONTEXT:.*\s+of this segment db input data/
s/\s+of this segment db input data//

# distributed transactions
m/(ERROR|WARNING|CONTEXT|NOTICE):.*gid\s+=\s+(\d+)/
s/gid.*/gid DUMMY/

m/(ERROR|WARNING|CONTEXT|NOTICE):.*DTM error.*gathered (\d+) results from cmd.*/
s/gathered.*results/gathered SOME_NUMBER_OF results/

# fix code locations eg "(xact.c:1458)" to "(xact.c:SOME_LINE)"
m/(ERROR|WARNING|CONTEXT|NOTICE):\s+Raise an error as directed by/
s/\.c\:\d+\)/\.c\:SOME_LINE\)/

m/(DETAIL|ERROR|WARNING|CONTEXT|NOTICE):\s+Raise .* for debug_dtm_action\s*\=\s* \d+/
s/\.c\:\d+\)/\.c\:SOME_LINE\)/

m/(ERROR|WARNING|CONTEXT|NOTICE):\s+Could not .* savepoint/
s/\.c\:\d+\)/\.c\:SOME_LINE\)/

m/(ERROR|WARNING|CONTEXT|NOTICE):.*connection.*failed.*(http|gpfdist)/
s/connection.*failed.*(http|gpfdist).*/connection failed dummy_protocol\:\/\/DUMMY_LOCATION/

# the EOF ends the HERE document
EOF_matchsubs

    $glob_match_then_sub_fnlist = [];
	
    my $stat = _build_match_subs($here_matchsubs, "DEFAULT");

    if (scalar(@{$stat}) > 1)
    {
        die $stat->[1];
    }

}

sub match_then_subs
{
    my $ini = shift;

    for my $ff (@{$glob_match_then_sub_fnlist})
    {

        # get the function and execute it
        my $fn1 = $ff->[0];
		if (!$glob_verbose)
		{
			$ini = &$fn1($ini);
		}
		else
		{
			my $subs = &$fn1($ini);

			unless ($subs eq $ini)
			{
				print "GP_IGNORE: was: $ini";				
				print "GP_IGNORE: matched $ff->[-3]\t$ff->[-2]\t$ff->[-1]\n"
			}

			$ini = &$fn1($ini);
		}

    }
    return $ini;
}

my $glob_match_then_ignore_fnlist;

sub _build_match_ignores
{
    my ($here_matchignores, $whomatch) = @_;

    my $stat = [1];

     # filter out the comments and blank lines
     $here_matchignores =~ s/^\s*\#.*$//gm;
     $here_matchignores =~ s/^\s+$//gm;

#    print $here_matchignores;

    # split up the document into separate lines
    my @foo = split(/\n/, $here_matchignores);

    my $matchignores_arr = [];

    # build an array of match expressions
    for my $lin (@foo)
    {
        next
            if ($lin =~ m/^\s*$/); # skip blanks
        
        push @{$matchignores_arr}, $lin;
    }

#    print Data::Dumper->Dump($matchignores_arr);

    my $bigdef;

    my $fn1;

    # build a lambda function for each expression, and load it into an
    # array
    my $mscount = 1;

    for my $defi (@{$matchignores_arr})
    {
        $bigdef = '$fn1 = sub { my $ini = shift; '. "\n";
        $bigdef .= 'return ($ini =~ ' . $defi;
        $bigdef .= ') ; } ' . "\n";
#        print $bigdef;

        if (eval $bigdef)
        {
            my $cmt = $whomatch . " matchignores \#" . $mscount;
            $mscount++;

            # store the function pointer and the text of the function
            # definition
            push @{$glob_match_then_ignore_fnlist}, 
			[$fn1, $bigdef, $cmt, $defi, "(ignore)"];
			if ($glob_verbose)
			{
				print "GP_IGNORE: Defined $cmt\t$defi\n"
			}

        }
        else
        {
            my $err1 = "bad eval: $bigdef";
            $stat->[0] = 1;
            $stat->[1] = $err1;
            return $stat;
        }

    }

#    print Data::Dumper->Dump($glob_match_then_ignore_fnlist);
   
    return $stat;

} # end _build_match_ignores

# list of all the match/ignore expressions
BEGIN
{
    my $here_matchignores;

# construct a "HERE" document of match expressions to ignore in input.
# Embedded comments and blank lines are ok (they get filtered out).

    $here_matchignores = << 'EOF_matchignores';

        # XXX XXX: note the discrepancy in the NOTICE messages
        # 'distributed by' vs 'DISTRIBUTED BY'
m/NOTICE:\s+Table doesn\'t have \'distributed by\' clause\, and no column type is suitable/i

m/NOTICE:\s+Table doesn\'t have \'DISTRIBUTED BY\' clause/i

m/NOTICE:\s+Dropping a column that is part of the distribution policy/

m/NOTICE:\s+Table has parent\, setting distribution columns to match parent table/

m/HINT:\s+The \'DISTRIBUTED BY\' clause determines the distribution of data/

m/WARNING:\s+Referential integrity \(.*\) constraints are not supported in Greenplum Database/


m/^\s*Distributed by:\s+\(.*\)\s*$/

        # ignore notices for DROP sqlobject IF EXISTS "objectname"
        # eg NOTICE:  table "foo" does not exist, skipping
        #
        # the NOTICE is different from the ERROR case, which does not
        # end with "skipping"
m/^NOTICE:\s+\w+\s+\".*\"\s+does not exist\,\s+skipping\s*$/


# the EOF ends the HERE document
EOF_matchignores

    $glob_match_then_ignore_fnlist = [];

    my $stat = _build_match_ignores($here_matchignores, "DEFAULT");

    if (scalar(@{$stat}) > 1)
    {
        die $stat->[1];
    }

}

# if the input matches, return 1 (ignore), else return 0 (keep)
sub match_then_ignore
{
    my $ini = shift;

    for my $ff (@{$glob_match_then_ignore_fnlist})
    {
        # get the function and execute it
        my $fn1 = $ff->[0];

		if (&$fn1($ini))
		{
			if ($glob_verbose)
			{
				print "GP_IGNORE: matched $ff->[-3]\t$ff->[-2]\t$ff->[-1]\n"
			}
			return 1; # matched
		}
    }
    return 0; # no match
}

# convert a postgresql psql formatted table into an array of hashes
sub tablelizer
{
    my ($ini, $got_line1) = @_;

    # first, split into separate lines, the find all the column headings

    my @lines = split(/\n/, $ini);

    return undef
        unless (scalar(@lines));

    # if the first line is supplied, then it has the column headers,
    # so don't try to find them (or the ---+---- separator) in
    # "lines"
    my $line1 = $got_line1;
    $line1 = shift @lines
        unless (defined($got_line1));

    # look for <space>|<space>
    my @colheads = split(/\s+\|\s+/, $line1);

    # fixup first, last column head (remove leading,trailing spaces)

    $colheads[0] =~ s/^\s+//;
    $colheads[0] =~ s/\s+$//;
    $colheads[-1] =~ s/^\s+//;
    $colheads[-1] =~ s/\s+$//;

    return undef
        unless (scalar(@lines));
    
    shift @lines # skip dashed separator (unless it was skipped already)
        unless (defined($got_line1));
    
    my @rows;

    for my $lin (@lines)
    {
        my @cols = split(/\|/, $lin, scalar(@colheads));
        last 
            unless (scalar(@cols) == scalar(@colheads));

        my $rowh = {};

        for my $colhdcnt (0..(scalar(@colheads)-1))
        {
            my $rawcol = shift @cols;

            $rawcol =~ s/^\s+//;
            $rawcol =~ s/\s+$//;

            my $colhd = $colheads[$colhdcnt];
            $rowh->{($colhdcnt+1)} = $rawcol;
        }
        push @rows, $rowh;
    }

    return \@rows;
}
# reformat the EXPLAIN output according to the directive hash
sub format_explain
{
    my ($outarr, $directive) = @_;
    my $prefix = "";
	my $xopt = "perl"; # normal case
	my $psuffix = "";

    $directive = {} unless (defined($directive));
    
    # Ignore plan content if its between start_ignore and end_ignore blocks
    # or if -ignore_plans is specified.
    $prefix = "GP_IGNORE:"
         if (exists($directive->{ignore})) || ($glob_ignore_plans);

    {
        use IO::File;
        use POSIX qw(tmpnam);

        my ($tmpnam, $tmpfh);

        for (;;) {
            $tmpnam = tmpnam();
            sysopen($tmpfh, $tmpnam, O_RDWR | O_CREAT | O_EXCL) && last;
        }

        if (scalar(@{$outarr}))
        {
            print $tmpfh "QUERY PLAN\n";
            # explain.pl expects a long string of dashes
            print $tmpfh "-" x 71, "\n";
            for my $lin (@{$outarr})
            {
                print $tmpfh $lin;
            }
            print $tmpfh "(111 rows)\n";
        }

        close $tmpfh;
		
        if (exists($directive->{explain})
			&& ($directive->{explain} =~ m/operator/i))
		{
			$xopt = "operator";
			$psuffix = " | sort ";
		}

		my $plantxt = "explain.pl -opt $xopt -prune heavily < $tmpnam $psuffix";

                
		my $xplan = `$plantxt`;


        unlink $tmpnam;

        if (defined($prefix) && length($prefix))
        {
            $xplan =~ s/^/$prefix/gm;
        }


		print $xplan;

		# for "force_explain operator", replace the outarr with the
		# processed output (for equivalence regions )
        if (scalar(@{$outarr}) 
			&& exists($directive->{explain})
			&& ($directive->{explain} =~ m/operator/i))
		{
			my @foo = split (/\n/, $xplan);

			# gross -- need to add the carriage return back!
			for my $ii (0..(scalar(@foo)-1))
			{
				$foo[$ii] .= "\n";
			}

			return  \@foo;
		}

    }
}    

# reformat the query output according to the directive hash
sub format_query_output
{
    my ($fqostate, $has_order, $outarr, $directive) = @_;
    my $prefix = "";

    $directive = {} unless (defined($directive));

	$fqostate->{count} += 1;

	if ($glob_verbose)
	{
		print "GP_IGNORE: start fqo $fqostate->{count}\n";
	}

    if (exists($directive->{make_equiv_expected}))
    {
		# special case for EXPLAIN PLAN as first "query"
		if (exists($directive->{explain}))
		{
			my $stat = format_explain($outarr, $directive);

			# save the first query output from equiv as "expected rows"

			if ($stat)
			{
				push @{$equiv_expected_rows}, @{$stat};
			}
			else
			{
				push @{$equiv_expected_rows}, @{$outarr};
			}

			if ($glob_verbose)
			{
				print "GP_IGNORE: end fqo $fqostate->{count}\n";
			}

			return ;

		}

        # save the first query output from equiv as "expected rows"
        push @{$equiv_expected_rows}, @{$outarr};
    }
    elsif (defined($equiv_expected_rows)
           && scalar(@{$equiv_expected_rows}))
    {
        # reuse equiv expected rows if you have them
        $outarr = [];
        push @{$outarr}, @{$equiv_expected_rows};
    }

	# explain (if not in an equivalence region)
    if (exists($directive->{explain}))
    {
       format_explain($outarr, $directive);
	   if ($glob_verbose)
	   {
		   print "GP_IGNORE: end fqo $fqostate->{count}\n";
	   }
	   return;
    }
	
    $prefix = "GP_IGNORE:"
        if (exists($directive->{ignore}));

    if (exists($directive->{sortlines}))
    {
        my $firstline = $directive->{firstline};
        my $ordercols = $directive->{order};
        my $mvdlist   = $directive->{mvd};

        # lines already have newline terminator, so just rejoin them.
        my $lines = join ("", @{$outarr});

        my $ah1 = tablelizer($lines, $firstline);

        unless (defined($ah1) && scalar(@{$ah1}))
        {
#            print "No tablelizer hash for $lines, $firstline\n";
#            print STDERR "No tablelizer hash for $lines, $firstline\n";

			if ($glob_verbose)
			{
				print "GP_IGNORE: end fqo $fqostate->{count}\n";
			}

            return;
        }

        my @allcols = sort (keys(%{$ah1->[0]}));

        my @presortcols;
        if (defined($ordercols) && length($ordercols))
        {
#        $ordercols =~ s/^.*order\s*//;
            $ordercols =~ s/\n//gm;
            $ordercols =~ s/\s//gm;

            @presortcols = split(/\s*\,\s*/, $ordercols);
        }

        my @mvdcols;
        my @mvd_deps;
        my @mvd_nodeps;
        my @mvdspec;
        if (defined($mvdlist) && length($mvdlist))
        {
            $mvdlist  =~ s/\n//gm;
            $mvdlist  =~ s/\s//gm;

            # find all the mvd specifications (separated by semicolons)
            my @allspecs = split(/\;/, $mvdlist);

#            print "allspecs:", Data::Dumper->Dump(\@allspecs);

            for my $item (@allspecs)
            {
                my $realspec;
                # split the specification list, separating the
                # specification columns on the left hand side (LHS)
                # from the "dependent" columns on the right hand side (RHS)
                my @colset = split(/\-\>/, $item, 2);
                unless (scalar(@colset) == 2)
                {
                    print "invalid colset for $item\n";
                    print STDERR "invalid colset for $item\n";
                    next;
                }
                # specification columns (LHS)
                my @scols = split(/\,/, $colset[0]);
                unless (scalar(@scols))
                {
                    print "invalid dependency specification: $colset[0]\n";
                    print STDERR 
                        "invalid dependency specification: $colset[0]\n";
                    next;
                }
                # dependent columns (RHS)
                my @dcols = split(/\,/, $colset[1]);
                unless (scalar(@dcols))
                {
                    print "invalid specified dependency: $colset[1]\n";
                    print STDERR "invalid specified dependency: $colset[1]\n";
                    next;
                }
                $realspec = {};
                my $scol2 = [];
                my $dcol2 = [];
                my $sdcol = [];
                $realspec->{spec} = $item;
                push @{$scol2}, @scols;
                push @{$dcol2}, @dcols;
                push @{$sdcol}, @scols, @dcols;
                $realspec->{scol} = $scol2;
                $realspec->{dcol} = $dcol2;
                $realspec->{allcol} = $sdcol;

                push @mvdcols, @scols, @dcols;
                # find all the dependent columns
                push @mvd_deps, @dcols;
                push @mvdspec, $realspec;
            }

            # find all the mvd cols which are *not* dependent.  Need
            # to handle the case of self-dependency, eg "mvd 1->1", so
            # must build set of all columns, then strip out the
            # "dependent" cols.  So this is the set of all LHS columns
            # which are never on the RHS.
            my %get_nodeps;

            for my $col (@mvdcols)
            {
                $get_nodeps{$col} = 1;
            }

            # remove dependent cols
            for my $col (@mvd_deps)
            {
                if (exists($get_nodeps{$col}))
                {
                    delete $get_nodeps{$col};
                }
            }
            # now sorted and unique, with no dependents
            @mvd_nodeps = sort (keys(%get_nodeps));
#            print "mvdspec:", Data::Dumper->Dump(\@mvdspec);
#            print "mvd no deps:", Data::Dumper->Dump(\@mvd_nodeps);
        }

        my %unsorth;
        
        for my $col (@allcols)
        {
            $unsorth{$col} = 1;
        }

		# clear sorted column list if just "order 0"
        if ((1 == scalar(@presortcols))
			&& ($presortcols[0] eq "0"))
        {
			@presortcols = ();
		}


        for my $col (@presortcols)
        {
            if (exists($unsorth{$col}))
            {
                delete $unsorth{$col};
            }
        }
        for my $col (@mvdcols)
        {
            if (exists($unsorth{$col}))
            {
                delete $unsorth{$col};
            }
        }
        my @unsortcols = sort(keys(%unsorth));

#        print Data::Dumper->Dump([$ah1]);

        if (scalar(@presortcols))
        {
            my $hd1 = "sorted columns " . join(", ", @presortcols);

            print $hd1, "\n", "-"x(length($hd1)), "\n";

            for my $h_row (@{$ah1})
            {
                my @collist;

                @collist = ();

#            print "hrow:",Data::Dumper->Dump([$h_row]), "\n";            

                for my $col (@presortcols)
                {
#                print "col: ($col)\n";
                    if (exists($h_row->{$col}))
                    {
                        push @collist, $h_row->{$col};
                    }
                    else
                    {
                        my $maxcol = scalar(@allcols);
                        my $errstr = 
                            "specified ORDER column out of range: $col vs $maxcol\n";
                        print $errstr;
                        print STDERR $errstr;
                        last;
                    }
                }
                print $prefix, join(' | ', @collist), "\n";
            }
        }

        if (scalar(@mvdspec))
        {
            my @outi;

            my $hd1 = "multivalue dependency specifications";

            print $hd1, "\n", "-"x(length($hd1)), "\n";

            for my $mspec (@mvdspec)
            {
                $hd1 = $mspec->{spec};
                print $hd1, "\n", "-"x(length($hd1)), "\n";

                for my $h_row (@{$ah1})
                {
                    my @collist;

                    @collist = ();

#            print "hrow:",Data::Dumper->Dump([$h_row]), "\n";            

                    for my $col (@{$mspec->{allcol}})
                    {
#                print "col: ($col)\n";
                        if (exists($h_row->{$col}))
                        {
                            push @collist, $h_row->{$col};
                        }
                        else
                        {
                            my $maxcol = scalar(@allcols);
                            my $errstr = 
                            "specified MVD column out of range: $col vs $maxcol\n";
                            print $errstr;
                            print STDERR $errstr;
                            last;
                        }

                    }
                    push @outi, join(' | ', @collist);
                }
                my @ggg= sort @outi;

                for my $line (@ggg)
                {
                    print $prefix, $line, "\n";
                }
                @outi = ();
            }
        }
        my $hd2 = "unsorted columns " . join(", ", @unsortcols);

        # the "unsorted" comparison must include all columns which are
        # not sorted or part of an mvd specification, plus the sorted
        # columns, plus the non-dependent mvd columns which aren't
        # already in the list
        if ((scalar(@presortcols))
            || scalar(@mvd_nodeps))
        {
            if (scalar(@presortcols))
            {
                if (scalar(@mvd_deps))
                {
                    my %get_presort;

                    for my $col (@presortcols)
                    {
                        $get_presort{$col} = 1;
                    }
                    # remove "dependent" (RHS) columns
                    for my $col (@mvd_deps)
                    {
                        if (exists($get_presort{$col}))
                        {
                            delete $get_presort{$col};
                        }
                    }
                    # now sorted and unique, minus all mvd dependent cols
                    @presortcols = sort (keys(%get_presort));

                }

                if (scalar(@presortcols))
                {
                    $hd2 .= " ( " . join(", ", @presortcols) . ")";
                    # have to compare all columns as unsorted 
                    push @unsortcols, @presortcols;
                }
            }
            if (scalar(@mvd_nodeps))
            {
                my %get_nodeps;

                for my $col (@mvd_nodeps)
                {
                    $get_nodeps{$col} = 1;
                }
                # remove "nodeps" which are already in the output list
                for my $col (@unsortcols)
                {
                    if (exists($get_nodeps{$col}))
                    {
                        delete $get_nodeps{$col};
                    }
                }
                # now sorted and unique, minus all unsorted/sorted cols
                @mvd_nodeps = sort (keys(%get_nodeps));
                if (scalar(@mvd_nodeps))
                {
                    $hd2 .= " (( " . join(", ", @mvd_nodeps) . "))";
                    # have to compare all columns as unsorted 
                    push @unsortcols, @mvd_nodeps;
                }

            }
            
        }

        print $hd2, "\n", "-"x(length($hd2)), "\n";

        my @finalunsort;

        if (scalar(@unsortcols))
        {
            for my $h_row (@{$ah1})
            {
                my @collist;

                @collist = ();

                for my $col (@unsortcols)
                {
                    if (exists($h_row->{$col}))
                    {
                        push @collist, $h_row->{$col};
                    }
                    else
                    {
                        my $maxcol = scalar(@allcols);
                        my $errstr = 
                            "specified UNSORT column out of range: $col vs $maxcol\n";
                        print $errstr;
                        print STDERR $errstr;
                        last;
                    }

                }
                push @finalunsort, join(' | ', @collist);
            }
            my @ggg= sort @finalunsort;

            for my $line (@ggg)
            {
                print $prefix, $line, "\n";
            }
        }

		if ($glob_verbose)
		{
			print "GP_IGNORE: end fqo $fqostate->{count}\n";
		}

        return;
    } # end order


    if ($has_order)
    {
        my @ggg= @{$outarr};

        if ($glob_ignore_whitespace)
        {
           my @ggg2;

           for my $line (@ggg)
           {
              # remove all leading, trailing whitespace (changes sorting)
              # and whitespace around column separators
              $line =~ s/^\s+//;
              $line =~ s/\s+$//;
              $line =~ s/\|\s+/\|/gm;
              $line =~ s/\s+\|/\|/gm;

              $line .= "\n" # replace linefeed if necessary
                unless ($line =~ m/\n$/);

              push @ggg2, $line;
           } 
           @ggg= @ggg2;
        }

        if ($glob_orderwarn)
        {
            # if no ordering cols specified (no directive), and
            # SELECT has ORDER BY, see if number of order
            # by cols matches all cols in selected lists
            if (exists($directive->{sql_statement})
                && (defined($directive->{sql_statement}))
                && ($directive->{sql_statement} =~ m/select.*order.*by/is))
            {
               my $fl2 = $directive->{firstline};
               my $sql_statement = $directive->{sql_statement};
               $sql_statement =~ s/\n/ /gm;
               my @ocols = 
                   ($sql_statement =~ m/select.*order.*by\s+(.*)\;/is);

#               print Data::Dumper->Dump(\@ocols);

               # lines already have newline terminator, so just rejoin them.
               my $line2 = join ("", @{$outarr});

               my $ah2 = tablelizer($line2, $fl2);
               my @allcols2;

#               print Data::Dumper->Dump([$ah2]);

               @allcols2 = (keys(%{$ah2->[0]}))
                 if (defined($ah2) && scalar(@{$ah2}));

               # treat the order by cols as a column separated list,
               # and count them.  works ok for simple ORDER BY clauses
               if (scalar(@ocols))
               {
                  my $ocolstr = shift @ocols;
                  my @ocols2  = split (/\,/, $ocolstr);
                  
                  if (scalar(@ocols2) < scalar(@allcols2))
                  {
				     print "GP_IGNORE: ORDER_WARNING: OUTPUT ",
                     scalar(@allcols2), " columns, but ORDER BY on ",
                     scalar(@ocols2), " \n";
                  }
               }
            }
        } # end if $glob_orderwarn

        for my $line (@ggg)
        {
            print $dpref, $prefix, $line;
        }
    }
    else
    {
        my @ggg= sort @{$outarr};

        if ($glob_ignore_whitespace)
        {
           my @ggg2;

           for my $line (@ggg)
           {
              # remove all leading, trailing whitespace (changes sorting)
              # and whitespace around column separators
              $line =~ s/^\s+//;
              $line =~ s/\s+$//;
              $line =~ s/\|\s+/\|/gm;
              $line =~ s/\s+\|/\|/gm;

              $line .= "\n" # replace linefeed if necessary
                unless ($line =~ m/\n$/);

              push @ggg2, $line;
           } 
           @ggg= sort @ggg2;
        }
        for my $line (@ggg)
        {
            print $bpref, $prefix, $line;
        }
    }

	if ($glob_verbose)
	{
		print "GP_IGNORE: end fqo $fqostate->{count}\n";
	}
}


sub bigloop
{
    my $sql_statement = "";
    my @outarr;

    my $getrows = 0;
    my $getstatement = 0;
    my $has_order = 0;
    my $copy_select = 0;
    my $directive = {};
    my $big_ignore = 0;
    my $define_match_expression = undef;
    my $error_detail_exttab_trifecta_skip = 0; # don't ask!
    my $verzion = "unknown";

    if (q$Revision$ =~ /\d+/)
    {
        $verzion = do { my @r = (q$Revision$ =~ /\d+/g); sprintf "%d."."%02d" x $#r, @r }; # must be all one line, for MakeMaker
    }
        my $format_fix = << "EOF_formatfix";
                                ))}
EOF_formatfix
    # NOTE: define $format_fix with HERE document just to fix emacs
    # indenting due to comment char in Q expression...

    $verzion = $0 . " version " . $verzion;
    print "GP_IGNORE: formatted by $verzion\n";

    my $do_equiv = $glob_compare_equiv || $glob_make_equiv_expected;

  L_bigwhile:
    while (<>) # big while
    {
        my $ini = $_;

        if ($error_detail_exttab_trifecta_skip)
        {
            $error_detail_exttab_trifecta_skip = 0;
            next;
        }

        # look for match/substitution or match/ignore expressions
        if (defined($define_match_expression))
        {
            unless (($ini =~ m/\-\-\s*end\_match(subs|ignore)\s*$/i))
            {
                $define_match_expression .= $ini;
                goto L_push_outarr;
            }

            my @foo = split(/\n/, $define_match_expression, 2);

            unless (2 == scalar(@foo))
            {
                $ini .= "GP_IGNORE: bad match definition\n";
                undef $define_match_expression;
                goto L_push_outarr;
            }

            my $stat;

            my $doc1 = $foo[1];

            # strip off leading comment characters
            $doc1 =~ s/^\s*\-\-//gm;

            if ($foo[0] =~ m/subs/)
            {
                $stat = _build_match_subs($doc1, "USER");
            }
            else
            {
                $stat = _build_match_ignores($doc1, "USER");
            }

            if (scalar(@{$stat}) > 1)
            {
                my $outi = $stat->[1];

                # print a message showing the error
                $outi =~ s/^(.*)/GP_IGNORE: ($1)/gm;
                $ini .= $outi;
            }
            else
            {
                $ini .=  "GP_IGNORE: defined new match expression\n";
            }

            undef $define_match_expression;
            goto L_push_outarr;
        } # end defined match expression

        if ($big_ignore > 0)
        {
            if (($ini =~ m/\-\-\s*end\_equiv\s*$/i) && !($do_equiv))
            { 
                $big_ignore -= 1;
            }
            if ($ini =~ m/\-\-\s*end\_ignore\s*$/i)
            { 
                $big_ignore -= 1;
            }
            print "GP_IGNORE:", $ini;
            next;
        }
        elsif (($ini =~ m/\-\-\s*end\_equiv\s*$/i) && $do_equiv)
        {
            $equiv_expected_rows = undef;
        }

        if ($ini =~ m/\-\-\s*end\_head(er|ers|ing|ings)\_ignore\s*$/i)
        {
            $glob_ignore_headers = 0;
        }

        if ($getrows) # getting rows from SELECT output
        {
            # special case for copy select
            if ($copy_select &&
                ($ini =~ m/(\-\-)|(ERROR)/))
            {
                my @ggg= sort @outarr;
                for my $line (@ggg)
                {
                    print $bpref, $line;
                }

                @outarr = ();
                $getrows = 0;
                $has_order = 0;
                $copy_select = 0;
                next;
            }


            # regex example: (5 rows)
            if ($ini =~ m/^\s*\(\d+\s+row(s)*\)\s*$/)
            {


                format_query_output($glob_fqo,
									$has_order, \@outarr, $directive);
          
      
                # Always ignore the rowcount for explain plan out as the skeleton plans might be the 
                # same even if the row counts differ because of session level GUCs. 
                if (exists($directive->{explain}))
                {
                    $ini = "GP_IGNORE:" . $ini;
                }

                $directive = {};
                @outarr = ();
                $getrows = 0;
                $has_order = 0;
            }
        }
        else # finding SQL statement or start of SELECT output
        { 
            if (($ini =~ m/\-\-\s*start\_match(subs|ignore)\s*$/i))
            {
                $define_match_expression = $ini;
                goto L_push_outarr;
            }
            if (($ini =~ m/\-\-\s*start\_ignore\s*$/i) ||
                (($ini =~ m/\-\-\s*start\_equiv\s*$/i) && !($do_equiv)))
            { 
                $big_ignore += 1;

                for my $line (@outarr)
                {
                    print $apref, $line;
                }
                @outarr = ();

                print "GP_IGNORE:", $ini;
                next;
            }
            elsif (($ini =~ m/\-\-\s*start\_equiv\s*$/i) && 
                   $glob_make_equiv_expected)
            {
                $equiv_expected_rows = [];
                $directive->{make_equiv_expected} = 1;
            }

            if ($ini =~ m/\-\-\s*start\_head(er|ers|ing|ings)\_ignore\s*$/i)
            {
                $glob_ignore_headers = 1;
            }

            # Note: \d is for the psql "describe"
            if ($ini =~ m/(insert|update|delete|select|\\d|copy)/i)
            {                
                $copy_select = 0;
                $has_order = 0;
                $sql_statement = "";

                if ($ini =~ m/explain.*(insert|update|delete|select)/i)
                { 
                   $directive->{explain} = "normal";
                }

            }

            if ($ini =~ m/\-\-\s*force\_explain\s+operator.*$/i)
            {
                # ENGINF-137: force_explain 
                $directive->{explain} = "operator";
            }
            if ($ini =~ m/\-\-\s*force\_explain\s*$/i)
            {
                # ENGINF-137: force_explain 
                $directive->{explain} = "normal";
            }
            if ($ini =~ m/\-\-\s*ignore\s*$/i)
            { 
                $directive->{ignore} = "ignore";
            }
            if ($ini =~ m/\-\-\s*order\s+\d+.*$/i)
            { 
                my $olist = $ini;
                $olist =~ s/^.*\-\-\s*order//;
                $directive->{order} = $olist;
            }
            if ($ini =~ m/\-\-\s*mvd\s+\d+.*$/i)
            { 
                my $olist = $ini;
                $olist =~ s/^.*\-\-\s*mvd//;
                $directive->{mvd} = $olist;
            }

            if ($ini =~ m/select/i)
            {
                $getstatement = 1;
            }
            if ($getstatement)
            {
                $sql_statement .= $ini;
            }
            if ($ini =~ m/\;/) # statement terminator
            {
                $getstatement = 0;
            }

            # prune notices with segment info if they are duplicates
#            if ($ini =~ m/^\s*(NOTICE|ERROR|HINT|DETAIL|WARNING)\:.*\s+\(seg.*pid.*\)/)
            if ($ini =~ m/^\s*(NOTICE|ERROR|HINT|DETAIL|WARNING)\:/)
            {
                $ini =~ s/\s+(\W)?(\W)?\(seg.*pid.*\)//;

				# also remove line numbers from errors
				$ini =~ s/\s+(\W)?(\W)?\(\w+\.[cph]+:\d+\)/ (SOMEFILE:SOMEFUNC)/;
                my $outsize = scalar(@outarr);

                my $lastguy = -1;

              L_checkfor:
                for my $jj (1..$outsize)
                {
                    my $checkstr = $outarr[$lastguy];

                    #remove trailing spaces for comparison
                    $checkstr =~ s/\s+$//;

                    my $skinny = $ini;
                    $skinny =~ s/\s+$//;

                    # stop when no more notices
                    last L_checkfor
                        if ($checkstr !~ m/^\s*(NOTICE|ERROR|HINT|DETAIL|WARNING)\:/);

                    # discard this line if matches a previous notice
                    if ($skinny eq $checkstr)
                    {
                        if (0) # debug code
                        {
                            $ini = "DUP: " . $ini;
                            last L_checkfor;
                        }
                        next L_bigwhile;
                    }
                    $lastguy--;
                } # end for


            } # end if pruning notices

            # MPP-1492 allow:
            #  copy (select ...) to stdout
            #  \copy (select ...) to stdout
            # and special case these guys:
            #  copy test1 to stdout
            #  \copy test1 to stdout
            #
            # ENGINF-129:
            # and "copy...;  -- copy_stdout " for copy.out
            my $copysel_regex = 
            '^\s*((.copy.*test1.*to stdout)|(copy.*test1.*to stdout\;)|(copy.*\;\s*\-\-\s*copy\_stdout))';

            # regex example: ---- or ---+---
            # need at least 3 dashes to avoid confusion with "--" comments
            if (($ini =~ m/^\s*((\-\-)(\-)+(\+(\-)+)*)+\s*$/)
                # special case for copy select
                || (($ini =~ m/$copysel_regex/i)
                    && ($ini !~ m/order\s+by/i)))
            { # sort this region

                $directive->{firstline} = $outarr[-1];

                if (exists($directive->{order}) ||
                    exists($directive->{mvd}))
                {
                    $directive->{sortlines} = $outarr[-1];
                }

                # special case for copy select
                if ($ini =~ m/$copysel_regex/i)
                {
#                    print "copy select: $ini\n";
                    $copy_select = 1;
                    $sql_statement = "";
                }
                # special case for explain
               if (exists($directive->{explain}) &&
                   ($ini =~ m/^\s*((\-\-)(\-)+(\+(\-)+)*)+\s*$/) &&
                   ($outarr[-1] =~ m/QUERY PLAN/))
                {
                   # ENGINF-88: fixup explain headers
                   $outarr[-1] = "QUERY PLAN\n";
                   $ini = ("_" x length($outarr[-1])) . "\n";

                   if ($glob_ignore_headers)
                   {
                      $ini = "GP_IGNORE:" . $ini;
                   }
                }

                $getstatement = 0;

                # ENGINF-180: ignore header formatting
                # the last line of the outarr is the first line of the header
                if ($glob_ignore_headers && $outarr[-1])
                {
                    $outarr[-1] = "GP_IGNORE:" . $outarr[-1];
                }

                for my $line (@outarr)
                {
                    print $apref, $line;
                }
                @outarr = ();

                # ENGINF-180: ignore header formatting
                # the current line is the last line of the header
                if ($glob_ignore_headers
                    && ($ini =~ m/^\s*((\-\-)(\-)+(\+(\-)+)*)+\s*$/))
                {
                    $ini = "GP_IGNORE:" . $ini;
                }
                
                print $apref, $ini;

                if (defined($sql_statement)
                    && length($sql_statement)
                    # multiline match 
                    && ($sql_statement =~ m/select.*order.*by/is))
                {
                    $has_order = 1; # so do *not* sort output

#                   $sql_statement =~ s/\n/ /gm;
#                   print "has order: ", $sql_statement, "\n";
                    $directive->{sql_statement} = $sql_statement;
                }
                else
                {
                    $has_order = 0; # need to sort query output

#                    $sql_statement =~ s/\n/ /gm;
#                    print "no order: ", $sql_statement, "\n";
                    $directive->{sql_statement} = $sql_statement;
                }
                $sql_statement = "";

                $getrows = 1;
                next;
            } # end sort this region
        } # end finding SQL


        # if MATCH then SUBSTITUTE
        # see HERE document for definitions
        $ini = match_then_subs($ini);

        if ($ini =~ m/External table .*line (\d)+/)
        {
            $ini =~ s/External table .*line (\d)+.*/External table DUMMY_EX, line DUMMY_LINE of DUMMY_LOCATION/;
              $ini =~ s/\s+/ /;
             # MPP-1557,AUTO-3: horrific ERROR DETAIL External Table trifecta
			if ($glob_verbose)
			{
				print "GP_IGNORE: External Table ERROR DETAIL fixup\n";
			}
             if ($ini !~ m/^DETAIL/)
             {
                # find a "blank" DETAIL tag preceding current line
                if (scalar(@outarr) && ($outarr[-1] =~ m/^DETAIL:\s+$/))
                {
                   pop @outarr;
                   $ini = "DETAIL: " . $ini;
                   $ini =~ s/\s+/ /;
                   # need to skip the next record
                   $error_detail_exttab_trifecta_skip = 1;
                }
             }
             if (scalar(@outarr) && 
                 ($outarr[-1] =~ m/^ERROR:\s+missing\s+data\s+for\s+column/))
             {
                $outarr[-1] = "ERROR:  missing data for column DUMMY_COL\n";
             }

        }

        # if MATCH then IGNORE
        # see HERE document for definitions
        if ( match_then_ignore($ini))
        {
           next; # ignore matching lines
        }

L_push_outarr:

        push @outarr, $ini;

    } # end big while

    for my $line (@outarr)
    {
        print $cpref, $line;
    }
} # end bigloop

if (1)
{
	goto l_big_bigloop
		unless (defined($glob_init) && scalar(@{$glob_init}));

	# ENGINF-200: allow multiple init files
	for my $init_file (@{$glob_init})
	{
		die "no such file: $init_file"
			unless (-e $init_file);

		# redirect stdin and stdout to perform initialization from
		# the init_file file

		open my $oldout, ">&STDOUT"     or die "Can't dup STDOUT: $!";
		
		close STDOUT;
		open (STDOUT, "> /dev/null" ) or die "can't open STDOUT: $!";
 
		open my $oldin, "<&STDIN"     or die "Can't dup STDIN: $!";
		close STDIN;
		open STDIN, "< $init_file" or die "could not open $init_file: $!";

		# run the standard loop

		bigloop();

		# reset stdin, stdout
		
		close STDIN;
		open STDIN, "<&", $oldin or die "Can't dup \$oldin: $!";

		close STDOUT;
		open STDOUT, ">&", $oldout or die "Can't dup \$oldout: $!";
	}

l_big_bigloop:
	# loop over existing stdin
	bigloop();
}


exit();