I am working in SQLite 3.1.3 on Mac OS X 10.4.4. I keep getting the
following error and am wondering why and what I can do about it? When
I query mukey, cokey, and comppct_r independently I get the correct
result. However, when I query for all three the columns are
misaligned and the first column gets dropped. Is this a problem with
the interface program in OS X's Terminal, or with SQLite itself?
mukey is numeric, comppct_r is numeric, cokey is text. This seems to
be messing up a join.
sqlite> select mukey from component limit 5;
mukey
----------
456557
456557
456557
456557
456557
sqlite> select cokey from component limit
5; cokey
--------------
456557:612401
456557:612402
456557:612403
456557:612404
456557:612405
sqlite> select comppct_r from component limit 5;
comppct_r
----------
85
3
2
5
5
sqlite> select mukey, cokey from component limit 5;
mukey cokey
---------- --------------
456557 456557:612401
456557 456557:612402
456557 456557:612403
456557 456557:612404
456557 456557:612405
sqlite> select mukey, cokey, comppct_r from component limit 5;
mukey cokey comppct_r
---------- -------------- ----------
85 456557:612401
3 456557:612402
2 456557:612403
5 456557:612404
5 456557:612405
sqlite> select cokey, comppct_r, mukey from component limit 5;
cokey comppct_r mukey
-------------- ---------- ----------
85 456557
3 456557
2 456557
5 456557
5 456557
But wait! It works as the following order! What's happening?
sqlite> select comppct_r, mukey, cokey from component limit 5;
comppct_r mukey cokey
---------- ---------- --------------
85 456557 456557:612401
3 456557 456557:612402
2 456557 456557:612403
5 456557 456557:612404
5 456557 456557:612405
And it seems dependent on the .mode:
Column mode:
sqlite> select cokey, comppct_r from component limit 5;
cokey comppct_r
-------------- ----------
85 1
3 2
2 3
5 4
5 5
List mode:
sqlite> select cokey, comppct_r from component limit 5;
cokey|comppct_r
|85557:612401
|36557:612402
|26557:612403
|56557:612404
|56557:612405
