Re: [sqlite] Version 3.1.3 is a headache

2005-02-28 Thread Christopher Petrilli
On Sat, 28 Feb 2004 11:52:03 +0100, Jakub Adamek <[EMAIL PROTECTED]> wrote:
> Is it really so that some database server returns a result set with two
> same column names? Seems very strange. And the lovely SQLite 3.0.8
> didn't do such things ...

PostgreSQL, which holds closer to the SQL spec than any other DB I'm
aware of, refuses this syntax:

SELECT * FROM a INNER JOIN b;

You are depending on implicit join syntax that I believe is illegal in
SQL.  The fact that it happens to work doesn't make that a good idea. 
If you are explicit in the join:

petrilli=# SELECT * FROM a, b WHERE a.id = b.id;
 id | x | id | y 
+---++---
  1 | 1 |  1 | 2


As you'll notice, it returns both id columns, because you selected ALL
columns.  This is the correct behavior.  In this case, you've simply
chosen the wrong behavior.  As several other people have commented,
you should, in all join cases, explictely call out all columns that
you are interested in:

petrilli=# SELECT a.id, x, y FROM a, b WHERE a.id = b.id;
 id | x | y 
+---+---
  1 | 1 | 2

Good luck.

Chris
-- 
| Christopher Petrilli
| [EMAIL PROTECTED]


RE: [sqlite] Version 3.1.3 is a headache

2005-02-27 Thread Brass Tilde
> The last one is really annoying and I can't believe the 
> auto-tests could have missed it ...
> 
>create table a (id, x);
>create table b (id, y);
>insert into a values (1,1);
>insert into b values (1,2);
>select * from a inner join b;
> 
>  column names returned: id,x,id,y 

You'll get the same result when you execute that query in SQL Server 2000.
I suspect you'll get the same result from a lot of SQL DB systems.

> How am I supposed to use such column names? 

The same way every other programmer does, by using alias on the fields that
you are interested in, and leaving the others out of it.




Re: [sqlite] Version 3.1.3 is a headache

2005-02-27 Thread Clay Dowling
Jakub Adamek wrote:
  select * from a inner join b;
Just as a general guide with any database system, this type of query 
isn't particularly recommended because of the namespace issue.  Here's 
what I do to get around the problem (again, not just with SQLite):

1. Specify the criteria for the join (i.e. "inner join b on a.id = 
b.id), or the result set gets ridiculously large.
2. Explicitly declare the columns that I'm interested in receiving, 
since it's pretty rare that I'm going to want all of the columns from 
both tables.  Even when I do want all of the columns, it lets me give 
alternate names to duplicate columns.

I suspect that this is how most other people handle it as well, since I 
haven't seen an excess of complaints about this issue.

Clay


RE: [sqlite] Version 3.1.3 is a headache

2005-02-27 Thread Cariotoglou Mike
The fact is, the pragmas regarding column names now seem completely
broken, as they do absolutely nothing. Was this by design, or a new bug
?

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Sunday, February 27, 2005 1:08 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Version 3.1.3 is a headache

On Sat, 2005-02-26 at 22:43 +0100, Jakub Adamek wrote:
> Hi, I really love SQLite, but upgrading to 3.1.3 was not a good idea. 
> I have already posted 3 tickets with rather serious problems with 
> column names.
> 
> The last one is really annoying and I can't believe the auto-tests 
> could have missed it ...
> 
>create table a (id, x);
>create table b (id, y);
>insert into a values (1,1);
>insert into b values (1,2);
>select * from a inner join b;
> 
>  column names returned: id,x,id,y How am I supposed to use such 
> column names? Ouwey. No wonder that my C++ wrapper does not want to 
> work with such a result set.
> 

Your tickets are unhelpful and will likely be ignored.  Rather than
complain about the column names, perhaps you can present alternative
suggestions.  Posting what PostgreSQL, Oracle, and MySQL do with the
same queries would be a good start.  Explaining why you think the
current names are undesirable (instead of just saying "Ouwey") would
also be a positive step toward getting the problem addressed.
--
D. Richard Hipp <[EMAIL PROTECTED]>



Re: [sqlite] Version 3.1.3 is a headache

