Author Topic: SQLite Can't add 1+1?  (Read 584 times)

Nigel_Simpson

  • EA Novice
  • *
  • Posts: 13
  • Karma: +0/-0
    • View Profile
SQLite Can't add 1+1?
« on: May 04, 2022, 08:44:00 pm »
I'm new to SQLite SQL so please can someone help explain why this SQL:

select 1 + 1 as two, typeof(1) as onetype, typeof(1 + 1) as twotype
from t_object
limit 1

returns 11, integer, text

Is there a very different approach to standard arithmetic in this database?

Richard Freggi

  • EA User
  • **
  • Posts: 390
  • Karma: +14/-7
    • View Profile
Re: SQLite Can't add 1+1?
« Reply #1 on: May 04, 2022, 08:48:45 pm »
Read up on sqlite.org, your syntax is off (SQLite is ANSI SQL, your query is not)

Nigel_Simpson

  • EA Novice
  • *
  • Posts: 13
  • Karma: +0/-0
    • View Profile
Re: SQLite Can't add 1+1?
« Reply #2 on: May 04, 2022, 09:01:10 pm »
Read up on sqlite.org, your syntax is off (SQLite is ANSI SQL, your query is not)
Please could you be more specific? What is the syntax I have done incorrectly?

Richard Freggi

  • EA User
  • **
  • Posts: 390
  • Karma: +14/-7
    • View Profile
Re: SQLite Can't add 1+1?
« Reply #3 on: May 05, 2022, 12:57:47 am »
Your query runs on my native SQLite (I don't have EA V16)

By ANSI SQL I meant

SELECT 1+1 AS "Two", typeof(1) AS "Type1", typeof(1+1) AS "Type2"

|Two|Type1  |Type2  |
|----|--------|---------|
|2   |integer |integer |

SQLite defaults to TEXT datatype whenever possible, so it may be the EA 16 SQLite driver that's playing games on you
« Last Edit: May 05, 2022, 01:02:38 am by Richard Freggi »

Nigel_Simpson

  • EA Novice
  • *
  • Posts: 13
  • Karma: +0/-0
    • View Profile
Re: SQLite Can't add 1+1?
« Reply #4 on: May 05, 2022, 02:03:19 am »
Your query runs on my native SQLite (I don't have EA V16)

By ANSI SQL I meant

SELECT 1+1 AS "Two", typeof(1) AS "Type1", typeof(1+1) AS "Type2"

|Two|Type1  |Type2  |
|----|--------|---------|
|2   |integer |integer |

SQLite defaults to TEXT datatype whenever possible, so it may be the EA 16 SQLite driver that's playing games on you

OK thanks for the clarification. I still have the problem if I just issue:

SELECT 1 + 1

I'm sure it will be the EA16 driver as you say. I've reported a bug.

Richard Freggi

  • EA User
  • **
  • Posts: 390
  • Karma: +14/-7
    • View Profile
Re: SQLite Can't add 1+1?
« Reply #5 on: May 05, 2022, 02:50:18 pm »
It's treating "1" as TEXT datatype, so it's concatenating 2 strings