getResultByName returns a number for something that was inserted into a STRING column and was a string

VERIFIED INVALID

Status

()

Toolkit
Storage
VERIFIED INVALID
6 years ago
6 years ago

People

(Reporter: mkaply, Unassigned)

Tracking

Trunk
Points:
---

Firefox Tracking Flags

(Not tracked)

Details

(Reporter)

Description

6 years ago
We're inserting a mix of numbers and strings into a table like this:

  var insert = searchHistoryDB().createStatement("INSERT INTO searchterms " +
      "(searchterm, visited) VALUES (:searchterm, :visited)");
  debug("typeof search term " + (typeof searchTerm));
  insert.params["searchterm"] = searchTerm.toString();
  insert.params["visited"] = new Date().toISOString();
  insert.executeAsync();

The table looks like this:

"id INTEGER PRIMARY KEY autoincrement, searchterm STRING, visited STRING");

When we retrieve the items out of the table, like this:

  var sel = searchHistoryDB().createStatement("SELECT " +
      "searchterm FROM searchterms GROUP BY searchterm ORDER BY id DESC LIMIT :amount");
  sel.params["amount"] = amount;
  sel.executeAsync(new sqlCallback(function (rows)
  {
    let terms = []
    for each (let row in rows)
    {
      terms.push(row.getResultByName("searchterm"));
    }
    resultCallback(terms);
  }, errorCallback));



row.getResultByName("searchterm") is sometimes a number and sometimes a string.

I would expect that when I put a string into a STRING column, when I get the value out, it will still be a string.

I realize that sqlite doesn't support true typing, but this behavior is just wrong.

Comment 1

6 years ago
> this behavior is just wrong.

ditto

"into a table" you mean into the Mozilla API or into sqlite?
1. it's important whether Mozilla or sqlite here mess up
2. and whether it's while storing or while reading
fwiw, using the same db field for multiple types sounds like calling for issues in your software and it's usually the wrong idea.
Assignee: Jan.Varga → nobody
Component: SQL → Storage
Product: Core → Toolkit
QA Contact: owen.marshall+bmo → storage
hm, and I think exactly due to the fact SQLite doesn't enforce a type for the columns, our behavior here is correct, we return what was stored. So imo this is wontfix.
(Reporter)

Comment 4

6 years ago
No, you're not returning what was stored.

You're returning number when I stored a string.

We're not using the same database field for multiple types.

The field is used for storing personal search history. A user can type a mix of numbers and strings when they are searching. So it's a string that happens to be a number.

All of their searches ("1234", "flights to dallas") are stored in the same table in the same way and they enter that table as Javascript strings into a STRING column.

When they are queried from the database, they come out of the database as a mix of Javascript numbers and Javascript strings.

I realize MySQL doesn't enforce typing, but there should be a way to get out what I put in.

Maybe using nsIVariant is the wrong choice here if it is too aggressive in converting things to numbers.
Use TEXT, not STRING

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

Note that a declared type of "FLOATING POINT" would give INTEGER affinity, not REAL affinity, due to the "INT" at the end of "POINT". And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.
(Reporter)

Comment 6

6 years ago
> And the declared type of "STRING" has an affinity of NUMERIC, not TEXT.

That seems odd.

The table on this page:

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

doesn't mention STRING for type affinity at all.

Where is it documented that STRING has a NUMERIC affinity?
I cut and pasted that bit from under the table in section 2.2.
(Reporter)

Comment 8

6 years ago
So this is our screw up.

There is no STRING type.  We should have used TEXT.

If it happens with TEXT, we'll open new bug.

Nothing to see here.
Status: NEW → RESOLVED
Last Resolved: 6 years ago
Resolution: --- → INVALID

Comment 9

6 years ago
v
Status: RESOLVED → VERIFIED
You need to log in before you can comment on or make changes to this bug.