2005-02-26 Thread D. Richard Hipp
On Sat, 2005-02-26 at 22:43 +0100, Jakub Adamek wrote:
> Hi, I really love SQLite, but upgrading to 3.1.3 was not a good idea. I 
> have already posted 3 tickets with rather serious problems with column 
> names.
> 
> The last one is really annoying and I can't believe the auto-tests could 
> have missed it ...
> 
>create table a (id, x);
>create table b (id, y);
>insert into a values (1,1);
>insert into b values (1,2);
>select * from a inner join b;
> 
>  column names returned: id,x,id,y How am I supposed to use such 
> column names? Ouwey. No wonder that my C++ wrapper does not want to work 
> with such a result set.
> 

Your tickets are unhelpful and will likely be ignored.  Rather
than complain about the column names, perhaps you can present
alternative suggestions.  Posting what PostgreSQL, Oracle, and
MySQL do with the same queries would be a good start.  Explaining
why you think the current names are undesirable (instead of
just saying "Ouwey") would also be a positive step toward
getting the problem addressed.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>



Re: [sqlite] Version 3.1.3 is a headache

2005-02-26 Thread Bernhard Döbler
Isn't ID the field INNER JOIN uses to combine the tables...
That means you have to columns named ID but theire content is the same.

Bernhard

- Original Message - 
From: "Jakub Adamek" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Saturday, February 26, 2005 10:43 PM
Subject: [sqlite] Version 3.1.3 is a headache


> Hi, I really love SQLite, but upgrading to 3.1.3 was not a good idea. I 
> have already posted 3 tickets with rather serious problems with column 
> names.
> 
> The last one is really annoying and I can't believe the auto-tests could 
> have missed it ...
> 
>create table a (id, x);
>create table b (id, y);
>insert into a values (1,1);
>insert into b values (1,2);
>select * from a inner join b;
> 
>  column names returned: id,x,id,y How am I supposed to use such 
> column names? Ouwey. No wonder that my C++ wrapper does not want to work 
> with such a result set.
> 



[sqlite] Version 3.1.3 is a headache

2005-02-26 Thread Jakub Adamek
Hi, I really love SQLite, but upgrading to 3.1.3 was not a good idea. I 
have already posted 3 tickets with rather serious problems with column 
names.

The last one is really annoying and I can't believe the auto-tests could 
have missed it ...

  create table a (id, x);
  create table b (id, y);
  insert into a values (1,1);
  insert into b values (1,2);
  select * from a inner join b;
column names returned: id,x,id,y How am I supposed to use such 
column names? Ouwey. No wonder that my C++ wrapper does not want to work 
with such a result set.

Jakub


Re: [sqlite] Version 3.1.3

2005-02-19 Thread Jeremy Hinegardner
On Sat, Feb 19, 2005 at 09:30:26PM -0500, D. Richard Hipp wrote:
> As always, please let me know if you find any
> problem.

gmake && gmake test on OpenBSD 3.6/amd64 results in the following
warnings and test errors.  Looks like it is all because of 64bit
architecture.  If you want me to test anything I'll be happy to do so.

These warnings are mentioned in ticket #848:

src/table.c: In function `sqlite3_get_table':
src/table.c:146: warning: cast to pointer from integer of different size
src/table.c: In function `sqlite3_free_table':
src/table.c:191: warning: cast from pointer to integer of different size
src/vdbeaux.c: In function `displayP3':
src/vdbeaux.c:389: warning: cast from pointer to integer of different size

These warnings aren't mentioned anywhere else:

src/test1.c: In function `test_collate_func':
src/test1.c:1086: warning: cast from pointer to integer of different size
src/test1.c: In function `test_collate_needed_cb':
src/test1.c:1168: warning: cast to pointer from integer of different size

These tests failed:

printf-8.1...
Error: integer value too large to represent
printf-8.2...
Error: integer value too large to represent
2 errors out of 19732 tests
Failures on these tests: printf-8.1 printf-8.2

enjoy,

-jeremy


-- 

 Jeremy Hinegardner  [EMAIL PROTECTED] 



[sqlite] Version 3.1.3

2005-02-19 Thread D. Richard Hipp
SQLite version 3.1.3 is now available on the website.
http://www.sqlite.org/  This release fixes a few minor
problems that were found in 3.1.2.  This release is
also designed to be forwards compatible with version 3.2.
Version 3.1.2 might not be able to read and write 
some database files generated by version 3.2 - specifically
databases on which ALTER TABLE has been used but have
not yet been VACUUMed.  But we believe that
version 3.1.3 will also be able to read and write any
3.2 database file.

As always, please let me know if you find any
problem.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>