So, I know this is a very generic error, but I don't have any better clue to what is happening on my query nor can I explain it better than this, thanks to MySQL not giving a **** to UX, so bear with me.
I'm using PHP 7.2.1 and MySQL 5.7.
I have this query:
SELECT
sp.*
FROM
`status` s
LEFT JOIN status_pedido sp ON sp.status_pedido_id = s.status_pedido_id
WHERE
s.status_id = (
SELECT
`status`.status_id
FROM
pedido_item_status p1
LEFT JOIN `status` ON `status`.status_id = p1.status_id
LEFT JOIN pedido_item ON pedido_item.pedido_item_id = p1.pedido_item_id
INNER JOIN ( SELECT MAX( pi.cadastrado ) AS maxcadastro FROM pedido_item_status pi GROUP BY pi.pedido_item_id ) p2 ON ( p1.cadastrado = p2.maxcadastro )
AND p1.excluido IS NULL
WHERE
p1.pedido_id = 15720
ORDER BY
`status`.sta_ordem ASC
LIMIT 1
)
It works fine on Navicat or Heidi, and returns me exactly 1 result. But when I execute on PHP mysqli->query returns empty rows:
object(mysqli_result)#135 (5) { ["current_field"]=> int(0) ["field_count"]=> int(11) ["lengths"]=> NULL ["num_rows"]=> int(0) ["type"]=> int(0) }
EXPLAIN on Navicat returned the following:
+----+-------------+-------------+--------+-------------------+-------------+---------+---------------------------+-------+---------------------------------------------------------------------+--+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+----+-------------+-------------+--------+-------------------+-------------+---------+---------------------------+-------+---------------------------------------------------------------------+--+
| 1 | PRIMARY | s | const | PRIMARY | PRIMARY | 4 | const | 1 | | |
| 1 | PRIMARY | sp | const | PRIMARY | PRIMARY | 4 | const | 1 | | |
| 2 | SUBQUERY | p1 | ref | idx_1,idx_2,idx_3 | idx_1 | 10 | const,const | 5 | Using index condition; Using where; Using temporary; Using filesort | |
| 2 | SUBQUERY | status | eq_ref | PRIMARY | PRIMARY | 4 | emgraf2.p1.status_id | 1 | | |
| 2 | SUBQUERY | pedido_item | eq_ref | PRIMARY | PRIMARY | 4 | emgraf2.p1.pedido_item_id | 1 | Using index | |
| 2 | SUBQUERY | <derived3> | ref | <auto_key0> | <auto_key0> | 5 | emgraf2.p1.cadastrado | 10 | Using index | |
| 3 | DERIVED | pi | ALL | idx_2,idx_3 | | | | 18750 | Using temporary; Using filesort | |
+----+-------------+-------------+--------+-------------------+-------------+---------+---------------------------+-------+---------------------------------------------------------------------+--+
While on PHP it returns:
array(10) {
["id"]=>
string(1) "1"
["select_type"]=>
string(7) "PRIMARY"
["table"]=>
NULL
["type"]=>
NULL
["possible_keys"]=>
NULL
["key"]=>
NULL
["key_len"]=>
NULL
["ref"]=>
NULL
["rows"]=>
NULL
["Extra"]=>
string(51) "Impossible WHERE noticed after reading const tables"
}
array(10) {
["id"]=>
string(1) "2"
["select_type"]=>
string(8) "SUBQUERY"
["table"]=>
string(2) "p1"
["type"]=>
string(3) "ref"
["possible_keys"]=>
string(17) "idx_1,idx_2,idx_3"
["key"]=>
string(5) "idx_1"
["key_len"]=>
string(2) "10"
["ref"]=>
string(11) "const,const"
["rows"]=>
string(1) "5"
["Extra"]=>
string(67) "Using index condition; Using where; Using temporary; Using filesort"
}
array(10) {
["id"]=>
string(1) "2"
["select_type"]=>
string(8) "SUBQUERY"
["table"]=>
string(6) "status"
["type"]=>
string(6) "eq_ref"
["possible_keys"]=>
string(7) "PRIMARY"
["key"]=>
string(7) "PRIMARY"
["key_len"]=>
string(1) "4"
["ref"]=>
string(20) "emgraf2.p1.status_id"
["rows"]=>
string(1) "1"
["Extra"]=>
NULL
}
array(10) {
["id"]=>
string(1) "2"
["select_type"]=>
string(8) "SUBQUERY"
["table"]=>
string(11) "pedido_item"
["type"]=>
string(6) "eq_ref"
["possible_keys"]=>
string(7) "PRIMARY"
["key"]=>
string(7) "PRIMARY"
["key_len"]=>
string(1) "4"
["ref"]=>
string(25) "emgraf2.p1.pedido_item_id"
["rows"]=>
string(1) "1"
["Extra"]=>
string(11) "Using index"
}
array(10) {
["id"]=>
string(1) "2"
["select_type"]=>
string(8) "SUBQUERY"
["table"]=>
string(10) "<derived3>"
["type"]=>
string(3) "ref"
["possible_keys"]=>
string(11) "<auto_key0>"
["key"]=>
string(11) "<auto_key0>"
["key_len"]=>
string(1) "5"
["ref"]=>
string(21) "emgraf2.p1.cadastrado"
["rows"]=>
string(2) "10"
["Extra"]=>
NULL
}
array(10) {
["id"]=>
string(1) "3"
["select_type"]=>
string(7) "DERIVED"
["table"]=>
string(2) "pi"
["type"]=>
string(3) "ALL"
["possible_keys"]=>
string(11) "idx_2,idx_3"
["key"]=>
NULL
["key_len"]=>
NULL
["ref"]=>
NULL
["rows"]=>
string(5) "18750"
["Extra"]=>
string(31) "Using temporary; Using filesort"
}
As far as I understood from similar questions, this has something to do with the fact that the client keeps the connection and executes the query after analysing the entire query, while PHP just stops when a internal WHERE seems to return 0 rows. But I can't figure out how I can fix that on my query, since I'm not setting any variable on SQL, just making subqueries.
There is another problem: the exact same query runs on another code block, and I can't understand why is that. Here is the problematic code:
$sql_sta = "SELECT pis.pedido_item_status_id, pi.pedido_item_id, pi.status_id, p.pedido_id, psp.pedido_status_pedido_id, sp.stp_ordem
FROM pedido_item_status pis
LEFT JOIN pedido_item pi ON pis.pedido_item_id = pi.pedido_item_id
LEFT JOIN pedido p ON pi.pedido_id = p.pedido_id
LEFT JOIN status_pedido sp ON sp.status_pedido_id = p.status_pedido_id
LEFT JOIN pedido_status_pedido psp ON (psp.status_pedido_id = sp.status_pedido_id AND psp.pedido_id = p.pedido_id)
WHERE pis.pedido_item_status_id = {$_GET['del']}
LIMIT 1";
$res_sta = ClassDb::query($sql_sta);
$status_del = $res_sta->fetch_assoc();
$sql_ped = "SELECT sp.* FROM `status` s
LEFT JOIN status_pedido sp ON sp.status_pedido_id = s.status_pedido_id
WHERE s.status_id = (
SELECT `status`.status_id FROM pedido_item_status p1
LEFT JOIN `status` ON `status`.status_id = p1.status_id
LEFT JOIN pedido_item ON pedido_item.pedido_item_id = p1.pedido_item_id
INNER JOIN ( SELECT pi.pedido_id, MAX( pi.cadastrado ) AS maxcadastro FROM pedido_item_status pi GROUP BY pi.pedido_item_id ) p2
ON ( p1.cadastrado = p2.maxcadastro ) AND p1.excluido IS NULL
WHERE p1.pedido_id = 15720
ORDER BY `status`.sta_ordem ASC
LIMIT 1
)";
$res_ped = ClassDb::query($sql_ped);
And here is the working code:
$sql_sta = "SELECT sp.* FROM pedido_item pi
LEFT JOIN pedido p ON pi.pedido_id = p.pedido_id
LEFT JOIN status_pedido sp ON sp.status_pedido_id = p.status_pedido_id
WHERE pi.pedido_item_id = {$_POST['pedido_item_id']}";
$res_sta = ClassDb::query($sql_sta);
$status_antigo = $res_sta->fetch_assoc();
$sql_ped = "SELECT sp.* FROM `status` s
LEFT JOIN status_pedido sp ON sp.status_pedido_id = s.status_pedido_id
WHERE s.status_id = (
SELECT `status`.status_id FROM pedido_item_status p1
LEFT JOIN `status` ON `status`.status_id = p1.status_id
LEFT JOIN pedido_item ON pedido_item.pedido_item_id = p1.pedido_item_id
INNER JOIN ( SELECT MAX( pi.cadastrado ) AS maxcadastro FROM pedido_item_status pi GROUP BY pi.pedido_item_id ) p2
ON ( p1.cadastrado = p2.maxcadastro ) AND p1.excluido IS NULL
WHERE p1.pedido_id = {$_POST['pedido_id']}
ORDER BY `status`.sta_ordem ASC
LIMIT 1
)";
$res_ped = ClassDb::query($sql_ped);
$status_atual = $res_ped->fetch_assoc();
The only differences between them is the fact I run a different query first to retrieve pedido_id, I tried to change the variable to a static value and comment the first query, the output is still the same, and also, the working code run after a form simple POST, after I INSERT a row, while the problematic run from an ajax GET request, after I UPDATE a row to soft delete it (setting column excluido to NOW(), consider it as an deleted_at from Laravel). Tried running the querys manually on Navicat, worked just fine.
Since the post is already too long, and the tables are big and complex, just assume the results are there, all the columns exist, the query execute well on Navicat and returns a result, and also returns a result on the working code block with the same query.
Edit1: There is no PHP error or exception, nor MYSQLI error, I already set them to display. The query just return empty results and the code flows normally.
Edit 2: Tried to run on MySQL Shell directly, the result is the same from Navicat or Heidi:
+------------------+-----------+----------------+-----------+----------+------------+------------+---------+----------+-----------+-----------+
| status_pedido_id | stp_nome | stp_observacao | stp_ordem | excluido | cadastrado | atualizado | stp_cor | stp_icon | stp_alert | status_id |
+------------------+-----------+----------------+-----------+----------+------------+------------+---------+----------+-----------+-----------+
| 7 | Cancelado | NULL | 8 | NULL | NULL | NULL | NULL | NULL | NULL | 9 |
+------------------+-----------+----------------+-----------+----------+------------+------------+---------+----------+-----------+-----------+
1 row in set (0.0269 sec)
Well, I just discovered the reason for the "error". In fact, it wasn't an error at all, just me being dumb.
On the PHP code, I update one row to set excluido to NOW(), so one of the selected rows is soft deleted, that caused the query to return 0 rows, but since autocommit was turned to FALSE and by the time I dumped the query I didn't commit my queries, the update was rolled back and I run the query on Navicat without any soft deleted rows, that's why it worked. Now, commiting the query before debugging, the row is soft deleted and Navicat/Heidi also returns 0 rows.
I still haven't figured out why it's returning 0 rows, but I think this issue belongs to a different question, not this one, so I'm going to make a new question if I can't solve the problem by myself. I'm just answering my own question to close this thread and not waste your precious time.
Thanks to everyone who tried to help me in the comments.