pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL is fetching results BLANK checks but the data has no BLANKS
8+ messages / 3 participants
[nested] [flat]
* SQL is fetching results BLANK checks but the data has no BLANKS
@ 2023-07-05 21:01 M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 8+ messages in thread
From: M Sarwar @ 2023-07-05 21:01 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL is fetching results BLANK checks but the data has no BLANKS
@ 2023-07-05 21:10 M Sarwar <sarwarmd02@outlook.com>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 8+ messages in thread
From: M Sarwar @ 2023-07-05 21:10 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 , ‘ ‘.
Thanks,
Sarwar
Phasing 90 degrees temperature Fahrenheat in EST today
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL is fetching results BLANK checks but the data has no BLANKS
@ 2023-07-05 21:25 Erik Wienhold <ewie@ewie.name>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 8+ messages in thread
From: Erik Wienhold @ 2023-07-05 21:25 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL is fetching results BLANK checks but the data has no BLANKS
@ 2023-07-05 21:34 M Sarwar <sarwarmd02@outlook.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 8+ messages in thread
From: M Sarwar @ 2023-07-05 21:34 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Erik,
Table has no indexes so far.
Thanks,
Sarwar
________________________________
From: Erik Wienhold <ewie@ewie.name>
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
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL is fetching results BLANK checks but the data has no BLANKS
@ 2023-07-05 21:46 Erik Wienhold <ewie@ewie.name>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 8+ messages in thread
From: Erik Wienhold @ 2023-07-05 21:46 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL is fetching results BLANK checks but the data has no BLANKS
@ 2023-07-05 21:56 M Sarwar <sarwarmd02@outlook.com>
parent: Erik Wienhold <ewie@ewie.name>
0 siblings, 1 reply; 8+ messages in thread
From: M Sarwar @ 2023-07-05 21:56 UTC (permalink / raw)
To: Erik Wienhold <ewie@ewie.name>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 ' %'
;
* Count is 878
SELECT TEST_NUMBER
FROM BRONX.TEST_TEST_DETAILS_ALL_MCM
WHERE LEFT(TEST_DESCRIPTION, 1) = ' '
;
Count is 878
Thanks,
Sarwar
________________________________
From: Erik Wienhold <ewie@ewie.name>
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
> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL is fetching results BLANK checks but the data has no BLANKS
@ 2023-07-05 22:06 David G. Johnston <david.g.johnston@gmail.com>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2023-07-05 22:06 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Wed, Jul 5, 2023 at 2:56 PM M Sarwar <sarwarmd02@outlook.com> wrote:
> 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 ' %'
> ;
>
> -
>
>
How about:
SELECT test_number, '[' || test_description || ']'
FROM bronx.test_test_details_all_mcm
WHERE LEFT(TEST_DESCRIPTION, 1) = ' '
LIMIT 5;
?
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SQL is fetching results BLANK checks but the data has no BLANKS
@ 2023-07-05 22:36 M Sarwar <sarwarmd02@outlook.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: M Sarwar @ 2023-07-05 22:36 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Erik Wienhold <ewie@ewie.name>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
David,
SELECT test_number
FROM bronx.test_test_details_all_mcm
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.
Thank you,
Sarwar
________________________________
From: David G. Johnston <david.g.johnston@gmail.com>
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
On Wed, Jul 5, 2023 at 2:56 PM M Sarwar <sarwarmd02@outlook.com<mailto:sarwarmd02@outlook.com>> wrote:
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 ' %'
;
*
How about:
SELECT test_number, '[' || test_description || ']'
FROM bronx.test_test_details_all_mcm
WHERE LEFT(TEST_DESCRIPTION, 1) = ' '
LIMIT 5;
?
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-07-05 22:36 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-05 21:01 SQL is fetching results BLANK checks but the data has no BLANKS M Sarwar <sarwarmd02@outlook.com>
2023-07-05 21:10 ` M Sarwar <sarwarmd02@outlook.com>
2023-07-05 21:25 ` Erik Wienhold <ewie@ewie.name>
2023-07-05 21:34 ` M Sarwar <sarwarmd02@outlook.com>
2023-07-05 21:46 ` Erik Wienhold <ewie@ewie.name>
2023-07-05 21:56 ` M Sarwar <sarwarmd02@outlook.com>
2023-07-05 22:06 ` David G. Johnston <david.g.johnston@gmail.com>
2023-07-05 22:36 ` M Sarwar <sarwarmd02@outlook.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox