Can a REAL type in SQLITE serve as an alias for the rowid like INTEGER, considering both are stored in 8 bytes?

Question

From the documentation:

Reals are stored as 8 bytes:

REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.

RowID are also 8 bytes / 64 bits

All rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table

And thus have a feature where you can take an INTEGER column and have it work as the RowID as well

If a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key".

Which is important if you consider:

The data for rowid tables is stored as a B-Tree structure containing one entry for each table row, using the rowid value as the key. This means that retrieving or sorting records by rowid is fast. Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value.

I am building a key-value store library in Qt/C++ with SQLITE as the backend, where any of the INTEGER, REAL, BLOB, TEXT datatypes are available as keys. With INTEGER and REAL being 64 bit, I'd like to take advantage of the rowid performance increase, considering they are both 8 byte.

SQLITE however only specifies that INTEGER can be used.

Questions:

1. Can REAL serve as an alias for the rowid?
2. If no, Why not exactly? Is it just an oversight from SQLITE developers, or is there a technical reason this can't be done?
3. If no, how would I go about doing this on Qt, where I convert the double into a long long int by way of its byte signature, and not by way of its value?

Thanks.

Answer 1

1. No

2. If a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid

   Besides, SQLite doesn't really have column types. Row IDs are always integers, no matter what. Even if the column was labelled as REAL, they would still be integer values. You can store any data type in any column in SQLite.

3. Convert it in your own program code, when reading rows from the database.

Answer 2

rowid is a special optimised case , unless you specifically use WITHOUT ROWID in the table definition (very seldom used) it always exists (with WITHOUT ROWID you must specify a PRIMARY KEY)

• see Clustered Indexes and the WITHOUT ROWID Optimization

considering both are stored in 8 bytes?

INTEGERS are stored as per:- INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.

Can REAL serve as an alias for the rowid?

No, as rowid is a special case. However, you could mimic the use a real and cast it to integer BUT it must resolve to a unique integer. (see example 2 and 3 and 4).

If no, Why not exactly? Is it just an oversight from SQLITE developers, or is there a technical reason this can't be done?

1. Because rowid's are a core aspect of the design of SQLite, like with most things you use them as intended (to uniquely identify a row, using them otherwise often results in angst).

   • The WITHOUT ROWID clause was a late addition.

2. Processing integers is faster than processing reals.

   • Rowid tables are distinguished by the fact that they all have a unique, non-NULL, signed 64-bit integer rowid that is used as the access key for the data in the underlying B-tree storage engine.

3. Integers will take less space 1-8 bytes as needed, Reals always use 8 bytes.

4. It's not an oversight, it's a design feature (see 5.). If you want a unique index according to REAL values then you are not prevented from using one (perhaps a WITHOUT ROWID table) but it would come with a performance hit of not using the fastest method of identifying rows.

5. The SQLite documentation includes

   • All of the complications above (and others not mentioned here) arise from the need to preserve backwards compatibility for the hundreds of billions of SQLite database files in circulation. In a perfect world, there would be no such thing as a "rowid" and all tables would following the standard semantics implemented as WITHOUT ROWID tables, only without the extra "WITHOUT ROWID" keywords. Unfortunately, life is messy. The designer of SQLite offers his sincere apology for the current mess. Rowid Tables

If no, how would I go about doing this on Qt, where I convert the double into a long long int by way of its byte signature, and not by way of its value?

You could CAST (see examples 2 and 3) BUT the result MUST be a unique INTEGER (if a PRIMARY KEY or UNIQUE index). Of course you could have an additional index according to the REAL value.

Here's some examples that demonstrate some of the above:-

DROP TABLE IF EXISTS example1;
DROP TABLE IF EXISTS example2;
DROP TABLE IF EXISTS example3;
DROP TABLE IF EXISTS example4;
/* rowid always exists for table unless WITHOUT ROWID table */
/* note cannot specify rowid value */
CREATE TABLE IF NOT EXISTS example1 (col1);
INSERT INTO example1 VALUES('x'),('y'),('z');
SELECT *,rowid, oid,_rowid_ FROM example1;

/* Ooops not an alias as INTEGER not specified , but rowid exists*/
CREATE TABLE IF NOT EXISTS example2 (col1, rowid_alias, PRIMARY KEY(rowid_alias));
INSERT INTO example2 VALUES('a',null),('b',CAST(10.4567 AS INTEGER)),('c',null);
SELECT *,rowid, oid,_rowid_ FROM example2;

/* rowid_alias is an alias of the rowid */
CREATE TABLE IF NOT EXISTS example3 (col1, rowid_alias INTEGER, PRIMARY KEY(rowid_alias));
INSERT INTO example3 VALUES('a',null),('b',CAST(10.4567 AS INTEGER)),('c',null);
SELECT *,rowid, oid,_rowid_ FROM example3;


/* sort of mimic rowid using real */
CREATE TABLE IF NOT EXISTS example4 (mimic_rowid);
INSERT OR IGNORE INTO example4 VALUES 
    ((coalesce((SELECT max(mimic_rowid) FROM example4),0.1234) + 1.11))
;
INSERT OR IGNORE INTO example4 VALUES 
    ((coalesce((SELECT max(mimic_rowid) FROM example4),0.1234) + 1.11))
;
SELECT *,rowid FROM example4;

DROP TABLE IF EXISTS example1;
DROP TABLE IF EXISTS example2;
DROP TABLE IF EXISTS example3;
DROP TABLE IF EXISTS example4;

Running the above:-

The first result shows that the rowid exists even if not aliased:-

The second/third results shows that to alias the rowid it must be INTEGER PRIMARY KEY (implicitly i.e. specifying the PRIMARY KEY at the table level rather than column level) :-

• First (example 2) result NOT an alias :-

• Second (example 3) aliased :-

  • Note CAST used to cast REAL to INTEGER for 2nd insert
  • as can be seen 3rd insert generates the next rowid using the max existing rowid + 1 (NOT GUARANTEED to be + 1, but typically so)

The last example sort of replicates (trigger would do it automatically) mimicking rowid but for REAL :-

• OR IGNORE will skip insertion rather than fail if not unique

Some links:-

• Rowid tables
• Without Rowid tables