0

I've been trying to figure out why this gives me an error:

PREPARE test FROM 'SELECT t.blah FROM (SELECT ? AS blah) t;';

ERROR 1054 (42S22): Unknown column 't.blah' in 'field list'

also:

PREPARE test FROM 'SELECT t.* FROM (SELECT ? AS blah) t;';

ERROR 1051 (42S02): Unknown table 't'

It would be great if someone could shed some light on these errors..Thanks!!

EDIT: There is a bug report here: http://bugs.mysql.com/bug.php?id=71577

Seems like the workaround would be to do:

PREPARE test FROM 'SELECT t.blah FROM (SELECT ? AS blah UNION SELECT ? AS blah) t;';
Boyan Penev
  • 844
  • 6
  • 10
  • I don't really see the use case for such a statement, but I see the difficulty to discriminate between a placeholder for a value (allowed) and a placeholder for a column (not allowed). – VMai Jun 26 '14 at 15:48
  • In the example above the placeholders are for values, not columns. These are allowed and it is an accepted bug (see link above). I intentionally simplified the question so it illustrates the concept and makes it easy to reproduce the issue. – Boyan Penev Jun 27 '14 at 12:10
  • @BoynanPenev First, before your edit with link to the bug report I assumed you would use placeholders for column names, but after reading the bug report realized that it should be constant values. – VMai Jun 27 '14 at 12:14
  • So we agree it's a bug then? :) – Boyan Penev Jun 27 '14 at 12:16
  • I do, I commented about the difficulty to discriminate both. But at a second look, if there's no `FROM` clause, it's got to be a value and not a column. – VMai Jun 27 '14 at 12:18
  • This was answered in https://stackoverflow.com/questions/32124682/unknown-column-in-field-list-when-prepared-statements-placeholder-is-in-sub/32127450#32127450 – chushik Jan 23 '18 at 01:50

1 Answers1

0

Why not simply use :

PREPARE test FROM 'SELECT ? AS blah';

Your workaround gives a different result.

Eran
  • 387,369
  • 54
  • 702
  • 768
  • I need it for: insert into f(id,a,b,c) select id,a,b,c from (select ? as id, ? as a, ? as b, ? as c) t where not exists(select 1 from f where f.id = t.id and f.tstamp >= t.tstamp) on duplicate key update a = t.a, b = t.b, c = t.c; in a prepared statement.. The workaround gives correct results if you do a few row by row inserts..well in my case. – Boyan Penev Jun 27 '14 at 00:19
  • UNION between two identical selects: SELECT 'a' as a UNION select 'a' as a will give you the same result. Could you please elaborate on how this can produce a different result??? – Boyan Penev Jun 27 '14 at 12:13