System.Data.SQLite: Check-in [1f05aa9dd6]

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview

Comment:	Do not emit ORDER BY, LIMIT, and OFFSET clauses for the left SELECT statement when a compound operator is involved. Candidate fix for [0a32885109].
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| tkt-0a32885109
Files:	files \| file ages \| folders
SHA1:	1f05aa9dd6d07a33d92e28b1cd4b995aade11734
User & Date:	mistachkin 2014-11-08 06:41:20

Context

2014-11-08
06:45		Remove UTF-8 BOM. check-in: 33014329d6 user: mistachkin tags: tkt-0a32885109
06:41		Do not emit ORDER BY, LIMIT, and OFFSET clauses for the left SELECT statement when a compound operator is involved. Candidate fix for [0a32885109]. check-in: 1f05aa9dd6 user: mistachkin tags: tkt-0a32885109
2014-11-07
23:48		Defer disposing of the connection created by the static SQLiteCommand.Execute method when a data reader is returned. Fix for [daeaf3150a]. check-in: ad79758d0c user: mistachkin tags: trunk

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to Doc/Extra/Provider/version.html.

    <h1 class="heading">Version History</h1>
    <p><b>1.0.95.0 - November XX, 2014 <font color="red">(release scheduled)</font></b></p>
    <ul>
      <li>Updated to <a href="http://www.sqlite.org/releaselog/3_8_7_1.html">SQLite 3.8.7.1</a>.</li>
      <li>Make sure SQL statements generated for DbUpdateCommandTree objects are properly delimited.</li>
      <li>Various minor performance enhancements to the SQLiteDataReader class. Pursuant to <a href="http://system.data.sqlite.org/index.html/info/e122d26e70">[e122d26e70]</a>.</li>
      <li>Defer disposing of connections created by the static SQLiteCommand.Execute method when a data reader is returned. Fix for <a href="http://system.data.sqlite.org/index.html/info/daeaf3150a">[daeaf3150a]</a>.</li>

      <li>In the SQLiteDataReader.VerifyType method, remove duplicate &quot;if&quot; statement for the DbType.SByte value and move the remaining &quot;if&quot; to the Int64 affinity. Fix for <a href="http://system.data.sqlite.org/index.html/info/c5cc2fb334">[c5cc2fb334]</a>.&nbsp;<b>** Potentially Incompatible Change **</b></li>
    </ul>
    <p><b>1.0.94.0 - September 9, 2014</b></p>
    <ul>
      <li>Updated to <a href="http://www.sqlite.org/releaselog/3_8_6.html">SQLite 3.8.6</a>.</li>
      <li>Updated to <a href="http://www.nuget.org/packages/EntityFramework/6.1.1">Entity Framework 6.1.1</a>.</li>
      <li>Refactor and simplify NuGet packages in order to support per-solution SQLite interop assembly files.&nbsp;<b>** Potentially Incompatible Change **</b></li>

    <h1 class="heading">Version History</h1>
    <p><b>1.0.95.0 - November XX, 2014 <font color="red">(release scheduled)</font></b></p>
    <ul>
      <li>Updated to <a href="http://www.sqlite.org/releaselog/3_8_7_1.html">SQLite 3.8.7.1</a>.</li>
      <li>Make sure SQL statements generated for DbUpdateCommandTree objects are properly delimited.</li>
      <li>Various minor performance enhancements to the SQLiteDataReader class. Pursuant to <a href="http://system.data.sqlite.org/index.html/info/e122d26e70">[e122d26e70]</a>.</li>
      <li>Defer disposing of connections created by the static SQLiteCommand.Execute method when a data reader is returned. Fix for <a href="http://system.data.sqlite.org/index.html/info/daeaf3150a">[daeaf3150a]</a>.</li>
      <li>Do not emit ORDER BY, LIMIT, and OFFSET clauses for the left SELECT statement when a compound operator is involved. Fix for <a href="http://system.data.sqlite.org/index.html/info/0a32885109">[0a32885109]</a>.</li>
      <li>In the SQLiteDataReader.VerifyType method, remove duplicate &quot;if&quot; statement for the DbType.SByte value and move the remaining &quot;if&quot; to the Int64 affinity. Fix for <a href="http://system.data.sqlite.org/index.html/info/c5cc2fb334">[c5cc2fb334]</a>.&nbsp;<b>** Potentially Incompatible Change **</b></li>
    </ul>
    <p><b>1.0.94.0 - September 9, 2014</b></p>
    <ul>
      <li>Updated to <a href="http://www.sqlite.org/releaselog/3_8_6.html">SQLite 3.8.6</a>.</li>
      <li>Updated to <a href="http://www.nuget.org/packages/EntityFramework/6.1.1">Entity Framework 6.1.1</a>.</li>
      <li>Refactor and simplify NuGet packages in order to support per-solution SQLite interop assembly files.&nbsp;<b>** Potentially Incompatible Change **</b></li>

