SQLite

Check-in [50add839fd]
Login

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

Overview
Comment:Add a test case demonstrating the collation problem with constant propagation.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | propagate-const-opt
Files: files | file ages | folders
SHA3-256: 50add839fd95665bd67a6ae5de8346fd09e83904bbcbad26fad280dff86d9e93
User & Date: drh 2018-07-26 23:54:19.662
Context
2018-07-27
16:57
Constant propagation is now restricted to just the WHERE clause. The mechanism is changed to take affinity and collation into account. This seems to give correct answers. But the search for constant propagation costs 4 million cycles in the speed test. (check-in: 82c67efb72 user: drh tags: propagate-const-opt)
2018-07-26
23:54
Add a test case demonstrating the collation problem with constant propagation. (check-in: 50add839fd user: drh tags: propagate-const-opt)
23:47
Generalize the constant propagation optimization so that it applies on every WHERE close, not just those that contain a subquery. This then demonstrates that the current implementation is inadequate since it does not take into account collating sequences. (check-in: 57eb2abd5b user: drh tags: propagate-const-opt)
Changes
Unified Diff Ignore Whitespace Patch
Changes to test/whereL.test.
︙︙
46
47
48
49
50
51
52
















53
54

  ORDER BY t1.a;
} {
  QUERY PLAN
  |--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (a=?)
  |--SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (a=?)
  `--SCAN TABLE t3
}

















finish_test








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



46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

  ORDER BY t1.a;
} {
  QUERY PLAN
  |--SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (a=?)
  |--SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (a=?)
  `--SCAN TABLE t3
}

# Constant propagation in the face of collating sequences:
#
do_execsql_test 200 {
  CREATE TABLE c3(x COLLATE binary, y COLLATE nocase, z COLLATE binary);
  CREATE INDEX c3x ON c3(x);
  INSERT INTO c3 VALUES('ABC', 'ABC', 'abc');
  SELECT * FROM c3 WHERE x=y AND y=z AND z='abc';
} {ABC ABC abc}

# If the constants are blindly propagated, as shown in the following
# query, the wrong answer results:
#
do_execsql_test 201 {
  SELECT * FROM c3 WHERE x='abc' AND y='abc' AND z='abc';
} {}

finish_test