SQLite

Check-in [bee73d429c]
Login

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

Overview
Comment:Change the display of the P4 operand of CursorHint in EXPLAIN output to function notation.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | cursor-hints
Files: files | file ages | folders
SHA1: bee73d429cb0e99b43fb191ac15e298d0353b135
User & Date: drh 2015-08-15 00:51:23.737
Context
2015-08-17
17:19
Avoid generating hints using constraints that are also used to initialize the cursor, since presumably the cursor already knows about those constraints. (check-in: 142b048ac7 user: drh tags: cursor-hints)
2015-08-15
00:51
Change the display of the P4 operand of CursorHint in EXPLAIN output to function notation. (check-in: bee73d429c user: drh tags: cursor-hints)
2015-08-14
23:57
Always provide the BTREE_BULKLOAD hint, even when SQLITE_ENABLE_CURSOR_HINTS is not defined, as that hint gives a 4% performance increase. (check-in: 83a844357e user: drh tags: cursor-hints)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/vdbeaux.c.
︙︙
1086
1087
1088
1089
1090
1091
1092
1093

1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140






1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153

1154
1155
1156
1157
1158
1159
1160
1161
1162
1163


#if VDBE_DISPLAY_P4 && defined(SQLITE_ENABLE_CURSOR_HINTS)
/*
** Translate the P4.pExpr value for an OP_CursorHint opcode into text
** that can be displayed in the P4 column of EXPLAIN output.
*/
static int displayP4Expr(int nTemp, char *zTemp, Expr *pExpr){
  const char *zBinOp = 0;

  switch( pExpr->op ){
    case TK_STRING:
      sqlite3_snprintf(nTemp, zTemp, "%Q", pExpr->u.zToken);
      break;

    case TK_INTEGER:
      sqlite3_snprintf(nTemp, zTemp, "%d", pExpr->u.iValue);
      break;

    case TK_NULL:
      sqlite3_snprintf(nTemp, zTemp, "NULL");
      break;

    case TK_REGISTER: {
      sqlite3_snprintf(nTemp, zTemp, "r[%d]", pExpr->iTable);
      break;
    }

    case TK_COLUMN: {
      if( pExpr->iColumn<0 ){
        sqlite3_snprintf(nTemp, zTemp, "rowid");
      }else{
        sqlite3_snprintf(nTemp, zTemp, "c%d", (int)pExpr->iColumn);
      }
      break;
    }

    case TK_LT:      zBinOp = "<";        break;
    case TK_LE:      zBinOp = "<=";       break;
    case TK_GT:      zBinOp = ">";        break;
    case TK_GE:      zBinOp = ">=";       break;
    case TK_NE:      zBinOp = "!=";       break;
    case TK_EQ:      zBinOp = "==";       break;
    case TK_IS:      zBinOp = " IS ";     break;
    case TK_ISNOT:   zBinOp = " IS NOT "; break;
    case TK_AND:     zBinOp = " AND ";    break;
    case TK_OR:      zBinOp = " OR ";     break;
    case TK_PLUS:    zBinOp = "+";        break;
    case TK_STAR:    zBinOp = "*";        break;
    case TK_MINUS:   zBinOp = "-";        break;
    case TK_REM:     zBinOp = "%";        break;
    case TK_BITAND:  zBinOp = "&";        break;
    case TK_BITOR:   zBinOp = "|";        break;
    case TK_SLASH:   zBinOp = "/";        break;
    case TK_LSHIFT:  zBinOp = "<<";       break;
    case TK_RSHIFT:  zBinOp = ">>";       break;
    case TK_CONCAT:  zBinOp = "||";       break;







    default:
      sqlite3_snprintf(nTemp, zTemp, "%s", "expr");
      break;
  }

  if( zBinOp && nTemp>5 ){
    int n = 1;
    zTemp[0] = '(';
    n += displayP4Expr(nTemp-n, zTemp+n, pExpr->pLeft);
    sqlite3_snprintf(nTemp-n, zTemp+n, "%s", zBinOp);
    n += sqlite3Strlen30(zTemp+n);
    n += displayP4Expr(nTemp-n, zTemp+n, pExpr->pRight);

    sqlite3_snprintf(nTemp-n, zTemp+n, ")");
  }

  return sqlite3Strlen30(zTemp);
}
#endif /* VDBE_DISPLAY_P4 && defined(SQLITE_ENABLE_CURSOR_HINTS) */


