Subquery in INSERT executed differently in PDO than SQL

I want to insert a new dataset into a MySQL table tab with external data, but also with data from another table otherTab using the others’ table primary key and another condition. However, it could be that the requested row simply does not exist (anymore) or the result set is empty due to a mismatch in the other supplied data. Then, the original INSERT should fail. All columns are forbidden to be NULL.

My first attempt was:

INSERT INTO tab (id, extid1, extid2, value)
SELECT 1,
       (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT'),
       (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG'),
       1234

but the problem with it is that a returned empty result set is cast to the type of the column in tab, leading to a 0 as entry data.

The query shall be efficient and avoid unnecessary querying. This is how I achieve it with four subqueries:

INSERT INTO tab (id, extid1, extid2, value)
SELECT 1,
       (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT'),
       (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG'),
       1234
WHERE EXISTS (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT')
  AND EXISTS (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG')

I tried with other constructs, e.g. (SELECT IFNULL(SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT', NULL)) to enforce NULL or even a string into the target column, but it also gets casted to a 0 or some value instead.

Here is the code for dbFiddle:

code

CREATE TABLE `tab` (
  `id` int NOT NULL,
  `seUuid4` binary(16) NOT NULL,
  `rxUuid4` binary(16) NOT NULL,
  `text` varchar(16)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `otherTab` (
  `uuid4` binary(16) NOT NULL,
  `lgUuid4` binary(16) NOT NULL,
  `data` varchar(16)
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `otherTab`
  ADD PRIMARY KEY(`uuid4`);
ALTER TABLE `tab`
  ADD CONSTRAINT `tab_ibfk_1` FOREIGN KEY (`rxUuid4`) REFERENCES `otherTab` (`uuid4`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `tab_ibfk_2` FOREIGN KEY (`seUuid4`) REFERENCES `otherTab` (`uuid4`) ON DELETE RESTRICT ON UPDATE RESTRICT;

INSERT INTO `otherTab` (uuid4, lgUuid4, data) VALUES
(UNHEX("22224444aaaa49c782408b2fe8c4dee0"), UNHEX("00001234aaaa4444aaaa432187654321"), "JPG"),
(UNHEX("11113333aaaa49c782408b2fe8c4dee0"), UNHEX("12340000bbbb6666bbbb432187654321"), "TXT");

INSERT INTO tab (id, seUuid4, rxUuid4, text)
SELECT
    1,
    (SELECT uuid4 FROM otherTab WHERE lgUuid4 = UNHEX('00001234aaaa4444aaaa432187654321') AND data = 'JPK' LIMIT 0,1),
    (SELECT uuid4 FROM otherTab WHERE lgUuid4 = UNHEX('12340000bbbb6666bbbb432187654321') AND data = 'TXT' LIMIT 0,1),
    'some text'

This interestingly works exactly as expected: Note the JPK instead of JPG. I verified my code and the PDO prepared statement fires out exactly the same command, but it gets inserted as INSERT INTO tab (id, seUuid4, rxUuid4) VALUES (1, 0x00000000000000000000000000000000, 0x00000000000000000000000000000000, 'datatext'); while the SQL client and phpMyadmin deliver the expected cannot insert null error message.

I could not find anything in the PDO options. If it helps, I use PDO with emulated prepared statements, but also tried without with no change.

PS: I posted already at dba.stackexchange.com/posts/276868

Answer

You could use a subquery:

INSERT INTO tab (id, extid1, extid2, value)
SELECT 
FROM (
    SELECT 
       1 id,
       (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT') extid1,
       (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG') extid2
       1234 value
) t
WHERE extid1 IS NOT NULL and extid2 IS NOT NULL

Or, probably better yet, you can CROSS JOIN the two subqueries:

INSERT INTO tab (id, extid1, extid2, value)
SELECT 1, t1.id, t2.id, 1234
FROM (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT') t1,
CROSS JOIN (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG') t2

Actually, since you are reurning the same value that you are filtering on, two exists subqueries are probably sufficient:

INSERT INTO tab (id, extid1, extid2, value)
SELECT t.*
FROM (SELECT 1 id, 12 extid1, 34 extid2, 1234 value) t
WHERE EXISTS (SELECT 1 FROM otherTab t1 WHERE t1.id = t.extid1 AND t1.data = 'TXT')
  AND EXISTS (SELECT 1 FROM otherTab t1 WHERE t1.id = t.extid2 AND t1.data = 'JPG')

Advertisement

Answer