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 Unified Diffs Ignore Whitespace Patch

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

45
46
47
48
49
50
51

52
53
54
55
56
57
58
    <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>
      <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>
    </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>







>







45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
    <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>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>
      <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>
      <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>
    </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>

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

2466
2467
2468
2469
2470
2471
2472

2473

2474
2475




2476




2477
2478
2479




2480



2481
2482
2483
2484
2485
2486
2487
      return result;
    }

    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);




      return setStatement;
    }


    #endregion








>

>


>
>
>
>

>
>
>
>



>
>
>
>

>
>
>







2466
2467
2468
2469
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
      return result;
    }

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

      SqlSelectStatement leftSelectStatement = VisitExpressionEnsureSqlStatement(left);
      bool leftOrderByLimitOrOffset = leftSelectStatement.HaveOrderByLimitOrOffset();
      SqlSelectStatement rightSelectStatement = VisitExpressionEnsureSqlStatement(right);
      bool rightOrderByLimitOrOffset = rightSelectStatement.HaveOrderByLimitOrOffset();

      SqlBuilder setStatement = new SqlBuilder();

      if (leftOrderByLimitOrOffset)
          setStatement.Append("SELECT * FROM (");

      setStatement.Append(leftSelectStatement);

      if (leftOrderByLimitOrOffset)
          setStatement.Append(") ");

      setStatement.AppendLine();
      setStatement.Append(separator); // e.g. UNION ALL
      setStatement.AppendLine();

      if (rightOrderByLimitOrOffset)
          setStatement.Append("SELECT * FROM (");

      setStatement.Append(rightSelectStatement);

      if (rightOrderByLimitOrOffset)
          setStatement.Append(") ");

      return setStatement;
    }


    #endregion

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

191
192
193
194
195
196
197





















198
199
200
201
202
203
204
    // 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">







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







191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
    // 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>
    /// Checks if the statement has an ORDER BY, LIMIT, or OFFSET clause.
    /// </summary>
    /// <returns>
    /// Non-zero if there is an ORDER BY, LIMIT, or OFFSET clause;
    /// otherwise, zero.
    /// </returns>
    public bool HaveOrderByLimitOrOffset()
    {
        if ((this.orderBy != null) && !this.orderBy.IsEmpty)
            return true;

        if (this.top != null)
            return true;

        if (this.skip != null)
            return true;

        return false;
    }

    #region ISqlFragment Members

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

Added Tests/tkt-0a32885109.eagle.

































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
###############################################################################
#
# 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 WHITC ANATR WHITC ANATR BERGS}}}

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

runSQLiteTestFilesEpilogue
runSQLiteTestEpilogue
runTestEpilogue

Changes to readme.htm.

212
213
214
215
216
217
218

219
220
221
222
223
224
225
    <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>
    <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>
</ul>
<p>
    <b>1.0.94.0 - September 9, 2014</b>
</p>
<ul>







>







212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
    <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>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>
    <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>
    <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>
</ul>
<p>
    <b>1.0.94.0 - September 9, 2014</b>
</p>
<ul>

Changes to testlinq/Program.cs.

72
73
74
75
76
77
78




79
80
81
82
83
84
85
...
213
214
215
216
217
218
219







































































220
221
222
223
224
225
226

                          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].
      //







>
>
>
>







 







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







72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
...
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301

                          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.

6
7
8
9
10
11
12

13
14
15
16
17
18
19
    <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>
    <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>
</ul>
<p>
    <b>1.0.94.0 - September 9, 2014</b>
</p>
<ul>







>







6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
    <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>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>
    <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>
    <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>
</ul>
<p>
    <b>1.0.94.0 - September 9, 2014</b>
</p>
<ul>