{"action":"opened","issue":{"url":"https://api.github.com/repos/tarantool/tarantool/issues/3650","repository_url":"https://api.github.com/repos/tarantool/tarantool","labels_url":"https://api.github.com/repos/tarantool/tarantool/issues/3650/labels{/name}","comments_url":"https://api.github.com/repos/tarantool/tarantool/issues/3650/comments","events_url":"https://api.github.com/repos/tarantool/tarantool/issues/3650/events","html_url":"https://github.com/tarantool/tarantool/issues/3650","id":354921829,"node_id":"MDU6SXNzdWUzNTQ5MjE4Mjk=","number":3650,"title":"sql: make <BLOB> accept only binary format","user":{"login":"Korablev77","id":22144201,"node_id":"MDQ6VXNlcjIyMTQ0MjAx","avatar_url":"https://avatars3.githubusercontent.com/u/22144201?v=4","gravatar_id":"","url":"https://api.github.com/users/Korablev77","html_url":"https://github.com/Korablev77","followers_url":"https://api.github.com/users/Korablev77/followers","following_url":"https://api.github.com/users/Korablev77/following{/other_user}","gists_url":"https://api.github.com/users/Korablev77/gists{/gist_id}","starred_url":"https://api.github.com/users/Korablev77/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Korablev77/subscriptions","organizations_url":"https://api.github.com/users/Korablev77/orgs","repos_url":"https://api.github.com/users/Korablev77/repos","events_url":"https://api.github.com/users/Korablev77/events{/privacy}","received_events_url":"https://api.github.com/users/Korablev77/received_events","type":"User","site_admin":false},"labels":[{"id":551526822,"node_id":"MDU6TGFiZWw1NTE1MjY4MjI=","url":"https://api.github.com/repos/tarantool/tarantool/labels/ansi%20sql","name":"ansi sql","color":"b60205","default":false},{"id":822133111,"node_id":"MDU6TGFiZWw4MjIxMzMxMTE=","url":"https://api.github.com/repos/tarantool/tarantool/labels/sql%20static%20types","name":"sql static types","color":"cdbafc","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-08-28T22:06:17Z","updated_at":"2018-08-28T22:06:17Z","closed_at":null,"author_association":"COLLABORATOR","body":"Currently, \\<BLOB> is really \\<SCALAR> under the hood, so it actually accepts any type. For example:\r\n```\r\nCREATE TABLE t1(id INT PRIMARY KEY, a BLOB);\r\nINSERT INTO t1 VALUES(1, 'x');\r\nINSERT INTO t1 VALUES(2, 3);\r\nINSERT INTO t1 VALUES(3, 3.0432);\r\n```\r\nMost of other DBs allow to store in BLOB only real binary format. Examples:\r\n\r\n- db2\r\n```\r\ndb2 => CREATE TABLE t1(b BLOB)\r\nDB20000I  The SQL command completed successfully.\r\n\r\ndb2 => INSERT INTO t1 VALUES (1)\r\nDB21034E  The command was processed as an SQL statement because it was not a \r\nvalid Command Line Processor command.  During SQL processing it returned:\r\nSQL0408N  A value is not compatible with the data type of its assignment \r\ntarget. Target name is \"B\".  SQLSTATE=42821\r\n\r\ndb2 => INSERT INTO t1 VALUES ('abc')\r\nDB21034E  The command was processed as an SQL statement because it was not a \r\nvalid Command Line Processor command.  During SQL processing it returned:\r\nSQL0408N  A value is not compatible with the data type of its assignment \r\ntarget. Target name is \"B\".  SQLSTATE=42821\r\n\r\ndb2 => INSERT INTO t1 VALUES(CAST('99' AS BLOB))\r\nDB20000I  The SQL command completed successfully.\r\n```\r\n\r\n- Oracle\r\n```\r\nCREATE TABLE t1(a BLOB)\r\nINSERT INTO t1 VALUES (1)\r\nORA-00932: inconsistent datatypes: expected BLOB got NUMBER\r\n```\r\nOrdinar string is OK:\r\n`INSERT INTO t1 VALUES('abc') `\r\n\r\n- Postgres lacks BLOB type, but still features \\<BYTEA>:\r\n```\r\nCREATE TABLE t1(a BYTEA);\r\nINSERT INTO t1 VALUES (1);\r\n42804: column \"a\" is of type bytea but expression is of type integer\r\n```\r\nNevertheless, it accepts strings:\r\n`INSERT INTO t1 VALUES('abc');`\r\n\r\nMoreover, we should ban implicit conversions from BLOB to other types, including string ones.\r\n- db2\r\n```\r\nCREATE TABLE t1(a CHAR(10))\r\nINSERT INTO t1 VALUES ('9')\r\nCREATE TABLE t2(b BLOB)\r\nINSERT INTO t2 VALUES (CAST('9' AS BLOB))\r\n\r\ndb2 => SELECT * FROM t1, t2 WHERE a = b\r\nSQL0401N  The data types of the operands for the operation \"=\" are not \r\ncompatible or comparable.  SQLSTATE=42818\r\n```\r\n- Postgres\r\n\r\n```\r\nCREATE TABLE t1(a CHAR(10));\r\nINSERT INTO t1 VALUES ('9');\r\nCREATE TABLE t2(b BYTEA);\r\nINSERT INTO t2 VALUES ('9');\r\nSELECT * FROM t1, t2 WHERE a = b;\r\n42883: operator does not exist: character = bytea\r\n```\r\n"}}
{"actor":{"display_login":"Korablev77"}}