Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Petite Abeille]

On Feb 20, 2013, at 9:35 PM, "Jay A. Kreibich"  wrote:

>  Not covert... works as documented:

"Let us be charitable, and call it a misleading feature" -- Larry Wall

> Not random either... at least, not any more random than any other
>  query.  Result order is never meaningful unless there is an
>  ORDER BY.

Q.E.D. 

> As for "productive", I suppose that depends on if you want SQL to
>  find poorly thought out queries on behalf of the developer, or just
>  assume the developer knows what they're doing and do the best it can
>  with what it was given.

That the problem right there:  … " do the best it can with what it was given"… 
That's basically second guessing and is rather harmful. Just the opposite of 
the first assertion ( "assume the developer knows what they're doing" ).  
SQLite shouldn't assume, or guess, anything and just fail-fast instead. 
Everyone would be better off that way.


> For good or bad, SQL is definitely a "shoot
>  yourself in the foot" language.

Nah. No more or less than any other programmatic constructs. On the other hand, 
there is a clear tendency in SQLite for creative second guessing (scalar, group 
by, etc) or ignore issues altogether (constraints violations opacity).

Just my 2¢ though.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Petite Abeille]

On Feb 20, 2013, at 9:29 PM, Richard Hipp  wrote:

> On Wed, Feb 20, 2013 at 3:25 PM, Petite Abeille 
> wrote:
> 
>> 
>> On Feb 20, 2013, at 2:15 PM, Richard Hipp  wrote:
>> 
>>> SQLite automatically adds a LIMIT 1 to a scalar subquery.
>> 
>> Yeah… that's a bit of a death trap though… would be much more productive
>> if SQLite would raise an exception instead of doing something covert and
>> random...
>> 
> 
> There are over one million applications that use SQLite as it is currently
> implement.  Perhaps you are right that the proposed behavior makes better
> sense.  (Or perhaps not - the case can be argued.)  But how many of those
> one million applications would be busted and need to be fixed?  You want to
> test them all?

Well… considering that such applications are already broken as is… making such 
defect explicit might be beneficial overall… just imagine how many hidden, 
potentially dangerous, bugs SQLite could uncover in one swell swoop... 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Jay A. Kreibich]

On Wed, Feb 20, 2013 at 09:25:54PM +0100, Petite Abeille scratched on the wall:
> On Feb 20, 2013, at 2:15 PM, Richard Hipp  wrote:
> 
> > SQLite automatically adds a LIMIT 1 to a scalar subquery.
> 
> Yeah? that's a bit of a death trap though? would be much more productive
> if SQLite would raise an exception instead of doing something covert 
> and random... 

  Not covert... works as documented:

  http://www.sqlite.org/lang_expr.html

Scalar Subqueries

A SELECT statement enclosed in parentheses may appear as a scalar
quantity. A SELECT used as a scalar quantity must return a result
set with a single column. The result of the expression is the value
of the only column in the first row returned by the SELECT
statement. If the SELECT yields more than one result row, all rows
after the first are ignored. If the SELECT yields no rows, then the
value of the expression is NULL. The LIMIT of a scalar subquery is
always 1. Any other LIMIT value given in the SQL text is ignored.

All types of SELECT statement, including aggregate and compound
SELECT queries (queries with keywords like UNION or EXCEPT) are
allowed as scalar subqueries. 


  Not random either... at least, not any more random than any other
  query.  Result order is never meaningful unless there is an
  ORDER BY.


  As for "productive", I suppose that depends on if you want SQL to
  find poorly thought out queries on behalf of the developer, or just
  assume the developer knows what they're doing and do the best it can
  with what it was given.  For good or bad, SQL is definitely a "shoot
  yourself in the foot" language.

   -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] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Richard Hipp]

On Wed, Feb 20, 2013 at 3:25 PM, Petite Abeille wrote:

>
> On Feb 20, 2013, at 2:15 PM, Richard Hipp  wrote:
>
> > SQLite automatically adds a LIMIT 1 to a scalar subquery.
>
> Yeah… that's a bit of a death trap though… would be much more productive
> if SQLite would raise an exception instead of doing something covert and
> random...
>

There are over one million applications that use SQLite as it is currently
implement.  Perhaps you are right that the proposed behavior makes better
sense.  (Or perhaps not - the case can be argued.)  But how many of those
one million applications would be busted and need to be fixed?  You want to
test them all?


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Petite Abeille]

On Feb 20, 2013, at 2:15 PM, Richard Hipp  wrote:

> SQLite automatically adds a LIMIT 1 to a scalar subquery.

Yeah… that's a bit of a death trap though… would be much more productive if 
SQLite would raise an exception instead of doing something covert and random... 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Simon Slavin]

On 20 Feb 2013, at 12:17pm, Marcin Kałuża  wrote:

> sqlite> select * from t where v = (select v from t);
> 1

There is no definition for what this means under SQL.  Any SQL implementation 
might consider it an error, or always evaluate (item = list) as false, or do 
anything else.  I don't think you can point to either postgres or SQLite as 
definitely being buggy on this one.

By the way it is an error to use postgres as a model of correct behaviour about 
SQL.  It has just as many weird characteristics as the other implementations of 
SQL I've seen.  Since SQL is not procedurally specified there are many muddy 
areas in its definition.

Simon. 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Richard Hipp]

On Wed, Feb 20, 2013 at 7:17 AM, Marcin Kałuża wrote:

> We've encountered strange sqlite behavior:
>
> SQLite version 3.7.15.2 2013-01-09 11:53:05
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t(v int4);
> sqlite> insert into t values (1),(2),(3);
> sqlite> select * from t where v = (select v from t);
> 1
> sqlite> select * from t where v in (select v from t);
> v
> --
> 1
> 2
> 3
>
> While on postgres it works like this (as far as I remember oracle does
> this as well, and that's the way it should work imho):
> mail=> create table t (v int4);
> CREATE TABLE
> mail=> insert into t values (1),(2),(3);
> INSERT 0 3
> mail=> select * from t where v = (select v from t);
> ERROR:  more than one row returned by a subquery used as an expression
> mail=> select * from t where v in (select v from t);
>  v
> ---
>  1
>  2
>  3
> (3 rows)
>
> Is this a bug, or a feature?
>

SQLite automatically adds a LIMIT 1 to a scalar subquery.



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BUG? sqlite not reporting error like "more than one row returned by a subquery used as an expression"

[Marcin Kałuża]

We've encountered strange sqlite behavior:

SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(v int4);
sqlite> insert into t values (1),(2),(3);
sqlite> select * from t where v = (select v from t);
1
sqlite> select * from t where v in (select v from t);
v
--
1
2
3

While on postgres it works like this (as far as I remember oracle does 
this as well, and that's the way it should work imho):

mail=> create table t (v int4);
CREATE TABLE
mail=> insert into t values (1),(2),(3);
INSERT 0 3
mail=> select * from t where v = (select v from t);
ERROR:  more than one row returned by a subquery used as an expression
mail=> select * from t where v in (select v from t);
 v
---
 1
 2
 3
(3 rows)

Is this a bug, or a feature?

Marcin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users