System.Data.SQLite
Check-in [a0f4a5ebcf]
Not logged in

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

Overview
Comment:Wrap SELECT statements in parenthesis if they have an ORDER BY, LIMIT, or OFFSET clause and a compound operator is involved. Fix for [0a32885109].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a0f4a5ebcf544f53e010a621d78f5316ceec10b3
User & Date: mistachkin 2014-11-14 00:55:05
Context
2014-11-14
17:57
The interop assembly methods should use 'ref' parameters, not 'out' parameters, since the native interop methods are not actually guaranteed to set output parameters to well-defined values. Also, add more parameter validation to the interop assembly, paying special attention to output parameters, especially optional ones. Pursuant to [3b43ffdbd7]. check-in: 0a1f243a29 user: mistachkin tags: trunk
01:18
The interop assembly methods should use 'ref' parameters, not 'out' parameters, since the native interop methods are not actually guaranteed to set output parameters to well-defined values. Also, add more parameter validation to the interop assembly, paying special attention to output parameters, especially optional ones. Pursuant to [3b43ffdbd7]. check-in: 5b359db222 user: mistachkin tags: interopParams2
00:58
Merge updates from trunk. check-in: d48e9010a9 user: mistachkin tags: publishWithNuGetPkg
00:55
Wrap SELECT statements in parenthesis if they have an ORDER BY, LIMIT, or OFFSET clause and a compound operator is involved. Fix for [0a32885109]. check-in: a0f4a5ebcf user: mistachkin tags: trunk
00:20
Merge updates from trunk. Closed-Leaf check-in: 8d77d459fa user: mistachkin tags: tkt-0a32885109
00:19
Handle Julian Day values that fall outside of the supported range for OLE Automation dates. Fix for [3e783eecbe]. check-in: 3bd76a0c9d user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

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

    45     45       <h1 class="heading">Version History</h1>
    46     46       <p><b>1.0.95.0 - November XX, 2014 <font color="red">(release scheduled)</font></b></p>
    47     47       <ul>
    48     48         <li>Updated to <a href="http://www.sqlite.org/releaselog/3_8_7_1.html">SQLite 3.8.7.1</a>.</li>
    49     49         <li>Make sure SQL statements generated for DbUpdateCommandTree objects are properly delimited.</li>
    50     50         <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>
    51     51         <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>
           52  +      <li>Wrap SELECT statements in parenthesis if they have an ORDER BY, LIMIT, or OFFSET clause and a compound operator is involved. Fix for <a href="http://system.data.sqlite.org/index.html/info/0a32885109">[0a32885109]</a>.</li>
    52     53         <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>
    53     54         <li>Handle Julian Day values that fall outside of the supported range for OLE Automation dates. Fix for <a href="http://system.data.sqlite.org/index.html/info/3e783eecbe">[3e783eecbe]</a>.&nbsp;<b>** Potentially Incompatible Change **</b></li>
    54     55       </ul>
    55     56       <p><b>1.0.94.0 - September 9, 2014</b></p>
    56     57       <ul>
    57     58         <li>Updated to <a href="http://www.sqlite.org/releaselog/3_8_6.html">SQLite 3.8.6</a>.</li>
    58     59         <li>Updated to <a href="http://www.nuget.org/packages/EntityFramework/6.1.1">Entity Framework 6.1.1</a>.</li>

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

  2466   2466         return result;
  2467   2467       }
  2468   2468   
  2469   2469       ISqlFragment VisitSetOpExpression(DbExpression left, DbExpression right, string separator)
  2470   2470       {
  2471   2471   
  2472   2472         SqlSelectStatement leftSelectStatement = VisitExpressionEnsureSqlStatement(left);
         2473  +      bool leftOrderByLimitOrOffset = leftSelectStatement.HaveOrderByLimitOrOffset();
  2473   2474         SqlSelectStatement rightSelectStatement = VisitExpressionEnsureSqlStatement(right);
         2475  +      bool rightOrderByLimitOrOffset = rightSelectStatement.HaveOrderByLimitOrOffset();
  2474   2476   
  2475   2477         SqlBuilder setStatement = new SqlBuilder();
         2478  +
         2479  +      if (leftOrderByLimitOrOffset)
         2480  +          setStatement.Append("SELECT * FROM (");
         2481  +
  2476   2482         setStatement.Append(leftSelectStatement);
         2483  +
         2484  +      if (leftOrderByLimitOrOffset)
         2485  +          setStatement.Append(") ");
         2486  +
  2477   2487         setStatement.AppendLine();
  2478   2488         setStatement.Append(separator); // e.g. UNION ALL
  2479   2489         setStatement.AppendLine();
         2490  +
         2491  +      if (rightOrderByLimitOrOffset)
         2492  +          setStatement.Append("SELECT * FROM (");
         2493  +
  2480   2494         setStatement.Append(rightSelectStatement);
         2495  +
         2496  +      if (rightOrderByLimitOrOffset)
         2497  +          setStatement.Append(") ");
  2481   2498   
  2482   2499         return setStatement;
  2483   2500       }
  2484   2501   
  2485   2502   
  2486   2503       #endregion
  2487   2504   

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

   191    191       // if not Order By should be omitted unless there is a corresponding TOP
   192    192       private bool isTopMost;
   193    193       internal bool IsTopMost
   194    194       {
   195    195         get { return this.isTopMost; }
   196    196         set { this.isTopMost = value; }
   197    197       }
          198  +
          199  +    /// <summary>
          200  +    /// Checks if the statement has an ORDER BY, LIMIT, or OFFSET clause.
          201  +    /// </summary>
          202  +    /// <returns>
          203  +    /// Non-zero if there is an ORDER BY, LIMIT, or OFFSET clause;
          204  +    /// otherwise, zero.
          205  +    /// </returns>
          206  +    public bool HaveOrderByLimitOrOffset()
          207  +    {
          208  +        if ((this.orderBy != null) && !this.orderBy.IsEmpty)
          209  +            return true;
          210  +
          211  +        if (this.top != null)
          212  +            return true;
          213  +
          214  +        if (this.skip != null)
          215  +            return true;
          216  +
          217  +        return false;
          218  +    }
   198    219   
   199    220       #region ISqlFragment Members
   200    221   
   201    222       /// <summary>
   202    223       /// Write out a SQL select statement as a string.
   203    224       /// We have to
   204    225       /// <list type="number">