Changes to System.Data.SQLite.Linq/SQL Generation/SqlGenerator.cs.


    ISqlFragment VisitSetOpExpression(DbExpression left, DbExpression right, string separator)
    {

      SqlSelectStatement leftSelectStatement = VisitExpressionEnsureSqlStatement(left);
      SqlSelectStatement rightSelectStatement = VisitExpressionEnsureSqlStatement(right);








      SqlBuilder setStatement = new SqlBuilder();
      setStatement.Append(leftSelectStatement);
      setStatement.AppendLine();
      setStatement.Append(separator); // e.g. UNION ALL
      setStatement.AppendLine();
      setStatement.Append(rightSelectStatement);








>
>
>
>
>
>
>


    ISqlFragment VisitSetOpExpression(DbExpression left, DbExpression right, string separator)
    {

      SqlSelectStatement leftSelectStatement = VisitExpressionEnsureSqlStatement(left);
      SqlSelectStatement rightSelectStatement = VisitExpressionEnsureSqlStatement(right);

      //
      // BUGFIX: [0a32885109] When using compound operators (e.g. UNION ALL),
      //         the non-rightmost SELECT statement(s) may NOT have an ORDER
      //         BY, LIMIT, or OFFSET clause.
      //
      leftSelectStatement.ClearOrderByLimitAndOffset();

      SqlBuilder setStatement = new SqlBuilder();
      setStatement.Append(leftSelectStatement);
      setStatement.AppendLine();
      setStatement.Append(separator); // e.g. UNION ALL
      setStatement.AppendLine();
      setStatement.Append(rightSelectStatement);

Changes to System.Data.SQLite.Linq/SQL Generation/SqlSelectStatement.cs.

    // if not Order By should be omitted unless there is a corresponding TOP
    private bool isTopMost;
    internal bool IsTopMost
    {
      get { return this.isTopMost; }
      set { this.isTopMost = value; }
    }











    #region ISqlFragment Members

    /// <summary>
    /// Write out a SQL select statement as a string.
    /// We have to
    /// <list type="number">








>
>
>
>
>
>
>
>
>
>

    // if not Order By should be omitted unless there is a corresponding TOP
    private bool isTopMost;
    internal bool IsTopMost
    {
      get { return this.isTopMost; }
      set { this.isTopMost = value; }
    }

    /// <summary>
    /// Clears the ORDER BY, LIMIT, and OFFSET clauses.
    /// </summary>
    public void ClearOrderByLimitAndOffset()
    {
        this.orderBy = null;
        this.top = null;
        this.skip = null;
    }

    #region ISqlFragment Members

    /// <summary>
    /// Write out a SQL select statement as a string.
    /// We have to
    /// <list type="number">

Changes to System.Data.SQLite.Linq/System.Data.SQLite.EF6.2010.csproj.

Added Tests/tkt-0a32885109.eagle.

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

###############################################################################
#
# tkt-0a32885109.eagle --
#
# Written by Joe Mistachkin.
# Released to the public domain, use at your own risk!
#
###############################################################################

package require Eagle
package require Eagle.Library
package require Eagle.Test

runTestPrologue

###############################################################################

package require System.Data.SQLite.Test
runSQLiteTestPrologue
runSQLiteTestFilesPrologue

###############################################################################

runTest {test tkt-0a32885109-1.1 {LINQ compound-operator handling} -body {
  #
  # NOTE: Re-copy the reference database file used for this unit test to the
  #       build directory in case it has been changed by a previous test run.
  #
  file copy -force $northwindEfDbFile \
      [file join [getBuildDirectory] [file tail $northwindEfDbFile]]

  set result [list]
  set output ""

  set code [catch {
    testClrExec $testLinqExeFile [list -eventflags Wait -directory \
        [file dirname $testLinqExeFile] -nocarriagereturns -stdout output \
        -success 0] -unionall
  } error]

  tlog "---- BEGIN STDOUT OUTPUT\n"
  tlog $output
  tlog "\n---- END STDOUT OUTPUT\n"

  lappend result $code

  if {$code == 0} then {
    lappend result [string trim $output]
  } else {
    lappend result [string trim $error]
  }

  set result
} -cleanup {
  unset -nocomplain code output error result
} -constraints {eagle monoToDo SQLite file_System.Data.SQLite.dll testExec\
file_System.Data.SQLite.Linq.dll file_testlinq.exe file_northwindEF.db} \
-result {0 {WHITC ANATR BERGS ALFKI BSBEV}}}

###############################################################################

runSQLiteTestFilesEpilogue
runSQLiteTestEpilogue
runTestEpilogue

Changes to readme.htm.

    <b>1.0.95.0 - November XX, 2014 <font color="red">(release scheduled)</font></b>
</p>
<ul>
    <li>Updated to <a href="http://www.sqlite.org/releaselog/3_8_7.html">SQLite 3.8.7</a>.</li>
    <li>Make sure SQL statements generated for DbUpdateCommandTree objects are properly delimited.</li>
    <li>Various minor performance enhancements to the SQLiteDataReader class. Pursuant to [e122d26e70].</li>
    <li>Defer disposing of connections created by the static SQLiteCommand.Execute method when a data reader is returned. Fix for [daeaf3150a].</li>

    <li>In the SQLiteDataReader.VerifyType method, remove duplicate &quot;if&quot; statement for the DbType.SByte value and move the remaining &quot;if&quot; to the Int64 affinity. Fix for [c5cc2fb334].&nbsp;<b>** Potentially Incompatible Change **</b></li>
</ul>
<p>
    <b>1.0.94.0 - September 9, 2014</b>
</p>
<ul>
    <li>Updated to <a href="http://www.sqlite.org/releaselog/3_8_6.html">SQLite 3.8.6</a>.</li>

    <b>1.0.95.0 - November XX, 2014 <font color="red">(release scheduled)</font></b>
</p>
<ul>
    <li>Updated to <a href="http://www.sqlite.org/releaselog/3_8_7.html">SQLite 3.8.7</a>.</li>
    <li>Make sure SQL statements generated for DbUpdateCommandTree objects are properly delimited.</li>
    <li>Various minor performance enhancements to the SQLiteDataReader class. Pursuant to [e122d26e70].</li>
    <li>Defer disposing of connections created by the static SQLiteCommand.Execute method when a data reader is returned. Fix for [daeaf3150a].</li>
    <li>Do not emit ORDER BY, LIMIT, and OFFSET clauses for the left SELECT statement when a compound operator is involved. Fix for [0a32885109].</li>
    <li>In the SQLiteDataReader.VerifyType method, remove duplicate &quot;if&quot; statement for the DbType.SByte value and move the remaining &quot;if&quot; to the Int64 affinity. Fix for [c5cc2fb334].&nbsp;<b>** Potentially Incompatible Change **</b></li>
</ul>
<p>
    <b>1.0.94.0 - September 9, 2014</b>
</p>
<ul>
    <li>Updated to <a href="http://www.sqlite.org/releaselog/3_8_6.html">SQLite 3.8.6</a>.</li>

Changes to testlinq/Program.cs.


                          if (arg != null)
                              pageSize = int.Parse(arg.Trim());
                      }

                      return SkipTest(pageSize);
                  }




              case "endswith":
                  {
                      string value = null;

                      if (args.Length > 1)
                      {
                          value = args[1];
................................................................................
                      Console.Write(' ');

                  Console.Write(customers.CustomerID);

                  once = true;
              }
          }








































































          return 0;
      }

      //
      // NOTE: Used to test the fix for ticket [ccfa69fc32].
      //








