MySQL Bugs: #97251: use order by Cause data exception

Bug #97251	use order by Cause data exception
Submitted:	16 Oct 2019 9:56	Modified:	17 Oct 2019 12:17
Reporter:	NULL ! NULL !	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	order by data exception

Description:
when i use order by select sub query contains allied form like  "select '1' as test",
final result that test value is ''

 

How to repeat:
SQL:
SELECT 
  tmp.* 
FROM
  (SELECT 
    * 
  FROM
    (SELECT 
      a1.id budgetinfoid,
      c.fnayear,
      a.id,
      a.id budgetorganizationid,
      '3' organizationtype,
      a1.budgetperiods,
      a.id yfpId,
      a.id yfsId,
      a.id spzId,
      '' sum_budgetaccount,
      '3' sqlTypeFlag,
      a.dsporder showorder,
      a.lastname orgName,
      '个人 ' orgTypeName 
    FROM
      HrmResource a 
      LEFT JOIN fnabudgetinfo a1 
        ON a.id = a1.budgetorganizationid 
        AND a1.organizationtype = 3 
        AND a1.status = 1 
      LEFT JOIN FnaYearsPeriods c 
        ON a1.budgetperiods = c.id 
    WHERE 1 = 1 
      AND (
        (
          EXISTS 
          (SELECT 
            1 
          FROM
            fnabudgetinfo fbi 
          WHERE fbi.organizationtype = 3 
            AND fbi.budgetorganizationid = a.id)
        ) 
        OR (a.status IN (0, 1, 2, 3))
      ) 
      AND a.departmentid = 1) t11 
  ORDER BY (t11.fnayear * - 1) ASC,
    t11.organizationtype ASC,
    t11.showorder ASC,
    t11.orgName ASC,
    t11.budgetorganizationid ASC) tmp 

result:

budgetinfoid  fnayear      id  budgetorganizationid  organizationtype  budgetperiods   yfpId   yfsId   spzId  sum_budgetaccount  sqlTypeFlag  showorder  orgName                      orgTypeName  
------------  -------  ------  --------------------  ----------------  -------------  ------  ------  ------  -----------------  -----------  ---------  ---------------------------  -------------
      (NULL)  (NULL)     1055                  1055                           (NULL)    1055    1055    1055                     3                 1055  llq004                       个人       
      (NULL)  (NULL)     1056                  1056                           (NULL)    1056    1056    1056                     3                 1056  llq005                       个人       
      (NULL)  (NULL)     1057                  1057                           (NULL)    1057    1057    1057                     3                 1057  llq006                       个人       
      (NULL)  (NULL)     1058                  1058                           (NULL)    1058    1058    1058                     3                 1058  llq007                       个人       
      (NULL)  (NULL)     1886                  1886                           (NULL)    1886    1886    1886                     3                 1886  LLQ001                       个人       
      (NULL)  (NULL)     1887                  1887                           (NULL)    1887    1887    1887                     3                 1887  LLQ002                       个人       
      (NULL)  (NULL)     1888                  1888                           (NULL)    1888    1888    1888                     3                 1888  LLQ003                       个人       
      (NULL)  (NULL)     2875                  2875                           (NULL)    2875    2875    2875                     3                 2875  cccc                         个人       
      (NULL)  (NULL)     2879                  2879                           (NULL)    2879    2879    2879                     3                 2879  ddasdasdasdccc               个人

the organizationtype value is losted

but i cast '3' as signed ,it's normal again.

Hello Mr. NULL,

Thank you for your bug report.

However, I fail to see what is it that you are reporting.

The language that you are using and the description of the problem is totally undecipherable to me. Can you, please, try to describe more precisely what is it that you find wrong with your query.

Next, in order to process further the bug, we would need a complete test case, which should include all tables that are used in the query.

Thanks in advance.

Next, you have a serious error in your query.

You use 'organizationtype' as character, while you are comparing it with an integer. Fixed that, for the starters !!!!!!!!!!

Hi ,Mr.Milivojevic

Thank you for your answer

I have uploaded the data related to the problem to the system in the form of an attachment, and can run the test sql on MySQL 5.7.27. I am not sure whether it is the problem caused by MySQL's bug or its SQL logic.

test data info

Attachment: test20191016.zip (application/zip, text), 1.96 MiB.

Hi,

I have ran your test case and I found out that the error is in your SQL.