Re: [sqlite] Possible bug in type conversion prior to comparison

[Petite Abeille]

On May 13, 2013, at 6:12 PM, Simon Slavin  wrote:

> I should have asked you for (1,2,20) as well and we could see whether it 
> outputs '10' or '10.0'.  But yes, it would appear that in Oracle, NUMERIC 
> means FLOAT.

Nah. Plus there is no such type as 'NUMERIC' per se in Oracle. Just NUMBER( 
precision, scale ) or… FLOAT( precision ). (ignoring binary types for 
simplicity's sake).

http://docs.oracle.com/cd/E11882_01/server.112/e17118/sql_elements001.htm#sthref118

A so-called 'integer' in Oracle is simple a number defined as NUMBER(p,0).

Th ANSI names such as NUMERIC(19,0), or DECIMAL(p,s), etc… are aliases for the 
relevant Oracle types, e.g. NUMBER(19,0).

Anyhow… none of this helps SQLite much… :P



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

Re: [sqlite] Possible bug in type conversion prior to comparison

[Michael Black]

May just be showing best representation
main()
{
float a=1,b=2,c=20;
printf("%g\n",a/b*c);
}
Answer: 10
For c=21
Answer: 10.5


SQL> insert into numtypes values(1,2,20);

1 row created.

SQL> select A/B*C from numtypes;

 A/B*C
--
  12.5
  13.5
  11.5
  10.5
10


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, May 13, 2013 11:12 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Possible bug in type conversion prior to comparison


On 13 May 2013, at 5:08pm, Michael Black <mdblac...@yahoo.com> wrote:

> Would appear it's not doing any casting to promote values but just
promoting
> everything to float.

I should have asked you for (1,2,20) as well and we could see whether it
outputs '10' or '10.0'.  But yes, it would appear that in Oracle, NUMERIC
means FLOAT.

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

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

Re: [sqlite] Possible bug in type conversion prior to comparison

[Paul van Helden]

Actually, to be more accurate, the internal storage may be far from a float
(as in IEEE double) but a divide on an integer-looking value will certainly
be done with floating point math.


On Mon, May 13, 2013 at 6:13 PM, Paul van Helden wrote:

>
> I should have asked you for (1,2,20) as well and we could see whether it
>> outputs '10' or '10.0'.  But yes, it would appear that in Oracle, NUMERIC
>> means FLOAT.
>>
>> Of course it does! All the others too.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug in type conversion prior to comparison

[Paul van Helden]

> I should have asked you for (1,2,20) as well and we could see whether it
> outputs '10' or '10.0'.  But yes, it would appear that in Oracle, NUMERIC
> means FLOAT.
>
> Of course it does! All the others too.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug in type conversion prior to comparison

[Simon Slavin]

On 13 May 2013, at 5:08pm, Michael Black  wrote:

> Would appear it's not doing any casting to promote values but just promoting
> everything to float.

I should have asked you for (1,2,20) as well and we could see whether it 
outputs '10' or '10.0'.  But yes, it would appear that in Oracle, NUMERIC means 
FLOAT.

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

Re: [sqlite] Possible bug in type conversion prior to comparison

[Michael Black]

Added that...
Would appear it's not doing any casting to promote values but just promoting
everything to float.

SQL> insert into numtypes values(1,2,21);
1 row created.

SQL> select A/B*C from numtypes;

 A/B*C
--
  12.5
  13.5
  11.5
  10.5

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, May 13, 2013 11:01 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Possible bug in type conversion prior to comparison


On 13 May 2013, at 4:57pm, Michael Black <mdblac...@yahoo.com> wrote:

> Oracle gives the right answer too for example(contrary to what somebody
said
> earlier).
> 
> create table numtypes (A NUMERIC, B NUMERIC, C NUMERIC);
> insert into numtypes values (1, 2, 25.23);
> insert into numtypes values (1.0, 2, 27.17);
> insert into numtypes values (1.1, 2, 22.92);
> select A/B*C from numtypes;
> 
> A/B*C
> --
>  12.5
>  13.5
>  11.5

Please add to your INSERTs (1,2,21) and see whether Oracle is using integer
arithmetic or not.

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

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

Re: [sqlite] Possible bug in type conversion prior to comparison

[Simon Slavin]

On 13 May 2013, at 4:57pm, Michael Black  wrote:

> Oracle gives the right answer too for example(contrary to what somebody said
> earlier).
> 
> create table numtypes (A NUMERIC, B NUMERIC, C NUMERIC);
> insert into numtypes values (1, 2, 25.23);
> insert into numtypes values (1.0, 2, 27.17);
> insert into numtypes values (1.1, 2, 22.92);
> select A/B*C from numtypes;
> 
> A/B*C
> --
>  12.5
>  13.5
>  11.5

Please add to your INSERTs (1,2,21) and see whether Oracle is using integer 
arithmetic or not.

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

Re: [sqlite] Possible bug in type conversion prior to comparison

[Michael Black]

Seems to me the SQL standard makes no distinction between columns and
literals does it?
Why should literals be ignored?

Oracle gives the right answer too for example(contrary to what somebody said
earlier).

create table numtypes (A NUMERIC, B NUMERIC, C NUMERIC);
insert into numtypes values (1, 2, 25.23);
insert into numtypes values (1.0, 2, 27.17);
insert into numtypes values (1.1, 2, 22.92);
select A/B*C from numtypes;

 A/B*C
--
  12.5
  13.5
  11.5

SQL> desc numtypes;
 Name  Null?Type
 - 

 A  NUMBER(38)
 B  NUMBER(38)
 C  NUMBER(38)

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
Sent: Sunday, May 12, 2013 6:29 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Possible bug in type conversion prior to comparison

On Sun, May 12, 2013 at 7:55 AM, Tomasz Pawlak <
tomasz.paw...@cs.put.poznan.pl> wrote:

>
> So, type of '1' is 'text'.
>
> * If one operand has INTEGER, REAL or NUMERIC affinity and the other
> operand as TEXT or NONE affinity then NUMERIC affinity is applied to other
> operand. "
>
> So, if we compare 1 with '1'  (e.g. 1='1'), '1' should be converted to
> numeric, right?
>

No.  '1' has type 'text' but it has no affinity at all.  Likewise 1 has
type 'integer' but no affinity.  So no conversions take place, and the
answer is FALSE.

Affinity is only associated with table columns.  Literals never have
affinity.

-- 
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Possible bug in type conversion prior to comparison

[Richard Hipp]

On Sun, May 12, 2013 at 7:55 AM, Tomasz Pawlak <
tomasz.paw...@cs.put.poznan.pl> wrote:

>
> So, type of '1' is 'text'.
>
> * If one operand has INTEGER, REAL or NUMERIC affinity and the other
> operand as TEXT or NONE affinity then NUMERIC affinity is applied to other
> operand. "
>
> So, if we compare 1 with '1'  (e.g. 1='1'), '1' should be converted to
> numeric, right?
>

No.  '1' has type 'text' but it has no affinity at all.  Likewise 1 has
type 'integer' but no affinity.  So no conversions take place, and the
answer is FALSE.

Affinity is only associated with table columns.  Literals never have
affinity.

-- 
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] Possible bug in type conversion prior to comparison

[Tomasz Pawlak]

Hello,

First of all, I'm not sure is this a good place to report a SQLite bug, 
however let me try.


SQLite version: 3.7.15.2

How to reproduce error:
1. Create in-memory (or disk, whatever) database
2. Execute following statements to prepare schema:

CREATE TABLE data (id INTEGER PRIMARY KEY AUTOINCREMENT, a INTEGER NOT 
NULL, b INTEGER NOT NULL)


INSERT INTO data(a,b) VALUES(1,1)
INSERT INTO data(a,b) VALUES(2,2)
INSERT INTO data(a,b) VALUES(3,3)
INSERT INTO data(a,b) VALUES(4,4)

CREATE VIEW dataView AS
SELECT d.a AS a,
(CASE WHEN d.a%2 = 1 THEN d.a ELSE d.b END) AS b,
CAST((CASE WHEN d.a%2 = 1 THEN d.a ELSE d.b END) AS INTEGER) AS c
FROM data d

For now, you have database set up.

3. Let us execute the following query:

SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM dataView

output:
1,integer,1,integer,1,integer
2,integer,2,integer,2,integer
3,integer,3,integer,3,integer
4,integer,4,integer,4,integer

As far, as we see all columns have type affinity "integer". Values of 
each column in corresponding rows are the same.


On the other hand, take a look at a type seen by SQLite for strings:

SELECT '1', typeof('1')

output:
1,text

So, type of '1' is 'text'.

4. Before we continue, let us cite the following sentence from SQLite 
documentation (Sec. 3.3. from Datatypes In SQLite Version 3):
"Affinity is applied to operands of a comparison operator prior to the 
comparison according to the following rules in the order shown:
* If one operand has INTEGER, REAL or NUMERIC affinity and the other 
operand as TEXT or NONE affinity then NUMERIC affinity is applied to 
other operand. "


So, if we compare 1 with '1'  (e.g. 1='1'), '1' should be converted to 
numeric, right? It's not particularly true. See the following point.


5. Let us execute a query from point 3 with additional constraint in 
WHERE clause (note the quotes around '1'):


SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM dataView WHERE a='1'

output:
1,integer,1,integer,1,integer

The output is not surprising, SQLite returned the only row that has 1 in 
'a' column. Ok, let us do the same for 'b' column:


SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM dataView WHERE b='1'

output:
0 rows

Strange, right? As we have seen there is a row for which b is 1, however 
SQLite did not returned that. What happen then? Note that 'b' is not a 
column in table 'data', its a column in view 'dataView' defined as a 
result of CASE statement. I hypothesize that the result of CASE 
statement, regardless type reported by typeof(b) function, is not 
internally seen as integer/numeric, so the conversion of the other 
argument (i.e. '1' to 1) is not applied. To confirm this hypothesis we 
created additional column 'c' in the view. C's definition is the same as 
b's, except that we explicitly cast value returned by CASE statement to 
integer. The query now works as expected.


SELECT a, typeof(a), b, typeof(b), c, typeof(c) FROM dataView WHERE c='1'

output:
1,integer,1,integer,1,integer


In conclusion, I think that there is an error in SQLite, that computes 
wrong type affinity for values returned by CASE statement.


I am looking forward for explanation of SQLite behavior or fixing of the 
bug described above.


With best regards,
Tomasz Pawlak



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