>
>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


                          if (arg != null)
                              pageSize = int.Parse(arg.Trim());
                      }

                      return SkipTest(pageSize);
                  }
              case "unionall":
                  {
                      return UnionAllTest();
                  }
              case "endswith":
                  {
                      string value = null;

                      if (args.Length > 1)
                      {
                          value = args[1];
................................................................................
                      Console.Write(' ');

                  Console.Write(customers.CustomerID);

                  once = true;
              }
          }

          return 0;
      }

      //
      // NOTE: Used to test the fix for ticket [0a32885109].
      //
      private static int UnionAllTest()
      {
          using (northwindEFEntities db = new northwindEFEntities())
          {
              bool once = false;

              var customers1 = db.Customers.Where(
                  f => f.Orders.Any()).OrderByDescending(
                    f => f.CompanyName).Skip(1).Take(1);

              var customers2 = db.Customers.Where(
                  f => f.Orders.Any()).OrderBy(
                    f => f.CompanyName).Skip(1).Take(1);

              var customers3 = db.Customers.Where(
                  f => f.CustomerID.StartsWith("B")).OrderBy(
                    f => f.CompanyName).Skip(1).Take(1);

              foreach (var customer in customers1)
              {
                  if (once)
                      Console.Write(' ');

                  Console.Write(customer.CustomerID);
                  once = true;
              }

              foreach (var customer in customers2)
              {
                  if (once)
                      Console.Write(' ');

                  Console.Write(customer.CustomerID);
                  once = true;
              }

              foreach (var customer in customers3)
              {
                  if (once)
                      Console.Write(' ');

                  Console.Write(customer.CustomerID);
                  once = true;
              }

              foreach (var customer in customers1.Concat(customers2))
              {
                  if (once)
                      Console.Write(' ');

                  Console.Write(customer.CustomerID);
                  once = true;
              }

              foreach (var customer in
                    customers1.Concat(customers2).Concat(customers3))
              {
                  if (once)
                      Console.Write(' ');

                  Console.Write(customer.CustomerID);
                  once = true;
              }
          }

          return 0;
      }

      //
      // NOTE: Used to test the fix for ticket [ccfa69fc32].
      //

