Why am I getting different results on a subquery and query?-CodePudding

I have the following table 'client_ticket_thread':

create table client_ticket_thread
(
    id                bigint unsigned auto_increment
        primary key,
    client            bigint unsigned                       not null,
    category          varchar(255) collate utf8mb4_bin      not null,
    subject           varchar(512) collate utf8mb4_bin      not null,
    message_count     int unsigned default 0                not null,
    client_read       bit          default b'1'             not null,
    closed            bit          default b'0'             not null,
    time_created      int unsigned default unix_timestamp() not null,
    time_last_message int unsigned default 0                not null,
    time_closed       int unsigned default 0                not null,
    constraint client_ticket_thread_client_credential_id_fk
        foreign key (client) references client_credential (id)
);

One of the entries in this table is as follows:

7,1,General Inquiry,Thread X,1,true,false,1641790706,1641790707,0

I am trying to write a query for a specific purpose and I will need external data from other tables as a result, so in a subquery, I am trying to retrieve the closed field with the following statement:

select (select ctt.closed from client_ticket_thread ctt where ctt.id=7 and ctt.client=1 limit 1) as thread_active;

But the result returns true.

However, the following statement returns false, as expected.

select closed as thread_active from client_ticket_thread where id=7 and client=1 limit 1;

When I try retrieving a different field, e.g. subject, I get the correct results. What might be causing this error?

Note: I am using MariaDB 10.7.1.

CodePudding user response：

The problem is you're using a bit field. Since this appears to be a flag, I recommend you change it to boolean. When using a boolean it returns 0/false as expected in each case.

Here is a dbfiddle.uk showing what I mean.

create table client_ticket_thread
(
    id                bigint unsigned auto_increment
        primary key,
    client            bigint unsigned                       not null,
    category          varchar(255) collate utf8mb4_bin      not null,
    subject           varchar(512) collate utf8mb4_bin      not null,
    message_count     int unsigned default 0                not null,
    client_read       bit          default b'1'             not null,
    closed            bit          default b'0'             not null,
    client_read_bool boolean default true not null,
    closed_bool boolean default false not null,
    time_created      int unsigned default unix_timestamp() not null,
    time_last_message int unsigned default 0                not null,
    time_closed       int unsigned default 0                not null
);

insert into client_ticket_thread
values
(7,1,'General Inquiry','Thread X',1,1,0,true,false,1641790706,1641790707,0)

select (
select closed_bool
from client_ticket_thread ctt 
where ctt.id=7 
and ctt.client=1 
) as thread_active;

What I was finding was that the bit field was in fact returning a value of 48 from the sub query, which I suppose was translated to 'true' in your case, and 0 without the sub query. I don't know why it was returning 48.

I read through the documentation for the bit data type here.

Bits are returned as binary, so to display them, either add 0, or use a function such as HEX, OCT or BIN to convert them.

So you can either use boolean in the first place, or SELECT (SELECT closed 0 FROM ...