Ở bài trước, mình có nói sơ qua về product mà mình đang tham gia rồi, bạn có thể đọc lại để hiểu thêm 1 chút. Bài này là về 1 bug khác ở 1 feature khác, mình chỉ nói tập trung về cái mình thấy thú vị và cái mình học được ở câu SQL mà khách hàng sử dụng. Bug chưa fix được nên không có bài học gì về việc fix bug ở đây cả.

I. Giới thiệu về feature

• Đây là tính năng cho phép người dùng lấy data từ DB bên ngoài, rồi tạo spreadsheet trong phần mềm để thực hiện các phép tính sau đó.
• Vì table của DB có thể rất lớn, data nhiều và khách hàng cần phải có cách để filter lấy những giá trị mà khách hàng muốn thôi.

Đây là cách product hoạt động:

• Cho phép khách hàng viết câu SQL (cha), sau đó đọc câu lệnh SQL, những column được định nghĩa trong câu SQL chuyển thành các column trong Spreadsheet.
• Cho phép khách hàng viết tiếp những custom SQL (con) cho từng column –> đây chính là filter.
• Khi khách hàng run câu SQL cha thì câu SQL con sẽ được run trước, rồi show value ra dạng dialog, user sẽ select values mà user mong muốn. Sau đó những values này được gắn vào câu SQL cha chỗ WHERE clause.

II. Bug xảy ra khi nào

Khách hàng report 1 bug chỗ câu SQL con, họ viết custom SQL mà những values này ko show đúng những gì họ mong muốn.

SELECT
    distinct TABLE1."BATCH_ID"
FROM
    "ALLPRODUCTS" TABLE1
WHERE
    (
        TABLE1."BIIB_CODE" IN ([BIIB_CODE])
        AND TABLE1."SITE" IN ([SITE])
        AND TABLE1."PRESENTATION" IN ([PRESENTATION])
        AND TABLE1."UNIT_PROCEDURE" IN ([UNIT_PROCEDURE])
        AND TABLE1."PARAMETER_NAME" IN ([PARAMETER_NAME])
        AND UPPER(TABLE1."CPV_CLASSIFICATION") like 'YES%'
    )
ORDER BY
    TABLE1."DATE_OF_MANUFACTURE"

Cái họ mong muốn là “BATCH_ID” phải được sort theo “DATE_OF_MANUFACTURE” khi show trên dialog filter, trong khi đó dialog cho sort ASC theo column “BATCH_ID“.

Khách hàng expect:

BATCH_ID
53104
42170
...
27110
25109
22111

Đây là cái dialog show ra:

BATCH_ID
25109
27110
....
42170
53104

Bạn có nhận ra được điểm khác thường trong câu SQL trên ko? Thử suy nghĩ 1 lát xem.

Nếu bạn không nhận ra được điểm khác thường thì cũng đừng buồn, mình cũng vậy. =)))) Sau 1 ngày cố gắng reproduce bug dựa vào data sample của khách hàng, mình run thử câu SQL trên thì nó show ra lỗi sau:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Giải thích nôm na: mày không thể ORDER BY dựa trên 1 cái column mà nó không xuất hiện chỗ SELECT DISTINCT

Vì sao lại vậy?

Vì thứ tự run câu SQL sẽ là

FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

Có nghĩa là ở step SELECT DISTINCT, kết quả sẽ là 1 working table chỉ có 1 column “BATCH_ID” mà chứa các giá trị duy nhất. SELECT DISTINCT khá giống GROUP BY, khi chọn 1 hoặc nhiều cặp column(s) chứa giá trị duy nhất thì các column còn lại phải sử dụng các aggregate operator kiểu COUNT(), SUM(), MAX(), MIN()….

Column “DATE_OF_MANUFACTURE” ko được nhắc đến chỗ SELECT DISTINCT, có nghĩa nó sẽ ở dạng aggregate column, khi ORDER BY sẽ không biết lấy giá trị nào để mà thực hiện order. Ví dụ, có 3 record BATCH_ID=25109 với 3 DATE_OF_MANUFACTURE khác nhau, thế sau khi DISTINCT rút gọn đc 1 BATCH_ID thì lấy giá trị nào trong 3 giá trị của DATE_OF_MANUFACTURE để mang đi order.

Điều mình ngạc nhiên tiếp theo là chỗ điều kiện WHERE.

WHERE
    (
        TABLE1."BIIB_CODE" IN ([BIIB_CODE])
        AND TABLE1."SITE" IN ([SITE])
        AND TABLE1."PRESENTATION" IN ([PRESENTATION])
        AND TABLE1."UNIT_PROCEDURE" IN ([UNIT_PROCEDURE])
        AND TABLE1."PARAMETER_NAME" IN ([PARAMETER_NAME])
        AND UPPER(TABLE1."CPV_CLASSIFICATION") like 'YES%'
    )

Cái này mình chưa nhìn thấy bao giờ, không biết ý nghĩa của nó là gì luôn.

TABLE1."BIIB_CODE" IN ([BIIB_CODE])

Mình tìm google cũng không hiểu được cái này có ý nghĩa là gì, may mà mình vứt cả câu SQL vào gemini của google, nó explain cho mình hết mọi thứ, thật tuyệt vời. =))))

WHERE clause: 
   This part defines the conditions for filtering the data:

   TABLE1."BIIB_CODE" IN ([BIIB_CODE]): This checks if the BIIB_CODE column's value is present in a list of values stored in a variable or array named [BIIB_CODE].

Có nghĩa là có 1 cái biến hoặc mảng tên là BIIB_CODE nằm ở đâu đó, có thể là trong product của mình và nó kiểm trả những values của column TABLE1.”BIIB_CODE” có nằm trong cái mảng BIIB_CODE không.

III. Bài học rút ra

• Luôn nhớ về thứ tự run câu SQL
• SELECT DISTINCT có tác dụng giống với GROUP BY nhưng khác nhau về mục đích.
• Cần giải thích 1 cái gì đó (1 đoạn code, 1 câu SQL) thì nên tìm đến AI tool như gemini hoặc chatgpt hơn là tự search trên google.

Hết rồi, hi vọng các bạn không ngáo ngơ về SQL như mình. Nice day!