Thread: SQL is fetching results BLANK checks but the data has no BLANKS
SELECT TEST_NUMBER
FROM BRONX.TEST_TEST_DETAILS_ALL_MCM
WHERE LEFT(TEST_DESCRIPTION, 1) = ' '
;
The above SQL is fetching lot of data but when I take closer look at the data in the table TEST_TEST_DETAILS_ALL_MCM, I see no prefixed BLANKS in the column TEST_DESCRIPTION.
test_description character varying(50) COLLATE pg_catalog."default", -- TEST_DESCRIPTION, type
What could be the reason here for fetching the data when I do not have the data which has prefixes of BLANK , ‘ ‘.
Thanks,
Sarwar
Phasing 90 degrees temperature Fahrenheat in EST today
Sent: Wednesday, July 5, 2023 5:01 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: SQL is fetching results BLANK checks but the data has no BLANKS
SELECT TEST_NUMBER
FROM BRONX.TEST_TEST_DETAILS_ALL_MCM
WHERE LEFT(TEST_DESCRIPTION, 1) = ' '
;
The above SQL is fetching lot of data but when I take closer look at the data in the table TEST_TEST_DETAILS_ALL_MCM, I see no prefixed BLANKS in the column TEST_DESCRIPTION.
test_description character varying(50) COLLATE pg_catalog."default", -- TEST_DESCRIPTION, type
What could be the reason here for fetching the data when I do not have the data which has prefixes of BLANK , ‘ ‘.
Thanks,
Sarwar
Phasing 90 degrees temperature Fahrenheat in EST today
> On 05/07/2023 23:10 CEST M Sarwar <sarwarmd02@outlook.com> wrote: > > Data in the database is uploaded using python. > > I have tried to test the same results by creating a sample table where I > inserted some sample data. In my sample test, I do not see any discrepancies. > > ------------------------------ > From: M Sarwar <sarwarmd02@outlook.com> > Sent: Wednesday, July 5, 2023 5:01 PM > To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org> > Subject: SQL is fetching results BLANK checks but the data has no BLANKS > > SELECT TEST_NUMBER > FROM BRONX.TEST_TEST_DETAILS_ALL_MCM > WHERE LEFT(TEST_DESCRIPTION, 1) = ' ' > ; > The above SQL is fetching lot of data but when I take closer look at the data > in the table TEST_TEST_DETAILS_ALL_MCM, I see no prefixed BLANKS in the column > TEST_DESCRIPTION. > > test_description character varying(50) COLLATE pg_catalog."default", -- TEST_DESCRIPTION, type > > What could be the reason here for fetching the data when I do not have the > data which has prefixes of BLANK , ‘ ‘. Is there a functional index on left(test_description, 1) ? My guess is that the index is corrupted. This can be fixed with REINDEX. -- Erik
Sent: Wednesday, July 5, 2023 5:25 PM
To: M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: SQL is fetching results BLANK checks but the data has no BLANKS
>
> Data in the database is uploaded using python.
>
> I have tried to test the same results by creating a sample table where I
> inserted some sample data. In my sample test, I do not see any discrepancies.
>
> ------------------------------
> From: M Sarwar <sarwarmd02@outlook.com>
> Sent: Wednesday, July 5, 2023 5:01 PM
> To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> Subject: SQL is fetching results BLANK checks but the data has no BLANKS
>
> SELECT TEST_NUMBER
> FROM BRONX.TEST_TEST_DETAILS_ALL_MCM
> WHERE LEFT(TEST_DESCRIPTION, 1) = ' '
> ;
> The above SQL is fetching lot of data but when I take closer look at the data
> in the table TEST_TEST_DETAILS_ALL_MCM, I see no prefixed BLANKS in the column
> TEST_DESCRIPTION.
>
> test_description character varying(50) COLLATE pg_catalog."default", -- TEST_DESCRIPTION, type
>
> What could be the reason here for fetching the data when I do not have the
> data which has prefixes of BLANK , ‘ ‘.
Is there a functional index on left(test_description, 1) ? My guess is that
the index is corrupted. This can be fixed with REINDEX.
--
Erik
> On 05/07/2023 23:34 CEST M Sarwar <sarwarmd02@outlook.com> wrote: > > Table has no indexes so far. Do you get the same results when using LIKE instead? SELECT test_number FROM bronx.test_test_details_all_mcm WHERE test_description LIKE ' %' -- Erik
FROM bronx.test_test_details_all_mcm
WHERE test_description LIKE ' %'
;
- Count is 878
Sent: Wednesday, July 5, 2023 5:46 PM
To: M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: SQL is fetching results BLANK checks but the data has no BLANKS
>
> Table has no indexes so far.
Do you get the same results when using LIKE instead?
SELECT test_number
FROM bronx.test_test_details_all_mcm
WHERE test_description LIKE ' %'
--
Erik
Yes, I am getting the same results for the following SQLs.SELECT test_number
FROM bronx.test_test_details_all_mcm
WHERE test_description LIKE ' %'
;
WHERE test_description LIKE ' %'
"TEST1P1 " "[ 1.8V Continuity (274 pins) ]"
"TEST1P2 " "[ DDR3 Continuity (287 pins) ]"
"TEST1P3 " "[ PCIe Continuity (82 pins) ]"
"TEST2P1 " "[ CLK_REF_A_IN Differential ]"
"TEST2P2 " "[ DDR3_REF_CLOCK Differential ]" They do have a space at the beginning. My bad.
Sent: Wednesday, July 5, 2023 6:06 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: Erik Wienhold <ewie@ewie.name>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: SQL is fetching results BLANK checks but the data has no BLANKS
Yes, I am getting the same results for the following SQLs.SELECT test_number
FROM bronx.test_test_details_all_mcm
WHERE test_description LIKE ' %'
;