#if VDBE_DISPLAY_P4
/*








|
>




<



<



<




<








<
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
>
>
>






|
|
|

|
|
|
>


<








1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098

1099
1100
1101

1102
1103
1104

1105
1106
1107
1108

1109
1110
1111
1112
1113
1114
1115
1116

1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158

1159
1160
1161
1162
1163
1164
1165


#if VDBE_DISPLAY_P4 && defined(SQLITE_ENABLE_CURSOR_HINTS)
/*
** Translate the P4.pExpr value for an OP_CursorHint opcode into text
** that can be displayed in the P4 column of EXPLAIN output.
*/
static int displayP4Expr(int nTemp, char *zTemp, Expr *pExpr){
  const char *zOp = 0;
  int n;
  switch( pExpr->op ){
    case TK_STRING:
      sqlite3_snprintf(nTemp, zTemp, "%Q", pExpr->u.zToken);
      break;

    case TK_INTEGER:
      sqlite3_snprintf(nTemp, zTemp, "%d", pExpr->u.iValue);
      break;

    case TK_NULL:
      sqlite3_snprintf(nTemp, zTemp, "NULL");
      break;

    case TK_REGISTER: {
      sqlite3_snprintf(nTemp, zTemp, "r[%d]", pExpr->iTable);
      break;
    }

    case TK_COLUMN: {
      if( pExpr->iColumn<0 ){
        sqlite3_snprintf(nTemp, zTemp, "rowid");
      }else{
        sqlite3_snprintf(nTemp, zTemp, "c%d", (int)pExpr->iColumn);
      }
      break;
    }

    case TK_LT:      zOp = "LT";      break;
    case TK_LE:      zOp = "LE";      break;
    case TK_GT:      zOp = "GT";      break;
    case TK_GE:      zOp = "GE";      break;
    case TK_NE:      zOp = "NE";      break;
    case TK_EQ:      zOp = "EQ";      break;
    case TK_IS:      zOp = "IS";      break;
    case TK_ISNOT:   zOp = "ISNOT";   break;
    case TK_AND:     zOp = "AND";     break;
    case TK_OR:      zOp = "OR";      break;
    case TK_PLUS:    zOp = "ADD";     break;
    case TK_STAR:    zOp = "MUL";     break;
    case TK_MINUS:   zOp = "SUB";     break;
    case TK_REM:     zOp = "REM";     break;
    case TK_BITAND:  zOp = "BITAND";  break;
    case TK_BITOR:   zOp = "BITOR";   break;
    case TK_SLASH:   zOp = "DIV";     break;
    case TK_LSHIFT:  zOp = "LSHIFT";  break;
    case TK_RSHIFT:  zOp = "RSHIFT";  break;
    case TK_CONCAT:  zOp = "CONCAT";  break;
    case TK_UMINUS:  zOp = "MINUS";   break;
    case TK_UPLUS:   zOp = "PLUS";    break;
    case TK_BITNOT:  zOp = "BITNOT";  break;
    case TK_NOT:     zOp = "NOT";     break;
    case TK_ISNULL:  zOp = "ISNULL";  break;
    case TK_NOTNULL: zOp = "NOTNULL"; break;

    default:
      sqlite3_snprintf(nTemp, zTemp, "%s", "expr");
      break;
  }

  if( zOp ){
    sqlite3_snprintf(nTemp, zTemp, "%s(", zOp);
    n = sqlite3Strlen30(zTemp);
    n += displayP4Expr(nTemp-n, zTemp+n, pExpr->pLeft);
    if( n<nTemp-1 && pExpr->pRight ){
      zTemp[n++] = ',';
      n += displayP4Expr(nTemp-n, zTemp+n, pExpr->pRight);
    }
    sqlite3_snprintf(nTemp-n, zTemp+n, ")");
  }

  return sqlite3Strlen30(zTemp);
}
#endif /* VDBE_DISPLAY_P4 && defined(SQLITE_ENABLE_CURSOR_HINTS) */


