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

Overview
Comment:Remove two MySQL-isms: Strings in double-quotes and identifiers quoted by grave accents.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: eec75c074c47d36422fa9467e995dc07ddc3e54f
User & Date: drh 2013-02-11 15:17:58.970
Context
2013-02-11
15:51
Fix a memory leak in the test harness. check-in: 108d62cab9 user: drh tags: trunk
15:17
Remove two MySQL-isms: Strings in double-quotes and identifiers quoted by grave accents. check-in: eec75c074c user: drh tags: trunk
2013-02-09
16:55
Have worker clients and writers that discard an old in-memory tree update a read-lock slot before concluding their work or write transaction. This is required for read-only clients - which cannot set the value of their own read-lock slot. check-in: 798d9e23be user: dan tags: trunk
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/expr.c.
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
** for this node and for the pToken argument is a single allocation
** obtained from sqlite4DbMalloc().  The calling function
** is responsible for making sure the node eventually gets freed.
**
** If dequote is true, then the token (if it exists) is dequoted.
** If dequote is false, no dequoting is performance.  The deQuote
** parameter is ignored if pToken is NULL or if the token does not
** appear to be quoted.  If the quotes were of the form "..." (double-quotes)
** then the EP_DblQuoted flag is set on the expression node.
**
** Special case:  If op==TK_INTEGER and pToken points to a string that
** can be translated into a 32-bit integer, then the token is not
** stored in u.zToken.  Instead, the integer values is written
** into u.iValue and the EP_IntValue flag is set.  No extra storage
** is allocated to hold the integer text and the dequote flag is ignored.
*/







|
<







362
363
364
365
366
367
368
369

370
371
372
373
374
375
376
** for this node and for the pToken argument is a single allocation
** obtained from sqlite4DbMalloc().  The calling function
** is responsible for making sure the node eventually gets freed.
**
** If dequote is true, then the token (if it exists) is dequoted.
** If dequote is false, no dequoting is performance.  The deQuote
** parameter is ignored if pToken is NULL or if the token does not
** appear to be quoted.

**
** Special case:  If op==TK_INTEGER and pToken points to a string that
** can be translated into a 32-bit integer, then the token is not
** stored in u.zToken.  Instead, the integer values is written
** into u.iValue and the EP_IntValue flag is set.  No extra storage
** is allocated to hold the integer text and the dequote flag is ignored.
*/
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
      }else{
        int c;
        pNew->u.zToken = (char*)&pNew[1];
        assert( pToken->z!=0 || pToken->n==0 );
        if( pToken->n ) memcpy(pNew->u.zToken, pToken->z, pToken->n);
        pNew->u.zToken[pToken->n] = 0;
        if( dequote && nExtra>=3 
             && ((c = pToken->z[0])=='\'' || c=='"' || c=='[' || c=='`') ){
          sqlite4Dequote(pNew->u.zToken);
          if( c=='"' ) pNew->flags |= EP_DblQuoted;
        }
      }
    }
#if SQLITE4_MAX_EXPR_DEPTH>0
    pNew->nHeight = 1;
#endif  
  }







|

<







402
403
404
405
406
407
408
409
410

411
412
413
414
415
416
417
      }else{
        int c;
        pNew->u.zToken = (char*)&pNew[1];
        assert( pToken->z!=0 || pToken->n==0 );
        if( pToken->n ) memcpy(pNew->u.zToken, pToken->z, pToken->n);
        pNew->u.zToken[pToken->n] = 0;
        if( dequote && nExtra>=3 
             && ((c = pToken->z[0])=='\'' || c=='"' || c=='[') ){
          sqlite4Dequote(pNew->u.zToken);

        }
      }
    }
#if SQLITE4_MAX_EXPR_DEPTH>0
    pNew->nHeight = 1;
#endif  
  }
