google sheets query weird behavior

Question

I've been banging my head around this issue for a couple days now, I'm using the query function in a google sheets sheet to pull a subset of data, the query is working however, if I add a value into a specific couple of fields, the query results are all wonky (technical term). The formula:

=QUERY(A3:X,"SELECT F,P WHERE D='G' AND P IS NOT NULL ORDER BY P")

It appears to be working great for most situations:

however, if I add another value in column P (the 5th column in these screenshots), I get some odd behavior:

Merle Hawkins is the first record, and when I add a value into column P for Merle, it works, but when I add a value (any value) into the next record, I get: as you may notice, Merle shows in the query results, without a value, but shouldn't. I should actually see Frank Hudson with the 5 in the query results

Another/different odd behavior I've noticed is when a value is added to the 4th record/row, it looks like this: here, the first returned row from the query is a concatenation of the first 3 records (even though none of them have values in the query result set)

the data itself is sporadic, as in not all fields are going to be populated, so my where clause is pulling only the not empty values. The values are also a mix of numbers and text, as displayed in the screenshots. Some data fields (in the same columns) will be numbers, others text - maybe I need to do a better WHERE clause to select the fields?

I'm doing several variations of the =query function and =filter functions on several sheets in this document, this is the only one that is causing issues. I've created several copies of the data sheet, I've started over with a fresh sheet, but continue to run into this issue and I'm not sure why it's happening.

TIA for any assistance

Answer 1

The query() function only supports one data type per column. A column's type is determined by the majority of the values in the column.

Your data seems to mix numbers with text strings in each column. If the majority of the values in a column are numbers, the text strings will be returned as nulls. If the majority of the values in a column are text strings, the numbers will be returned as nulls.

One workaround is to format the columns as Format > Number > Plain text. This will make query() treat everything as text.

Another workaround is to use filter() instead of query().

Answer 2

The QUERY function does not work well with columns containing mixed data types. QUERY will automatically determine the column type and ignore the exceptions. So if you have a column mainly containing numbers, QUERY will ignore the few items of text.

Answer 3

As I had suspected, because the data set is a mixed data type, I needed to dig a little deeper into the complexity of the equation. I ended up rewriting the functions using the TO_TEXT on the mixed data column to convert non-text fields to text, so that the WHERE clause could be written only having to check for not null text fields and also building out my dataset a little more succinctly.

=ArrayFormula(QUERY({A3:F,TO_TEXT(P3:P)},"SELECT Col6,Col7 WHERE Col4='G' AND Col7 IS NOT NULL ORDER BY Col7"))

Thank you to both @doubleunary & @Aresvik for pointing me in the correct direction