#if VDBE_DISPLAY_P4
/*
︙︙
Changes to test/cursorhint.test.
︙︙
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122

# no cursor hints occur for t1 (since it is a full table scan) but that
# each t2 access has a cursor hint based on the current t1.a value.
#
do_test 1.1 {
  p4_of_opcode db CursorHint {
     SELECT * FROM t1 CROSS JOIN t2 WHERE a=x
  }
} {/(r*==c0)/}
do_test 1.2 {
  p5_of_opcode db OpenRead . {
     SELECT * FROM t1 CROSS JOIN t2 WHERE a=x
  }
} {00 00}

# Do the same test the other way around.
#
do_test 2.1 {
  p4_of_opcode db CursorHint {
     SELECT * FROM t2 CROSS JOIN t1 WHERE a=x
  }
} {/(c0==r*)/}
do_test 2.2 {
  p5_of_opcode db OpenRead . {
     SELECT * FROM t2 CROSS JOIN t1 WHERE a=x
  }
} {00 00}

# Various expressions captured by CursorHint
#
do_test 3.1 {
  p4_of_opcode db CursorHint {
    SELECT * FROM t1 WHERE a=15 AND c=22 AND rowid!=98
  }
} {/(c0==15).*(c2==22).*(rowid!=98)/}
do_test 3.2 {
  p4_of_opcode db CursorHint {
    SELECT * FROM t3 WHERE a<15 AND b>22 AND id!=98
  }
} {/(c1<15).*(c2>22).*(c0!=98)/}

# Indexed queries
#
do_test 4.1 {
  db eval {
    CREATE INDEX t1bc ON t1(b,c);
    CREATE INDEX t2yz ON t2(y,z);
  }
  p4_of_opcode db CursorHint {
    SELECT * FROM t1 WHERE b>11;
  }
} {/(c0>11)/}
do_test 4.2 {
  p5_of_opcode db OpenRead . {
    SELECT * FROM t1 WHERE b>11;
  }
} {02 00}
do_test 4.3 {
  p4_of_opcode db CursorHint {
    SELECT c FROM t1 WHERE b>11;
  }
} {/(c0>11)/}
do_test 4.4 {
  p5_of_opcode db OpenRead . {
    SELECT c FROM t1 WHERE b>11;
  }
} {00}










|












|












|




|











|









|








55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122

# no cursor hints occur for t1 (since it is a full table scan) but that
# each t2 access has a cursor hint based on the current t1.a value.
#
do_test 1.1 {
  p4_of_opcode db CursorHint {
     SELECT * FROM t1 CROSS JOIN t2 WHERE a=x
  }
} {{EQ(r[1],c0)}}
do_test 1.2 {
  p5_of_opcode db OpenRead . {
     SELECT * FROM t1 CROSS JOIN t2 WHERE a=x
  }
} {00 00}

# Do the same test the other way around.
#
do_test 2.1 {
  p4_of_opcode db CursorHint {
     SELECT * FROM t2 CROSS JOIN t1 WHERE a=x
  }
} {{EQ(c0,r[1])}}
do_test 2.2 {
  p5_of_opcode db OpenRead . {
     SELECT * FROM t2 CROSS JOIN t1 WHERE a=x
  }
} {00 00}

# Various expressions captured by CursorHint
#
do_test 3.1 {
  p4_of_opcode db CursorHint {
    SELECT * FROM t1 WHERE a=15 AND c=22 AND rowid!=98
  }
} {AND(AND(EQ(c0,15),EQ(c2,22)),NE(rowid,98))}
do_test 3.2 {
  p4_of_opcode db CursorHint {
    SELECT * FROM t3 WHERE a<15 AND b>22 AND id!=98
  }
} {AND(AND(LT(c1,15),GT(c2,22)),NE(c0,98))}

# Indexed queries
#
do_test 4.1 {
  db eval {
    CREATE INDEX t1bc ON t1(b,c);
    CREATE INDEX t2yz ON t2(y,z);
  }
  p4_of_opcode db CursorHint {
    SELECT * FROM t1 WHERE b>11;
  }
} {GT(c0,11)}
do_test 4.2 {
  p5_of_opcode db OpenRead . {
    SELECT * FROM t1 WHERE b>11;
  }
} {02 00}
do_test 4.3 {
  p4_of_opcode db CursorHint {
    SELECT c FROM t1 WHERE b>11;
  }
} {GT(c0,11)}
do_test 4.4 {
  p5_of_opcode db OpenRead . {
    SELECT c FROM t1 WHERE b>11;
  }
} {00}
︙︙