System.Data.SQLite

Check-in [1f05aa9dd6]
Login

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
Timelines: family | ancestors | descendants | both | tkt-0a32885109
Files: files | file ages | folders
SHA1: 1f05aa9dd6d07a33d92e28b1cd4b995aade11734
User & Date: mistachkin 2014-11-08 06:41:20.258
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
Unified Diff 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>
    </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>







>







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>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.
2468
2469
2470
2471
2472
2473
2474







2475
2476
2477
2478
2479
2480
2481

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








>
>
>
>
>
>
>







2468
2469
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488

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
































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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 ALFKI BSBEV}}}

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

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







>







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>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.
72
73
74
75
76
77
78




79
80
81
82
83
84
85

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

                      return SkipTest(pageSize);
                  }




              case "endswith":
                  {
                      string value = null;

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







>
>
>
>







72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89

                          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];
213
214
215
216
217
218
219







































































220
221
222
223
224
225
226
                      Console.Write(' ');

                  Console.Write(customers.CustomerID);

                  once = true;
              }
          }








































































          return 0;
      }

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







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







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
                      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.
1
2
3
4
5
6
7
8
9
10
11
12

13
14
15
16
17
18
19
<title>News</title>

<b>Version History</b>

<p>
    <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>












>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<title>News</title>

<b>Version History</b>

<p>
    <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>