Risk of Hardware Dependant SQL Code when Using Binary Data
Caution! SQL using binary data may not return the expected values. When using binary data types, it is possible to create hardware dependant SQL.
To demonstrate “hardware dependant SQL when using binary data,” create a table with data as follows:
CREATE TABLE t1 (code INT);
INSERT INTO t1 VALUES (...);
For Ingres 2.6 (su4.us5) on Sparc hardware or other most-significant-byte-first big-endian system, the query:
SELECT LENGTH(CHAR(BYTE(code+48))),
RIGHT(CHAR(BYTE(code+48)),1)
FROM t1
returns:
+------+------+
| col1 | col2 |
+------+------+
| 4 | 0 |
+------+------+
Check the hex presentation of the value BYTE(code+48):
SELECT LENGTH(CHAR(BYTE(code+48))),
RIGHT(CHAR(BYTE(code+48)),1),
HEX(BYTE(code+48))
FROM t1
This query returns:
+------+------+--------+
| col1 | col2 | col3 |
+------+------+--------+
| 4 | 0 |00000030|
+------+------+--------+
For Ingres 2006 (a64.lnx) on an Intel x86 (increasing numeric significance with increasing memory addresses--little-endian) system, the same queries return:
+-------------+--------+
| col1 | col2 |
+-------------+--------+
| 1 | |
+-------------+--------+
+-------------+--------+----------------+
| col1 | col2 | col3 |
+-------------+--------+----------------+
| 1 | |3000000000000000|
+-------------+--------+----------------+