Added Tests/tkt-0a32885109.eagle.

            1  +###############################################################################
            2  +#
            3  +# tkt-0a32885109.eagle --
            4  +#
            5  +# Written by Joe Mistachkin.
            6  +# Released to the public domain, use at your own risk!
            7  +#
            8  +###############################################################################
            9  +
           10  +package require Eagle
           11  +package require Eagle.Library
           12  +package require Eagle.Test
           13  +
           14  +runTestPrologue
           15  +
           16  +###############################################################################
           17  +
           18  +package require System.Data.SQLite.Test
           19  +runSQLiteTestPrologue
           20  +runSQLiteTestFilesPrologue
           21  +
           22  +###############################################################################
           23  +
           24  +runTest {test tkt-0a32885109-1.1 {LINQ compound-operator handling} -body {
           25  +  #
           26  +  # NOTE: Re-copy the reference database file used for this unit test to the
           27  +  #       build directory in case it has been changed by a previous test run.
           28  +  #
           29  +  file copy -force $northwindEfDbFile \
           30  +      [file join [getBuildDirectory] [file tail $northwindEfDbFile]]
           31  +
           32  +  set result [list]
           33  +  set output ""
           34  +
           35  +  set code [catch {
           36  +    testClrExec $testLinqExeFile [list -eventflags Wait -directory \
           37  +        [file dirname $testLinqExeFile] -nocarriagereturns -stdout output \
           38  +        -success 0] -unionall
           39  +  } error]
           40  +
           41  +  tlog "---- BEGIN STDOUT OUTPUT\n"
           42  +  tlog $output
           43  +  tlog "\n---- END STDOUT OUTPUT\n"
           44  +
           45  +  lappend result $code
           46  +
           47  +  if {$code == 0} then {
           48  +    lappend result [string trim $output]
           49  +  } else {
           50  +    lappend result [string trim $error]
           51  +  }
           52  +
           53  +  set result
           54  +} -cleanup {
           55  +  unset -nocomplain code output error result
           56  +} -constraints {eagle monoToDo SQLite file_System.Data.SQLite.dll testExec\
           57  +file_System.Data.SQLite.Linq.dll file_testlinq.exe file_northwindEF.db} \
           58  +-result {0 {WHITC ANATR BERGS WHITC ANATR WHITC ANATR BERGS}}}
           59  +
           60  +###############################################################################
           61  +
           62  +runSQLiteTestFilesEpilogue
           63  +runSQLiteTestEpilogue
           64  +runTestEpilogue

Changes to readme.htm.

   212    212       <b>1.0.95.0 - November XX, 2014 <font color="red">(release scheduled)</font></b>
   213    213   </p>
   214    214   <ul>
   215    215       <li>Updated to <a href="http://www.sqlite.org/releaselog/3_8_7.html">SQLite 3.8.7</a>.</li>
   216    216       <li>Make sure SQL statements generated for DbUpdateCommandTree objects are properly delimited.</li>
   217    217       <li>Various minor performance enhancements to the SQLiteDataReader class. Pursuant to [e122d26e70].</li>
   218    218       <li>Defer disposing of connections created by the static SQLiteCommand.Execute method when a data reader is returned. Fix for [daeaf3150a].</li>
          219  +    <li>Wrap SELECT statements in parenthesis if they have an ORDER BY, LIMIT, or OFFSET clause and a compound operator is involved. Fix for [0a32885109].</li>
   219    220       <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>
   220    221       <li>Handle Julian Day values that fall outside of the supported range for OLE Automation dates. Fix for [3e783eecbe].&nbsp;<b>** Potentially Incompatible Change **</b></li>
   221    222   </ul>
   222    223   <p>
   223    224       <b>1.0.94.0 - September 9, 2014</b>
   224    225   </p>
   225    226   <ul>

