Hello, In v1.4.188 I was trying to do this query: SELECT * FROM master_data WHERE my_code IN ( SELECT my_code FROM stats_table WHERE <some_condition> GROUP BY my_code ORDER BY COUNT(*) DESC LIMIT 10 )
My tables has the following structure: --- Master data contains ~100k records and around 8 data columns indexed only by the primary key CREATE TABLE master_data ( my_code varchar(255) PRIMARY KEY NOT NULL, --- primary key my_column1 varchar(255), --- some data my_column2 varchar(255) ); --- Stats table contain keywords that describe master data, along with some statistics, --- Contains around ~400k records --- A pair of (my_code, my_keyword) is always unique CREATE TABLE stats_table ( id bigint PRIMARY KEY NOT NULL, --- Generated surrogate key my_code varchar(255), --- foreign key (without FK constraint) my_keyword varchar(255), --- indexed column for searching my_stats varchar(255) --- data payload ); When I perform this query: SELECT * FROM master_data WHERE my_code IN ( SELECT my_code FROM stats_table WHERE <some_condition> GROUP BY my_code ORDER BY COUNT(*) DESC LIMIT 10 ) I got this error: Error: General error: "java.lang.NullPointerException"; SQL statement: SELECT * FROM MASTER_DATA WHERE HOTEL_CODE IN ( ... ) [50000-188] SQLState: HY000 ErrorCode: 50000 I tried various conditions for "<some_condition>" in the subquery, whether I get the NPE seems to be dependent on the number of records returned (before GROUP BY and LIMIT) e.g.: When the subquery returns ~40k before GROUP BY and LIMIT, I get a NPE, and when it returns ~1k, the query works. Note that the on its on subquery executes properly for all conditions I tried. I also tried different ORDER BYs: These work: SELECT * FROM master_data WHERE my_code IN ( SELECT my_code FROM stats_table WHERE <some_condition_with_1k_results> --- A smaller result set to begin with works GROUP BY my_code ORDER BY COUNT(*) DESC LIMIT 10 ) SELECT * FROM master_data WHERE my_code IN ( SELECT my_code FROM stats_table WHERE <some_condition_with_40k_results> GROUP BY my_code --- ORDER BY COUNT(*) DESC --- Removing the order by count(*) clause LIMIT 10 --- As tested, the limit clause doesn't matter too ) SELECT * FROM master_data WHERE my_code IN ( SELECT my_code FROM stats_table WHERE <some_condition_with_40k_results> GROUP BY my_code ORDER BY my_code DESC --- ordering by the group by field itself LIMIT 10 ) SELECT * FROM master_data WHERE my_code IN ( SELECT my_code FROM stats_table WHERE <some_condition_with_40k_results> --- The simplest form also work ) Is this a bug, a known issue or there's something wrong with the nature of my query? Thanks in advance! -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