Changes to src/resolve.c.
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
    ** we have a match (cnt>0) or when we run out of name contexts.
    */
    if( cnt==0 ){
      pNC = pNC->pNext;
    }
  }

  /*
  ** If X and Y are NULL (in other words if only the column name Z is
  ** supplied) and the value of Z is enclosed in double-quotes, then
  ** Z is a string literal if it doesn't match any column names.  In that
  ** case, we need to return right away and not make any changes to
  ** pExpr.
  **
  ** Because no reference was made to outer contexts, the pNC->nRef
  ** fields are not changed in any context.
  */
  if( cnt==0 && zTab==0 && ExprHasProperty(pExpr,EP_DblQuoted) ){
    pExpr->op = TK_STRING;
    pExpr->pTab = 0;
    return WRC_Prune;
  }

  /*
  ** cnt==0 means there was not match.  cnt>1 means there were two or
  ** more matches.  Either way, we have an error.
  */
  if( cnt!=1 ){
    const char *zErr;
    zErr = cnt==0 ? "no such column" : "ambiguous column name";







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







338
339
340
341
342
343
344
















345
346
347
348
349
350
351
    ** we have a match (cnt>0) or when we run out of name contexts.
    */
    if( cnt==0 ){
      pNC = pNC->pNext;
    }
  }

















  /*
  ** cnt==0 means there was not match.  cnt>1 means there were two or
  ** more matches.  Either way, we have an error.
  */
  if( cnt!=1 ){
    const char *zErr;
    zErr = cnt==0 ? "no such column" : "ambiguous column name";
Changes to src/sqliteInt.h.
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
*/
#define EP_FromJoin   0x0001  /* Originated in ON or USING clause of a join */
#define EP_Agg        0x0002  /* Contains one or more aggregate functions */
#define EP_Resolved   0x0004  /* IDs have been resolved to COLUMNs */
#define EP_Error      0x0008  /* Expression contains one or more errors */
#define EP_Distinct   0x0010  /* Aggregate function with DISTINCT keyword */
#define EP_VarSelect  0x0020  /* pSelect is correlated, not constant */
#define EP_DblQuoted  0x0040  /* token.z was originally in "..." */
#define EP_InfixFunc  0x0080  /* True for an infix function: LIKE, GLOB, etc */
#define EP_ExpCollate 0x0100  /* Collating sequence specified explicitly */
#define EP_FixedDest  0x0200  /* Result needed in a specific register */
#define EP_IntValue   0x0400  /* Integer value contained in u.iValue */
#define EP_xIsSelect  0x0800  /* x.pSelect is valid (otherwise x.pList is) */
#define EP_Hint       0x1000  /* Optimizer hint. Not required for correctness */
#define EP_Reduced    0x2000  /* Expr struct is EXPR_REDUCEDSIZE bytes only */







<







1652
1653
1654
1655
1656
1657
1658

1659
1660
1661
1662
1663
1664
1665
*/
#define EP_FromJoin   0x0001  /* Originated in ON or USING clause of a join */
#define EP_Agg        0x0002  /* Contains one or more aggregate functions */
#define EP_Resolved   0x0004  /* IDs have been resolved to COLUMNs */
#define EP_Error      0x0008  /* Expression contains one or more errors */
#define EP_Distinct   0x0010  /* Aggregate function with DISTINCT keyword */
#define EP_VarSelect  0x0020  /* pSelect is correlated, not constant */

#define EP_InfixFunc  0x0080  /* True for an infix function: LIKE, GLOB, etc */
#define EP_ExpCollate 0x0100  /* Collating sequence specified explicitly */
#define EP_FixedDest  0x0200  /* Result needed in a specific register */
#define EP_IntValue   0x0400  /* Integer value contained in u.iValue */
#define EP_xIsSelect  0x0800  /* x.pSelect is valid (otherwise x.pList is) */
#define EP_Hint       0x1000  /* Optimizer hint. Not required for correctness */
#define EP_Reduced    0x2000  /* Expr struct is EXPR_REDUCEDSIZE bytes only */
Changes to src/tokenize.c.
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
      *tokenType = TK_BITAND;
      return 1;
    }
    case '~': {
      *tokenType = TK_BITNOT;
      return 1;
    }
    case '`':
    case '\'':
    case '"': {
      int delim = z[0];
      testcase( delim=='`' );
      testcase( delim=='\'' );
      testcase( delim=='"' );
      for(i=1; (c=z[i])!=0; i++){







<







223
224
225
226
227
228
229

230
231
232
233
234
235
236
      *tokenType = TK_BITAND;
      return 1;
    }
    case '~': {
      *tokenType = TK_BITNOT;
      return 1;
    }

    case '\'':
    case '"': {
      int delim = z[0];
      testcase( delim=='`' );
      testcase( delim=='\'' );
      testcase( delim=='"' );
      for(i=1; (c=z[i])!=0; i++){
Changes to test/ctime.test.
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
    SELECT sqlite_compileoption_used('SQLITE4_THREADSAFE');
  }
} {0 1}
do_test ctime-1.4.2 {
  catchsql {
    SELECT sqlite_compileoption_used('THREADSAFE');
  }
} {0 1}
do_test ctime-1.4.3 {
  catchsql {
    SELECT sqlite_compileoption_used("THREADSAFE");
  }
} {0 1}

do_test ctime-1.5 {
  set ans1 [ catchsql {
    SELECT sqlite_compileoption_used('THREADSAFE=0');
  } ]
  set ans2 [ catchsql {







<
<
<
<
<







68
69
70
71
72
73
74





75
76
77
78
79
80
81
    SELECT sqlite_compileoption_used('SQLITE4_THREADSAFE');
  }
} {0 1}
do_test ctime-1.4.2 {
  catchsql {
    SELECT sqlite_compileoption_used('THREADSAFE');
  }





} {0 1}

do_test ctime-1.5 {
  set ans1 [ catchsql {
    SELECT sqlite_compileoption_used('THREADSAFE=0');
  } ]
  set ans2 [ catchsql {
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
  }
} {1 {wrong number of arguments to function sqlite_compileoption_used()}}
do_test ctime-2.1.2 {
  catchsql {
    SELECT sqlite_compileoption_used(NULL);
  }
} {0 {{}}}
do_test ctime-2.1.3 {
  catchsql {
    SELECT sqlite_compileoption_used("");
  }
} {0 0}
do_test ctime-2.1.4 {
  catchsql {
    SELECT sqlite_compileoption_used('');
  }
} {0 0}
do_test ctime-2.1.5 {
  catchsql {







<
<
<
<
<







113
114
115
116
117
118
119





120
121
122
123
124
125
126
  }
} {1 {wrong number of arguments to function sqlite_compileoption_used()}}
do_test ctime-2.1.2 {
  catchsql {
    SELECT sqlite_compileoption_used(NULL);
  }
} {0 {{}}}





do_test ctime-2.1.4 {
  catchsql {
    SELECT sqlite_compileoption_used('');
  }
} {0 0}
do_test ctime-2.1.5 {
  catchsql {
Changes to test/in.test.
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
  }
} {1 2 3 4 6 8 10}

do_test in-8.1 {
  execsql {
    SELECT b FROM t1 WHERE a IN ('hello','there')
  }
} {world}
do_test in-8.2 {
  execsql {
    SELECT b FROM t1 WHERE a IN ("hello",'there')
  }
} {world}

# Test constructs of the form:  expr IN tablename
#
do_test in-9.1 {
  execsql {
    CREATE TABLE t4 AS SELECT a FROM tb;







<
<
<
<
<







269
270
271
272
273
274
275





276
277
278
279
280
281
282
  }
} {1 2 3 4 6 8 10}

do_test in-8.1 {
  execsql {
    SELECT b FROM t1 WHERE a IN ('hello','there')
  }





} {world}

# Test constructs of the form:  expr IN tablename
#
do_test in-9.1 {
  execsql {
    CREATE TABLE t4 AS SELECT a FROM tb;
Changes to test/join.test.
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
} {1 a xxx 2 b xxx 3 c {}}

# A test for ticket #247.
#
do_test join-7.1 {
  execsql {
    CREATE TABLE t7 (x, y);
    INSERT INTO t7 VALUES ("pa1", 1);
    INSERT INTO t7 VALUES ("pa2", NULL);
    INSERT INTO t7 VALUES ("pa3", NULL);
    INSERT INTO t7 VALUES ("pa4", 2);
    INSERT INTO t7 VALUES ("pa30", 131);
    INSERT INTO t7 VALUES ("pa31", 130);
    INSERT INTO t7 VALUES ("pa28", NULL);

    CREATE TABLE t8 (a integer primary key, b);
    INSERT INTO t8 VALUES (1, "pa1");
    INSERT INTO t8 VALUES (2, "pa4");
    INSERT INTO t8 VALUES (3, NULL);
    INSERT INTO t8 VALUES (4, NULL);
    INSERT INTO t8 VALUES (130, "pa31");
    INSERT INTO t8 VALUES (131, "pa30");

    SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a;
  }
} {1 999 999 2 131 130 999}

# Make sure a left join where the right table is really a view that
# is itself a join works right.  Ticket #306.







|
|
|
|
|
|
|


|
|


|
|







427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
} {1 a xxx 2 b xxx 3 c {}}

# A test for ticket #247.
#
do_test join-7.1 {
  execsql {
    CREATE TABLE t7 (x, y);
    INSERT INTO t7 VALUES ('pa1', 1);
    INSERT INTO t7 VALUES ('pa2', NULL);
    INSERT INTO t7 VALUES ('pa3', NULL);
    INSERT INTO t7 VALUES ('pa4', 2);
    INSERT INTO t7 VALUES ('pa30', 131);
    INSERT INTO t7 VALUES ('pa31', 130);
    INSERT INTO t7 VALUES ('pa28', NULL);

    CREATE TABLE t8 (a integer primary key, b);
    INSERT INTO t8 VALUES (1, 'pa1');
    INSERT INTO t8 VALUES (2, 'pa4');
    INSERT INTO t8 VALUES (3, NULL);
    INSERT INTO t8 VALUES (4, NULL);
    INSERT INTO t8 VALUES (130, 'pa31');
    INSERT INTO t8 VALUES (131, 'pa30');

    SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a;
  }
} {1 999 999 2 131 130 999}

# Make sure a left join where the right table is really a view that
# is itself a join works right.  Ticket #306.
Changes to test/quote.test.
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
65
} {0 {}}
do_test quote-1.2.1 {
  catchsql {SELECT * FROM '@abc'}
} {0 {5 hello}}
do_test quote-1.2.2 {
  catchsql {SELECT * FROM [@abc]}  ;# SqlServer compatibility
} {0 {5 hello}}
do_test quote-1.2.3 {
  catchsql {SELECT * FROM `@abc`}  ;# MySQL compatibility
} {0 {5 hello}}
do_test quote-1.3 {
  catchsql {
    SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc'
  }
} {0 {hello 10}}
do_test quote-1.3.1 {
  catchsql {
    SELECT '!pqr', '#xyz'+5 FROM '@abc'
  }
} {0 {!pqr 5}}
do_test quote-1.3.2 {
  catchsql {
    SELECT "!pqr", "#xyz"+5 FROM '@abc'
  }
} {0 {hello 10}}
do_test quote-1.3.3 {
  catchsql {
    SELECT [!pqr], `#xyz`+5 FROM '@abc'
  }
} {0 {hello 10}}
do_test quote-1.3.4 {
  set r [catch {
    execsql {SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc'}
  } msg ]
  lappend r $msg
} {0 {hello 10}}







<
<
<














<
<
<
<
<







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
} {0 {}}
do_test quote-1.2.1 {
  catchsql {SELECT * FROM '@abc'}
} {0 {5 hello}}
do_test quote-1.2.2 {
  catchsql {SELECT * FROM [@abc]}  ;# SqlServer compatibility
} {0 {5 hello}}



do_test quote-1.3 {
  catchsql {
    SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc'
  }
} {0 {hello 10}}
do_test quote-1.3.1 {
  catchsql {
    SELECT '!pqr', '#xyz'+5 FROM '@abc'
  }
} {0 {!pqr 5}}
do_test quote-1.3.2 {
  catchsql {
    SELECT "!pqr", "#xyz"+5 FROM '@abc'
  }





} {0 {hello 10}}
do_test quote-1.3.4 {
  set r [catch {
    execsql {SELECT '@abc'.'!pqr', '@abc'.'#xyz'+5 FROM '@abc'}
  } msg ]
  lappend r $msg
} {0 {hello 10}}
Changes to test/select6.test.
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
  execsql2 {
    SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE x=3));
  }
} {x 3 y 2}
do_test select6-3.2 {
  execsql {
    SELECT * FROM
      (SELECT a.q, a.p, b.r
       FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
            (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
       WHERE a.q=b.s ORDER BY a.q)
    ORDER BY "a.q"
  }
} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
do_test select6-3.3 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
  }
} {10.5 3.7 14.2}







