Hi Bill, thank you very much for your quick response and for the bug fix in sqlite.ijs.
I tested my „real“ SQLite application case where I first noticed the issue and it works fine. ;-) Regards, Ulrich > > Message: 1 > Date: Thu, 24 Mar 2022 22:32:59 +0800 > From: bill lam <[email protected]> > To: Programming forum <[email protected]> > Subject: Re: [Jprogramming] Issue with qualified column name in SQLite > query > Message-ID: > <CAC22voYikJmfgTczQCGxnnfoRGZ3798j0-9=afya-gdmlfa...@mail.gmail.com> > Content-Type: text/plain; charset="UTF-8" > > There was a bug in sqlite.ijs now fixed. Please get update and test again. > Thank you for the detail report. > > On Thu, Mar 24, 2022 at 4:08 PM Ulrich Vollert <[email protected]> wrote: > >> Hi, >> >> I noticed an issue with SQLite and qualified column names - working with >> j903/j64/darwin and data/sqlite 1.0.35. >> >> When I use two table names in a join as shown below, and the first column >> name after the SELECT is qualified, I get no reply?? >> >> When the qualified name is the second column in the query, there is a >> correct reply. >> >> require'data/sqlite' >> >> F =: '~temp/error_test.sqlite' >> db=: sqlcreate_psqlite_ F >> >> sqlcmd__db 'create table t1 (a int, b int)' >> sqlcmd__db 'create table t2 (a int, c int)' >> >> sqlinsert__db't1';('a';'b');<1;2 >> sqlinsert__db't2';('a';'c');<1;3 >> >> >> sqlreads__db'select t1.a, b, c from t1, t2 where t1.a = t2.a' >> >> evaluates to >> ┌──────┬─┬─┐ >> │"t1.a“. │b │c │ >> ├──────┼─┼─┤ >> └──────┴─┴─┘ >> >> which is not the correct answer….?? >> >> sqlreads__db'select b, t1.a, c from t1, t2 where t1.a = t2.a' >> >> evaluates correctly to >> >> ┌─┬─┬─┐ >> │b │a │c │ >> ├─┼─┼─┤ >> │2 │1 │3 │ >> └─┴─┴─┘ >> >> >> Regards, >> Ulrich >> ---------------------------------------------------------------------- >> For information about J forums see http://www.jsoftware.com/forums.htm >> > > > ------------------------------ > > Message: 2 > Date: Thu, 24 Mar 2022 12:41:40 -0400 > From: Raul Miller <[email protected]> > To: Programming forum <[email protected]> > Subject: Re: [Jprogramming] Issue with qualified column name in SQLite > query > Message-ID: > <cad2jou84csrmcm9mtjnabaezaek5vcv9govg3pkgxfxf573...@mail.gmail.com> > Content-Type: text/plain; charset="UTF-8" > > Note that there's still an issue. > > Quoting in sql is a mechanism which removes the special significance > of certain characters, allowing them to be used in column names. > > And, the data/sqlite 'fixselect__db' mechanism is based on an > assumption that '.' was intended to be used in the name of a column, > rather than being used to select an otherwise ambiguous reference to a > column. > > The fix deployed here limits the scope of that assumption -- it only > kicks in if a column name is quoted in the select. > > However, if I create a column name which has a dot in it (which is the > situation that fixselect__db is designed to address), I can no longer > use a dot to mark which table a column is coming from. > > Here's a demonstration: > > load'data/sqlite' > > F =: '~temp/errorquote_test.sqlite' > db=: sqlcreate_psqlite_ F > > sqlcmd__db 'create table t1 (a int, "b." int)' > sqlcmd__db 'create table t2 (a int, c int)' > > sqlinsert__db't1';('a';'b.');<1;2 > sqlinsert__db't2';('a';'c');<1;3 > > echo sqlreads__db'select * from t1, t2 where t1.a = t2.a' > echo sqlreads__db'select t1.a, "b.", c from t1, t2 where t1.a = t2.a' > > I hope this makes sense, > > -- > Raul > > On Thu, Mar 24, 2022 at 10:33 AM bill lam <[email protected]> wrote: >> >> There was a bug in sqlite.ijs now fixed. Please get update and test again. >> Thank you for the detail report. >> >> On Thu, Mar 24, 2022 at 4:08 PM Ulrich Vollert <[email protected]> wrote: >> >>> Hi, >>> >>> I noticed an issue with SQLite and qualified column names - working with >>> j903/j64/darwin and data/sqlite 1.0.35. >>> >>> When I use two table names in a join as shown below, and the first column >>> name after the SELECT is qualified, I get no reply?? >>> >>> When the qualified name is the second column in the query, there is a >>> correct reply. >>> >>> require'data/sqlite' >>> >>> F =: '~temp/error_test.sqlite' >>> db=: sqlcreate_psqlite_ F >>> >>> sqlcmd__db 'create table t1 (a int, b int)' >>> sqlcmd__db 'create table t2 (a int, c int)' >>> >>> sqlinsert__db't1';('a';'b');<1;2 >>> sqlinsert__db't2';('a';'c');<1;3 >>> >>> >>> sqlreads__db'select t1.a, b, c from t1, t2 where t1.a = t2.a' >>> >>> evaluates to >>> ┌──────┬─┬─┐ >>> │"t1.a“. │b │c │ >>> ├──────┼─┼─┤ >>> └──────┴─┴─┘ >>> >>> which is not the correct answer….?? >>> >>> sqlreads__db'select b, t1.a, c from t1, t2 where t1.a = t2.a' >>> >>> evaluates correctly to >>> >>> ┌─┬─┬─┐ >>> │b │a │c │ >>> ├─┼─┼─┤ >>> │2 │1 │3 │ >>> └─┴─┴─┘ >>> >>> >>> Regards, >>> Ulrich >>> ---------------------------------------------------------------------- >>> For information about J forums see http://www.jsoftware.com/forums.htm >>> >> ---------------------------------------------------------------------- >> For information about J forums see http://www.jsoftware.com/forums.htm > > > ------------------------------ > > Subject: Digest Footer > > ---------------------------------------------------------------------- > For information about J forums see http://www.jsoftware.com/forums.htm > > > ------------------------------ > > End of Programming Digest, Vol 198, Issue 22 > ******************************************** ---------------------------------------------------------------------- For information about J forums see http://www.jsoftware.com/forums.htm