Changes to testlinq/Program.cs.

    72     72   
    73     73                             if (arg != null)
    74     74                                 pageSize = int.Parse(arg.Trim());
    75     75                         }
    76     76   
    77     77                         return SkipTest(pageSize);
    78     78                     }
           79  +              case "unionall":
           80  +                  {
           81  +                      return UnionAllTest();
           82  +                  }
    79     83                 case "endswith":
    80     84                     {
    81     85                         string value = null;
    82     86   
    83     87                         if (args.Length > 1)
    84     88                         {
    85     89                             value = args[1];
................................................................................
   213    217                         Console.Write(' ');
   214    218   
   215    219                     Console.Write(customers.CustomerID);
   216    220   
   217    221                     once = true;
   218    222                 }
   219    223             }
          224  +
          225  +          return 0;
          226  +      }
          227  +
          228  +      //
          229  +      // NOTE: Used to test the fix for ticket [0a32885109].
          230  +      //
          231  +      private static int UnionAllTest()
          232  +      {
          233  +          using (northwindEFEntities db = new northwindEFEntities())
          234  +          {
          235  +              bool once = false;
          236  +
          237  +              var customers1 = db.Customers.Where(
          238  +                  f => f.Orders.Any()).OrderByDescending(
          239  +                    f => f.CompanyName).Skip(1).Take(1);
          240  +
          241  +              var customers2 = db.Customers.Where(
          242  +                  f => f.Orders.Any()).OrderBy(
          243  +                    f => f.CompanyName).Skip(1).Take(1);
          244  +
          245  +              var customers3 = db.Customers.Where(
          246  +                  f => f.CustomerID.StartsWith("B")).OrderBy(
          247  +                    f => f.CompanyName).Skip(1).Take(1);
          248  +
          249  +              foreach (var customer in customers1)
          250  +              {
          251  +                  if (once)
          252  +                      Console.Write(' ');
          253  +
          254  +                  Console.Write(customer.CustomerID);
          255  +                  once = true;
          256  +              }
          257  +
          258  +              foreach (var customer in customers2)
          259  +              {
          260  +                  if (once)
          261  +                      Console.Write(' ');
          262  +
          263  +                  Console.Write(customer.CustomerID);
          264  +                  once = true;
          265  +              }
          266  +
          267  +              foreach (var customer in customers3)
          268  +              {
          269  +                  if (once)
          270  +                      Console.Write(' ');
          271  +
          272  +                  Console.Write(customer.CustomerID);
          273  +                  once = true;
          274  +              }
          275  +
          276  +              foreach (var customer in customers1.Concat(customers2))
          277  +              {
          278  +                  if (once)
          279  +                      Console.Write(' ');
          280  +
          281  +                  Console.Write(customer.CustomerID);
          282  +                  once = true;
          283  +              }
          284  +
          285  +              foreach (var customer in
          286  +                    customers1.Concat(customers2).Concat(customers3))
          287  +              {
          288  +                  if (once)
          289  +                      Console.Write(' ');
          290  +
          291  +                  Console.Write(customer.CustomerID);
          292  +                  once = true;
          293  +              }
          294  +          }
   220    295   
   221    296             return 0;
   222    297         }
   223    298   
   224    299         //
   225    300         // NOTE: Used to test the fix for ticket [ccfa69fc32].
   226    301         //

Changes to www/news.wiki.

     6      6       <b>1.0.95.0 - November XX, 2014 <font color="red">(release scheduled)</font></b>
     7      7   </p>
     8      8   <ul>
     9      9       <li>Updated to [http://www.sqlite.org/releaselog/3_8_7_1.html|SQLite 3.8.7.1].</li>
    10     10       <li>Make sure SQL statements generated for DbUpdateCommandTree objects are properly delimited.</li>
    11     11       <li>Various minor performance enhancements to the SQLiteDataReader class. Pursuant to [e122d26e70].</li>
    12     12       <li>Defer disposing of connections created by the static SQLiteCommand.Execute method when a data reader is returned. Fix for [daeaf3150a].</li>
           13  +    <li>Wrap SELECT statements in parenthesis if they have an ORDER BY, LIMIT, or OFFSET clause and a compound operator is involved. Fix for [0a32885109].</li>
    13     14       <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>
    14     15       <li>Handle Julian Day values that fall outside of the supported range for OLE Automation dates. Fix for [3e783eecbe].&nbsp;<b>** Potentially Incompatible Change **</b></li>
    15     16   </ul>
    16     17   <p>
    17     18       <b>1.0.94.0 - September 9, 2014</b>
    18     19   </p>
    19     20   <ul>