After loading the pokedex into PostgreSQL 9.1 and also 9.5, searching for Pokemon by name doesn't work.
My poking around suggests to use .label on one of the subqueries in order to keep pgsql happy, but it's not really clear to me where in the code this query is being generated:
(SELECT pokemon_species_names.name AS name
FROM pokemon_species_names, pokemon_species
WHERE pokemon_species_names.pokemon_species_id = pokemon_species.id AND pokemon_species_names.local_language_id = %(_default_language_id)s)
The same query works fine on the sqlite3 engine.
Full traceback and steps to reproduce:
$ sudo -u postgres psql
psql> create database pokedex owner myUsername;
$ pokedex setup -e postgresql:///pokedex
# Completes successfully
$ pokedex search --name=eevee -e postgresql:///pokedex
Connected to database postgresql:///pokedex (from command line)
Traceback (most recent call last):
File "/usr/local/bin/pokedex", line 9, in <module>
load_entry_point('Pokedex==0.1', 'console_scripts', 'pokedex')()
File "/usr/local/lib/python2.7/dist-packages/Pokedex-0.1-py2.7.egg/pokedex/main.py", line 27, in setuptools_entry
main(*sys.argv)
File "/usr/local/lib/python2.7/dist-packages/Pokedex-0.1-py2.7.egg/pokedex/main.py", line 23, in main
args.func(parser, args)
File "/usr/local/lib/python2.7/dist-packages/Pokedex-0.1-py2.7.egg/pokedex/cli/search.py", line 20, in command_search
results = search(session, **vars(args))
File "/usr/local/lib/python2.7/dist-packages/Pokedex-0.1-py2.7.egg/pokedex/search.py", line 65, in search
return query.all()
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.0.12-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2588, in all
return list(self)
File "/usr/local/lib/python2.7/dist-packages/Pokedex-0.1-py2.7.egg/pokedex/db/multilang.py", line 216, in __iter__
return super(MultilangQuery, self).__iter__()
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.0.12-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2736, in __iter__
return self._execute_and_instances(context)
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.0.12-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2751, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.0.12-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 914, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.0.12-py2.7-linux-x86_64.egg/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.0.12-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.0.12-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1146, in _execute_context
context)
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.0.12-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
exc_info
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.0.12-py2.7-linux-x86_64.egg/sqlalchemy/util/compat.py", line 200, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.0.12-py2.7-linux-x86_64.egg/sqlalchemy/engine/base.py", line 1139, in _execute_context
context)
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-1.0.12-py2.7-linux-x86_64.egg/sqlalchemy/engine/default.py", line 450, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) subquery in FROM must have an alias
LINE 4: FROM pokemon_species, (SELECT pokemon_species_names.name AS ...
^
HINT: For example, FROM (SELECT ...) [AS] foo.
[SQL: 'SELECT pokemon.id AS pokemon_id, pokemon.identifier AS pokemon_identifier, pokemon.species_id AS pokemon_species_id, pokemon.height AS pokemon_height, pokemon.weight AS pokemon_weight, pokemon.base_experience AS pokemon_base_experience, pokemon."order" AS pokemon_order, pokemon.is_default AS pokemon_is_default, pokemon_forms_1.id AS pokemon_forms_1_id, pokemon_forms_1.identifier AS pokemon_forms_1_identifier, pokemon_forms_1.form_identifier AS pokemon_forms_1_form_identifier, pokemon_forms_1.pokemon_id AS pokemon_forms_1_pokemon_id, pokemon_forms_1.introduced_in_version_group_id AS pokemon_forms_1_introduced_in_version_group_id, pokemon_forms_1.is_default AS pokemon_forms_1_is_default, pokemon_forms_1.is_battle_only AS pokemon_forms_1_is_battle_only, pokemon_forms_1.is_mega AS pokemon_forms_1_is_mega, pokemon_forms_1.form_order AS pokemon_forms_1_form_order, pokemon_forms_1."order" AS pokemon_forms_1_order, pokemon_form_names_1.pokemon_form_id AS pokemon_form_names_1_pokemon_form_id, pokemon_form_names_1.local_language_id AS pokemon_form_names_1_local_language_id, pokemon_form_names_1.form_name AS pokemon_form_names_1_form_name, pokemon_form_names_1.pokemon_name AS pokemon_form_names_1_pokemon_name, pokemon_forms_2.id AS pokemon_forms_2_id, pokemon_forms_2.identifier AS pokemon_forms_2_identifier, pokemon_forms_2.form_identifier AS pokemon_forms_2_form_identifier, pokemon_forms_2.pokemon_id AS pokemon_forms_2_pokemon_id, pokemon_forms_2.introduced_in_version_group_id AS pokemon_forms_2_introduced_in_version_group_id, pokemon_forms_2.is_default AS pokemon_forms_2_is_default, pokemon_forms_2.is_battle_only AS pokemon_forms_2_is_battle_only, pokemon_forms_2.is_mega AS pokemon_forms_2_is_mega, pokemon_forms_2.form_order AS pokemon_forms_2_form_order, pokemon_forms_2."order" AS pokemon_forms_2_order, pokemon_form_names_2.pokemon_form_id AS pokemon_form_names_2_pokemon_form_id, pokemon_form_names_2.local_language_id AS pokemon_form_names_2_local_language_id, pokemon_form_names_2.form_name AS pokemon_form_names_2_form_name, pokemon_form_names_2.pokemon_name AS pokemon_form_names_2_pokemon_name, pokemon_species_1.id AS pokemon_species_1_id, pokemon_species_1.identifier AS pokemon_species_1_identifier, pokemon_species_1.generation_id AS pokemon_species_1_generation_id, pokemon_species_1.evolves_from_species_id AS pokemon_species_1_evolves_from_species_id, pokemon_species_1.evolution_chain_id AS pokemon_species_1_evolution_chain_id, pokemon_species_1.color_id AS pokemon_species_1_color_id, pokemon_species_1.shape_id AS pokemon_species_1_shape_id, pokemon_species_1.habitat_id AS pokemon_species_1_habitat_id, pokemon_species_1.gender_rate AS pokemon_species_1_gender_rate, pokemon_species_1.capture_rate AS pokemon_species_1_capture_rate, pokemon_species_1.base_happiness AS pokemon_species_1_base_happiness, pokemon_species_1.is_baby AS pokemon_species_1_is_baby, pokemon_species_1.hatch_counter AS pokemon_species_1_hatch_counter, pokemon_species_1.has_gender_differences AS pokemon_species_1_has_gender_differences, pokemon_species_1.growth_rate_id AS pokemon_species_1_growth_rate_id, pokemon_species_1.forms_switchable AS pokemon_species_1_forms_switchable, pokemon_species_1."order" AS pokemon_species_1_order, pokemon_species_1.conquest_order AS pokemon_species_1_conquest_order, pokemon_species_names_1.pokemon_species_id AS pokemon_species_names_1_pokemon_species_id, pokemon_species_names_1.local_language_id AS pokemon_species_names_1_local_language_id, pokemon_species_names_1.name AS pokemon_species_names_1_name, pokemon_species_names_1.genus AS pokemon_species_names_1_genus, types_1.id AS types_1_id, types_1.identifier AS types_1_identifier, types_1.generation_id AS types_1_generation_id, types_1.damage_class_id AS types_1_damage_class_id, type_names_1.type_id AS type_names_1_type_id, type_names_1.local_language_id AS type_names_1_local_language_id, type_names_1.name AS type_names_1_name \nFROM pokemon LEFT OUTER JOIN pokemon_forms AS pokemon_forms_1 ON pokemon.id = pokemon_forms_1.pokemon_id LEFT OUTER JOIN pokemon_form_names AS pokemon_form_names_1 ON pokemon_form_names_1.pokemon_form_id = pokemon_forms_1.id AND pokemon_form_names_1.local_language_id = %(_default_language_id)s LEFT OUTER JOIN pokemon_forms AS pokemon_forms_2 ON pokemon.id = pokemon_forms_2.pokemon_id AND pokemon_forms_2.is_default = true LEFT OUTER JOIN pokemon_form_names AS pokemon_form_names_2 ON pokemon_form_names_2.pokemon_form_id = pokemon_forms_2.id AND pokemon_form_names_2.local_language_id = %(_default_language_id)s JOIN pokemon_species AS pokemon_species_1 ON pokemon_species_1.id = pokemon.species_id LEFT OUTER JOIN pokemon_species_names AS pokemon_species_names_1 ON pokemon_species_names_1.pokemon_species_id = pokemon_species_1.id AND pokemon_species_names_1.local_language_id = %(_default_language_id)s JOIN pokemon_types AS pokemon_types_1 ON pokemon.id = pokemon_types_1.pokemon_id JOIN types AS types_1 ON types_1.id = pokemon_types_1.type_id LEFT OUTER JOIN type_names AS type_names_1 ON type_names_1.type_id = types_1.id AND type_names_1.local_language_id = %(_default_language_id)s \nWHERE EXISTS (SELECT 1 \nFROM pokemon_species, (SELECT pokemon_species_names.name AS name \nFROM pokemon_species_names, pokemon_species \nWHERE pokemon_species_names.pokemon_species_id = pokemon_species.id AND pokemon_species_names.local_language_id = %(_default_language_id)s) \nWHERE pokemon_species.id = pokemon.species_id AND lower((SELECT pokemon_species_names.name \nFROM pokemon_species_names \nWHERE pokemon_species_names.pokemon_species_id = pokemon_species.id AND pokemon_species_names.local_language_id = %(_default_language_id)s)) = %(lower_1)s) ORDER BY pokemon_forms_1."order" ASC, pokemon_forms_1.form_identifier ASC, pokemon_types_1.slot ASC'] [parameters: {'lower_1': 'eevee', '_default_language_id': 9}]
magical
commented
4 years ago
After loading the pokedex into PostgreSQL 9.1 and also 9.5, searching for Pokemon by name doesn't work.
My poking around suggests to use
.labelon one of the subqueries in order to keep pgsql happy, but it's not really clear to me where in the code this query is being generated:The same query works fine on the sqlite3 engine.
Full traceback and steps to reproduce: