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
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