|



|







161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
  execsql2 {
    SELECT * FROM (SELECT * FROM (SELECT * FROM t1 WHERE x=3));
  }
} {x 3 y 2}
do_test select6-3.2 {
  execsql {
    SELECT * FROM
      (SELECT a.q AS x, a.p, b.r
       FROM (SELECT count(*) as p , b as q FROM t2 GROUP BY q) AS a,
            (SELECT max(a) as r, b as s FROM t2 GROUP BY s) as b
       WHERE a.q=b.s ORDER BY a.q)
    ORDER BY x
  }
} {1 1 1 2 2 3 3 4 7 4 8 15 5 5 20}
do_test select6-3.3 {
  execsql {
    SELECT a,b,a+b FROM (SELECT avg(x) as 'a', avg(y) as 'b' FROM t1)
  }
} {10.5 3.7 14.2}
Changes to test/subquery.test.
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
    INSERT INTO t4 VALUES('four',4);
    CREATE TABLE t5(a,b);
    INSERT INTO t5 VALUES(1,11);
    INSERT INTO t5 VALUES(2,22);
    INSERT INTO t5 VALUES(3,33);
    INSERT INTO t5 VALUES(4,44);
    SELECT b FROM t5 WHERE a IN 
       (SELECT callcnt(y)+0 FROM t4 WHERE x="two")
  }
} {22}
do_test subquery-5.2 {
  # This is the key test.  The subquery should have only run once.  If
  # The double-quoted identifier "two" were causing the subquery to be
  # processed as a correlated subquery, then it would have run 4 times.
  set callcnt







|







385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
    INSERT INTO t4 VALUES('four',4);
    CREATE TABLE t5(a,b);
    INSERT INTO t5 VALUES(1,11);
    INSERT INTO t5 VALUES(2,22);
    INSERT INTO t5 VALUES(3,33);
    INSERT INTO t5 VALUES(4,44);
    SELECT b FROM t5 WHERE a IN 
       (SELECT callcnt(y)+0 FROM t4 WHERE x='two')
  }
} {22}
do_test subquery-5.2 {
  # This is the key test.  The subquery should have only run once.  If
  # The double-quoted identifier "two" were causing the subquery to be
  # processed as a correlated subquery, then it would have run 4 times.
  set callcnt
Changes to test/tkt3442.test.
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
# These tests perform an EXPLAIN QUERY PLAN on both versions of the 
# SELECT referenced in ticket #3442 (both '5000' and "5000") 
# and verify that the query plan is the same.
#
ifcapable explain {
  do_test tkt3442-1.2 {
    EQP { SELECT node FROM listhash WHERE id='5000' LIMIT 1; }
  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}}
  do_test tkt3442-1.3 {
    EQP { SELECT node FROM listhash WHERE id="5000" LIMIT 1; }
  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}}
}


# Some extra tests testing other permutations of 5000.
#
ifcapable explain {







<
<
<







45
46
47
48
49
50
51



52
53
54
55
56
57
58
# These tests perform an EXPLAIN QUERY PLAN on both versions of the 
# SELECT referenced in ticket #3442 (both '5000' and "5000") 
# and verify that the query plan is the same.
#
ifcapable explain {
  do_test tkt3442-1.2 {
    EQP { SELECT node FROM listhash WHERE id='5000' LIMIT 1; }



  } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}}
}


# Some extra tests testing other permutations of 5000.
#
ifcapable explain {
Changes to test/tkt3841.test.
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
}

do_test tkt3841.1 {
  execsql {
    CREATE TABLE table2 (key TEXT, x TEXT);
    CREATE TABLE list (key TEXT, value TEXT);
  
    INSERT INTO table2 VALUES ("a", "alist");
    INSERT INTO table2 VALUES ("b", "blist");
    INSERT INTO list VALUES ("a", 1);
    INSERT INTO list VALUES ("a", 2);
    INSERT INTO list VALUES ("a", 3);
    INSERT INTO list VALUES ("b", 4);
    INSERT INTO list VALUES ("b", 5);
    INSERT INTO list VALUES ("b", 6);

    SELECT
      table2.x,
      (SELECT group_concat(list.value)
        FROM list
        WHERE list.key = table2.key)
    FROM table2;







|
|
|
|
|
|
|
|







23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
}

do_test tkt3841.1 {
  execsql {
    CREATE TABLE table2 (key TEXT, x TEXT);
    CREATE TABLE list (key TEXT, value TEXT);
  
    INSERT INTO table2 VALUES ('a', 'alist');
    INSERT INTO table2 VALUES ('b', 'blist');
    INSERT INTO list VALUES ('a', 1);
    INSERT INTO list VALUES ('a', 2);
    INSERT INTO list VALUES ('a', 3);
    INSERT INTO list VALUES ('b', 4);
    INSERT INTO list VALUES ('b', 5);
    INSERT INTO list VALUES ('b', 6);

    SELECT
      table2.x,
      (SELECT group_concat(list.value)
        FROM list
        WHERE list.key = table2.key)
    FROM table2;