Re: [sqlite] Parametrized Queries issue (Possible bug)
I done a path to make this works, if someone wants!
Now...
CREATE TABLE a (a, b);
INSERT INTO a VALUES (0, 0);
INSERT INTO a VALUES (0, 1);
INSERT INTO a VALUES (1, 0);
INSERT INTO a VALUES (1, 1);
SELECT * FROM a WHERE a.a = :a AND a.b = ?1;
SELECT * FROM a WHERE a.a = ?1 AND a.b = :b;
Have the same behavior!
|Index: D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c
|===
|--- D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c (revisão 323)
|+++ D:/FS_trunk/devpalm_01/poli_sqlite_lib/src/expr.c (cópia de trabalho)
|@@ -557,9 +557,21 @@
| sqlite3ErrorMsg(pParse, "variable number must be between ?1 and ?%d",
| db->aLimit[SQLITE_LIMIT_VARIABLE_NUMBER]);
| }
|- if( i>pParse->nVar ){
|- pParse->nVar = i;
|+
|+ // give to expression parameter with same index, another number to avoid
conflicts
|+ // with named parameter (:AAA, $AAA, @AAA) and indexed parameter (?NNN)
|+ for(i=0; inVarExpr; i++){
|+ Expr *pE = pParse->apVarExpr[i];
|+ assert( pE!=0 );
|+ if( pE->iColumn==pExpr->iColumn ){
|+ pE->iColumn = (ynVar)(++pParse->nVar);
|+ break;
|+ }
| }
|+
|+ if( pExpr->iColumn>pParse->nVar ){
|+ pParse->nVar = pExpr->iColumn;
|+ }
| }else{
| /* Wildcards like ":aaa", "$aaa" or "@aaa". Reuse the same variable
| ** number as the prior appearance of the same name, or if the name
--
Atenciosamente/Regards,
Israel Lins Albuquerque
Desenvolvimento/Development
Polibrás Brasil Software Ltda.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parametrized Queries issue (Possible bug)
I read that but that say "To avoid confusion, it is best to avoid mixing...", but you need never mix them, if you want have none collateral effects !!! - Mensagem original - De: "Jay A. Kreibich" <j...@kreibi.ch> Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Enviadas: Segunda-feira, 14 de Junho de 2010 15:41:01 Assunto: Re: [sqlite] Parametrized Queries issue (Possible bug) On Mon, Jun 14, 2010 at 03:03:52PM -0300, Israel Lins Albuquerque scratched on the wall: > I know what you booth are are saying. But if this is a limitation of sqlite, > this need be documented, and doesn't have any comment of this behavior. http://sqlite.org/lang_expr.html#varparam "Named parameters are also numbered. The number assigned is the next unused number. To avoid confusion, it is best to avoid mixing named and numbered parameters." The docs are pretty clear about what happened, and that you shouldn't do that, and that if you do it anyways you will likely be confused. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Atenciosamente/Regards, Israel Lins Albuquerque Desenvolvimento/Development Polibrás Brasil Software Ltda. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parametrized Queries issue (Possible bug)
On Mon, Jun 14, 2010 at 03:03:52PM -0300, Israel Lins Albuquerque scratched on the wall: > I know what you booth are are saying. But if this is a limitation of sqlite, > this need be documented, and doesn't have any comment of this behavior. http://sqlite.org/lang_expr.html#varparam "Named parameters are also numbered. The number assigned is the next unused number. To avoid confusion, it is best to avoid mixing named and numbered parameters." The docs are pretty clear about what happened, and that you shouldn't do that, and that if you do it anyways you will likely be confused. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parametrized Queries issue (Possible bug)
I know what you booth are are saying. But if this is a limitation of sqlite, this need be documented, and doesn't have any comment of this behavior. And the name of parameter ':a' are changed, we can't set his value, only using the index or your new name '?1' !!! - Mensagem original - De: "David Bicking" <dbic...@yahoo.com> Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Enviadas: Segunda-feira, 14 de Junho de 2010 13:06:02 Assunto: Re: [sqlite] Parametrized Queries issue (Possible bug) I think in your first example, the :a comes first, so it is assigned the first index value. You then use ?1, which also uses the first parameter index. In the second, you use ?1 first, then :b, which sees the first index has been used and thus assigns it to the second index. As I believe Jay said, you should not mix named and numbered parameter syntax. David --- On Mon, 6/14/10, Israel Lins Albuquerque <israel...@polibrasnet.com.br> wrote: > From: Israel Lins Albuquerque <israel...@polibrasnet.com.br> > Subject: Re: [sqlite] Parametrized Queries issue (Possible bug) > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Monday, June 14, 2010, 10:55 AM > Once more detail... > > Doing this... > > SELECT * > FROM a > WHERE a.a = :a > AND a.b = ?1; > > and this... > > SELECT * > FROM a > WHERE a.a = ?1 > AND a.b = :b; > > Will have different behavior! > > > > - Mensagem original - > De: "Israel Lins Albuquerque" <israel...@polibrasnet.com.br> > > Para: "sqlite-users" <sqlite-users@sqlite.org> > > Enviadas: Segunda-feira, 14 de Junho de 2010 11:51:07 > Assunto: [sqlite] Parametrized Queries issue (Possible bug) > > > Using the example above I see one not documented issue. > When I use parameters ?NNN and : or @ or $ , > in same query as above some of then will be omitted. I > don't know if this is the expected behavior or not. > > CREATE TABLE a (a, b); > CREATE TABLE b (a, b); > > INSERT INTO a VALUES (0, 1); > INSERT INTO a VALUES (1, 0); > INSERT INTO a VALUES (1, 1); > INSERT INTO b VALUES (0, 1); > INSERT INTO b VALUES (1, 0); > INSERT INTO b VALUES (1, 1); > > SELECT * > FROM a > JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) > WHERE a.a = ?1; > > In this query as 2 parameters but will be see like only > one! > > -- > > Atenciosamente/Regards, > > Israel Lins Albuquerque > Desenvolvimento/Development > Polibrás Brasil Software Ltda. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > > Atenciosamente/Regards, > > Israel Lins Albuquerque > Desenvolvimento/Development > Polibrás Brasil Software Ltda. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Atenciosamente/Regards, Israel Lins Albuquerque Desenvolvimento/Development Polibrás Brasil Software Ltda. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parametrized Queries issue (Possible bug)
I think in your first example, the :a comes first, so it is assigned the first index value. You then use ?1, which also uses the first parameter index. In the second, you use ?1 first, then :b, which sees the first index has been used and thus assigns it to the second index. As I believe Jay said, you should not mix named and numbered parameter syntax. David --- On Mon, 6/14/10, Israel Lins Albuquerque <israel...@polibrasnet.com.br> wrote: > From: Israel Lins Albuquerque <israel...@polibrasnet.com.br> > Subject: Re: [sqlite] Parametrized Queries issue (Possible bug) > To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> > Date: Monday, June 14, 2010, 10:55 AM > Once more detail... > > Doing this... > > SELECT * > FROM a > WHERE a.a = :a > AND a.b = ?1; > > and this... > > SELECT * > FROM a > WHERE a.a = ?1 > AND a.b = :b; > > Will have different behavior! > > > > - Mensagem original - > De: "Israel Lins Albuquerque" <israel...@polibrasnet.com.br> > > Para: "sqlite-users" <sqlite-users@sqlite.org> > > Enviadas: Segunda-feira, 14 de Junho de 2010 11:51:07 > Assunto: [sqlite] Parametrized Queries issue (Possible bug) > > > Using the example above I see one not documented issue. > When I use parameters ?NNN and : or @ or $ , > in same query as above some of then will be omitted. I > don't know if this is the expected behavior or not. > > CREATE TABLE a (a, b); > CREATE TABLE b (a, b); > > INSERT INTO a VALUES (0, 1); > INSERT INTO a VALUES (1, 0); > INSERT INTO a VALUES (1, 1); > INSERT INTO b VALUES (0, 1); > INSERT INTO b VALUES (1, 0); > INSERT INTO b VALUES (1, 1); > > SELECT * > FROM a > JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) > WHERE a.a = ?1; > > In this query as 2 parameters but will be see like only > one! > > -- > > Atenciosamente/Regards, > > Israel Lins Albuquerque > Desenvolvimento/Development > Polibrás Brasil Software Ltda. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > -- > > Atenciosamente/Regards, > > Israel Lins Albuquerque > Desenvolvimento/Development > Polibrás Brasil Software Ltda. > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parametrized Queries issue (Possible bug)
On Mon, Jun 14, 2010 at 12:07:08PM -0300, Israel Lins Albuquerque scratched on the wall: > Yes. I know that, but the names of parameters are different, > and I debug this and the parameter named as ':a' will be overwrited by '?1', > and if I use sqlite3_bind_parameter_index(stmt, ":a") will fail, returning 0. > But actually they are not the same. Yes they are. Names are just short-cuts to indexes. Named parameters use "the first unused index." You then set an explicit index that is already in use. Each index can only have one name, so it is over-written with the last-used representation. To avoid such problems, it is strongly advised you do not mix parameter types. Either use bare parameters, explicit indexes, or names-- but pick one and stick with it. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parametrized Queries issue (Possible bug)
Yes. I know that, but the names of parameters are different, and I debug this and the parameter named as ':a' will be overwrited by '?1', and if I use sqlite3_bind_parameter_index(stmt, ":a") will fail, returning 0. But actually they are not the same. - Mensagem original - De: "Jay A. Kreibich" <j...@kreibi.ch> Para: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Enviadas: Segunda-feira, 14 de Junho de 2010 12:00:32 Assunto: Re: [sqlite] Parametrized Queries issue (Possible bug) On Mon, Jun 14, 2010 at 11:51:07AM -0300, Israel Lins Albuquerque scratched on the wall: > Using the example above I see one not documented issue. When I use parameters > ?NNN and : or @ or $ , > in same query as above some of then will be omitted. I don't know if this is > the expected behavior or not. > > CREATE TABLE a (a, b); > CREATE TABLE b (a, b); > > INSERT INTO a VALUES (0, 1); > INSERT INTO a VALUES (1, 0); > INSERT INTO a VALUES (1, 1); > INSERT INTO b VALUES (0, 1); > INSERT INTO b VALUES (1, 0); > INSERT INTO b VALUES (1, 1); > > SELECT * > FROM a > JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) > WHERE a.a = ?1; > > In this query as 2 parameters but will be see like only one! It is expected. Nearly all SQL interfaces work this way. It is a feature. The same value will be used in both locations in the query. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Atenciosamente/Regards, Israel Lins Albuquerque Desenvolvimento/Development Polibrás Brasil Software Ltda. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parametrized Queries issue (Possible bug)
On Mon, Jun 14, 2010 at 11:51:07AM -0300, Israel Lins Albuquerque scratched on the wall: > Using the example above I see one not documented issue. When I use parameters > ?NNN and : or @ or $ , > in same query as above some of then will be omitted. I don't know if this is > the expected behavior or not. > > CREATE TABLE a (a, b); > CREATE TABLE b (a, b); > > INSERT INTO a VALUES (0, 1); > INSERT INTO a VALUES (1, 0); > INSERT INTO a VALUES (1, 1); > INSERT INTO b VALUES (0, 1); > INSERT INTO b VALUES (1, 0); > INSERT INTO b VALUES (1, 1); > > SELECT * > FROM a > JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) > WHERE a.a = ?1; > > In this query as 2 parameters but will be see like only one! It is expected. Nearly all SQL interfaces work this way. It is a feature. The same value will be used in both locations in the query. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Parametrized Queries issue (Possible bug)
Once more detail... Doing this... SELECT * FROM a WHERE a.a = :a AND a.b = ?1; and this... SELECT * FROM a WHERE a.a = ?1 AND a.b = :b; Will have different behavior! - Mensagem original - De: "Israel Lins Albuquerque" <israel...@polibrasnet.com.br> Para: "sqlite-users" <sqlite-users@sqlite.org> Enviadas: Segunda-feira, 14 de Junho de 2010 11:51:07 Assunto: [sqlite] Parametrized Queries issue (Possible bug) Using the example above I see one not documented issue. When I use parameters ?NNN and : or @ or $ , in same query as above some of then will be omitted. I don't know if this is the expected behavior or not. CREATE TABLE a (a, b); CREATE TABLE b (a, b); INSERT INTO a VALUES (0, 1); INSERT INTO a VALUES (1, 0); INSERT INTO a VALUES (1, 1); INSERT INTO b VALUES (0, 1); INSERT INTO b VALUES (1, 0); INSERT INTO b VALUES (1, 1); SELECT * FROM a JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) WHERE a.a = ?1; In this query as 2 parameters but will be see like only one! -- Atenciosamente/Regards, Israel Lins Albuquerque Desenvolvimento/Development Polibrás Brasil Software Ltda. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Atenciosamente/Regards, Israel Lins Albuquerque Desenvolvimento/Development Polibrás Brasil Software Ltda. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Parametrized Queries issue (Possible bug)
Using the example above I see one not documented issue. When I use parameters ?NNN and : or @ or $ , in same query as above some of then will be omitted. I don't know if this is the expected behavior or not. CREATE TABLE a (a, b); CREATE TABLE b (a, b); INSERT INTO a VALUES (0, 1); INSERT INTO a VALUES (1, 0); INSERT INTO a VALUES (1, 1); INSERT INTO b VALUES (0, 1); INSERT INTO b VALUES (1, 0); INSERT INTO b VALUES (1, 1); SELECT * FROM a JOIN b ON (a.a = b.a AND a.b = :b AND a.b = b.b) WHERE a.a = ?1; In this query as 2 parameters but will be see like only one! -- Atenciosamente/Regards, Israel Lins Albuquerque Desenvolvimento/Development Polibrás Brasil Software Ltda. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