Changes to www/news.wiki.

    <b>1.0.95.0 - November XX, 2014 <font color="red">(release scheduled)</font></b>
</p>
<ul>
    <li>Updated to [http://www.sqlite.org/releaselog/3_8_7_1.html|SQLite 3.8.7.1].</li>
    <li>Make sure SQL statements generated for DbUpdateCommandTree objects are properly delimited.</li>
    <li>Various minor performance enhancements to the SQLiteDataReader class. Pursuant to [e122d26e70].</li>
    <li>Defer disposing of connections created by the static SQLiteCommand.Execute method when a data reader is returned. Fix for [daeaf3150a].</li>

    <li>In the SQLiteDataReader.VerifyType method, remove duplicate &quot;if&quot; statement for the DbType.SByte value and move the remaining &quot;if&quot; to the Int64 affinity.  Fix for [c5cc2fb334].&nbsp;<b>** Potentially Incompatible Change **</b></li>
</ul>
<p>
    <b>1.0.94.0 - September 9, 2014</b>
</p>
<ul>
    <li>Updated to [http://www.sqlite.org/releaselog/3_8_6.html|SQLite 3.8.6].</li>

    <b>1.0.95.0 - November XX, 2014 <font color="red">(release scheduled)</font></b>
</p>
<ul>
    <li>Updated to [http://www.sqlite.org/releaselog/3_8_7_1.html|SQLite 3.8.7.1].</li>
    <li>Make sure SQL statements generated for DbUpdateCommandTree objects are properly delimited.</li>
    <li>Various minor performance enhancements to the SQLiteDataReader class. Pursuant to [e122d26e70].</li>
    <li>Defer disposing of connections created by the static SQLiteCommand.Execute method when a data reader is returned. Fix for [daeaf3150a].</li>
    <li>Do not emit ORDER BY, LIMIT, and OFFSET clauses for the left SELECT statement when a compound operator is involved. Fix for [0a32885109].</li>
    <li>In the SQLiteDataReader.VerifyType method, remove duplicate &quot;if&quot; statement for the DbType.SByte value and move the remaining &quot;if&quot; to the Int64 affinity.  Fix for [c5cc2fb334].&nbsp;<b>** Potentially Incompatible Change **</b></li>
</ul>
<p>
    <b>1.0.94.0 - September 9, 2014</b>
</p>
<ul>
    <li>Updated to [http://www.sqlite.org/releaselog/3_8_6.html|SQLite 3.8.6].</li>