MYSQLi не возвращает никаких строк, хотя он имеет допустимый синтаксис SQL и PHP

Question

Код SQL, который у меня есть, работает в HeidiSQL при многократном запуске, но когда я клонирую его в PHP и запускаю mysqli_query($db,$sql), он не работает.

Следующий код PHP/MySQL является действительным и отлично работает.

$sql = "select `ID`,`User` from (
            select * from 
                (SELECT 
                    `ID`,
                    `User`,
                    `BI`,
                    (@cnt:= @cnt + (`BI`/(select SUM(`BI`) from `ax`))) as `Entirety`
                from `ax` as `t`
                    CROSS JOIN (SELECT @cnt := 0) AS var
                order by `BI`
            ) d
            where `Entirety`>(@rnd)
            order by `BI`
        ) as `l`
        cross join (select (@rnd := rand()) as `RandomValue`) as var2
        limit 1;";

Затем я запускаю $ sql через

$result = mysqli_query($db,$sql);
$results = mysqli_fetch_array($result);

где $ db - действительное и открытое соединение с сервером MySQL. Но возвращение объекта, когда я делаю

print_r($result);

выходит как

mysqli_result Object ( 
    [current_field] => 0 
    [field_count] => 2 
    [lengths] => 
    [num_rows] => 0 
    [type] => 0
)

Я не хочу этого, и num_rows должен быть '1', как это показано в HeidiSQL, когда я его запускаю. Вот изображение HeidiSQL, показывающее результаты:

У кого-нибудь есть идеи?

КОЛОННЫ стола ax являются ID, User а также BI, В SQL он создает два временных столбца с именем "Целостность", который является счетчиком вероятности и столбцом с именем RandomValue который является rand() от 0 -> 1.

Единственная строка в этой таблице имеет значения (1,1,10). Хотя это 10, у него есть Entirety из "1", что означает, что он на 100% гарантированно будет выбран. Несмотря на это, ничего не выбрано.

Answer 1

Если вы добавите EXPLAIN к вашему запросу в PHP, mysqli сообщит вам об ошибке для одного подзапроса:

Impossible WHERE noticed after reading const tables

а также

const row not found

Это может много значить, но в этом контексте моя ставка на недопустимое значение в столбце. я точно уверен

select (@rnd := rand()) as `RandomValue`

выполняется после

where `Entirety`>(@rnd)

и поэтому не установлено.

Итак, почему это работает в вашем клиенте? Потому что клиент поддерживает связь. Таким образом, он завершается неудачно при первом выполнении запроса, но во второй раз он имеет сохраненное значение @rnd с первого выполнения. Вы можете проверить это, переименовав @rnd в @ rnd1 и выполнить запрос один раз.

Таким образом, решение (как указал Мэтт) сначала установить переменную @rnd, а затем использовать ее.

Вот связанный вопрос SO о пользовательских переменных в подзапросах:

Пользовательская переменная в подзапросе MySQL

От моего клиента:

Первый раз Объясните:

Второй раз Объясните:

Надеюсь это поможет.

Answer 2

Здесь интересно: если вы поменяете порядок перекрестного запроса для переменной @rnd:

select `ID`,`User` from 
    (select (@rnd := rand()) as `RandomValue`) as var2 CROSS JOIN 
    (
        select * from 
            (SELECT 
                `ID`,
                `User`,
                `BI`,
                (@cnt:= @cnt + (`BI`/(select SUM(`BI`) from `ax`))) as `Entirety`
            from `ax` as `t`
                CROSS JOIN (SELECT @cnt := 0) AS var
            order by `BI`
        ) d
        where `Entirety`>(@rnd)
        order by `BI`
    ) as `l`        
    limit 1;

... тогда я считаю, что это логически тот же запрос. Однако эта версия возвращает строку, которую вы ожидаете при запуске из PHP и стандартного клиента MySQL для меня, тогда как я могу воспроизвести отсутствие результата в PHP с вашим оригиналом.

NB: Я до сих пор не могу сказать вам, почему это происходит. Даже если это технически отвечает на ваш вопрос, я бы лично не пометил его как ответ, и оставил бы его для привлечения других ответов от людей, которые могут понять, что на самом деле происходит, по крайней мере, на день или два. Если он все еще будет открыт через 48 часов, я наложу на него награду, как мне любопытно.

Answer 3

Суть проблемы в том, что @rnd не инициализируется при оценке. Это в основном проблема порядка операций.

Оператор, кажется, работает в HeidiSQL, потому что оператор использует значение, которое было ранее присвоено @rndпо ранее выполненному заявлению. (Мы знаем, что определяемые пользователем переменные MySQL сохраняют свои значения при выполнении запросов в течение сеанса связи с базой данных MySQL).

Иметь RAND() оцениваемый один раз, в начале выполнения оператора, мы можем переместить присвоение вниз во встроенное представление самого низкого уровня, которое будет оцениваться первым. (Это работает из-за способа, которым MySQL оценивает запрос встроенного представления для материализации "производной таблицы" перед выполнением внешнего запроса.

Если бы у меня была гарантия, что BI столбец содержит положительные целочисленные значения, тогда я бы предпочел вернуть результат с помощью следующего предложения:

SELECT d.ID
     , d.User
  FROM ( SELECT t.ID
              , t.User
              , t.BI
              , @rt := @rt + t.BI AS rt
           FROM `ax` t
          CROSS
           JOIN ( SELECT @rt := 0
                       , @rnd := FLOOR(1 + RAND() * (r.tot – 1))
                    FROM ( SELECT SUM(q.BI) AS tot FROM `ax` q ) r
                ) s
          ORDER BY t.BI
       ) d
 WHERE d.rt > @rnd
 ORDER BY d.BI
 LIMIT 1

NB Справочное руководство по MySQL утверждает, что поведение, которое мы наблюдаем с пользовательскими переменными внутри оператора, не гарантируется и на него нельзя полагаться.

Но мы наблюдаем последовательное поведение, по крайней мере, в MySQL 5.1 и 5.5. Документация MySQL проясняет, что будущие выпуски MySQL могут свободно изменять это поведение.

(Если BI не является целым числом, или, если оно может быть нулевым или отрицательным, то метод, который оператор использует для определения "промежуточного итога" (rt) может работать неправильно. Я полагаю, что у запроса OP может быть похожая проблема, связанная с нецелыми или отрицательными значениями.)